Hello everyone, a few weeks ago, during a project, our client requested that during the database migration process we take the SQL PLAN Baseline of the current databases.

I found the topic interesting to share with you, how to do this migration of SQL PLAN Baselines from one database to another.

NOTE: The database user must have “administer sql management object” to execute the steps below.

1 . Create a staging table from the source database. A staging table cannot be created in SYS schema.(ORA-19381)

I used the SYSTEM schema.

exec DBMS_SPM.CREATE_STGTAB_BASELINE('MIGBASELINE', MIGUSER);

2. Pack baselines in the source database:

Here we can do it in four ways.

A) Pack all baselines in the database.

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

B) Pack ALL Baselines Plans of a query

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

C) Pack a specific Baseline Plan of a Query

set serveroutput on
DECLARE
x number;
BEGIN
x := DBMS_SPM.PACK_STGTAB_BASELINE('MIGBASELINE', MIGUSER, sql_handle => ' SYS_SQL_58d940ad9b3ac043', plan_name => 'SYS_SQL_PLAN_d380ae0c0a76c437' );
dbms_output.put_line(to_char(x) || ' plan baselines packed');
END;

/

D) Pack only ACCEPTED Baseline Plans of a Query

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

3. Export the staging table MIGBASELINE from the source database.

4. Import the staging table MIGBASELINE into the target database.

5. Unpack the baselines in the target database.

set serveroutput on
DECLARE
x number;
BEGIN
x := DBMS_SPM.UNPACK_STGTAB_BASELINE('MIGBASELINE', MIGUSERN);
dbms_output.put_line(to_char(x) || ' plan baselines unpacked');
END;
/

I hope this helps you!!!

Andre Luiz Dutra Ontalba

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited  to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful