NLSRTL 11.2.0.3.0 Production
Oracle Database 11g Enterprise Edition 11.2.0.3.0 64bit Production
PL/SQL 11.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: 11.2.0.3.0 Production
I have a database user U1 and the user has been granted the role DBA
connect u1/<password>@db
SELECT * FROM user_role_privs;
The output is:
U1 CONNECT NO YES NO
U1 DBA NO YES NO
U1 EXECUTE_CATALOG_ROLE NO YES NO
U1 EXEC_SYS_PACKAGES_ROLE NO YES NO
If I execute with the same user U1 call to the package DBMS_MONITOR it works fine:
begin
DBMS_MONITOR.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE);
end;
/
If I try to make a call to the package DBMS_MONITOR within the body of a function/procedure/package owned by the same user U1, it fails to compile with the error:
create or replace procedure trace_proc
is
begin
DBMS_MONITOR.SESSION_TRACE_ENABLE(waits => FALSE, binds => TRUE);
end trace_proc;
/
The compilation fails with error: [Error] PLS-00201 (4: 3): PLS-00201: identifier 'DBMS_MONITOR' must be declared
I tried the same by prefixing the package with "SYS" but it still fails to compile.
Can you please give some clues why is it happening?
Thank you