Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will be learning how to find the trace file of a session. We can do this in 3 different ways.
1. It can be learned from TOAD or SQL*Plus by running the following script.
1 2 3 4 5 6 7 8 9 10 11 12 13 | select u_dump.value || '/' || db_name.value || '_ora_' || v$process.spid || nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc' "Trace File" from v$parameter u_dump cross join v$parameter db_name cross join v$process join v$session on (addr = paddr) where u_dump.name = 'user_dump_dest' and db_name.name = 'db_name' and v$session.audsid=sys_context('userenv','sessionid'); |
2. It can be learned from TOAD or SQL*Plus by running the following script.
1 2 3 4 5 | SQL> SELECT TRACEFILE FROM V$PROCESS JOIN V$SESSION ON (ADDR=PADDR) WHERE V$SESSION.SID = SYS_CONTEXT ('USERENV', 'SID'); TRACEFILE -------------------------------------------------------------------------------- /oracle/diag/rdbms/onurdb/ONURDB/trace/ONURDB_ora_31069.trc |
3. It can be learned from SQL*Plus as follows.
1 2 3 4 | SQL> oradebug setmypid Statement processed. SQL> oradebug tracefile_name /oracle/diag/rdbms/onurdb/ONURDB/trace/ONURDB_ora_31069.trc |
