迁移baseline
2019-11-08 20:39:57
供稿：网友
生成baselineDECLARE ret PLS_INTEGER;BEGIN ret:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'093bjyjkc76ry',plan_hash_value=>4194351669,fixed=>'YES'); dbms_output.put_line(ret || ' SQL plan baseline(s) created');END;--- 源端DB和目标DB都要执行BEGINDBMS_SPM.CREATE_STGTAB_BASELINE( table_name =>'spm_stageing_tab', table_owner => 'TP', tablespace_name => 'TP');END;-- 源DB的执行DECLARE l_plans_packed PLS_INTEGER;BEGIN l_plans_packed := DBMS_SPM.pack_stgtab_baseline( table_name =>'spm_stageing_tab', table_owner => 'TP'); DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);END;expdp sysem/xxx tables=TP.spm_stageing_tab directory=DUMP_DIR content=data_only dumpfile=spm_stageing_tab.dmp logfile=spm_stageing_tab.expdp.log-- 目标DB执行impdp system/xxx tables=TP.spm_stageing_tab directory=DUMP_DIR content=data_only dumpfile=spm_stageing_tab.dmp logfile=spm_stageing_tab.impdp.log-- unpack sql_plan_baselinesDECLARE l_plans_unpacked PLS_INTEGER;BEGIN l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline( table_name =>'spm_stageing_tab', table_owner => 'TP'); DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);END;-----baseline迁移结束-- drop sql_plan_baselineDECLARE l_plans_dropped PLS_INTEGER;BEGIN l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline ( sql_handle => 'SQL_2ac181fb3c1a89ca', plan_name => 'SQL_PLAN_2phc1zcy1p2fa34b6538d'); DBMS_OUTPUT.put_line(l_plans_dropped);END;DECLARE l_plans_dropped PLS_INTEGER; cur_handle sys_refcursor; v_handle varchar2(100);BEGIN open cur_handle for select distinct sql_handle from dba_sql_plan_baselines; fetch cur_handle into v_handle; while cur_handle%found loop l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (sql_handle => v_handle); DBMS_OUTPUT.put_line(l_plans_dropped); fetch cur_handle into v_handle; end loop; close cur_handle;END;
