本文共 1808 字，大约阅读时间需要 6 分钟。

生成baseline DECLARE   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都要执行 BEGIN DBMS_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_baselines DECLARE   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_baseline DECLARE   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;

转载地址：http://bkhji.baihongyu.com/