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

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Move SQL PROFILE to other database

Related posts: