Forum Stats

  • 3,780,546 Users
  • 2,254,409 Discussions
  • 7,879,377 Comments

Discussions

Necessary privileges to use dbms_sqltune

Laury
Laury Member Posts: 1,657 Silver Badge

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

Tagged:

Answers

  • Arsalan Dehghanisariyarghan
    Arsalan Dehghanisariyarghan Member Posts: 366 Bronze Trophy

    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
    saratpvv Member Posts: 910 Gold Badge

    @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
    Laury Member Posts: 1,657 Silver Badge

    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
    Arsalan Dehghanisariyarghan Member Posts: 366 Bronze Trophy
    edited Jul 16, 2021 7:47AM

    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
    Arsalan Dehghanisariyarghan Member Posts: 366 Bronze Trophy

    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
    Laury Member Posts: 1,657 Silver Badge

    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
    Arsalan Dehghanisariyarghan Member Posts: 366 Bronze Trophy

    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
    Arsalan Dehghanisariyarghan Member Posts: 366 Bronze Trophy

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

  • Laury
    Laury Member Posts: 1,657 Silver Badge

    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.