Database Reference
In-Depth Information
Based on the information provided here, it's possible to write a query like the one you can find in the
map_session_to_tracefile.sql
script. Such a query is useful only in version 10.2, though. As of version 11.1,
it's much easier to query either the
v$diag_info
or
v$process
views, as shown in the following examples:
SQL> SELECT value
2 FROM v$diag_info
3 WHERE name = 'Default Trace File';
VALUE
--------------------------------------------------------------------
/u00/app/oracle/diag/rdbms/dba111/DBA111/trace/DBA111_ora_23731.trc
SQL> SELECT p.tracefile
2 FROM v$process p, v$session s
3 WHERE p.addr = s.paddr
4 AND s.sid = sys_context('userenv','sid');
TRACEFILE
--------------------------------------------------------------------
/u00/app/oracle/diag/rdbms/dba111/DBA111/trace/DBA111_ora_23731.trc
Note that the
v$diag_info
view provides information for the current session only.
DO traCe FILeS CONtaIN CONFIDeNtIaL INFOrMatION?
by default, trace files aren't accessible to everyone. this is good because they may contain confidential
information. in fact, both the sQl statements, which may contain data (literals), and the values of bind variables
end up in trace files. basically, this means that every piece of data stored in the database could be written to a
trace file as well.
for example, on unix/linux database servers, the trace files belong to the user and group running the database
engine binaries and by default have 0640 as privileges. in other words, only users in the same group as the user
running the database engine can read the trace files.
there is, however, really no good reason for preventing those that already have access to the data in the database
from having access to the trace files, if they're required to perform a task. in fact, from a security point of view,
trace files are a useful source of information only for those without access to the database. for this reason, the
database engine provides an undocumented initialization parameter named
_trace_files_public
. per default,
it's set to
FALSE
. if set to
TRUE
, the trace files are made readable to everyone having access to the database server.
because the initialization parameter isn't dynamic, a database instance bounce is necessary to change its value.
be also aware that in a 12.1 multitenant environment, the
_trace_files_public
initialization parameter can't be
set at the pdb level.
for example, on unix/linux with
_trace_files_public
set to
TRUE
, the default privileges become 0644. this
way, all users that have access to the database server can read the trace files.
from a security point of view, setting the
_trace_files_public
initialization parameter to
TRUE
is problematic only
when the access to the database server isn't restricted. in providing simple access to the trace files, it's also common
to share the directories containing them via smb, via nfs, or through an http interface. in any case, and for obvious