Oracle, Oracle Database March 2, 2010 0

Problem when creating the SQL Profile

Scenario: UserX tries to run SQL Tuning Advisor on a long running SELECT query in the database.

The SQL Tuning Advisor has came up with the tuning recomendation and UserX attempts to apply it.
An ERROR message has been thrown in OEM Grid Control 10g

There was a problem creating the SQL Profile
ORA-13605: The specified task OR object SQL_TUNING_XX does NOT exist FOR the current user.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86ORA-06512: at "SYS.PRVT_ADVISOR", line 4744
ORA-06512: at "SYS.DBMS_SQLTUNE", line 5526
ORA-06512: at line 1

 

Solution: Grant the UserX the appropriate privilege. But the question is which privileges?

Grant the UserX the following privileges:

 

For Managing SQL Profiles

CONN sys/password AS SYSDBA
GRANT CREATE ANY SQL PROFILE TO UserX;
GRANT ALTER  ANY SQL PROFILE TO UserX;
GRANT DROP   ANY SQL PROFILE TO UserX;

For SQL Tuning Sets

GRANT ADMINISTER ANY SQL TUNING SET TO UserX;

 

For SQL Tuning Advisor

GRANT ADVISOR TO UserX;

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close