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'));
Leave a Reply