Database Reference
In-Depth Information
When writing to the alert log, the string written is unconditionally preceded by a time-
stamp and the format is “Dy Mon DD HH24:MI:SS YYYY” (e.g., “Mon Jun 25 15:17:37 2007”).
Since the package
DBMS_SYSTEM
has functionality that should only be accessible to privi-
leged sessions, it is advisable to provide access to
DBMS_SYSTEM
through wrapper procedures.
Such wrapper procedures are part of the open source ORACLE instrumentation library ILO by
Hotsos.
2
Their names are
HOTSOS_SYSUTIL.WRITE_DATESTAMP
and
HOTSOS_SYSUTIL.WRITE_TO_
TRACE
. Execute permission on these packages is granted to
PUBLIC
.
Examples
The following anonymous block shows how timing information for expensive tasks can be
written to a trace file. The string “===” at the beginning of the line serves to make sure that the
line is ignored by the TKPROF utility.
DBMS_UTILITY.GET_CPU_TIME
is not available in Oracle9
i
.
SQL> DECLARE
elapsed_time_t1 number;
elapsed_time_t2 number;
cpu_time_t1 number;
cpu_time_t2 number;
BEGIN
elapsed_time_t1:=dbms_utility.get_time;
cpu_time_t1:=dbms_utility.get_cpu_time;
dbms_stats.gather_schema_stats(user); -- do something expensive
elapsed_time_t2:=dbms_utility.get_time;
cpu_time_t2:=dbms_utility.get_cpu_time;
sys.dbms_system.ksdddt;
sys.dbms_system.ksdwrt(1, '=== Elapsed time: ' ||
to_char((elapsed_time_t2 - elapsed_time_t1)/100)||
' sec CPU: ' || to_char((cpu_time_t2 - cpu_time_t1)/100) || ' sec');
END;
/
This anonymous block writes entries such as these into a trace file:
*** 2007-06-25 16:23:12.316
=== Elapsed time: 1.15 sec CPU: .68 sec
Another example for leveraging
DBMS_SYSTEM.KSDWRT
might be to record errors that are not
normally logged to the alert log by creating a
SERVERERROR
trigger that calls
KSDWRT
. For example,
Oracle9
i
does not write entries to the alert log when “ORA-01555: snapshot too old” occurs
(Oracle10
g
does). By implementing a
SERVERERROR
trigger that checks for error code 1555, this
error and the statement that failed could be written to the alert log of an Oracle9
i
instance.
Many monitoring tools such as Enterprise Manager or Tivoli TEC are able to parse the alert log
of an instance and to relay errors found to a management console. By writing custom errors
such as ORA-20000 to the alert log, a simple yet efficient integration may be built.
2.