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.
See http://www.hotsos.com .
 
Search WWH ::




Custom Search