Move SQL Baseline from One Database to Another in Oracle

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

How to move the sql baselines in Oracle

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

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

exec DBMS_SPM.CREATE_STGTAB_BASELINE(table_name=>'STAGE',schema_name=>'SCOTT');

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

Pack all baselines in the database.

set serveroutput on
DECLARE
x number;
BEGIN
x := DBMS_SPM.PACK_STGTAB_BASELINE('STAGE', 'SCOTT');
dbms_output.put_line(to_char(x) || ' plan baselines packed');
END;
/

Pack ALL Baselines Plans of a query

set serveroutput on
DECLARE
x number;
BEGIN
x := DBMS_SPM.PACK_STGTAB_BASELINE('STAGE', 'SCOTT', sql_handle => '&sql_handle');
dbms_output.put_line(to_char(x) || ' plan baselines packed');
END;
/

Pack a specific Baseline Plan of a Query


set serveroutput on
DECLARE
x number;
BEGIN
x := DBMS_SPM.PACK_STGTAB_BASELINE('STAGE', 'SCOTT', sql_handle => '&sql_handle', plan_name => '&plan_value' );
dbms_output.put_line(to_char(x) || ' plan baselines packed');
END;
/

Pack only ACCEPTED Baseline Plans of a Query


set serveroutput on
DECLARE
x number;
BEGIN
x := DBMS_SPM.PACK_STGTAB_BASELINE('STAGE', 'SCOTT', sql_handle => '&SQL_HANDLE', accepted => 'YES' );
dbms_output.put_line(to_char(x) || ' plan baselines packed');
END;
/

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_SPM.UNPACK_STGTAB_BASELINE('STAGE','SCOTT');

Step 6

On System B: Check if the baseline is enabled

SELECT sql_handle, plan_name, origin, enabled, accepted
FROM dba_sql_plan_baselines;

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

How to move the sql baselines in Oracle

Related Posts

Leave a Comment Cancel Reply