Move SQL Profiles from One Database to Another in Oracle

In this post, we will check out how to move SQL Profiles from One Database to Another

How to move the sql profiles in Oracle

Suppose you have created a sql profile in System A and Now you want to transfer that sql profile to System B.

Step 1
On System A: Creating a staging table to store the SQL Profiles

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>'SCOTT');

Step 2
On System A: Pack the SQL Profiles into the Staging Table

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'my_sql_profile');

Step 3
On System A: Export from Source Database

$ exp scott/tiger file=stage.dmp tables=stage

Step 4
On System B: Import the staging table

$ imp scott/tiger file=stage.dmp full=y

Step 5
On System B: Unpack Staging Table

If importing to the same schema, the schema owner does not need to be specified:

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE');

However, if importing to a different schema, the staging schema owner needs to be changed:

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE',staging_schema_owner => 'SQLTXPLAIN');

Step 6

On System B: Check if the profile is enabled

select * from table(dbms_xplan.display_cursor('&1', NULL, 'ALL'));

I hope you will find this article on How to move the sql profiles useful and helpful. Please do provide the feedback

Related Articles