Transfer SQL Tuning Sets between Databases

1. Create a staging table to put the SQL tuning sets into. Then put the SQL Tuning Sets into the staging table.
See Appendix A: Create SQL Tuning Set table and put SQL Tuning Sets into table


2. Ensure that you have a directory in Oracle to use to export the table using datapump
SQL> create directory DATA_PUMP_DIR as ‘C:\oracle\product\10.2.0\admin\orcl\dpdump\’ ;


3. Export the SQL Tuning Set Staging Table to a file as to analyze the SQL tuning set will need to be done in a 11g database.

expdp /@orcl directory=DATA_PUMP_DIR dumpfile=sqlset_stage_tab_exp.dmp logfile=sqlnet_stage_tab_exp.log tables=mrmessin.sqlnet_stage_tab

Export: Release 10.2.0.4.0 - Production on Monday, 04 August, 2008 19:40:41

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "MRMESSIN"."SYS_EXPORT_TABLE_01": /********@orcl directory=DATA_PUMP_DIR dumpfi
xp.dmp logfile=sqlnet_stage_tab_exp.log tables=MRMESSIN.SQLSET_STAGE_TAB
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 640 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "MRMESSIN"."SQLSET_STAGE_TAB" 49.13 KB 44 rows
. . exported "MRMESSIN"."SQLSET_STAGE_TAB_CPLANS" 73.24 KB 175 rows
. . exported "MRMESSIN"."SQLSET_STAGE_TAB_CBINDS" 9.492 KB 0 rows
Master table "MRMESSIN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MRMESSIN.SYS_EXPORT_TABLE_01 is:
C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\DPDUMP\SQLSET_STAGE_TAB_EXP.DMP
Job "MRMESSIN"."SYS_EXPORT_TABLE_01" successfully completed at 19:41:16


4. create a directory for the 11g database we are going to import the sqlset staging table into.
create directory DATA_PUMP_DIR as ‘c:\oracle\admin\orcl11g\dpdump\’ ;


5. Copy the file to the 10g database DATA_PUMP_DIR location to the DATA_PUMP_DIR location for the 11g database.
copy C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\DPDUMP\SQLSET_STAGE_TAB_EXP.DMP c:\oracle\admin\orcl11g\dpdump\



6. Import the SQL tuning set staging table into the 11g database.

impdp /@orcl11g full=y dumpfile=sqlset_stage_tab_exp.dmp directory=DATA_PUMP_DIR logfile=sqlset_stage_tab_imp.log

Import: Release 11.1.0.6.0 - Production on Monday, 04 August, 2008 20:04:38

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MRMESSIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "MRMESSIN"."SYS_IMPORT_FULL_01": /********@orcl11g full=y dumpfile=sqlset_stage_ta
y=DATA_PUMP_DIR logfile=sqlset_stage_tab_imp.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MRMESSIN"."SQLSET_STAGE_TAB" 49.13 KB 44 rows
. . imported "MRMESSIN"."SQLSET_STAGE_TAB_CPLANS" 73.24 KB 175 rows
. . imported "MRMESSIN"."SQLSET_STAGE_TAB_CBINDS" 9.492 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Job "MRMESSIN"."SYS_IMPORT_FULL_01" successfully completed at 20:06:22


7. Move the SQL Tuning Sets in the imported Staging table back into database SQL Tuning Sets
See APPENDIX B: Put SQL Tuning Sets in Imported Staging Table Back into Database SQL Tuning Sets


8. Run Analysis on the SQL Tuning Sets


Appendix A: Create SQL Tuning Set table and Put SQL Tuning Sets into Table

---------------------------------------------------------------------------
-- Script: spa_sts_to_table.sql
-- Author; Michael R. Messina, Senior Consultant TUSC, A Rolta Company
--
-- create a staging table to put the SQL tuning sets into and then
-- put the SQL tuning sets into the stage table to be moved to another
-- database.
---------------------------------------------------------------------------
--
set serveroutput on size 1000000

BEGIN
DBMS_OUTPUT.PUT_LINE ('Creating the staging table to put the sql tuning set into.') ;
-- create the staging table to put the sql tuning set into
DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET_STAGE_TAB',
schema_name => 'MRMESSIN',
tablespace_name => 'USERS');

DBMS_OUTPUT.PUT_LINE ('Putting the first SQL tuning sets into the staging table.') ;
-- put the first SQL tuning set into the staging table
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'STS_SPA_1',
sqlset_owner => 'MRMESSIN',
staging_table_name => 'SQLSET_STAGE_TAB',
staging_schema_owner => 'MRMESSIN');

END ;
/

---------------------------------------------------------------------------
-- Query and view our sql tuning sets
---------------------------------------------------------------------------
select owner, name, created, statement_count from dba_sqlset ;

spool off



APPENDIX B: Put SQL Tuning Sets in Imported Staging Table Back into Database SQL Tuning Sets

---------------------------------------------------------------------------
-- Script: spa_stage_tab_to_sts.sql
-- Author; Michael R. Messina, Senior Consultant TUSC, A Rolta Company
--
-- take the imported staging table for the SQL Tuning Sets and unpack them
-- back into STS for analysis
---------------------------------------------------------------------------
--
-- Write Output to a file for later review if needed.
spool spa_stage_tab_to_sts.out


BEGIN
DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => '%',
sqlset_owner => 'MRMESSIN',
replace => TRUE,
staging_table_name => 'SQLSET_STAGE_TAB',
staging_schema_owner => 'MRMESSIN');
END;
/

---------------------------------------------------------------------------
-- Query and view our sql tuning sets
---------------------------------------------------------------------------
select owner, name, created, statement_count from dba_sqlset ;

spool off