My Oracle Support Banner

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

Last updated on NOVEMBER 21, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 8.1.7 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 Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
Checked for relevance on 28-May-2013


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
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;';

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



To view full details, 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 a vibrant support community of peers and Oracle experts.