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

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016