The Oracle SQL Trace facility is what you use here. In your session, issue the following:
ALTER SESSION SET sql_trace=TRUE;
Then run your application. At the end, set SQL_TRACE to FALSE. This will create a trace file in the USER_DUMP_DEST directory.
If you want to start a trace in another user's session, you'll have to determine that session's SID and SERIAL# values as can be seen in V$SESSION. Then sign on to SQL*Plus as SYSTEM and issue the following:
To stop the trace, issue the following:
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(sid,serial);Details on the SQL Trace package can be found in the Oracle documentation.
This was first published in May 2007