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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : June-2018


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]

I am certified Oracle Cloud Architect:
Oracle Cloud Infrastructure 2018 Certified Architect Associate
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate

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

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

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;
   abc:=dbms_spm.drop_sql_plan_baseline('SQL_PLAN_ahjc9da9z8g361a1720db ');

PL/SQL procedure successfully completed.

No comments: