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)
How To View Contents of the Alert Log using a Query [message #676970] Fri, 02 August 2019 12:38 Go to next message
wtolentino
Messages: 274
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.
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 Go to previous messageGo to next message
BlackSwan
Messages: 26588
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)
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66518
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

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 Go to previous messageGo to next message
JPBoileau
Messages: 63
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;

JP
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66518
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

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 Go to previous messageGo to next message
JPBoileau
Messages: 63
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
Re: How To View Contents of the Alert Log using a Query [message #676977 is a reply to message #676976] Fri, 02 August 2019 14:35 Go to previous messageGo to next message
BlackSwan
Messages: 26588
Registered: January 2009
Location: SoCal
Senior Member
JPBoileau wrote on Fri, 02 August 2019 12:16


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

Where else beside SYSTEM tablespace do VIEWS reside?
Re: How To View Contents of the Alert Log using a Query [message #676978 is a reply to message #676970] Sat, 03 August 2019 01:21 Go to previous messageGo to next message
John Watson
Messages: 7995
Registered: January 2010
Location: Global Village
Senior Member
v$diag_alert_ext is the view you need.
Re: How To View Contents of the Alert Log using a Query [message #676992 is a reply to message #676974] Mon, 05 August 2019 09:48 Go to previous messageGo to next message
wtolentino
Messages: 274
Registered: March 2005
Senior Member
thank you all.
Re: How To View Contents of the Alert Log using a Query [message #676993 is a reply to message #676992] Mon, 05 August 2019 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 66518
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So do you access the information now? And how?

Re: How To View Contents of the Alert Log using a Query [message #677048 is a reply to message #676993] Mon, 12 August 2019 09:28 Go to previous messageGo to next message
wtolentino
Messages: 274
Registered: March 2005
Senior Member
thru the v$diag_alert_ext
Re: How To View Contents of the Alert Log using a Query [message #677050 is a reply to message #677048] Mon, 12 August 2019 10:22 Go to previous message
Michel Cadot
Messages: 66518
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, thanks.

Previous Topic: Do I need to explicity run datapatch after upgrading
Next Topic: IMPDP gives failed messages in oracle 19c
Goto Forum:
  


Current Time: Sat Aug 24 16:01:19 CDT 2019