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

 

 

[Post Views: 223]

Read Offline: PDF Download

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

Be First to Comment

Leave a Reply

Required fields are marked *