If you want to know how to query the alert log file then you are in the right place. Today we will learn how to check the alert log using SQL query.

Please find the below scripts to check the alert log using SQL query/script:

1.Login to the database and run the below query:

select VALUE from v$diag_info where NAME=’Diag Trace’;
cd ‘value’
ls -ltr alert*

shift + G —>to get the tail end
?ORA-1652 —>to search of the error
shift + N —>to step for next reported error

2. To check ORA error in the alert log :

SET linesize 160 pagesize 200
col RECORD_ID FOR 9999999 head ID
col ORIGINATING_TIMESTAMP FOR a20 head DATE
col MESSAGE_TEXT FOR a120 head Message
SELECT
record_id,
to_char(originating_timestamp,’DD.MM.YYYY HH24:MI:SS’),
message_text
FROM
x$dbgalertext
where
originating_timestamp > (sysdate-3)
and
message_text like ‘%ORA-%’
order by
originating_timestamp;

3. To check Deadlock in the alert log :

SET linesize 160 pagesize 200
col RECORD_ID FOR 9999999 head ID
col ORIGINATING_TIMESTAMP FOR a20 head DATE
col MESSAGE_TEXT FOR a120 head Message
SELECT
record_id,
to_char(originating_timestamp,’DD.MM.YYYY HH24:MI:SS’),
message_text
FROM
x$dbgalertext
where
originating_timestamp > (sysdate-3)
and
message_text like ‘%Deadlock%’
order by
originating_timestamp;

4. To check the WARNING in the alert log :

SET linesize 160 pagesize 200
col RECORD_ID FOR 9999999 head ID
col ORIGINATING_TIMESTAMP FOR a20 head DATE
col MESSAGE_TEXT FOR a120 head Message
SELECT
record_id,
to_char(originating_timestamp,’DD.MM.YYYY HH24:MI:SS’),
message_text
FROM
x$dbgalertext
where
originating_timestamp > (sysdate-3)
and
message_text like ‘%WARN%’
order by
originating_timestamp;