Writing to the alert log from PL/SQL in Oracle Databases can be a convenient way to monitor significant events or errors that occur during database operations. For example, you could write custom messages to the alert log to track the execution of specific PL/SQL procedures, or to log particular errors that occur during data processing tasks. This can be particularly useful for database administrators who need to troubleshoot issues or analyze the performance of the database.
However, it’s important to note that while the DBMS_SYSTEM.KSDWRT package can be used to write messages directly to the alert log from PL/SQL, this is an undocumented and unsupported feature. As such, it should be used with caution and thorough testing.
In the following example, the first parameter of DBMS_SYSTEM.KSDWRT indicates that the message should be written to the alert log, and the string that follows is the custom message to be written.
set SERVEROUTPUT on begin dbms_system.ksdwrt(dbms_system.alert_file, '*** Start writing to alert_file ***'); dbms_system.ksdwrt(dbms_system.alert_file, ' Some output from pl/sql'); dbms_system.ksdwrt(dbms_system.alert_file, ' More output from pl/sql'); dbms_system.ksdwrt(dbms_system.alert_file, '*** Stop writing to alert_file ***'); end;
Now, let’s check the output in the alert log file.
[oracle@primary ~]$ adrci
ADRCI: Release 19.0.0.0.0 - Production on Mon Apr 15 17:10:09 2024
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci> show homes
ADR Homes:
diag/rdbms/orcl/orcl
diag/tnslsnr/primary/listener
adrci> set home diag/rdbms/orcl/orcl
adrci> show alert -tail 10
2024-04-15 17:20:08.781000 +01:00
*** Start writing to alert_file ***
Some output from pl/sql
More output from pl/sql
*** Stop writing to alert_file ***
adrci>
Use the parameter dbms_system.trace_file to write directly to the trace insteads of the alert log.
EXEC dbms_system.ksdwrt(dbms_system.trace_file, '*** Writing to trace_file');
For more insights into diagnostics in Oracle Databases, I highly recommend reading my latest post about the Automatic Diagnostic Repository (ADR). It provides a wealth of information on how ADR can simplify the task of diagnosing and resolving issues in Oracle Databases.
Oracle Essentials: Oracle Database 12c
$49.99 (as of December 20, 2024 21:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
Learn SQL in less than 20 hours: Understand the Language and Do It Yourself
$9.99 (as of December 20, 2024 21:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
Oracle Database 11g SQL (Oracle Press)
$51.01 (as of December 20, 2024 21:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)