RSS Feed

Method R Discussion Group

On behalf of Michael Erwin...

Has anyone seen or know of an issue, where you do an alter system, set 10046 level 12, and traces are not being generated?

1. The maximum dump file size was exceeded
2. Trace files were removed on operating systems where files aren’t recreated.

From Craig Anderson...

3. There is any issue with the “udump” directory.  For example, if the “udump” directory was deleted after the database was started, if write permissions have been revoked from the directory, or if “udump” is a symbolic link to a directory that does not exist

4. The “udump” directory has never been purged.  Trace files will append to existing trace files if the session information for the current session results in a file name that matches one inthe  file system.  This is fine if the existing file is from the current session, but really confusing if it appends the current session’s trace to an existing file from 3 months ago!

5. The session for some reason does not have ALTER SESSION privilege

6. Trace files are generated only for new sessions, not existing ones. If you want traces for existing sessions, then you have to enumerate them.

One additional reason for 10046 trace files not being generated can be the use of XA to support two phase commits.  If you have connections to an Oracle database using JDBC XA drivers, you cannot turn tracing ON or OFF once the database session has started.  This stems from the database session being under the control of a transaction manager external to Oracle.  The only workaround I have found for this problem is the write a logon trigger for the database account, which turns 10046 tracing on during session startup.  These triggers must belong to the SYS account.  These triggers will cause tracing of every new session for the designated account, whenoever the triggers are enabled.  Following are samples of logon and logoff triggers for the SCOTT account:

CREATE OR REPLACE TRIGGER SYS.START_TRACE_SESSIONS_SCOTT
  AFTER LOGON ON SCOTT.SCHEMA
DECLARE
  AUD_ID  VARCHAR2 (256);
  S_ID    NUMBER;
BEGIN
  SELECT SYS_CONTEXT ('USERENV','SESSIONID') INTO
      AUD_ID FROM DUAL;
  SELECT SID INTO S_ID FROM V_$SESSION
      WHERE AUDSID=AUD_ID AND AUDSID != 0;
  -- DISALLOW TRACING OF SESSIONS OWNED BY SYS
  IF S_ID IS NOT NULL THEN
      SYS.DBMS_SUPPORT.START_TRACE (WAITS=>TRUE,
        BINDS=>FALSE);
  END IF;
END;
/

CREATE OR REPLACE TRIGGER SYS.STOP_TRACE_SESSIONS_SCOTT
    BEFORE LOGOFF ON SCOTT.SCHEMA
BEGIN
  SYS.DBMS_SUPPORT.STOP_TRACE;
END;
/