Writing user defined message to Alert Log in Oracle.

In Oracle , there is a undocumented package/API ( dbms_system) to write user defined messages in the alert log.  This can be executed an user with SYSDBA privilege . You may want to use this with caution , as it is not supported by Oracle.

The signature of this procedure call is

dbms_system.ksdwrt ( Trace Identifier , User Defined Message )

Note :
When Trace Identifier is 1 , the message is written to a trace file only.
When Trace Identifier is 2 , the message is written to a alert log only.
When Trace Identifier is 3 , the message is written to a alert log and trace file. 


Here is an example.


SQL> show parameter user_dump_de

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      C:\app\mohideen\diag\rdbms\zmdb
                                                 rd11\zmdbrd11\trace



SQL> exec dbms_system.ksdwrt(1, 'Writing Alert message with 1');

PL/SQL procedure successfully completed.

SQL> exec dbms_system.ksdwrt(2, 'Writing Alert message with 2');

PL/SQL procedure successfully completed.

SQL> exec dbms_system.ksdwrt(3, 'Writing Alert message with 3');

PL/SQL procedure successfully completed.



C:\app\mohideen\diag\rdbms\zmdbrd11\zmdbrd11\trace>type alert_zmdbrd11.log
Thu Jun 13 15:31:44 2013
Writing Alert message with 2
Writing Alert message with 3



C:\app\mohideen\diag\rdbms\zmdbrd11\zmdbrd11\trace>type zmdbrd11_ora_8928.trc
Trace file C:\APP\MOHIDEEN\diag\rdbms\zmdbrd11\zmdbrd11\trace\zmdbrd11_ora_8928.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU                 : 8 - type 8664, 8 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:4194M/12285M, Ph+PgF:14916M/24569M
Instance name: zmdbrd11
Redo thread mounted by this instance: 1
Oracle process number: 20
Windows thread id: 8928, image: ORACLE.EXE (SHAD)


*** 2013-06-13 15:31:38.973
*** SESSION ID:(131.1077) 2013-06-13 15:31:38.973
*** CLIENT ID:() 2013-06-13 15:31:38.973
*** SERVICE NAME:(SYS$USERS) 2013-06-13 15:31:38.973
*** MODULE NAME:(sqlplus.exe) 2013-06-13 15:31:38.973
*** ACTION NAME:() 2013-06-13 15:31:38.973

Writing Alert message with 1

*** 2013-06-13 15:31:52.171
Writing Alert message with 3

At times , this trick could be useful . You can see my another post on how to monitor the alert log (
http://mfzahirdba.blogspot.com/2010/10/monitoring-alert-log.html) .

Comments Welcome.