Oracle by Example brandingTransfer a SQL Tuning Set from One Database to Another Database

section 0Before You Begin

This 15-minute tutorial shows you how to transfer a SQL tuning set from one Oracle database to another Oracle database.

Background

You can use a staging table to transfer a SQL tuning set from one Oracle database (the source database) to another Oracle database (the target database). On the source database, load the SQL tuning set into a staging table. Use Oracle Data Pump to export the contents of the staging table to a file on the source database host. Transfer the file to the target database host. Use Oracle Data Pump to import the contents of the staging table into the target database, and then unpack the SQL tuning set into the target database.

What Do You Need?

  • A source Oracle database
  • A target Oracle database
  • The SYSDBA administrative privilege on both databases
  • The SYSTEM user password on both databases
  • On the source database, a SQL tuning set. This tutorial assumes that the SQL tuning set is named MY_WORKLOAD_STS.

section 1Create a Staging Table in the Source Database

  1. Use SQL*Plus to log in to the source database as a user who has the SYSDBA administrative privilege.
    $ sqlplus / as sysdba
  2. Create staging table MY_STAGING_TABLE in schema SYSTEM.
    SQL> BEGIN
    2 DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
    3 table_name => 'MY_STAGING_TABLE',
    4 schema_name => 'SYSTEM');
    5 END;
    6 /
  3. Load SQL tuning set MY_WORKLOAD_STS into staging table MY_STAGING_TABLE.
    SQL> BEGIN
    2 DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
    3 sqlset_name => 'MY_WORKLOAD_STS',
    4 sqlset_owner => 'SYS',
    5 staging_table_name => 'MY_STAGING_TABLE',
    6 staging_schema_owner => 'SYSTEM');
    7 END;
    8 /

section 2Copy the Staging Table from the Source Database to the Target Database

  1. While still connected to SQL*Plus on the source database, create a directory object that references a staging directory in the /tmp directory.
    SQL> CREATE DIRECTORY stg_dir AS '/tmp/stg_dir';
  2. Exit SQL*Plus.
    SQL> exit
  3. Create the staging directory.
    $ mkdir /tmp/stg_dir
  4. Use Oracle Data Pump to export the contents of the staging table to a dumpfile in the staging directory. The dumpfile is named my_workload_sts.dmp.
    $ expdp SYSTEM/password DIRECTORY=stg_dir DUMPFILE=my_workload_sts.dmp TABLES=system.my_staging_table
  5. Copy the dumpfile from the source database host to the target database host by using a secure copy utility like scp or WinSCP.
  6. Log in to the target database host.
  7. Create a staging directory on the target database host.
    $ mkdir /tmp/stg_dir
  8. Navigate to the directory containing the dumpfile that you just transferred to the target database host, and move the dumpfile to the staging directory.
    $ mv my_workload_sts.dmp /tmp/stg_dir
  9. Use SQL*Plus to log in to the target database as a user who has the SYSDBA administrative privilege.
    $ sqlplus / as sysdba
  10. Create a directory object that references the staging directory.
    SQL> CREATE DIRECTORY stg_dir AS '/tmp/stg_dir';
  11. Exit SQL*Plus.
    SQL> exit
  12. Use Oracle Data Pump to import the contents of the staging table from the dumpfile in the staging directory.
    $ impdp SYSTEM/password DIRECTORY=stg_dir DUMPFILE=my_workload_sts.dmp TABLES=system.my_staging_table

section 3Load the SQL Tuning Set into the Target Database

  1. While still connected to the target database host, invoke SQL*Plus and log in to the target database as a user who has the SYSDBA administrative privilege.
    $ sqlplus / as sysdba
  2. Load the SQL tuning set MY_WORKLOAD_STS from the staging table into the target database.
    SQL> BEGIN
    2 DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
    3 sqlset_name => 'MY_WORKLOAD_STS',
    4 replace => true,
    5 staging_table_name => 'MY_STAGING_TABLE',
    6 staging_schema_owner => 'SYSTEM');
    7 END;
    8 /

section 4Clean Up

  1. While still connected to the target database, delete the staging directory and the staging table.
    SQL> DROP DIRECTORY stg_dir;
    SQL> DROP TABLE SYSTEM.MY_STAGING_TABLE;
  2. Exit SQL*Plus.
    SQL> exit
  3. Remove the staging directory from the target database host.
    $ rm -rf /tmp/stg_dir
  4. Log in to the source database host.
  5. Use SQL*Plus to log in to the source database as a user who has the SYSDBA administrative privilege.
    $ sqlplus / as sysdba
  6. On the source datbase, delete the staging directory and the staging table.
    SQL> DROP DIRECTORY stg_dir;
    SQL> DROP TABLE SYSTEM.MY_STAGING_TABLE;
  7. Exit SQL*Plus.
    SQL> exit
  8. Remove the staging directory from the source database host.
    $ rm -rf /tmp/stg_dir

more informationWant to Learn More?