Page History

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
/