Sql plan base line export and import it in new database

Check the count of sql baselines to be exported:-

select count(*) from dba_sql_plan_baselines;
725

Create a user with which the base should be exported:-

create user sqbl identified by “#####”;

grant connect,resource,dba to sqbl;

alter user sqbl default tablespace users;

Connect to the database and create baseline table:-

sqlplus “/as sysdba”

conn sqlbl/#####;

BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => ‘stage1’);
END;
/

var x number;
begin
😡 := DBMS_SPM.PACK_STGTAB_BASELINE(‘STAGE1′,’sqbl’);
end;
/

Export the baseline table:-

expdp sqbl tables=sqbl.stage1 directory=DATA2 dumpfile=baseline_test.dmp logfile=baseline_test.log

SCP the dump from source to target

Target database:-

Create the same user in target database:-

create user sqbl identfied by “#####”;
grant connect,resource,dba to sqbl;

alter USERS tablespace of whatever default tablespace for import user to at least 8G size.

Import the table in target database using the follwoing:-

impdp sqbl tables=sqbl.stage1 directory=DATA2 file=baseline_test.dmp logfile=baseline_test_imp.log

sqlplus “/as sysdba”

Unpack the baselines in the target database:-

var x number;
begin
😡 := DBMS_SPM.UNPACK_STGTAB_BASELINE(‘STAGE1’, ‘sqbl’);
end;