If you have access to the source code, it should be possible to execute a SQL statement like this to enable a 10046 extended trace at level 12 (waits and binds):
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
If you do not have access to the source code, you can remotely enable an extended trace for another session with:
* Logon trigger that executes the above SQL statement for a specific user/application.
* System wide 10046 extended trace (change SESSION in the above SQL statement to SYSTEM).
* Session specific 10046 trace remotely enabled using:
EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,12,'');
-or-
EXEC SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION(SID,SERIAL#,WAITS=>TRUE,BINDS=>FALSE);
-or-
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>sid,SERIAL_NUM=>serial#,WAITS=>TRUE,BINDS=>TRUE)
-or-
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,TRUE);
The SID and SERIAL# may be retrieved from V$SESSION.
The SYS.DBMS_SYSTEM.SET_EV method is unsupported, but it works fine on older versions of Oracle where the other methods do not exist.
Oracle will create the trace files in the server's udump directory.
Before executing any of the above (especially the system wide method), determine how to disable a 10046 trace, depending on how it was started:
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
-or-
EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,0,'');
-or-
EXEC SYS.DBMS_SUPPORT.STOP_TRACE_IN_SESSION(SID,SERIAL#);
-or-
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,FALSE);
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
