OraFAQ Forum: Server Administration » How To View Contents of the Alert Log using a Query

Home » RDBMS Server » Server Administration » How To View Contents of the Alert Log using a Query (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How To View Contents of the Alert Log using a Query [message #676970]

Fri, 02 August 2019 12:38

wtolentino
Messages: 421
Registered: March 2005

Senior Member

how can I view the contents of the alert log using a query? I googled found this X$DBGALERTEXT but apparently it does not exists. I checked using the view dba_objects and that view does not exists. is there any view/table for the alert log? thank you.

Report message to a moderator

Re: How To View Contents of the Alert Log using a Query [message #676972 is a reply to message #676970]

Fri, 02 August 2019 13:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Nothing prevent you from using EXTERNAL TABLE to map alert_SID.log file to a table with a single column of VARCHAR2(4000)

Report message to a moderator

Re: How To View Contents of the Alert Log using a Query [message #676973 is a reply to message #676970]

Fri, 02 August 2019 13:27

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

X$DBGALERTEXT is only accessible by SYS and, as all SYS.X$ objects, it is not in DBA_OBJECTS.

Report message to a moderator

Re: How To View Contents of the Alert Log using a Query [message #676974 is a reply to message #676970]

Fri, 02 August 2019 13:30

JPBoileau
Messages: 88
Registered: September 2017

Member

This gets me everything, including the date and time, from the alert log. I get it for the last 24 hours, in this case.

SELECT CASE							
		    WHEN LAG (						
			    TO_CHAR (CAST (ORIGINATING_TIMESTAMP AS DATE),					
				     'YYYY-MM-DD HH24:MI:SS'),				
			    1,					
			    0)					
			 OVER (ORDER BY ORIGINATING_TIMESTAMP) =					
			    TO_CHAR (CAST (ORIGINATING_TIMESTAMP AS DATE),					
				     'YYYY-MM-DD HH24:MI:SS')				
		    THEN						
		       NULL						
		    ELSE						
		       TO_CHAR (CAST (ORIGINATING_TIMESTAMP AS DATE),						
				'YYYY-MM-DD HH24:MI:SS')				
		 END						
		    AS TS,						
		 MESSAGE_TEXT						
	    FROM SYS.VW_X$DBGALERTEXT							
	   WHERE ORIGINATING_TIMESTAMP BETWEEN SYSDATE - 1 AND SYSDATE							
	ORDER BY ORIGINATING_TIMESTAMP;

Report message to a moderator

Re: How To View Contents of the Alert Log using a Query [message #676975 is a reply to message #676974]

Fri, 02 August 2019 13:53

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You forgot to mention you created a custom view upon SYS.X$DBGALERTEXT.

Report message to a moderator

Re: How To View Contents of the Alert Log using a Query [message #676976 is a reply to message #676975]

Fri, 02 August 2019 14:16

JPBoileau
Messages: 88
Registered: September 2017

Member

Good catch, I wrote this a couple years ago and forgot all about it.

The reason is that you can only grant select on a fixed view.

create view vw_X$DBGALERTEXT as select * from sys.X$DBGALERTEXT;
grant select on sys.vw_X$DBGALERTEXT to AUDITUSER; -- AUDITUSER is the user that emails me the alert.log daily.

I'm not too wild about creating views in the SYS directory.

JP

Report message to a moderator