Oracle outstanding alerts

Oracle outstanding alerts



set linesize   200
set pagesize  1000
set trimout     on
set trimspool   on
Set Feedback   off
set timing     off
set verify     off


prompt

prompt -- ----------------------------------------------------------------------- ---

prompt --   Outstanding Alert                                                     ---

prompt -- ----------------------------------------------------------------------- ---

prompt


column ct                          format a18              heading  "Creation Time"
column instance_name               format a8               heading  "Instance|Name"
column object_type                 format a14              heading  "Object|Type"
column message_type                format a9               heading  "Message|Type"
column message_level               format 9999             heading  "Mess.|Lev."
column reason                      format a30              heading  "Reason"
column suggested_action            format a75              heading  "Suggested|Action"

Select  
       To_Char(Creation_Time, 'DD-MM-YYYY HH24:MI') ct
     , instance_name          
     , object_type            
     , message_type         
     , message_level       
     , reason             
     , suggested_action  
  From  dba_outstanding_alerts
 Order  By Creation_Time
;


Prompt

           



                  Instance Object         Message   Mess.                                Suggested
Creation Time      Name     Type           Type       Lev. Reason                         Action
------------------ -------- -------------- --------- ----- ------------------------------ ---------------------------------------------------------------------------
30-06-2011 13:20   db    TABLESPACE     Warning       5 Tablespace [DM_CCCPROD1_INDEX] Add space to the tablespace
                                                            is [85 percent] full

13-09-2011 16:45   db    TABLESPACE     Warning       5 Tablespace [DM_CCCPROD1_DOCBAS Add space to the tablespace
                                                           E] is [85 percent] full

17-10-2011 15:51   db    EVENT_CLASS    Warning       5 Metrics "Database Time Spent W Run ADDM to get more performance analysis about your system.
                                                           aiting (%)" is at 33.97673 for
                                                            event class "Network"


===========================

## Alerts database ##
set lines 250 pages 1000
col "Alter_from" for a20
col creation_time for a20
col reason for a120
select 'Alert_outstanding' "Alter_from",to_char(creation_time,'DD/MM/YYYY HH24:MI:SS') creation_time, reason 
from dba_outstanding_alerts
union
select 'History' "Alter_from",to_char(creation_time,'DD/MM/YYYY HH24:MI:SS') creation_time, reason 
from dba_alert_history 
order by 1 desc,creation_time;




## Alert log ##
set linesize 160 pagesize 0
col time for a20
col message_text for a120 head Message
SELECT to_char(originating_timestamp,'DD/MM/YYYY HH24:MI:SS') time, message_text FROM v$diag_alert_ext 
where originating_timestamp>sysdate-8/24
order by RECORD_ID ;




set linesize 200 pagesize 200
col record_id for 9999999 head id
col message_text for a120 head message
select record_id, to_char(originating_timestamp,'dd-mon-yyyy hh24:mi:ss') , message_text from x$dbgalertext 
where originating_timestamp > systimestamp - 15 
and regexp_like(message_text, '(ORA-|error)') 
order by record_id;


set linesize 254
col ORIGINATING_TIMESTAMP format a50
col MESSAGE_TEXT format a170
 
SELECT ORIGINATING_TIMESTAMP, message_text FROM X$DIAG_ALERT_EXT
where 1 = 1
  and originating_timestamp >= sysdate - 30
  and message_text like 'ALTER SYSTEM%'
  and message_text not like 'ALTER SYSTEM ARCHIVE LOG%'
;


set linesize 254
col originating_timestamp format a50
col message_text format a70 wrap
 
select inst_id, originating_timestamp, message_text
    FROM TABLE (
            gv$ (
               CURSOR (
                  SELECT inst_id, originating_timestamp, MESSAGE_TEXT
                    FROM v$diag_alert_ext
                   WHERE  1=1   
				      --and originating_timestamp > (SYSDATE - 10/1440)
					  and originating_timestamp > (SYSDATE - 1)
                         AND (MESSAGE_TEXT LIKE '%ORA-%' 
						 --or MESSAGE_TEXT LIKE '%TNS-%'
						 )
                         AND MESSAGE_TEXT NOT LIKE '%TNS-12502%'
                      )
                )
               )
ORDER BY inst_id, originating_timestamp;




set linesize 200 pagesize 200
col record_id for 9999999 head id
col message_text for a120 head message
select record_id, to_char(originating_timestamp,'dd-mon-yyyy hh24:mi:ss') , message_text from x$dbgalertext 
where originating_timestamp > systimestamp - 15 
and regexp_like(message_text, '(ORA-|error)') 
order by record_id;