Write to Trace files and Alert Log with DBMS_LOG

From time to time it can be handy to write your own custom messages to the alert log. You can also write messages to trace files.

I’ve used this approach while performing a massive partitioned table redesign just so I could keep track of what happened during the 5 day process. For example, I may have a message that says “Rebuilding Partition #1 – Begin”.

Here is a little documentation on the DBMS_LOG package – new in Oracle 12c.

If you are writing to a trace file you may want to know what process id you are using and/or what trace file your session will write to.

-- Find the pid and trace file for this session.
column spid format a10
column tracefile format a70
set linesize 85

select p.spid, p.tracefile
from   v$process p, v$session s
where  p.addr = s.paddr
and    sid = sys_context( 'USERENV', 'SID' );

-- sample output
SPID       TRACEFILE
---------- ----------------------------------------------------------------------
5472       /u01/app/oracle/diag/rdbms/whse/WHSE/trace/WHSE_ora_5472.trc

DBMS_LOG.KSDDDT

The DBMS_LOG.KSDDDT procedure will write a timestamp to the trace file for this PID. The trace file name can be obtained from the TRACEFILE column in the above SQL statement.

exec dbms_log.ksdddt;

-- sample output as written to the trace file.
*** 2015-02-06 17:19:46.765

DBMS_LOG.KSDIND

The DBMS_LOG.KSDIND procedure will prepend the next line written by a specified number of colons (:). These colons are only written to trace files, not the alert log.

-- Prepend the next line written to a trace file with 3 colons.
exec dbms_log.ksdind(3);
exec dbms_log.ksdddt;

-- sample output produced from combining these 2 procedures.
:::*** 2015-02-06 17:19:46.765

DBMS_LOG.KSDWRT

The DBMS_LOG.KSDWRT procedure will write to either the trace file for this PID, the alert log, or both, depending on the first parameter in the call to KSDWRT.

exec dbms_log.ksdwrt( 2, '-- Rebuilding Partition #1 - Begin --' );

The first parameter to ksdwrt in a destination parameter and indicates where the text will be written to.

destination = 1 – writes to trace file for the connected process id – <ORACLE_SID>_ora_<PID>.trc
Output is show below

*** 2015-02-06 17:04:11.758
-- Rebuilding Partition #1 - Begin --

destination = 2 – writes to alert log
(the line below with the date stamp may or may not be written depending on the last time the alert log has been written to)

Fri Feb 06 17:05:01 2015
-- Rebuilding Partition #1 - Begin --

destination = 3 – writes to both trace file and alert log

Using constant values

The DBMS_LOG package provides some constants that can be used as the parameter to the dbms_log.kdswrt procedure.

exec dbms_log.ksdwrt( dbms_log.trace_file + dbms_log.alert_file,
	'-- Rebuilding Partition #1 - Begin --' );

Post a Comment