This is probably as complete a way of killing an Oracle database as you could wish for. The sys tables contain all the metadata about every object in the database -- objects, segments, extents ... so the database now contains no information on what user tables it stores, including the tables that store the data about that.
New database, I think.
And no more sys connection accidents.
1) If you are using Oracle 11g or later:
Starting from 11g Oracle, was been introduced a fixed table, X$DBGALERTEXT, from which you can retrieve all the data what alert.log does.
Here is a simple query that retrieves all errors which occured today, like you want.
SET linesize 200 pagesize 200
col RECORD_ID FOR 9999999 head ID
col MESSAGE_TEXT FOR a120 head Message
SELECT record_id, message_text
FROM X$DBGALERTEXT
WHERE originating_timestamp > systimestamp - 1 AND regexp_like(message_text, '(ORA-|error)')
As Twinkles mentioned in his answer, you can take advantage of ADRCI utility to select alert filters that you need (in fact, this utility queries against the X$DBGALERTEXT table). For example, you can build a simple function and call it from OS prompt:
vealert() {
adrci exec="set home diag\/rdbms\/$DB_UNIQUE_NAME/$ORACLE_SID\; \
show alert -p \\\"message_text like '%ORA-%' \
and originating_timestamp > systimestamp - $1\\\""
}
in file .bashrc, for example:
export DB_UNIQUE_NAME=<unique_name>
. $HOME/bin/vealert
Then:
bash
vealert 1
..
Tue Dec 24 17:46:05 2013
ORA-28 signalled during: ALTER TABLESPACE DATA_APP_199903 READ ONLY...
..
2) Otherwise:
Go and have a look at this web page. There is a script that can be used to check any ORA- errors are there in the alertlog file for the present day.
To schedule this job, you can add a line in your cron table specifying the time you want the job to execute. Alternatively, you can take advantage of the Oracle Scheduler.
Best Answer
From the Database Reference I see that the view
V$DIAG_ALERT_EXTcontains aMESSAGE_GROUPcolumn. Maybe this column contains such kind of information.I am not aware of a view that contains such information. I searched for
*us.msgfiles in the $ORACLE_HOME directories and subdirectories of a 12.2 installation by executingfind $ORACLE_HOME -name '*us.msg'and found the file$ORACLE_HOME/rdbms/mesg/oraus.msgthat contains error messages in increasing order of error number and often containing a comment lines that describes the type of the error numbers in the following range. I extracted these comment lines. The output is shown in the following code block. There are more captions in comments that have a format thast will not ber recognized by this grep command.