dbms_spm.drop_sql_plan_baseline Fails With ‘ORA-14552: Cannot Perform A DDL, Commit Or Rollback Inside A Query Or DML

SYMPTOM

Drop a sql execution plan baseline with following errors:

SQL> select dbms_spm.drop_sql_plan_baseline(sql_handle=>    -
                       'SQL_89d3699974845bdb') from dual;
select dbms_spm.drop_sql_plan_baseline(sql_handle=>        -
                       'SQL_89d3699974845bdb') from dual
*
ERROR at line 1:
ORA-14552: cannot perform a DDL,commit or rollback inside 
           a query or DML
ORA-06512: at "SYS.DBMS_SPM", line 1210
ORA-06512: at "SYS.DBMS_SPM", line 1200
ORA-06512: at line 1

dbms_spm.drop_sql_plan_baseline updates dictionary tables. So you should not use it in a query.

SOLUTION

SQL> exec :xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>   -
                                   'SQL_89d3699974845bdb');

PL/SQL procedure successfully completed.

SQL> select :xx from dual;

:XX
----------
1

SQL> print :xx

XX
----------
1
Drop all sql execution plan baselines.
SQL> set serveroutput on
SQL> declare
     v_count pls_integer;
     v_sql_handle varchar2(50);
     cursor c1 is
     select sql_handle from dba_sql_plan_baselines
         where sql_handle like 'SQL_%';
     begin
        open c1 ;
        loop
            fetch c1 into v_sql_handle;
            exit when c1%notfound;
            v_count := dbms_spm.drop_sql_plan_baseline( 
                             sql_handle=>v_sql_handle );
           dbms_output.put_line(v_count);
      end loop;
  end;

 /
1
1
..
.
PL/SQL procedure successfully completed.

SQL> select count(*) from dba_sql_plan_baselines;

    COUNT(*)
 ----------
         0

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.