dbms_system.ksdwrt:向告警日志文件写指定信息

有朋友提问：如何向Oracle的告警日志写入自己指定的信息！
其实Oracle的DBMS_SYSTEM包的KSDWRT过程就可以实现这个功能：

如在SQL*PLUS发出如下命令：

SQL> exec sys.dbms_system.ksdwrt(2, 'ORA-99999 :Just For testing');

PL/SQL procedure successfully completed.

此时在告警日志文件中就会记录如下一行信息：

Tue Jul  7 19:37:46 2009
ORA-99999 :Just For testing

DBMS_SYSTEM包的功能及其强大：

SQL> desc dbms_system
PROCEDURE DIST_TXN_SYNC
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 INST_NUM                       NUMBER                  IN
PROCEDURE GET_ENV
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 VAR                            VARCHAR2                IN
 VAL                            VARCHAR2                OUT
PROCEDURE KCFRMS
PROCEDURE KSDDDT
PROCEDURE KSDFLS
PROCEDURE KSDIND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LVL                            BINARY_INTEGER          IN
PROCEDURE KSDWRT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST                           BINARY_INTEGER          IN
 TST                            VARCHAR2                IN
PROCEDURE READ_EV
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 IEV                            BINARY_INTEGER          IN
 OEV                            BINARY_INTEGER          OUT
PROCEDURE SET_BOOL_PARAM_IN_SESSION
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SID                            NUMBER                  IN
 SERIAL#                        NUMBER                  IN
 PARNAM                         VARCHAR2                IN
 BVAL                           BOOLEAN                 IN
PROCEDURE SET_EV
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SI                             BINARY_INTEGER          IN
 SE                             BINARY_INTEGER          IN
 EV                             BINARY_INTEGER          IN
 LE                             BINARY_INTEGER          IN
 NM                             VARCHAR2                IN
PROCEDURE SET_INT_PARAM_IN_SESSION
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SID                            NUMBER                  IN
 SERIAL#                        NUMBER                  IN
 PARNAM                         VARCHAR2                IN
 INTVAL                         BINARY_INTEGER          IN
PROCEDURE SET_SQL_TRACE_IN_SESSION
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SID                            NUMBER                  IN
 SERIAL#                        NUMBER                  IN
 SQL_TRACE                      BOOLEAN                 IN
PROCEDURE WAIT_FOR_EVENT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 EVENT                          VARCHAR2                IN
 EXTENDED_ID                    BINARY_INTEGER          IN
 TIMEOUT                        BINARY_INTEGER          IN

KSDWRT共有两个输入参数，一个用来指定写出的目的地，一个用来指定消息文本：

PROCEDURE KSDWRT

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 DEST                           BINARY_INTEGER          IN

 TST                            VARCHAR2                IN

其中目的地参数有三个选项：

• 1 - Write to trace file.
• 2 - Write to alertlog.
• 3 - Write to both.

较为常见的，在通过触发器屏蔽某些危险操作后，我们可以用这个过程在告警日志中记录提示警告！

-The End-