Bazen mevcut db’deki sql plan baseline’ların sık değiştirilebilmesi veya eşlenik bir db ortamına taşınması ihtiyaçları doğabilir. Bu durumda aşağıdaki gibi sql planlara ait baseline’ların yedeğini alıp aktarabiliriz ;

select * from dba_sql_plan_baselines;

create table d_bck.dba_sql_plan_baselines_160217 as select * from dba_sql_plan_baselines;

select * from d_bck.dba_sql_plan_baselines_160217;
 EXEC dbms_spm.create_stgtab_baseline (table_name => 'MY_STG_TAB_160217', table_owner =>'SYSTEM' , tablespace_name => 'USERS');

select * from SYSTEM.MY_STG_TAB_160217;
 DECLARE
  l_plans_packed PLS_INTEGER;
 BEGIN
  l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
  table_name => 'MY_STG_TAB_160217',
  table_owner => 'SYSTEM');

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

select * from SYSTEM.MY_STG_TAB_160217;
 expdp \'/ as sysdba\' DIRECTORY=EXPORT_BACKUP_DIR tables= SYSTEM.MY_STG_TAB_160217 DUMPFILE=MY_STG_TAB_160217.dmp logfile=MY_STG_TAB_160217.log
 testdb01@oracle </backup/oracle/export> expdp \'/ as sysdba\' DIRECTORY=EXPORT_BACKUP_DIR tables= SYSTEM.MY_STG_TAB_160217 DUMPFILE=MY_STG_TAB_160217.dmp logfile=MY_STG_TAB_160217.log
 Export: Release 11.2.0.3.0 - Production on Thu Feb 16 15:50:38 2017
 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
 With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing options
 Starting "SYS"."SYS_EXPORT_TABLE_09": "/******** AS SYSDBA" DIRECTORY=EXPORT_BACKUP_DIR tables= DUMPFILE=MY_STG_TAB_160217.dmp logfile=MY_STG_TAB_160217.log 
 Estimate in progress using BLOCKS method...
 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 Total estimation using BLOCKS method: 16 MB
 Processing object type TABLE_EXPORT/TABLE/TABLE
 Processing object type TABLE_EXPORT/TABLE/PRE_TABLE_ACTION
 . . exported "SYSTEM"."MY_STG_TAB_160217" 9.272 MB 977 rows
 Master table "SYS"."SYS_EXPORT_TABLE_09" successfully loaded/unloaded
 ******************************************************************************
 Dump file set for SYS.SYS_EXPORT_TABLE_09 is:
  /backup/oracle/export/MY_STG_TAB_160217.dmp
 Job "SYS"."SYS_EXPORT_TABLE_09" successfully completed at 15:51:20

testdb01@oracle </backup/oracle/export> 
 testdb01@oracle </backup/oracle/export> ls -la
 total 19240
 drwxr-xr-x 2 oracle dba 256 Feb 16 15:50 .
 drwxrwxrwx 7 oracle dba 4096 Feb 01 11:49 ..
 -rw-r----- 1 oracle dba 9838592 Feb 16 15:51 MY_STG_TAB_160217.dmp
 -rw-r--r-- 1 oracle dba 1158 Feb 16 15:51 MY_STG_TAB_160217.log
 --****** sadece ilgili baseline'ı aktarmak için **********
 SELECT sql_handle,
  plan_name,
  enabled,
  accepted,
  fixed
  FROM dba_sql_plan_baselines
  WHERE LOWER (sql_text) LIKE '%DENEME_KOLON%';

select * from al_directories

EXEC dbms_spm.pack_stgtab_baseline ( table_name => 'MY_STG_TAB', table_owner =>'SYSTEM', sql_handle => 'SYS_SQL_818c1879b000a439',plan_name => 'SYS_SQL_PLAN_b000a439c0e983c6', FIXED => 'YES')
 --*********************************************************

impdp \"/ as sysdba\" DIRECTORY=D_DIR tables= SYSTEM.MY_STG_TAB_160217 DUMPFILE=MY_STG_TAB_160217.dmp logfile=MY_STG_TAB_160217.log
 DECLARE
  l_plans_unpacked PLS_INTEGER;
 BEGIN
  l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
  table_name => 'MY_STG_TAB_160217',
  table_owner => 'SYSTEM');

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

 

Referans: https://oracle-base.com/articles/11g/sql-plan-management-11gr1