My Oracle Support Banner

ORA-1031 from DBMS_SQLTUNE.EXECUTE_TUNING_TASK after upgrading to 11g (Doc ID 1082465.1)

Last updated on FEBRUARY 13, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.1 [Release 11.1 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

ORA-1031 is seen in SQL Tuning Task on 11.1 or 11.2 when running DBMS_SQLTUNE
to analyze SQL statement run by another user in the database.
On 10g the same script used to work fine. Following is an example of such a script:

Connect to sqlplus using a user who has sysdba privileges
--
drop user oemview cascade;
create user oemview identified by oemview;
grant create session to oemview;
grant select any dictionary to oemview;
grant oem_advisor to oemview;
grant administer any sql tuning set to oemview;
grant analyze any to oemview;
grant alter any sql profile to oemview;
grant create any sql profile to oemview;
grant drop any sql profile to oemview;
grant execute on dbms_sqltune to oemview;
--
conn <User_name>/<Password>;
select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
--
conn oemview/oemview;
set serveroutput on;
var param varchar2(1000);
DECLARE
 cursor test_cursor is
 select sql_id from v$sql where sql_text like 'select e.ename, d.dname%';
 pbuf varchar2(4000);
 psqlid varchar2(1000);
BEGIN
 open test_cursor;
 loop
  fetch test_cursor into psqlid;
  exit when test_cursor%notfound;
 end loop;
 close test_cursor;
 :param := dbms_sqltune.create_tuning_task(sql_id=>psqlid);
 dbms_output.put_line(:param);
 dbms_sqltune.execute_tuning_task(task_name=>:param);
END;
/
--
select * from dba_advisor_findings where task_name = :param;


Executing above script will show ORA-1031:

SQL> select * from dba_advisor_findings where task_name = :param;

OWNER    TASK_ID    TASK_NAME  TYPE MESSAGE
-------- ---------- ---------- ----------------------------------------
OEMVIEW          42    TASK_42 ERROR ORA-01031: insufficient privileges

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.