Move SQL Profiles from One Database to Another 

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

How to move the sql profiles

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