Migrate SQL Profiles from One Instance to Another Instance

1. Creating a staging table to store the SQL Profiles

SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'SQL_PROFILES_MOVE',schema_name=>'SYSTEM');

2. Packing the SQL Profiles to the staging table

SQL> SELECT name FROM dba_sql_profiles;

NAME
---------
SYS_SQLPROF_612f26136ae78001
SYS_SQLPROF_712f260d3e1a1400
SYS_SQLPROF_812ed90a425b2303
SYS_SQLPROF_912f2622a4622103

SQL>

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_MOVE',profile_name=>'SYS_SQLPROF_612f26136ae78001');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_MOVE',profile_name=>'SYS_SQLPROF_712f260d3e1a1400');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_MOVE',profile_name=>'SYS_SQLPROF_812ed90a425b2303');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_MOVE',profile_name=>'SYS_SQLPROF_912f2622a4622103');

3. Export table using exp tool from the source database

exp SYSTEM/password file=SQL_PROFILES.dmp tables=SQL_PROFILES_MOVE

4. Import table using imp tool into the target database

imp SYSTEM/password file=SQL_PROFILES.dmp full=y

5. Unpack the SQL Profiles in the target database

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE,staging_table_name => 'SQL_PROFILES_MOVE');

6. SQL profiles names available in the Target database.

SQL> SELECT name FROM dba_sql_profiles;

NAME
---------
SYS_SQLPROF_612f26136ae78001
SYS_SQLPROF_712f260d3e1a1400
SYS_SQLPROF_812ed90a425b2303
SYS_SQLPROF_912f2622a4622103