Database Reference
In-Depth Information
■
the
ALTER SESSION
privilege required to execute the previous trigger can't be granted through a role. instead,
it has to be granted directly to the user executing the trigger.
Note
Another approach is to add code enabling SQL trace directly in the application. Some kind of parameterization
triggering that code would need to be added as well. A command-line parameter for a fat-client application or an
additional HTTP parameter for a web application are examples of this.
Timing Information in Trace Files
The
timed_statistics
initialization parameter, which can be set to either
TRUE
or
FALSE
, controls the availability of
timing information such as the elapsed time and CPU time in the trace files. If it's set to
TRUE
, timing information is
added to the trace files. If it's set to
FALSE
, they should be missing; however, depending on the platform you're working
on, they could be partially available as well. The default value of
timed_statistics
depends on another initialization
parameter:
statistics_level
. If
statistics_level
is set to
basic
,
timed_statistics
defaults to
FALSE
. Otherwise,
timed_statistics
defaults to
TRUE
.
Generally speaking, if timing information isn't available, the trace files are useless. So, before enabling SQL trace,
make sure
timed_statistics
is set to
TRUE
. You can do this, for example, by executing the following SQL statement:
ALTER SESSION SET timed_statistics = TRUE
DYNaMIC INItIaLIZatION paraMeterS
some initialization parameters are static, and others are dynamic. When they're dynamic, it means they can be
changed without bouncing the database instance. among the dynamic initialization parameters, some of them can
be changed only at the session level, some only at the system level, and others at the session and system levels. to
change an initialization parameter at the session and system levels, you use the
ALTER SESSION
and
ALTER SYSTEM
statements, respectively. initialization parameters changed at the system level take effect immediately or only for
sessions created after the modification. the
v$parameter
view, or more precisely the
isses_modifiable
and
issys_modifiable
columns, provide information about which situation an initialization parameter can be changed in.
Limiting the Size of Trace Files
Usually, you're not interested in limiting the size of trace files. If it's necessary to do so, however, it's possible to set at
the session or system level the
max_dump_file_size
initialization parameter. A numerical value followed by a
K
or
M
suffix specifies, in kilobytes or megabytes, the maximum trace file size. If no limit is wanted, as shown in the following
SQL statement, you can set the initialization parameter to the value
unlimited
:
ALTER SESSION SET max_dump_file_size = 'unlimited'
As of version 11.1, when the limit is reached, a message like the following is written to the alert log:
Non critical error ORA-48913 caught while writing to trace file "/u00/app/oracle/diag/rdbms/
dbm11203/DBM11203/trace/DBM11203_ora_6777.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [512000] reached
