ORA-1031 While Executing DBMS_SESSION Through Procedure (Doc ID 208234.1)

Last updated on NOVEMBER 22, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.2.0.3 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.
Checked for relevance on 28-May-2013


Symptoms

A user executing the package SYS.DBMS_SESSION through a procedure encounters the
error ORA-1031 although it has been granted the EXECUTE privilege on the package directly

 

create user usr1 identified by usr1 ;
grant connect to usr1 ;
grant resource to usr1 ;

grant execute on sys.dbms_session to usr1 ;

conn usr1/usr1

create or replace PROCEDURE test IS
BEGIN
execute immediate 'alter session set timed_statistics=true';
execute immediate 'begin dbms_session.set_sql_trace(true); end;';
execute immediate 'alter session set timed_statistics=false';
execute immediate 'begin dbms_session.set_sql_trace(false); end;';
END;
/


SQL> execute test

BEGIN test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 126
ORA-06512: at line 1
ORA-06512: at "USR1.TEST", line 4
ORA-06512: at line 1

 

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