3 Replies Latest reply: Jul 16, 2009 2:54 PM by Srini Chavali-Oracle RSS

    help with "alter system set events = '1000 trace name ERRORSTACK level 3'"


      I am trying to find out wich sql code is creating the error ORA-01000 in my application (database release is I have read this http://www.orafaq.com/node/758 and there they suggest doing this:
      Sometimes you will need to get a full stack trace of the problem by setting a trace for ORA-1000 as in:
      alter system set events = '1000 trace name ERRORSTACK level 3'
      alter session set events = '1000 trace name ERRORSTACK level 3'
      Then set open_cursors to a low enough number to catch the open cursor voilator in your net.
      I have no idea about this traces, so these are my questions:
      1. Do those commands force a trace every time the ORA-01000 errors rises in every session? (I have forced the ORA-01000 error in one sqplusl session after I wrote "alter session set events = '1000 trace name ERRORSTACK level 3'" in another sqlplus session)
      2. What does level 3 mean?
      3. I see that in the dump/user directory I have several trace files, how can I know which error traces are enabled in my db?
      4. How do I disable the traces once I find out to solve the problem?
      5. How do I identify in the traces the ORA-1000 error?

      Thanks in advance.
        • 1. Re: help with "alter system set events = '1000 trace name ERRORSTACK level 3'"
          1 alter session is alter session, so pertains to the current session only. Obviously 'alter system set events=' would hit everyone.
          Your session will open a trace file as soon as it hits ora-1000. The other session will not open a trace file as you didn't enable the event on an instance-wide basis.
          2 Usually the depth of the stack, but there are different numbers for every level. IIRC 3 also dumps the statement invoking the error.
          3 using dbms_system.read_ev, you can just loop from 1 to 20000 and call read_ev for everyone of them.
          the command is a bit strange. Usually they end with ',context forever' and ',context off' switches them off. When you set them on session level you don't need to worry.
          Please note open_cursors is a session limit, it is not a system limit.
          5 just use grep or find.


          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: help with "alter system set events = '1000 trace name ERRORSTACK level 3'"
            Thanks for all the answers.

            What am I doing bad? I have tried ti find out what traces are enabled, and there seems to be none.
            SQL> r
              1  DECLARE
              2     OUT   BINARY_INTEGER;
              3  BEGIN
              4     DBMS_OUTPUT.put_line ('Init');
              6     FOR i IN 1 .. 20000
              7     LOOP
              8        SYS.DBMS_SYSTEM.read_ev (i, OUT);
             10        IF (OUT <> 0)
             11        THEN
             12           DBMS_OUTPUT.put_line ('i: ' || i || '; ' || OUT);
             13        END IF;
             14     END LOOP;
             16     DBMS_OUTPUT.put_line ('End');
             17* END;
            But, otherwise, there are trc files which have been modfiied recently? I seems as if this procedure can't detect all the tracles enabled:
            (oracle)mano1re(principal)$ ls -lt *.trc | more
            -rw-r-----   1 oracle   dba       348294 jul 14 18:35 reflex_ora_11706.trc
            -rw-r-----   1 oracle   dba       997820 jul  9 16:32 reflex_ora_15766.trc
            -rw-r-----   1 oracle   dba       399571 jul  3 15:23 reflex_ora_25955.trc
            -rw-r-----   1 oracle   dba      1637069 jun  9 15:34 reflex_ora_19742.trc
            PL/SQL procedure successfully completed.
            • 3. Re: help with "alter system set events = '1000 trace name ERRORSTACK level 3'"
              Srini Chavali-Oracle
              Pl see if these MOS Docs help

              75713.1 - Important Customer information about using Numeric Events
              218105.1 - Introduction to ORACLE Diagnostic EVENTS
              18591.1 - OERR: ORA-1000 maximum open cursors exceeded