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!!

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close