Procedure to get the alert log from within the database

Here’s a quick and easy procedure (if you’re on Oracle 11), to get the alert log from within the database. Because the alert log table (X$DBGALERTEXT) is an “X$”, you need to be SYSDBA in order to access it. So the best way to allow “normal” users access to it is to create a procedure. This will return a CLOB containing the lines of the alert log. This functionality will be included in Oracletool 2.3.4, btw.


Create or replace procedure get_alertlog_lines (num_lines in number, alertlog out clob)
/*
   This procedure should be owned by SYS, and you should grant
   execute privileges on it to whatever user you connect to your
   database as within Oracletool.

   Oracletool will not display the alertlog menu item unless this
   procedure exists.
*/

as

   v_alertlog clob;

Begin

   For i in (
      Select
         indx,
         to_char(originating_timestamp,'MM/DD/YYYY HH24:MI:SS') as timestamp,
         message_text as text
      from X$DBGALERTEXT
         where indx > (Select max(indx)-num_lines from X$DBGALERTEXT) order by indx asc)
   loop
      v_alertlog := v_alertlog||i.timestamp||': '||i.text;
   end loop;

   alertlog := v_alertlog;

End get_alertlog_lines;
/

--
-- Example grant
--
Grant execute on get_alertlog_lines to ADAM;

That’s it! let’s try it out via SQL*Plus! First thing I’m going to do is create a synonym for the procedure.


[oracle@mastiff /home/oracle] [DEMO]$ sqlplus adam/<password>

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 22 09:25:30 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

09:25:30 SQL> Set serveroutput on;
09:25:35 SQL> Create synonym get_alertlog_lines for sys.get_alertlog_lines;

Synonym created.

09:25:43 SQL> Declare
09:25:54 SQL> alertlog_lines clob;
09:26:10 SQL> Begin
09:26:14 SQL>    get_alertlog_lines(25,alertlog_lines);
09:26:38 SQL>    dbms_output.put_line(alertlog_lines);
09:26:54 SQL> End;
09:26:55 SQL> /
03/22/2012 03:51:08: Private strand flush not complete
03/22/2012 03:51:08: Current log# 2 seq# 3512 mem# 0:/apps/oracle/oradata/RMAN/onlinelog/o1_mf_2_5rg7qlfr_.log
03/22/2012 03:51:08: Current log# 2 seq# 3512 mem# 1:/apps/oracle/backup/flash_recovery_area/RMAN/onlinelog/o1_mf_2_5rg7qp8t_.log
03/22/2012 03:51:12: Thread 1 advanced to log sequence 3513 (LGWR switch)
03/22/2012 03:51:12:   Current log# 3 seq# 3513 mem# 0: /apps/oracle/oradata/RMAN/onlinelog/o1_mf_3_5rg7qsmw_.log
03/22/2012 03:51:12: Current log# 3 seq# 3513 mem# 1: /apps/oracle/backup/flash_recovery_area/RMAN/onlinelog/o1_mf_3_5rg7qwm0_.log
03/22/2012 03:51:18: Archived Log entry 3470 added for thread 1 sequence 3512 ID 0x5f45a87f dest 1:
03/22/2012 08:50:07: ALTER SYSTEM ARCHIVE LOG
03/22/2012 08:50:07: Thread 1 cannot allocate new log, sequence 3514
03/22/2012 08:50:07: Private strand flush not complete
03/22/2012 08:50:07:   Current log# 3 seq# 3513 mem# 0: /apps/oracle/oradata/RMAN/onlinelog/o1_mf_3_5rg7qsmw_.log
03/22/2012 08:50:07: Current log# 3 seq# 3513 mem# 1: /apps/oracle/backup/flash_recovery_area/RMAN/onlinelog/o1_mf_3_5rg7qwm0_.log
03/22/2012 08:50:09: Thread 1 advanced to log sequence 3514 (LGWR switch)
03/22/2012 08:50:09:   Current log# 1 seq# 3514 mem# 0: /apps/oracle/oradata/RMAN/onlinelog/o1_mf_1_5rg7q7wc_.log
03/22/2012 08:50:09: Current log# 1 seq# 3514 mem# 1: /apps/oracle/backup/flash_recovery_area/RMAN/onlinelog/o1_mf_1_5rg7qfn0_.log
03/22/2012 08:50:14: Archived Log entry 3471 added for thread 1 sequence 3513 ID 0x5f45a87f dest 1:
03/22/2012 08:50:19: ALTER SYSTEM ARCHIVE LOG
03/22/2012 08:50:20: Thread 1 cannot allocate new log, sequence 3515
03/22/2012 08:50:20: Private strand flush not complete
03/22/2012 08:50:20:   Current log# 1 seq# 3514 mem# 0: /apps/oracle/oradata/RMAN/onlinelog/o1_mf_1_5rg7q7wc_.log
03/22/2012 08:50:20: Current log# 1 seq# 3514 mem# 1: /apps/oracle/backup/flash_recovery_area/RMAN/onlinelog/o1_mf_1_5rg7qfn0_.log
03/22/2012 08:50:22: Thread 1 advanced to log sequence 3515 (LGWR switch)
03/22/2012 08:50:22:   Current log# 2 seq# 3515 mem# 0: /apps/oracle/oradata/RMAN/onlinelog/o1_mf_2_5rg7qlfr_.log
03/22/2012 08:50:22: Current log# 2 seq# 3515 mem# 1: /apps/oracle/backup/flash_recovery_area/RMAN/onlinelog/o1_mf_2_5rg7qp8t_.log
03/22/2012 08:50:24: Archived Log entry 3472 added for thread 1 sequence 3514 ID 0x5f45a87f dest 1:

PL/SQL procedure successfully completed.

🙂

Oracletool v2.3.3 released!

Not a lot added to this version in terms of functionality, but lots under the covers. Much cleanup was done in terms of checking for older versions of Oracle, all the way back to version 7. Oracletool is showing her age..

No longer testing with Oracle 7 / 8 / 9 / 10, and queries no longer tailored for these versions.

Enhancements
============

Added search for constraints to search box.
Added additional info when displaying user account info

Bug fixes
=========

Fixed bug where passwords were being truncated to 20 characters in password entry box.
Thanks to Jim Bouma for pointing it out.