Move the SQL plan from one database to other in SQL PLAN Management.

  1. Verify the existing SQL plan in the database.

SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines WHERE sql_text LIKE '%emp%';

2. Create a staging table for transfer the stats.

BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'scott_Plan_backup',
table_owner => 'SCOTT',
tablespace_name => 'USERS');
END;
/

3. Load the plan baseline to staging table.

SET SERVEROUTPUT ON
DECLARE
var_plans_loaded PLS_INTEGER;
BEGIN
var_plans_loaded := DBMS_SPM.pack_stgtab_baseline(table_name => 'scott_Plan_backup',table_owner => 'SCOTT');
DBMS_OUTPUT.put_line('Plans loaded: ' || var_plans_loaded);
END;
/
Plans loaded: 2

4. Take an Export/EXPDP logical backup of staging table.

SQL> create directory bkpsqlplan as 'c:\oracle';
--Execute the EXPDP datapump job
Expdb directory=bkpsqlplan tables=scott_Plan_backup dumpfile=sqlplan.dmp logfole=sqlplan.log

5. FTP to another server.

6. Import/IMPDP the staging table on another database server.

create directory bkpsqlplan as 'c:\oracle';
IMPDP directory=bkpsqlplan dumpfile=sqlplan.dmp logfile=sqlplan.log

7. Unload the execution plan to database server.

SET SERVEROUTPUT ON
DECLARE
l_plans_unloaded PLS_INTEGER;
BEGIN
l_plans_unloaded := DBMS_SPM.unpack_stgtab_baseline(table_name => 'scott_Plan_backup',table_owner => 'SCOTT');
DBMS_OUTPUT.put_line('Plans Unloaded: ' || l_plans_unloaded);
END;
/
Plans Unloaded: 2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.