Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Thursday, August 20, 2015

PLS-00221: 'DROP_SQL_PLAN_BASELINE' is not a procedure or is undefined

How to drop SQL Plan Baselines:


SQL> exec dbms_spm.drop_sql_plan_baseline('SQL_PLAN_ahjc9da9z8g361a1720db ');
BEGIN dbms_spm.drop_sql_plan_baseline('SQL_PLAN_ahjc9da9z8g361a1720db '); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'DROP_SQL_PLAN_BASELINE' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines
  2  where  sql_handle='SYS_SQL_a845896a93f43c66';

 SIGNATURE SQL_HANDLE              PLAN_NAME             ORIGIN     ENA ACC AUT
---------- ------------------------------ ------------------------------ -------------- --- --- ---
1.2125E+19 SYS_SQL_a845896a93f43c66      SQL_PLAN_ahjc9da9z8g361a1720db AUTO-CAPTURE    YES YES YES
1.2125E+19 SYS_SQL_a845896a93f43c66      SQL_PLAN_ahjc9da9z8g3693e18217 AUTO-CAPTURE    YES YES YES


Fix:-
===
To drop all plans related to the sql_handle:
SQL> declare
  2  abc pls_integer;
  3  begin
  4  abc:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_a845896a93f43c66');          
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines  where  sql_handle='SYS_SQL_a845896a93f43c66';

no rows selected

or to drop individual plans use plan_name.

SQL> declare
      abc pls_integer;
   begin
   abc:=dbms_spm.drop_sql_plan_baseline('SQL_PLAN_ahjc9da9z8g361a1720db ');
   end;
   /

PL/SQL procedure successfully completed.

No comments: