In this guide, I’ll walk you through moving SQL profiles using a staging table. SQL profiles help databases optimize query execution plans.
Step#1 Create the Staging Table
First, create a staging table to hold SQL profiles that need to be transferred.
SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name => 'SQL_STG_TAB', schema_name => 'SYS');
This will create a table SQL_STG_TAB that holds SQL profiles. Its structure matches the DBA_SQL_PROFILES table
Step#2 Find the Profile Name to Move
To pack a particular SQL profile, identify its name by querying the DBA_SQL_PROFILES table.
SQL> select name from DBA_SQL_PROFILES where SQL_TEXT like '%SELECT%EMPLOYEE%NAME%'; NAME --------------------- SYS_SQLPROF_014b
Step#3 Pack the Custom Profile to Staging Table
To pack a specific profile into the staging table:
SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(staging_table_name => 'SQL_STG_TAB', profile_name => 'SYS_SQLPROF_014b');
-- For all profiles use '%' in profile_name.
SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(staging_table_name => 'SQL_STG_TAB', profile_name => '%');
To pack all profiles under the DEFAULT category:
SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(staging_table_name => 'SQL_STG_TAB');
Step#4 Export the Staging Table
Now, export the staging table using exp:
$ exp "'/ as sysdba'" tables=SQL_STG_TAB file=SQL_STG_TAB.dmp
Step#5 Import the Staging Table
Copy the dump file to the target database instance and use imp to import it:
$ imp "'/ as sysdba'" file=SQL_STG_TAB.dmp full=y
Step#6 Unpack the Profile
SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE, staging_table_name => 'SQL_STG_TAB');
By following these steps, you’ll successfully move SQL profiles between databases.