Drop the SQL Profile generated by SQL Advisory DBMS_SQLTUNE package
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;
OR
COLUMN category FORMAT a10
COLUMN sql_text FORMAT a20
SELECT NAME,type, SQL_TEXT, CATEGORY, STATUS, created FROM DBA_SQL_PROFILES;
4. Disable the SQL profile.
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');
Example:
exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_01788c5a3db90001');