rmoff's random ramblings
about talks

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

Published Mar 9, 2010 by in Oracle, Performance at https://rmoff.net/2010/03/09/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 :)


Robin Moffatt

Robin Moffatt works on the DevRel team at Confluent. He likes writing about himself in the third person, eating good breakfasts, and drinking good beer.

Story logo

© 2025