Necessary privileges to use dbms_sqltune

Laury

Hi,

I am working with Oracle 19c.

Does someone what privileges I need for a common user (not SYS or SYSTEM) to run this procedure:

set serveroutput on

declare

l_sql_tune_task_id varchar2(100);

begin

l_sql_tune_task_id := dbms_sqltune.create_tuning_task (

sql_id => 'g0ujpp7y5s1wp',

scope => dbms_sqltune.scope_comprehensive,

time_limit => 60,

task_name => 'g0ujpp7y5s1wp_tuning_task',

description => 'tuning task for statement g0ujpp7y5s1wp.'

);

 dbms_output.put_line('l_sql_tune_task_id (task name): ' || l_sql_tune_task_id);

end;

/

I end up with this error:

ORA-13773: insufficient privileges to select data from the cursor cache

It is solved if I grant select any dictionary to the user. But I would like to avoid it.

Kind Regards

Arsalan Dehghanisariyarghan

Hi,

Good to post full version number and platform info. This can be useful in case of bugs affecting a specific version/platform.

Cheers

Arsalan

saratpvv

@Laury

I believe without sys privilege's you cannot run this procedure  dbms_sqltune.create_tuning_task

you need to give Advisor,dbms_sqltune grants to common user. Pertain to grants there is no new features are associated to 19c i believe.

Laury

Hi,

@Arsalan Dehghanisariyarghan:

Version Oracle RDBMS EE 19.3

Are there so big difference between 12c and 19?

@saratpvv:

This is what I am wondering. I have not find clear information about it in the Oracle documentation. I should be able to able to perform it with a dedicated user (for the sake of duty separation).

Any other idea?

Kind Regards

Arsalan Dehghanisariyarghan

https://community.oracle.com/tech/developers/discussion/comment/16802437#Comment_16802437

Hi Laury,

Thanks for your update. I search the MOS site and currently no documented database bugs are linked to "ORA-13773" on DBMS_SQLTUNE. So I think you need to execute the commands below:

CONNECT / AS SYSDBA
grant select on sys.DBA_HIST_BASELINE  to <your_user> ;
grant select on sys.DBA_HIST_SQLTEXT to <your_user> ;
grant select on sys.DBA_HIST_SQLSTAT to <your_user> ;
grant select on sys.DBA_HIST_SQLBIND to <your_user> ;
grant select on sys.DBA_HIST_OPTIMIZER_ENV to <your_user> ;
grant select on sys.DBA_HIST_SNAPSHOT to <your_user> ;
grant SELECT on V$SQL_BIND_CAPTURE to <your_user> 
grant advisor to <your_user>;

Check again after these permissions have been granted

HTH

Cheers

Arsalan

Arsalan Dehghanisariyarghan

In addition, you can also do the following;

CREATE ROLE RUNADVISOR;
grant ​select on sys.DBA_HIST_BASELINE  to "RUNADVISOR" ;
grant ​select on sys.DBA_HIST_SQLTEXT to "RUNADVISOR" ;
grant ​select on sys.DBA_HIST_SQLSTAT to "RUNADVISOR" ;
grant ​select on sys.DBA_HIST_SQLBIND to "RUNADVISOR";
grant ​select on sys.DBA_HIST_OPTIMIZER_ENV to "RUNADVISOR" ;
grant ​select on sys.DBA_HIST_SNAPSHOT to "RUNADVISOR" ;
grant ​select on V$SQL_BIND_CAPTURE to "RUNADVISOR" ;
grant ​advisor to "RUNADVISOR";
grant "RUNADVISOR" TO "<USER>" WITH ADMIN OPTION;

Laury

Hi,

Having a user TEST_DATA with these privileges:

grant RESOURCE to TEST_DATA;                                                 

grant CONNECT to TEST_DATA;  

grant execute on dbms_stats to TEST_DATA;

grant select on v_$session to TEST_DATA;

create role RUNADVISOR;

grant select on sys.DBA_HIST_BASELINE to RUNADVISOR;

grant select on sys.DBA_HIST_SQLTEXT to RUNADVISOR;

grant select on sys.DBA_HIST_SQLSTAT to RUNADVISOR;

grant select on sys.DBA_HIST_SQLBIND to RUNADVISOR;

grant select on sys.DBA_HIST_OPTIMIZER_ENV to RUNADVISOR;

grant select on sys.DBA_HIST_SNAPSHOT to RUNADVISOR;

grant select on V$SQL_BIND_CAPTURE to RUNADVISOR;

grant advisor to RUNADVISOR;

grant RUNADVISOR to TEST_DATA with admin option;

I still get the same error.

Any other idea?

Kind Regards

Arsalan Dehghanisariyarghan

Hi,

Thanks for your update.

Please try again after granting the privilege below:

SQL> grant select on v_$sqlarea_plan_hash to test_data;

Cheers

Arsalan

Arsalan Dehghanisariyarghan

In addition, are you seeing any other errors than these in the alert log too?

Laury

Hi Arsalan,

If I create a TEST_DATA user with these privileges:

grant RESOURCE to TEST_DATA;                                                 

grant CONNECT to TEST_DATA;  

grant execute on dbms_stats to TEST_DATA;

grant select on v_$session to TEST_DATA;

create role RUNADVISOR;

grant select on sys.DBA_HIST_BASELINE to RUNADVISOR;

grant select on sys.DBA_HIST_SQLTEXT to RUNADVISOR;

grant select on sys.DBA_HIST_SQLSTAT to RUNADVISOR;

grant select on sys.DBA_HIST_SQLBIND to RUNADVISOR;

grant select on sys.DBA_HIST_OPTIMIZER_ENV to RUNADVISOR;

grant select on sys.DBA_HIST_SNAPSHOT to RUNADVISOR;

grant select on V$SQL_BIND_CAPTURE to RUNADVISOR;

grant select on V_$SQLAREA_PLAN_HASH to test_data;

grant advisor to RUNADVISOR;

grant RUNADVISOR to TEST_DATA;

I still get the dame error when running dbms_sqltune.create_tuning_task:

ERROR at line 1:

ORA-13774: insufficient privileges to select data from the workload repository

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 589

ORA-06512: at "SYS.DBMS_SQLTUNE", line 825

ORA-06512: at line 4

I am wondering if this is somewhere documented. I was unable to find it.

Any other idea?

Kind Regards.