While
not a utility per se, the ability to write
messages to the Oracle alert log is an extremely
useful ability.
This is being
mentioned in this chapter because it is an often
needed and useful, but not well documented
ability.
There are many cases where
the Oracle DBA may want to write custom messages
directly into the standard Oracle alert log file
located in $ORACLE_HOME/admin/$ORACLE_SID/bdump.
By
writing custom messages to the Oracle alert log
file,
the default Oracle
alerts can be supplemented with custom messages.
Writing to the Oracle alert log is exactly the
same as writing to any flat file and there are
these choices:
-
OS scripts and
languages - Use UNIX/Linux utilities to
write OS messages, e.g.
RAM
swapping alerts, into the alert log.
Of course, all procedural languages
(C++) can also write messages to the Oracle
alert log.
-
PL/SQL - Use the standard
utl_file
package or call the
dbms_system.ksdwrt
procedure to write messages to the Oracle
alert log file.
-
SQL (read only) -
Define the alert log as an external table
and access the alert log messages with SQL
statements.
The best method to write
to the alert log is to use
SYS.DBMS_SYSTEM.KSDWRT.
This very handy
utility will write a message to the alert log;
it takes two arguments, a number (1 or 2), and
the text.
The number
indicates whether to write to alert or trace.
If set to 1, it
writes to a trace file.
If 2, it writes to
the alert log.
Because it uses the
dbms_system package, write a wrapper around the
call and grant privileges to the wrapper
procedure or function. Then an execute will not
have to be granted on the dbms_system package.
A possible function
to accomplish this purpose is below:
create or replace
function write_alert_log(log_or_trace in
number, text_message in varchar2) return
number
is
begin
SYS.DBMS_SYSTEM.KSDWRT(log_or_trace,text_message);
return 0;
exception
when others
then
return 1;
end;
It is also possible to write to the alert log
using utl_file, but there are many privilege
issues that arise when using this method and
many more lines of programming.
The easy method is to use the
DBMS_SYSTEM.KSDWRT package.
