Access alert logs through external tables
Map the alert log file to a virtual table through an external table and transfer it from: http://www.jb51.net/article/34590.htm
1. Check the operating system version
SQL > select * from v$version
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod
PL/SQL Release 10.2.0.1.0-Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0-Production
NLSRTL Version 10.2.0.1.0-Production
SQL >
2. View the location of the warning log file (alert_sid.log)
SQL > show parameter dump
NAME TYPE VALUE
-
Background_core_dump string partial
Background_dump_dest string / u01/oracle/admin/bdump
Core_dump_dest string / u01/oracle/admin/cdump
Max_dump_file_size string 10240
Shadow_core_dump string partial
User_dump_dest string / u01/oracle/admin/udump
3. Create a directory alert
Note: directory is not an entity, it's just a point to a path in os
SQL > create or replace directory alert as'/ u01qqoracleUniverse
Directory created.
SQL >
4. Create an external table alert
SQL > create table alert
1 (log varchar2 (1000))
2 organization external
3 (type oracle_loader
4 default directory alert
5 access parameters
6 (records delimited by newline)
7 location ('alert_PROD.log'))
8 reject limit unlimited
Table created.
5. View the contents of alert
SQL > select * from alert where rownum
6. Check out the lovely ORA- errors in the database.
SQL > select * from alert where log like'% ORA-%'
LOG
-
ORA-959 signalled during: alter database default tablespace users...
ORA-959 signalled during: drop tablespace uses...
ORA-12919 signalled during: drop tablespace users...
ORA-1549 signalled during: drop tablespace users...
ORA-1505 signalled during: alter database add logfile group 1
ORA-1184 signalled during: alter database add logfile group 1
ORA-1013 signalled during: alter tablespace tts read only...
ORA-1013 signalled during: alter tablespace tts read only...
ORA-1013 signalled during: alter tablespace users read only...
ORA-1539 signalled during: alter tablespace users read only...
