First create a directory
- SQL> showUser
- USERIs"SYS"
- SQL>Create Or ReplaceDirectory bdumpAs '/U01/app/Oracle/admin/orcl/bdump';
- The directory has been created.
Create an External table
- SQL>Create TableAlert_log (text varchar2 (400 ))
- 2 organization external (
- 3 type oracle_loader
- 4DefaultDirectory bdump
- 5 access parameters (
- 6 records delimitedByNewline
- 7 nobadfile
- 8 nodiscardfile
- 9 nologfile
- 10)
- 11 location ('Alert _ orcl. Log')
- 12) reject limit unlimited;
- The table has been created.
Query Alert Log Files
- SQL>Select Count(*)FromAlert_log;
- COUNT(*)
- ----------
- 5793
- SQL>Select*FromAlert_logWhereRownum <10;
- TEXT
- Bytes ----------------------------------------------------------------------------------------------------
- Wed Sep 14 01:01:35 2011
- Starting ORACLE instance (normal)
- LICENSE_MAX_SESSION = 0
- LICENSE_SESSIONS_WARNING = 0
- Shared memory segmentForInstance monitoring created
- Picked latch-FreeSCN scheme 2
- Using LOG_ARCHIVE_DEST_10 parameterDefaultValueAsUSE_DB_RECOVERY_FILE_DEST
- AutotuneOfUndo retentionIsTurnedOn.
- IMODE = BR
- Nine rows have been selected.
A ora-error has occurred while viewing the database.
- SQL>Select*FromAlert_logWhereTextLike '% ORA-%';
- TEXT
- Certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------
- ORA-00942: Table or view does not exist
- ORA-12012: Automatic Execution of Job 1 error
- ORA-01435: user does not exist
- ORA-12012: Automatic job 21 Error
- ORA-01435: user does not exist
- ORA-12012: Automatic job 21 Error
- ORA-01435: user does not exist
- ORA-1109 signalled:ALTER DATABASE CLOSENORMAL...
- ORA-19504: Unable to Create File"/U01/app/oracle/archivelog/10952_761792499.dbf"
- ORA-27040: file creation error
- ORA-16038: Log 1Sequence#52 archiving failure
- ORA-19504: Unable to Create File""
- ORA-00312: Online log 1 thread 1:'/U01/app/oracle/oradata/orcl/redo01.log'
- ORA-16014: Log 1Sequence#52 not archived
- ORA-00312: Online log 1 thread 1:'/U01/app/oracle/oradata/orcl/redo01.log'
- ORA-16014: Log 1Sequence#52 not archived
- ORA-00312: Online log 1 thread 1:'/U01/app/oracle/oradata/orcl/redo01.log'
- ORA-16014: Log 1Sequence#52 not archived
- ORA-00312: Online log 1 thread 1:'/U01/app/oracle/oradata/orcl/redo01.log'
- ORA-16014: Log 1Sequence#52 not archived
- ORA-00312: Online log 1 thread 1:'/U01/app/oracle/oradata/orcl/redo01.log'
- ORA-1509 signalled:Alter DatabaseTest rename file'/U01/app/oracle/test01.dbf' To '/U01/app/oracle/oradata/orcl/test01.dbf'...
- ORA-1525 signalled:AlterTablespace test rename datafile'/U01/app/oracle/test01.dbf' To '/U01/app/oracle/oradata/orcl/test01.dbf'...
- ORA-1525 signalled:AlterTablespace test rename datafile'/U01/app/oracle/test01.dbf' To '/U01/app/oracle/oradata/orcl/test001.dbf'...
- ORA-1525 signalled:AlterTablespace test rename datafile'/U01/app/oracle/test01.dbf' To '/U01/app/oracle/oradata/orcl/test001.dbf'...
- ORA-959 signalled:Alter Database DefaultTablespace test...
- ORA-959 signalled:Alter Database DefaultTablespace test1...
- ORA-3217 signalled:AlterTablespaceTempOffline...
- Select 28 rows.