You can create SQL Tuning Set for analyzing and tuning SQL statements. You may want to move STS to test database. You can do it with Oracle Enterprise Manager database console. And also you can do it with using DBMS_SQLTUNE package. In the background OEM will move STS with using this package.
STS moving steps are as follows.
1- Create staging table for STS
BEGIN
DBMS_SQLTUNE.create_stgtab_sqlset (table_name => ‘STG_TABLE’,
schema_name => ‘TALIP’,
tablespace_name => ‘TALIP_TS’
);
END;
/
2- Pack STS to staging table
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset (sqlset_name => ‘STS_TALIP’,
sqlset_owner => ‘TALIP’,
staging_table_name => ‘STG_TABLE’,
staging_schema_owner => ‘TALIP’
);
END;
/
3- Export the staging table and copy export file to server which you want to move STS.
exp userid=talip@dbtalip file=stg_table.dmp log=stg_table.log tables=talip.stg_table compress=no recordlength=65535 direct=y feedback=1000000 CONSISTENT=N
This export will take 3 tables. These tables are “STG_TABLE” , “STG_TABLE_CBINDS” and “STG_TABLE_CPLANS”. The first table contains our sql statements, the second table contains our bind variables and the last table contains execution plans.
4- Import the staging table to test database.
imp userid=talip@testdb file=stg_table.dmp log=stg_table_imp.log fromuser=talip touser=talip recordlength=65535 feedback=1000000
5- Unpack staging table to STS.
BEGIN
DBMS_SQLTUNE.unpack_stgtab_sqlset (sqlset_name => ‘STS_TALIP’,
sqlset_owner => ‘TALIP’,
replace => TRUE,
staging_table_name => ‘STG_TABLE’,
staging_schema_owner => ‘TALIP’
);
END;
/
Your SQL Tuning Set (STS) is ready for analyzing and tuning.
Talip Hakan Öztürk
Talip Hakan Ozturk's ORACLE BLOG 