Transfer SQL Profiles from One database to other database.

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');

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.

Related Posts