ORA-13757: “SQL Tuning Set” “string” owned by user “string” is active.

ORA-13757: “SQL Tuning Set” “string”
owned by user “string” is active.

Demonstration

In today's demonstration, I am trying to recreate the SQL Tuning Set with existing name.  Approach we are using to complete this is delete the old STS and create the new one with existing name.  But when we try to drop the tuning set, we are getting below error. 

Reason for the error is dependencies existing on existing tuning set.  Goal is remove the dependency and the drop the existing tuning set.

Below are step by step workaround to solve this issue.

On Enterprise Manager :

We are getting below error while dropping from EM Console.

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

And when we manually try to delete from SQLPLUS then getting below error.  This error clearly shows that this tuning set is having some dependency.

To resolve this issue first we need to remove the dependency then will delete this STS.

ORA-13757: “SQL Tuning Set” “string” owned by user “string” is active.

Workaround

Conn /as sysdba

SQL> set lin200 pages 200

SQL> select d.name

FROM wri$_sqlset_definitions d, wri$_sqlset_references r

WHERE d.name = 'STS_TEST'

AND r.sqlset_id = d.id;

NAME

------------------------------

STS_TEST

Try to remove tuning set.  Getting error "Active Tuning Set"

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

BEGIN dbms_sqltune.drop_sqlset('STS_TEST'); END;

*

ERROR at line 1:

ORA-13757: "SQL Tuning Set" "STS_TEST" owned by user "SYS" is active.

ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 13213

ORA-06512: at "SYS.DBMS_SQLTUNE", line 4563

ORA-06512: at line 1

Check the active tuning set related entry from WRI$_SQL_REFERENCES

SQL> SELECT count(*)

FROM wri$_sqlset_definitions d, wri$_sqlset_references r

WHERE d.name = 'STS_TEST'

AND r.sqlset_id = d.id;

  COUNT(*)

----------

         1

SQL> select d.name

FROM wri$_sqlset_definitions d, wri$_sqlset_references r

WHERE d.name = 'STS_TEST'

AND r.sqlset_id = d.id; 

NAME

------------------------------

STS_TEST

Manually delete The related entry from WRI$_SQL_REFERENCES

SQL> delete from wri$_sqlset_references

where sqlset_id in (select id

                  from wri$_sqlset_definitions

                  where name ='STS_TEST'

                  );

1 row deleted.

SQL> commit ;

Commit complete.

Now it's high time to drop the SQL Tuning Set.

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

PL/SQL procedure successfully completed.

Validate the dropped SQL Tuning Set.

SQL> SELECT COUNT(*) FROM DBA_SQLSET WHERE  NAME = 'STS_TEST';

  COUNT(*)

----------

         0

Hope this help :)