Skip to content

Move SQL Plan Baseline to other Instance

Oracle Database supports the export and import of SQL plan baselines using the Oracle Data Pump Import and Export utilities. Use the DBMS_SPM package to define a staging table, which you can use to pack and unpack SQL plan baselines.

To import a set of SQL plan baselines from one system to another:

STEP 1) create a staging table

Use the CREATE_STGTAB_BASELINE procedure.

SQL> BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'stage');
END;
/

PL/SQL procedure successfully completed.

 

STEP 2) Pack the SQL plan baselines

Pack the sql plan baseline that you want to export from the SQL management base into the staging table using the PACK_STGTAB_BASELINE function.

The following example packs enabled plan baselines created by user dba into staging table stage. You can select SQL plan baselines using the plan name (plan_name), SQL handle (sql_handle), or any other plan criteria. The table_name parameter is mandatory.

DECLARE
  my_plans number;
BEGIN
  my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
    table_name => 'stage',
    enabled => 'yes',
    SQL_HANDLE => 'SQL_5bf39dfrta1a5b89');
END;
/

STEP 3) Export the staging table

Export the table (in our case stage table) into a flat file using the Oracle Data Pump Export utility.

$ sqlplus / as sysdba
SQL> Grant EXEMPT ACCESS POLICY to system;
SQL> CREATE OR REPLACE DIRECTORY test_dir AS '/usr/users/oadba';
SQL> GRANT READ, WRITE ON DIRECTORY test_dir TO system;
SQL> exit;


$ expdp system/<password> tables=stage directory=TEST_DIR dumpfile=stage.dmp logfile=stage.log compression=all

Or

Use export command exp

exp system/<password>  file=stage.dmp tables=stage

 

STEP 4) Transfer the flat file to the target system.

Using secure copy or other method copy the generated stage.dmp to target system

 

STEP 5) Import the staging table

Import the staging table from the flat file using the Oracle Data Pump Import utility.

imp system/<password> file=stage.dmp tables=stage

 

STEP 6 ) Unpack the SQL plan baselines

Unpack the SQL plan baseline from the staging table into the SQL management base on the target system using the UNPACK_STGTAB_BASELINE function.

The following example unpacks all fixed plan baselines stored in the staging table stage:

sqlplus system/<password>

SET SERVEROUTPUT ON
DECLARE
  l_plans_unpacked  PLS_INTEGER;
BEGIN
  l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
    table_name      => 'STAGE',
    table_owner     => 'SYSTEM',
    creator         => 'SYSTEM');
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/

Plans Unpacked: 1
PL/SQL procedure successfully completed.

 

STEP 7) VERIFY

Verify if SQL plan baseline got imported successfully

select * from dba_sql_plan_baselines;

 

Brijesh Gogia

10 Comments

  1. sam sam

    Mentioned wrong table name in step -1
    It should be stage not stage1.

    • sam sam

      By d way, you explained so nicely with example.

    • Brijesh Gogia Brijesh Gogia

      Thanks. Corrected Typo.

  2. Manoj Kumar Patro Manoj Kumar Patro

    Can I migrate a baseline from 11.2.0.4 to an Oracle 12 database?

    • Zeeshan Moin Zeeshan Moin

      yes u can

  3. aj__ aj__

    thanks Brijesh

  4. Ed1971 Ed1971

    Good information but it would be handled if you included the SQL to get the SQL_HANDLE.

  5. Dexter Dexter

    When I tried to unpack the plan I got the following errors. I’m not sure what to do. I’ve run the same sql script against both databases so they should both have it.

    declare l_plans_unpacked PLS_INTEGER;
    *
    ERROR at line 1:
    ORA-38172: No SQL management object satisfies specified filters
    ORA-06512: at “SYS.DBMS_SMB”, line 1615
    ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
    ORA-06512: at “SYS.DBMS_SMB”, line 1600
    ORA-06512: at “SYS.DBMS_SPM”, line 1840
    ORA-06512: at line 3

  6. tchouk44@gmail.com tchouk44@gmail.com

    thank for your publication.

  7. Anonymous Anonymous

    will it work? if I migrate sql profiles and base line plans from existing 11.2.0.4 to 19c. pls advise. thanks.

Leave a Reply to sam Cancel reply