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 : Jan-2019

**********************************************************************************


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]


My Cloud Certifications:
Oracle Cloud Infrastructure 2018 Certified Architect Associate.
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.
Oracle Database Cloud Administrator Certified Professional.
Oracle Database Cloud Service Operations Certified 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';

 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: