bulk delete sql_plan baselines

if any time you have enabled auto capture sqlplan baselines, you can accumulate more of 3000 baselines in few seconds and if you want to disable auto capture again, you must delete all baselines before.

You can do it with this code:

SET SERVEROUTPUT ON;
DECLARE
vResult Binary_Integer;

BEGIN
for vPlan In 
  (SELECT distinct Plan_Name FROM dba_sql_plan_baselines) Loop
    DBMS_OUTPUT.PUT_LINE('Deleting ' || vPLan.Plan_Name );
    vResult := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
               sql_handle=>NULL, 
               plan_name=>vPlan.Plan_Name
    );
END LOOP;
END;
/

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *