In order to take advantage of all tuning features, each user must have a specific set of permissions. The code below creates a role with all required permissions. To create the required role, execute the SQL against the target data source, modified according to the specific needs of your site:
/* Create the role */ CREATE ROLE SQLTUNING NOT IDENTIFIED
/ GRANT SQLTUNING TO "CONNECT"
/ GRANT SQLTUNING TO SELECT_CATALOG_ROLE
/ GRANT ANALYZE ANY TO SQLTUNING
/ GRANT CREATE ANY OUTLINE TO SQLTUNING
/ GRANT CREATE ANY PROCEDURE TO SQLTUNING
/ GRANT CREATE ANY TABLE TO SQLTUNING
/ GRANT CREATE ANY TRIGGER TO SQLTUNING
/ GRANT CREATE ANY VIEW TO SQLTUNING
/ GRANT CREATE PROCEDURE TO SQLTUNING
/ GRANT CREATE SESSION TO SQLTUNING
/ GRANT CREATE TRIGGER TO SQLTUNING
/ GRANT CREATE VIEW TO SQLTUNING
/ GRANT DROP ANY OUTLINE TO SQLTUNING
/ GRANT DROP ANY PROCEDURE TO SQLTUNING
/ GRANT DROP ANY TRIGGER TO SQLTUNING
/ GRANT DROP ANY VIEW TO SQLTUNING
/ GRANT SELECT ON SYS.V_$SESSION TO SQLTUNING
/ GRANT SELECT ON SYS.V_$SESSTAT TO SQLTUNING
/ GRANT SELECT ON SYS.V_$SQL TO SQLTUNING
/ GRANT SELECT ON SYS.V_$STATNAME TO SQLTUNING
/
Once complete, you can assign the role to users who will be running tuning jobs:
/* Create a sample user*/ CREATE USER TUNINGUSER IDENTIFIED BY VALUES
'05FFD26E95CF4A4B'
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS
PROFILE DEFAULT ACCOUNT
UNLOCK
/ GRANT SQLTUNING TO TUNINGUSER
/ ALTER USER TUNINGUSER DEFAULT ROLE SQLTUNING
/