Oracle provides and documents a huge load of possibilities and functions for nearly every purpose. For me it is impossible to know all of them. Even to know such an area exists is hard.
But still sometimes these functions Oracle does not document for customers purpose seems to be more attractive than those officially available.
One of these attractive packages is DBMS_SYSTEM. You will not find any description of this package in the official Oracle documentation. There are some small traces available, but nothing really useful.
Oracle also have quite clear words about using such unofficial, and hidden, packages:
In How To Edit, Read, and Query the Alert.Log [ID 1072547.1] you can read:
For some reasons I'm one of those which likes to play with forbidden toys like these. I found a procedure in DBMS_SYSTEM which changed behavior slightly in 11gR2 (I've tested with 184.108.40.206 patchset - so maybe other patchsets behave quite different!)
I'm talking about DBMS_SYSTEM.READ_EV. This procedure more or less calls directly the internal C-routine READ_EV_ICD. Common sense is, it should return the level of an event given. This is also quite true, just for one exception: the probably most known event in Oracle world: 10046 - or sql_trace.
My test-script here
VARIABLE lev number SET AUTOPRINT ON EXECUTE sys.dbms_system.read_ev(10046, :lev) ALTER SESSION SET EVENTS '10046 trace name context forever, level 8'; SELECT sql_trace, sql_trace_waits, sql_trace_binds FROM v$session WHERE sid=userenv('sid') EXECUTE sys.dbms_system.read_ev(10046,:lev) oradebug setmypid oradebug eventdump session
gives the expected result in one of my 10g test DBs:
@test_read_ev.sql PL/SQL procedure successfully completed. LEV ---------- 0 Session altered. PL/SQL procedure successfully completed. LEV ---------- 8 Statement processed. 10046 trace name CONTEXT level 8, forever
but an unexpected result in my 220.127.116.11 test DB:
@test_read_ev.sql PL/SQL procedure successfully completed. LEV ---------- 0 Session altered. PL/SQL procedure successfully completed. LEV ---------- 0 Statement processed. sql_trace level=8
I guessed events with an ALIAS might be excluded somehow, but other tests with
DB_FILES==10222shows this special behavior only with sql_trace.
My todays conclusion is easy:
If it's not there for you, don't guess you can play with it without any consequences.