Trace and Alert log files on Oracle Database Server (11g)

Trace and Alert log files

Root ‘log dump’ location:
[sql]select value from v$parameter where name = ‘background_dump_dest’;[/sql]

Find the trace file path of current session use the following PL/SQL snippet:
[sql]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 v$process.addr = v$session.paddr
where = ‘user_dump_dest’
and = ‘db_name’
and v$session.audsid = sys_context(‘userenv’, ‘sessionid’);[/sql]

This would output something similar to:

The ALERT file would be:

You could also create a DB table that would keep the alert log file and you do a select
to view a list of the logged items.


Credits to: Rene Nyffenegger