Search My Oracle Blog

Custom Search

29 May, 2011

Deleting SQL Plan Baselines

Following the example of capturing SQL Plan Baselines, here's a deletion :
SQL> select sql_handle, sql_text from dba_sql_plan_baselines
2 where sql_text like 'SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS %';

SQL_HANDLE SQL_TEXT
------------------------------ --------------------------------------------------------------------------------
SYS_SQL_2bdf77bedbcdea50 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPE

SQL>
SQL> set serveroutput on
SQL> declare
2 ret_value pls_integer;
3 sql_handle_in varchar2(30);
4 cursor c1 is
5 select sql_handle from dba_sql_plan_baselines
6 where sql_text like 'SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS %' ;
7 begin
8 open c1 ;
9 loop
10 fetch c1 into sql_handle_in;
11 exit when c1%notfound;
12 ret_value := dbms_spm.drop_sql_plan_baseline(sql_handle=>sql_handle_in,plan_name=>NULL);
13 dbms_output.put_line(ret_value);
14 end loop;
15 end;
16 /
1

PL/SQL procedure successfully completed.

SQL> select sql_handle, sql_text from dba_sql_plan_baselines
2 where sql_text like 'SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS %'
3 /

no rows selected

SQL>


Deletion of multiple SQL_HANDLEs or PLAN_NAMEs can also be executed similarly.

.
.
.

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com