Hi All,
We have oracle 11.2 on Aix. One of the sql id has two plan has value.
We did same thing for different db's. Now we need it in the same db.
Please anyone check the below steps and suggest me.
First ,find SQL ID and PLAN HASH VALUE from the source.
Sql_id = '45g4pghgh9rt' & Plan_Hash_value :- 2904123173
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
On source
---------------
Step 1 :
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id =>'45g4pghgh9rt', plan_hash_value=>'2904123173');
end;
/
Step 2 :
Run the below query, taken the value of sql_handle & plan_name.These two values is input for step 4.
select sql_handle, sql_text, plan_name,PARSING_SCHEMA_NAME,enabled,accepted,fixed,to_char(last_executed,'dd-mon-yy HH24:MI') last_executed from dba_sql_plan_baselines;
Step 3 :
exec DBMS_SPM.CREATE_STGTAB_BASELINE('STGTAB', 'AMIT');
Step 4 :
var x number;
begin
:= DBMS_SPM.PACK_STGTAB_BASELINE('STGTAB', 'AMIT', sql_handle => 'SQL_27c1b5a44008ee73', plan_name => 'SQL_PLAN_2ghdpnj00jvmm46bd3d4a' );
end;
/
Step 5 :
exp user/pass file=exp_t_apr-13.dmp log=exp_t_apr-13.log tables=STGTAB.AMIT
On Target
--------------
Step 6 :
imp user/pass file=exp_t_apr-13.dmp log=imp_t_apr-13.log fromuser=STGTAB touser=STGTAB
Step 7 :
var x number;
begin
:= DBMS_SPM.UNPACK_STGTAB_BASELINE('STGTAB', 'AMIT');
end;
/
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,can i skip step 5 and step 6.
2, Some time step2 not show any output. ( i created dummy tables with few rows in sys schema and tried different schema also )
Any Suggestions
Thanks & Regards,