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

Last updated on JUNE 30, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1 - Release: 11.1 to 11.2
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:

conn /as sysdba;
--
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 scott/tiger;
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


Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms