Follow below steps for migrating sql plan baselines from one database to another.
1. Get the sql_plan and sql_handle[SOURCE]
SQL> SELECT sql_handle,plan_name,enabled,accepted,fixed from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ENA ACC FIX ---------------------------------- -------------------------------------- --- --- --- SQL_63267a8ff7127534 SQL_PLAN_669mujzvj4x9n0d025331 YES YES NO
2. Create a staging table[SOURCE]
BEGIN DBMS_SPM.CREATE_STGTAB_BASELINE( table_name => 'spm_stageing_tab', table_owner => 'DBARAJ', tablespace_name => 'USERS'); END; /
3. Pack the sql_plan to the staging table[SOURCE]
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 => 'DBARAJ',
sql_handle => 'SQL_63267a8ff7127534',
plan_name => 'SQL_PLAN_669mujzvj4x9n0d025331');
DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/
4. Take export of staging table[SOURCE]
expdp dumpfile=spm_stageing_tab.dmp logfile=spm_stageing_tab.log directory=LOYMIG tables=dbaraj.spm_stageing_tab Export: Release 12.1.0.2.0 - Production on Sat May 28 00:39:58 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA dumpfile=spm_stageing_tab.dmp logfile=spm_stageing_tab.log directory=LOYMIG tables=dbaraj.spm_stageing_tab Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 4.625 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER . . exported "DBARAJ"."SPM_STAGEING_TAB" 1.233 MB 86 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /loytemp-pool/LOYMIGRATION/spm_stageing_tab.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat May 28 00:42:14 2016 elapsed 0 00:02:04
5. Copy the dump file and import in the target database.[TARGET]
impdp dumpfile=spm_stageing_tab.dmp logfile=imp_spm_stageing_tab.log directory=ULA2 Import: Release 12.1.0.2.0 - Production on Sat May 28 00:51:28 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA dumpfile=spm_stageing_tab.dmp logfile=imp_spm_stageing_tab.log directory=ULA2 Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "DBARAJ"."SPM_STAGEING_TAB" 1.233 MB 86 rows Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Sat May 28 00:51:58 2016 elapsed 0 00:00:26
6. Unpack the staging table:[TARGET]
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 => 'DBARAJ');
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/
7. Check whether sql plan has been loaded or not[TARGET]
SQL> SELECT sql_handle,plan_name,enabled,accepted,fixed from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ENA ACC FIX ---------------------------------- -------------------------------------- --- --- --- SQL_63267a8ff7127534 SQL_PLAN_669mujzvj4x9n0d025331 YES YES NO
SIMILAR ARTICLE
Migrate sql profiles from one database to another:
Run sql tuning advisor for a sql_id
Change The Execution Plan Without Changing The Sql Query
