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;