Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to remove sys-owned referencies of sts

hqt200475Jun 6 2012 — edited Jun 6 2012

Hi experts

I have a beginner questions:

After defined some sql tuning sets (sts)!
Now I want to remove some of them! I have to remove at first their referencies:

select * from DBA_SQLSET_REFERENCES ;
SQLSET_NAME           SQLSET_OWNER SQLSET_ID ID OWNER CREATED DESCRIPTION
MY_STS	        HR	1	1	SYS	08.03.12	created by: SQL Tuning Advisor - task: SQL_TUNING_1331216081407
MYWORKLOAD_1	SH	20	37	SYS	05.04.12	Access Advisor task SQLACCESS5537425          " <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<"
STS_20120603_01	SH	21	39	SYS	03.06.12	created by: SQL Tuning Advisor - task: SQL_TUNING_SH_STS_20120603_01
MYWORKLOAD_1	SH	20	35	SYS	05.04.12	created by: SQL Tuning Advisor - task: SQL_TUNING_1333609886368
MYWORKLOAD_1	SH	20	36	SYS	05.04.12	created by: SQL Tuning Advisor - task: SQL_TUNING_1333610666557
STS_20120603_01	SH	21	40	SYS	03.06.12	created by: SQL Tuning Advisor - task: SQL_STS_20120603_01
$$SQLA$$_1	SYS	18	32	SH	03.04.12	$$SQLA$$ (Internal use only): SYS.WORKLOAD_240
$$SQLA$$_1	SYS	18	33	SH	03.04.12	$$SQLA$$ (Internal use only): SYS.WORKLOAD_240

So I tried for example to remove Reference 37 as sys user and got the error:

DECLARE
BEGIN
  -- Delete the reference.
  DBMS_SQLTUNE.remove_sqlset_reference (
    sqlset_name  => 'MYWORKLOAD_1',
    reference_id => 37);
END;
/

ORA-06512: in "SYS.DBMS_SQLTUNE_INTERNAL", Zeile 15117
ORA-06512: in "SYS.DBMS_SQLTUNE", Zeile 5501
ORA-06512: in Zeile 4
13754. 00000 -  "\"SQL Tuning Set\" \"%s\" does not exist for user \"%s\"."
*Cause:    The user attempted to access a SQL Tuning Set that does not exist.
*Action:   Check the speelling of the SQL Tuning Set name and retry
           the operation.

and as user sh and got:

DECLARE
BEGIN
  -- Delete the reference.
  DBMS_SQLTUNE.remove_sqlset_reference (
    sqlset_name  => 'MYWORKLOAD_1',
    reference_id => 37);
END;
/

ORA-06512: in "SYS.DBMS_SQLTUNE_INTERNAL", Zeile 15117
ORA-06512: in "SYS.DBMS_SQLTUNE", Zeile 5501
ORA-06512: in Zeile 4
13759. 00000 -  "User \"%s\" cannot remove reference \"%s\"."
*Cause:    The user attempted to remove a SQL Tuning Set reference that
           does not exist. The user might not own the reference.
*Action:   Check the reference ID and the reference owner and retry
           the operation.

I spent long time to look for the right oracle documents but cannot find them!

Please help me to find the right document or the way to remove easily the sts

thank and regards

hqt200475

Edited by: hqt200475 on Jun 6, 2012 3:08 AM

Edited by: hqt200475 on Jun 6, 2012 4:42 AM

This post has been answered by Karan on Jun 6 2012

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 4 2012

Added on Jun 6 2012

#11g, #and, #database, #general-database-discussions, #performance, #tuning

10 comments

632 views