SQL Plan Baselines: Migration from one Instance to Another.

1. Create staging table. it needs to be created only once. Script to create staging table is
   
   DECLARE i number; BEGIN DBMS_SPM.create_stgtab_baseline ( table_name => 'XXFND_SQL_PLAN_BASELINES' , table_owner => 'XXFND' ) ; END;
   
   
2. Transfer baselines to staging table. Script to transfer sql plan baselines to staging table is
   
   DECLARE l_ret_value NMBER; BEGIN l_ret_value := DBMS_SPM.pack_stgtab_baseline ( table_name => 'XXFND_SQL_PLAN_BASELINES' , table_owner => 'XXFND' , enabled => 'YES' , accepted => 'YES' ) ; END;
   
   
3. Export staging table


4. Copy file to desired instance and then import it


5. Load sql plan baselines from staging table. Script to load sql plan baselines is
   
   declare i number ; begin i := dbms_spm.unpack_stgtab_baseline ( table_name => 'xxfnd_sql_plan_baselines' , table_owner => 'XXFND' ) ; dbms_output.put_line('Plans Loaded : ' || i); end;
   
   


6. Record from Staging table can be deleted so that only new sql plan baselines can be migrated or you may unpack only selected sql plan baselines using function dbms_spm.unpack_stgtab_baseline.