Transfering Sql Plan Baselines to another database

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;