DB level collecting an traces.

Collect SID and Serial and enable the trace.

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>3258, serial#=>10207, sql_trace=>TRUE)

PL/SQL procedure successfully completed.

SQL> SELECT p.tracefile FROM   v$session s  JOIN v$process p ON s.paddr = p.addr WHERE  s.sid = 3258;

TRACEFILE

--------------------------------------------------------------------------------

/u02/app/oracle/diag/rdbms/ebsts1/EBSTS11/trace/EBSTS11_ora_233861.trc

------------------------------------------------------------------------------------------------------------

1. Create Tigger for particular user.

2. enable the trace and collect the trace 

3. Disable the traces.

CREATE OR REPLACE TRIGGER ON_MY_SCHEMA_LOGIN

AFTER LOGON ON DATABASE

WHEN ( USER = 'XXBPC_INTF' )

BEGIN  execute immediate 'ALTER SESSION SET TRACEFILE_IDENTIFIER = "XXBPC_INTF_TRACE"';

execute immediate 'alter session set events ''10046 trace name context forever, level 12''';

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

 

alter trigger ON_MY_SCHEMA_LOGIN enable;

 

 alter trigger ON_MY_SCHEMA_LOGIN disable;