ORA-13757: Can’t drop SQL Tuning Set

The following errors occur when dropping a SQL tunning set:

SQL> exec dbms_sqltune.drop_sqlset('STS_TEST','SYSTEM');
BEGIN dbms_sqltune.drop_sqlset('STS_TEST','SYSTEM'); END;
*
ERROR at line 1:
ORA-13757: "SQL Tuning Set" "STS_TEST" owned by user "SYSTEM" is active.
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 13226
ORA-06512: at "SYS.DBMS_SQLTUNE", line 4563
ORA-06512: at line 1

OR

On OEM console when you try to remove it, you get the error as follows:

Error
This SQL Tuning Set cannot be deleted because there are advisors tasks dependent on it.

CAUSE

The SQL Tuning Advisor have been created under the STS, and hence it is not allowing to drop the STS.

SOLUTION

1.Check for the STS view references to the sqlset:

SQL> select description, created, owner
    from DBA_SQLSET_REFERENCES
    where sqlset_name = 'STS_TEST';

DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATED           OWNER
----------------- ------------------------------
created by: SQL Tuning Advisor - task: SQL_TUNING_1664512487897
20220930-14:35:51 SYSTEM

2.Drop the dependent SQL tasks under the STS:

SQL>  exec DBMS_SQLTUNE.DROP_TUNING_TASK('SQL_TUNING_1664512487897');

PL/SQL procedure successfully completed.

3. Now you can drop the STS.

SQL> exec dbms_sqltune.drop_sqlset('STS_TEST','SYSTEM');

PL/SQL procedure successfully completed.