Script to Drop Plan Baseline 

set long 32767
set pages 9999
set lines 200
set serveroutput on size 1000000

accept SQL_Handle prompt “Enter the SQL handle: “;

accept plan_name  prompt “Enter the SQL plan name : “;

DECLARE
    lv_plans       pls_integer;
    lv_sql_handle  VARCHAR2(30) := ‘&SQL_Handle’;
    lv_plan_name   VARCHAR2(30) := ‘&plan_name’;
BEGIN
    lv_plans := DBMS_SPM.drop_SQL_PLAN_BASELINE (
        sql_handle => lv_sql_handle,
        plan_name  => lv_plan_name
    );
    — DBMS_OUTPUT.PUT_LINE(TO_CHAR(lv_plans));
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;
/

  • September 28, 2018 | 25 views
  • Comments