User Trace

In order to trace all the actions by a specific user we can user below log on trigger:

CREATE OR REPLACE TRIGGER myuser_logon_trace
AFTER LOGON ON MYUSER.SCHEMA
BEGIN
execute immediate 'alter session set tracefile_identifier="MYUSER"';
DBMS_SESSION.SET_SQL_TRACE(TRUE);
END;
/

Now, we can find the output of the trace at "user_dump_dest" with "MYUSER" in the name.

Tracing a specific ORA error

To trace a specific ORA error I am using below scenario:

One of out users complained us about below error while running a third party application:
"ORA-01775: looping chain of synonyms"

We have a 4 node RAC and we went through all 4 alert logs but could not find it on alert logs.
Then we have done below on "node 1":
Start trace:
SQL> alter system set events '1775 trace name ERRORSTACK level 3';

Then, told the user to connect to "node 1" [using SID & IP in TNS ] repeat the action caused the error.

Now on alert log on "node 1" we found below entry:

Sat Mar 05 11:59:31 2011
Errors in file /u01/app/oracle/diag/rdbms/dw/dw1/trace/dw1_ora_30788.trc:
ORA-01775: looping chain of synonyms

From the trace file found in the alert log we point out the query & object responsible for the error.
In this case we found that a synonym pointing a table but the table was not in dba_objects.

Now,on "node 1" stop trace:
SQL> alter system set events '1775 trace name errorstack off';

More about trace:
http://toddlerdba.blogspot.com/2011/01/session-trace.html
https://netfiles.uiuc.edu/jstrode/www/oraelmt/trace_using_parameters.html

Session Trace

Follow steps below to trace the current session or other running session:

1. Find the session info[for current session use v$mystat to get current session info]:
select a.sid, a.serial#, b.spid, a.username
from v$session a, v$process b
where a.username = '[USERNAME]'
and a.sid = [session id]
and a.PADDR = b.ADDR;

2. Start the trace[in this case event = 10046,trace level=12], trace file will be created at user_dump_dest as [DBNAME]_ora_[SPID].trc :
exec dbms_system.set_ev(&sid,&serial,10046,12,'');

3. stop the trace :
exec dbms_system.set_ev(&sid,&serial,10046,0,'');

4. convert the trace to readable format [ommit sys=no is you want sys queries in output file] :
tkprof [trace file name] [output file name] sys=no

**Trace Levels:
0 - No trace. Like switching sql_trace off.
2 - The equivalent of regular sql_trace.
4 - The same as 2, but with the addition of bind variable values.
8 - The same as 2, but with the addition of wait events.
12 - The same as 2, but with both bind variable values and wait events.

**Event Numbers:
10046: enable SQL statement timing
10053: CBO Enable optimizer trace

List of Trace Events:
https://netfiles.uiuc.edu/jstrode/www/oraparm/events.html
http://priitp.wordpress.com/oracle-11g-trace-events/

More about Trace:
http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php