bulk delete sql_plan baselines

Posted on Tue 22 September 2015 in Databases • 1 min read

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;
/