Skip to content

Move SQL PROFILE to other database

A)  CREATE STAGING TABLE

First Create the staging table which will hold sql profiles to be moved over.

SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>’SQL_STG_TAB’,schema_name=>’SYS’);

(This will create a table SQL_STG_TAB, which will be able to hold the required sql profiles, the structure of this table is exactly same as that of DBA_SQL_PROFILES table)

B) FIND PROFILE NAME THAT YOU WANT TO MOVE

If you want to pack  a particular SQL profiles and you don’t know the names of profiles, then you can query dba_sql_profiles table.

SQL> select name from DBA_SQL_PROFILES where SQL_TEXT like ‘%SELECT%EMPLOYEE%NAME%’;
NAME
——————————
SYS_SQLPROF_014b

 

C) PACK CUSTOM PROFILE TO STAGING TABLE

To pack a custom profile to stage table,

SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => ‘SQL_STG_TAB’, profile_name=>'SYS_SQLPROF_014b');

If you want to Pack all profiles under DEFAULT category to stage table,

SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => ‘SQL_STG_TAB’);

 

D) EXPORT STAGING TABLE

Export stage tab using exp.

$ exp “‘/ as sysdba'” tables=SQL_STG_TAB file=SQL_STG_TAB.dmp

 

E) IMPORT STAGING TABLE

Copy the dmp file to the target database instance

Import the stage tab on the target database using imp

 

$ imp “‘/ as sysdba'” file=SQL_STG_TAB.dmp full=y

 

F) UNPACK THE PROFILE

Unpack the profiles at the target database,

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => ‘SQL_STG_TAB’);

 

 

Brijesh Gogia
Leave a Reply