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

I’ve been playing around with SQL Tuning Sets, and was trying to clear up my mess.

To list all the tuning sets: [sourcecode language=“sql”] SET WRAP OFF SET LINE 140 COL NAME FOR A15 COL DESCRIPTION FOR A50 WRAPPED

select name,created,last_modified,statement_count,description from DBA_SQLSET [/sourcecode]

[sourcecode] NAME CREATED LAST_MODI STATEMENT_COUNT DESCRIPTION ————— ——— ——— ————— —————————————————————————————- sts_test_02 09-MAR-10 09-MAR-10 1 Test run 1 sts_test_01 12-FEB-10 12-FEB-10 1 an old STS test test test [/sourcecode]

To delete a tuning set: [sourcecode language=“sql”] BEGIN DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => ‘sts_test_01’); END; [/sourcecode]

But you may hit this message:

ORA-13757: “SQL Tuning Set” “string” owned by user “string” is active. Cause: The user attempted to update an active SQL Tuning Set. Action: Remove all reference to the SQL Tuning Set and retry the operation.

For example: ORA-13757: “SQL Tuning Set” “sts_test_01” owned by user “badger” is active.

Error code reference

To look up why the STS is considered active, check the SQL Tuning Information Views, in this case DBA_SQLSET_REFERENCES

[sourcecode language=“sql”] SET WRAP OFF SET LINE 140 COL NAME FOR A15 COL DESCRIPTION FOR A50 WRAPPED

select description, created, owner from DBA_SQLSET_REFERENCES where sqlset_name = ‘sts_test_01’; [/sourcecode]

which in my case showed this: [sourcecode] DESCRIPTION CREATED OWNER ————————————————– ——— —————————— created by: SQL Tuning Advisor - task: RNM_TT 12-FEB-10 badger [/sourcecode]

So we check for this on DBA_ADVISOR_TASKS: [sourcecode language=“sql”] SET WRAP OFF SET LINE 140 COL NAME FOR A15 COL OWNER FOR A10 COL DESCRIPTION FOR A50 WRAPPED

select owner,description, created,last_modified from DBA_ADVISOR_TASKS where task_name = ‘RNM_TT’ [/sourcecode]

and it shows this: [sourcecode] OWNER DESCRIPTION CREATED LAST_MODI ———- ————————————————– ——— ——— badger SQL Advisor - sts_test_01 12-FEB-10 12-FEB-10 [/sourcecode]

So now we know it’s a stale SQL Tuning Advisor task that uses the SQL Tuning Set, and I definitely want to delete it:

[sourcecode language=“sql”] BEGIN DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => ‘RNM_TT’); END; [/sourcecode]

and then I can delete my original SQL Tuning Set:

[sourcecode language=“sql”] BEGIN DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => ‘sts_test_01’); END; [/sourcecode]

All done :)