Drop accepted plan in SQL Advisory

1. From SQL Advisory we accepted the plan

begin
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_69287', task_owner => 'SYSTEM', replace => TRUE);
end;
/


2. Check that SQL profile created is used

Set autotrace on
— run the SQL Query it will show execution plan which last line show you that plan is used or not and you get name of SQL Profile.


3. Check the sql profiles which is associated with SQL

select * from dba_sql_profiles;


4. Disable the SQL profile if you want

begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE_NAME','STATUS','DISABLED');
end;
/


5. Drop the SQL Profile

exec dbms_sqltune.drop_sql_profile('SQL_PROFILE_NAME');