Oracle April 13, 2011 0

Copy SQL Profiles to another Oracle database

Copying SQL Profiles from one database to another involves the following steps:

  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_012f26136ae90001
SYS_SQLPROF_012f260d3e1a0000
SYS_SQLPROF_012ed90a425b0003
SYS_SQLPROF_012f2622a4620003

SQL> 
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>'SYS_SQLPROF_012f26136ae90001'); 
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>'SYS_SQLPROF_012f260d3e1a0000'); 
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>'SYS_SQLPROF_012ed90a425b0003'); 
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>'SYS_SQLPROF_012f2622a4620003');

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

Cheers!!