Friday, November 8, 2013

How to drop a plan from the SMB

Find the SQL Plan Baselines name and sql handle:
SELECT  SQL_HANDLE,PLAN_NAME
FROM    DBA_SQL_PLAN_BASELINES 
WHERE   PLAN_NAME ='SQL_PLAN_b8p2xjvyx9ja5bcc2b4c9';
Run the following PL/SQL as a privileged user (a DBA or "as sysdba")
SET SERVEROUTPUT ON
DECLARE
  l_plans_dropped  PLS_INTEGER;
BEGIN
  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle => 'SQL_b4545d8efdd4c545',
    plan_name  => 'SQL_PLAN_b8p2xjvyx9ja5bcc2b4c9');

  DBMS_OUTPUT.put_line('Number of plans dropped: ' || l_plans_dropped);
END;
/

No comments:

Post a Comment