Drop SQL Baselines In Oracle

1.Get the sql_handle and sql_baseline name of the sql_id:


SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE signature IN ( SELECT exact_matching_signature FROM gv$sql WHERE sql_id='&SQL_ID')

SQL_HANDLE                                    PLAN_NAME
--------------------------------------------- ----------------------------------------------------
SQL_a7ac813cbf25e65f                          SQL_PLAN_agb417kzkbtkz479e6372

2. Drop the baseline:

SQL> select sql_handle,plan_name from dba_sql_plan_baselines where plan_name='SQL_PLAN_agb417kzkbtkz479e6372';

SQL_HANDLE                                    PLAN_NAME
--------------------------------------------- -------------------------------------------------------------------
SQL_a7ac813cbf25e65f                          SQL_PLAN_agb417kzkbtkz479e6372


declare
drop_result pls_integer;
begin
drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_a7ac813cbf25e65f',
plan_name => 'SQL_PLAN_agb417kzkbtkz479e6372');
dbms_output.put_line(drop_result);
end;
/

PL/SQL procedure successfully completed.

SQL> select sql_handle,plan_name from dba_sql_plan_baselines where plan_name='SQL_PLAN_agb417kzkbtkz479e6372';

no rows selected

A  sql_handle can have multiple sql baselines attached, So if you want to drop all the sql baselines of that handle, then drop the sql handle without adding plan_name.


declare
 drop_result pls_integer;
 begin
 drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
 sql_handle => 'SQL_a7ac813cbf25e65f');
 dbms_output.put_line(drop_result); 
 end;
/