Enabling sql trace in OC4J

Create a new dummy user in DB say test. Create a Connection in the
data-source.xml file with the min-connections="1" and max-connections="1".

On the DB side create a login trigger ,
CREATE OR REPLACE TRIGGER test_user_trg_after_logon
AFTER LOGON ON DATABASE
BEGIN
IF (user = 'TEST') THEN
DBMS_SESSION.SET_SQL_TRACE(true);
END IF;
END;

The sql trace files will be generated in the USER_DUMP_DESTINATION for the
database.

To check the sql's run the command
tkprof sys=no

The above procedure will not give you the bind variables. To get the bind variable , please
perform the following steps.

cd $OH/rdbms/admin/
connect / AS SYSDBA
dbmssupp.sql
SQL> GRANT execute ON dbms_support TO ;
SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;

Create the stored procedure ,

CREATE OR REPLACE TRIGGER test_user_trg_after_logon
AFTER LOGON ON DATABASE
BEGIN
IF (user = 'SCOTT') THEN
dbms_support.start_trace (binds=>true);
END IF;
END;

The above steps will have the bind variables in the trace files.

To disable the tracing , perform the step as ,
drop trigger test_user_trg_after_logon;

Set the event 942 to capture ORA-942 errors

alter system set events '942 trace name errorstack level 12';
Try to find the udump directory

show parameters user_dump
user_dump_dest string /home/ias/infra101202/admin/ias1012/udump
cd /home/ias/infra101202/admin/ias1012/udump and find out the latest file.

Use SYS.DBMS_SYSTEM.SET_EV() procedure. Here is the specification for this procedure:

PROCEDURE SET_EV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SI BINARY_INTEGER IN
SE BINARY_INTEGER IN
EV BINARY_INTEGER IN
LE BINARY_INTEGER IN
NM VARCHAR2 IN
SID: V$SESSION.SID
SE: V$SESSION.SERIAL#
EV: Event number. For example:
10046: SQL traces.
10053: Optimizer traces.
NNN : ORA-NNN errors.
65535: IMMEDIATE traces.
LE: Event level. For Event 10046 events:
0: Disable event.
1: PARSE, FETCH, EXEC, EXECUTION PLAN
4: Level 1 + BINDS
8: Level 1 + WAITS
12: Level 4 + Level 8
NM: Event name. For example:
ERRORSTACK.......: For error stack traces.
PROCESSSTATE...: For process states
SYSTEMSTATE.......: For System states.
''..................................: For CONTEXT FOREVER.
Sample:
Dumps PROCESSSTATE trace IMMEDIATELY in LEVEL 10:

SQL> exec dbms_system.set_ev(8,1056,65535,10,'PROCESSSTATE');
Dumps ERRORSTACK trace in LEVEL 3 on ORA-942 error:

SQL> exec dbms_system.set_ev(8,1060,942,3,'ERRORSTACK');
Dumps Event 10046 trace in LEVEL 8 for CONTEXT FOREVER:

SQL> exec dbms_system.set_ev(8,1060,10046,8,'');