Copying SQL Plan Baselines between Databases

Copying SQL Plan Baselines between databases

The DBMS_SPM package provides functionality for transferring SQL plan baselines between databases. First, a staging table must be created in the source database using the CREATE_STGTAB_BASELINE procedure.

BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
    table_name      => 'spm_stageing_tab',
    table_owner     => 'GPS_TEST',
    tablespace_name => 'USERS');
END;
/

The PACK_STGTAB_BASELINE function exports the SQL plan baselines to the staging table. There are several parameters allowing you to limit amount and type of data you export. The following example exports all SQL plan baselines.

SET SERVEROUTPUT ON
DECLARE
  l_plans_packed  PLS_INTEGER;
BEGIN
  l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
    table_name      => 'spm_stageing_tab',
    table_owner     => 'GPS_TEST');

  DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/
Plans Packed: 131

PL/SQL procedure successfully completed.

SQL>

The staging table is then transferred to the destination database using data pump or the original export/import utilities. Once in the destination database, the SQL plan baselines are imported into the dictionary using the UNPACK_STGTAB_BASELINE function. Once again, there are several parameters allowing you to limit amount and type of data you import. The following example imports all SQL plan baselines owned by the user "GPS_TEST".

SET SERVEROUTPUT ON
DECLARE
  l_plans_unpacked  PLS_INTEGER;
BEGIN
  l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
    table_name      => 'spm_stageing_tab',
    table_owner     => 'GPS_TEST',
    creator         => 'GPS_TEST');

  DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/
Plans Unpacked: 11

PL/SQL procedure successfully completed.

SQL>

Shrink datafiles in all tablespace below HWM

Shrink Data files in all tablespaces

Spool /tmp/shrink_tablespaces.sql

select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil(ROUND((((nvl(hwm, 1) * &&BlockSize) / 1024 / 1024) + 50), -2)) || 'm' cmd
from dba_data_files a,
(select file_id, max(block_id + blocks - 1) hwm
from dba_extents
group by file_id) b
where a.file_id = b.file_id(+)
and ceil(blocks * &BlockSize / 1024 / 1024) -
ceil((nvl(hwm, 1) * &&BlockSize) / 1024 / 1024) > 0
and a.tablespace_name not in
('SYSTEM', 'TEMP', 'UNDO_SDATA01', 'UNDO_SDATA02')
/

spool off;

@/tmp/shrink_tablespaces.sql


Schema Size

select 'TABLE' "SEGMENT_TYPE",round(sum(bytes)/1024/1024/1024) "SIZE in GB" from dba_segments where owner='&OWNER'
and segment_type in('TABLE','TABLE PARTITION','LOBSEGMENT') group by 'TABLE'
UNION
select 'INDEX' "SEGMENT_TYPE",round(sum(bytes)/1024/1024/1024) "SIZE in GB" from dba_segments where owner='&OWNER'
and segment_type in('INDEX','INDEX PARTITION','LOBINDEX') group by 'INDEX'
/