-- =======================================================================
--  The V$X$DBGALERTEXT ==> V$ALERT_LOG ::  View Config.
-- =======================================================================
desc X$DBGALERTEXT

SQL> create or replace view sys.v_$alert_log as SELECT * FROM x$dbgalertext;
View SYS.V_$ALERT_LOG created.

SQL> create public synonym v$alert_log for sys.v_$alert_log;
Synonym V$ALERT_LOG created.

SQL> grant SELECT on v$alert_log to c##USER;
Grant succeeded.

-- =======================================================================
--  Table X$DBGDIREXT - [diagnostic_dest]/diag :: --
-- =======================================================================
SELECT
   lpad(' ', lvl, ' ') || logical_file file_name 
FROM
   x$dbgdirext 
WHERE
   rownum <= 20; 
  
-- =======================================================================
--  MESSAGE_LEVEL [ 1: CRITICAL 2: INCIDENT_ERROR 3: ERROR ]
-- =======================================================================
SELECT message_text FROM x$dbgalertext WHERE message_level = 1;  
SELECT message_text FROM x$dbgalertext WHERE message_type IN (2, 3); 

-- =======================================================================
--  ERROR 
-- =======================================================================
SELECT DISTINCT
   message_text 
FROM
   x$dbgalertext 
WHERE
   originating_timestamp > sysdate - 2 
   AND message_text like '%ORA-00600%' 
   OR message_text like '%Fatal%';


SELECT
  rownum "line",
  message_text "error" 
FROM
  x$dbgalertext 
WHERE
  originating_timestamp > (sysdate - 5 / 1440) 
  AND message_text like '%ORA-%' 
ORDER BY
  originating_timestamp;
   
-- =============================================================
--  Todos as ultimas 200 linhas do ALERT do banco de dados
-- =============================================================
set lines 300
set pages 2000
col data for a20
col MESSAGE_TEXT for a120
SELECT
  to_char (ORIGINATING_TIMESTAMP, 'DD-MM-YYYY HH24:MI') data,
  message_text 
FROM
  x$dbgalertext 
WHERE
  indx > (SELECT COUNT(*) - 200 FROM x$dbgalertext );			
  
  
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;
  
-- ==========================================================
-- 	ALERT LOG: Todos os erros ORA nas ultimas 24 horas
-- ==========================================================
set lines 300
set pages 2000
col data for a20
col MESSAGE_TEXT for a100

SELECT
  to_char (ORIGINATING_TIMESTAMP, 'DD-MM-YYYY HH24:MI') data,
  message_text 
FROM
  x$dbgalertext 
WHERE
  ORIGINATING_TIMESTAMP > (SYSDATE - 1) 
  AND message_text LIKE '%ORA-%';

-- ==========================================================
-- 	ALERT LOG: Todos os erros ORA nas ultimas 1 hora
-- ==========================================================
col TIMESTAMP for a22
col message_text for a100
SELECT DISTINCT
  TO_CHAR(originating_timestamp, 'DD-MM-RRRR HH24:MI:SS') timestamp,
  message_text 
FROM
  x$dbgalertext 
WHERE
  originating_timestamp > sysdate - 1 / 24 
ORDER BY 1;