3 Replies Latest reply: Aug 20, 2012 9:16 AM by PuraVidaOTN RSS

    Find if events 10046 and 00942 are set and at what level it is set

    PuraVidaOTN
      On 11.2.0.1 database on Windows x64 and Linux x64, I issued the following queries.
      I have two questions:

      (1) Why I cannot find if event 10046 is set this way, how can I find it?
      (2) Event 942 is set at level 3, why does it show level 1?

      Thanks a lot.

      SQL> set serveroutput on size unlimited

      SQL> alter system set events '10046 trace name context forever, level 12';
      System altered.

      SQL> declare
      2 event_level number;
      3 begin
      4 for i in 1..10999 loop
      5 sys.dbms_system.read_ev(i,event_level);
      6
      7 if (event_level > 0) then
      8 dbms_output.put_line('Event '||to_char(i)||' set at level '||
      9 to_char(event_level));
      10 end if;
      11 end loop;
      12 end;
      13 /

      PL/SQL procedure successfully completed.

      SQL> alter system set events '942 trace name ERRORSTACK level 3';

      System altered.

      SQL> declare
      2 event_level number;
      3 begin
      4 for i in 1..10999 loop
      5 sys.dbms_system.read_ev(i,event_level);
      6
      7 if (event_level > 0) then
      8 dbms_output.put_line('Event '||to_char(i)||' set at level '||
      9 to_char(event_level));
      10 end if;
      11 end loop;
      12 end;
      13 /
      Event 942 set at level 1

      PL/SQL procedure successfully completed.

      SQL> alter system set events '10046 trace name context off';

      System altered.

      SQL> alter system set events '942 trace name ERRORSTACK off';

      System altered.
        • 1. Re: Find if events 10046 and 00942 are set and at what level it is set
          sb92075
          query V$SESSION
          SQL> desc v$session
           Name                            Null?    Type
           ----------------------------------------- -------- ----------------------------
           SADDR                                  RAW(8)
           SID                                  NUMBER
           SERIAL#                             NUMBER
           AUDSID                              NUMBER
           PADDR                                  RAW(8)
           USER#                                  NUMBER
           USERNAME                             VARCHAR2(30)
           COMMAND                             NUMBER
           OWNERID                             NUMBER
           TADDR                                  VARCHAR2(16)
           LOCKWAIT                             VARCHAR2(16)
           STATUS                              VARCHAR2(8)
           SERVER                              VARCHAR2(9)
           SCHEMA#                             NUMBER
           SCHEMANAME                             VARCHAR2(30)
           OSUSER                              VARCHAR2(30)
           PROCESS                             VARCHAR2(24)
           MACHINE                             VARCHAR2(64)
           PORT                                  NUMBER
           TERMINAL                             VARCHAR2(30)
           PROGRAM                             VARCHAR2(48)
           TYPE                                  VARCHAR2(10)
           SQL_ADDRESS                             RAW(8)
           SQL_HASH_VALUE                         NUMBER
           SQL_ID                              VARCHAR2(13)
           SQL_CHILD_NUMBER                        NUMBER
           SQL_EXEC_START                         DATE
           SQL_EXEC_ID                             NUMBER
           PREV_SQL_ADDR                             RAW(8)
           PREV_HASH_VALUE                        NUMBER
           PREV_SQL_ID                             VARCHAR2(13)
           PREV_CHILD_NUMBER                        NUMBER
           PREV_EXEC_START                        DATE
           PREV_EXEC_ID                             NUMBER
           PLSQL_ENTRY_OBJECT_ID                        NUMBER
           PLSQL_ENTRY_SUBPROGRAM_ID                   NUMBER
           PLSQL_OBJECT_ID                        NUMBER
           PLSQL_SUBPROGRAM_ID                        NUMBER
           MODULE                              VARCHAR2(48)
           MODULE_HASH                             NUMBER
           ACTION                              VARCHAR2(32)
           ACTION_HASH                             NUMBER
           CLIENT_INFO                             VARCHAR2(64)
           FIXED_TABLE_SEQUENCE                        NUMBER
           ROW_WAIT_OBJ#                             NUMBER
           ROW_WAIT_FILE#                         NUMBER
           ROW_WAIT_BLOCK#                        NUMBER
           ROW_WAIT_ROW#                             NUMBER
           TOP_LEVEL_CALL#                        NUMBER
           LOGON_TIME                             DATE
           LAST_CALL_ET                             NUMBER
           PDML_ENABLED                             VARCHAR2(3)
           FAILOVER_TYPE                             VARCHAR2(13)
           FAILOVER_METHOD                        VARCHAR2(10)
           FAILED_OVER                             VARCHAR2(3)
           RESOURCE_CONSUMER_GROUP                   VARCHAR2(32)
           PDML_STATUS                             VARCHAR2(8)
           PDDL_STATUS                             VARCHAR2(8)
           PQ_STATUS                             VARCHAR2(8)
           CURRENT_QUEUE_DURATION                    NUMBER
           CLIENT_IDENTIFIER                        VARCHAR2(64)
           BLOCKING_SESSION_STATUS                   VARCHAR2(11)
           BLOCKING_INSTANCE                        NUMBER
           BLOCKING_SESSION                        NUMBER
           FINAL_BLOCKING_SESSION_STATUS                   VARCHAR2(11)
           FINAL_BLOCKING_INSTANCE                   NUMBER
           FINAL_BLOCKING_SESSION                    NUMBER
           SEQ#                                  NUMBER
           EVENT#                              NUMBER
           EVENT                                  VARCHAR2(64)
           P1TEXT                              VARCHAR2(64)
           P1                                  NUMBER
           P1RAW                                  RAW(8)
           P2TEXT                              VARCHAR2(64)
           P2                                  NUMBER
           P2RAW                                  RAW(8)
           P3TEXT                              VARCHAR2(64)
           P3                                  NUMBER
           P3RAW                                  RAW(8)
           WAIT_CLASS_ID                             NUMBER
           WAIT_CLASS#                             NUMBER
           WAIT_CLASS                             VARCHAR2(64)
           WAIT_TIME                             NUMBER
           SECONDS_IN_WAIT                        NUMBER
           STATE                                  VARCHAR2(19)
           WAIT_TIME_MICRO                        NUMBER
           TIME_REMAINING_MICRO                        NUMBER
           TIME_SINCE_LAST_WAIT_MICRO                   NUMBER
           SERVICE_NAME                             VARCHAR2(64)
           SQL_TRACE                             VARCHAR2(8)
           SQL_TRACE_WAITS                        VARCHAR2(5)
           SQL_TRACE_BINDS                        VARCHAR2(5)
           SQL_TRACE_PLAN_STATS                        VARCHAR2(10)
           SESSION_EDITION_ID                        NUMBER
           CREATOR_ADDR                             RAW(8)
           CREATOR_SERIAL#                        NUMBER
           ECID                                  VARCHAR2(64)
          • 2. Re: Find if events 10046 and 00942 are set and at what level it is set
            rp0428
            >
            (1) Why I cannot find if event 10046 is set this way, how can I find it?
            >
            Short of using ORADEBUG I don't know of a way.

            See the reply by Charles Hooper in this thread from last week
            how to check if there is any active 10053 or 10046 trace
            • 3. Re: Find if events 10046 and 00942 are set and at what level it is set
              PuraVidaOTN
              I cannot find events information on v$session and I do not think it has the information.
              as rp0428 said, use oradebug which has both session events and system events, levels, etc
              exactly as what you said.