Copying SQL Profiles from one database to another

1. Creating a staging table to store the SQL Profiles
2. Packing the SQL Profiles to the staging table
3. Export the table using Datapump or Export/Import
4. Importing the SQL Profiles to the target database
5. Unpack the SQL Profiles in the target database

 1. Creating a staging table to store the SQL Profiles

SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'SQL_PROFILES_TT',schema_name=>'SCOTT');

2. Packing the SQL Profiles to the staging table

--Select SQL profiles names available in the source database.
SQL> SELECT name FROM dba_sql_profiles; 
NAME
---------
SYS_SQLPROF_x
SYS_SQLPROF_x
SYS_SQLPROF_x
SYS_SQLPROF_x

SQL> 
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>'SYS_SQLPROF_x'); 
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>'SYS_SQLPROF_x'); 
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>'SYS_SQLPROF_x'); 
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>'SYS_SQLPROF_x');

3. Export table using “exp” tool from the source database

$ exp SCOTT/password file=SQL_PROFILES.dmp tables=SQL_PROFILES_TT

4. Import table using “imp” tool into the target database

$ imp SCOTT/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_TT');