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

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