Writing to AlertLog or Trace file

Package DBMS_SYSTEM has few useful procedures that you can use in various occasions. It could be used for setting trace event (set_ev), checking which events are already set (read_ev), tracing session (set_sql_trace_in_session) or it could be used to write messages to alertlog or tracefile (kdswrt).

I mostly use ksdwrt procedure of DBMS_SYSTEM package for logging messages about procedure erorrs or to log some info messages.


KSDWRT

dbms_system.ksdwrt (destination IN BINARY_INTEGER, message IN VARCHAR2);

destination:
1: Write to the standard trace file
2: Write to the alert log
3: Write to both files at once




Little example:

[oracle@dibidus ~]$ sqlplus “/as sysdba”

SQL> declare
l_start  number;
l_number number;
l_total_time number;
begin
l_start := dbms_utility.get_time;
select count(*) into l_number
from msutic.big_table;
l_total_time := dbms_utility.get_time - l_start;
dbms_system.ksdwrt(2,'INFO - Total Time: '|| l_total_time || ' hsecs');
end;
/

SQL>!tail /oracle/diag/rdbms/test11/test11/trace/alert_test11.log

Mon Oct 13 02:00:00 2008
Clearing Resource Manager plan via parameter
Mon Oct 13 02:14:23 2008
Thread 1 advanced to log sequence 635 (LGWR switch)
Current log# 2 seq# 635 mem# 0: /oracle/oradata/TEST11/onlinelog/o1_mf_2_45gzogt7_.log
Current log# 2 seq# 635 mem# 1: /oracle/flash_recovery_area/TEST11/onlinelog/o1_mf_2_45gzojf6_.log
Mon Oct 13 10:30:03 2008
INFO - Total Time: 395 hsecs