Oracle使用外部表访问告警日志文件
Oracle使用外部表访问告警日志文件
首先创建目录
- SQL> show user
- USER 为 "SYS"
- SQL> create or replace directory bdump as '/u01/app/Oracle/admin/orcl/bdump';
- 目录已创建。
然后创建外部表
- SQL> create table alert_log(text varchar2(400))
- 2 organization external(
- 3 type oracle_loader
- 4 default directory bdump
- 5 access parameters(
- 6 records delimited by newline
- 7 nobadfile
- 8 nodiscardfile
- 9 nologfile
- 10 )
- 11 location('alert_orcl.log')
- 12 )reject limit unlimited;
- 表已创建。
查询告警日志文件
- SQL> select count(*) from alert_log;
- COUNT(*)
- ----------
- 5793
- SQL> select * from alert_log where rownum < 10;
- TEXT
- ----------------------------------------------------------------------------------------------------
- Wed Sep 14 01:01:35 2011
- Starting ORACLE instance (normal)
- LICENSE_MAX_SESSION = 0
- LICENSE_SESSIONS_WARNING = 0
- Shared memory segment for instance monitoring created
- Picked latch-free SCN scheme 2
- Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
- Autotune of undo retention is turned on.
- IMODE=BR
- 已选择9行。
查看数据库中曾经出现过ORA-错误
- SQL> select * from alert_log where text like '%ORA-%';
- TEXT
- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
- ORA-00942: 表或视图不存在
- ORA-12012: 自动执行作业 1 出错
- ORA-01435: 用户不存在
- ORA-12012: 自动执行作业 21 出错
- ORA-01435: 用户不存在
- ORA-12012: 自动执行作业 21 出错
- ORA-01435: 用户不存在
- ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
- ORA-19504: 无法创建文件"/u01/app/oracle/archivelog/1_52_761792499.dbf"
- ORA-27040: 文件创建错误
- ORA-16038: 日志 1 sequence# 52 无法归档
- ORA-19504: 无法创建文件""
- ORA-00312: 联机日志 1 线程 1: '/u01/app/oracle/oradata/orcl/redo01.log'
- ORA-16014: 日志 1 sequence# 52 未归档
- ORA-00312: 联机日志 1 线程 1: '/u01/app/oracle/oradata/orcl/redo01.log'
- ORA-16014: 日志 1 sequence# 52 未归档
- ORA-00312: 联机日志 1 线程 1: '/u01/app/oracle/oradata/orcl/redo01.log'
- ORA-16014: 日志 1 sequence# 52 未归档
- ORA-00312: 联机日志 1 线程 1: '/u01/app/oracle/oradata/orcl/redo01.log'
- ORA-16014: 日志 1 sequence# 52 未归档
- ORA-00312: 联机日志 1 线程 1: '/u01/app/oracle/oradata/orcl/redo01.log'
- ORA-1509 signalled during: alter database test rename file '/u01/app/oracle/test01.dbf' to '/u01/app/oracle/oradata/orcl/test01.dbf'...
- ORA-1525 signalled during: alter tablespace test rename datafile '/u01/app/oracle/test01.dbf' to '/u01/app/oracle/oradata/orcl/test01.dbf'...
- ORA-1525 signalled during: alter tablespace test rename datafile '/u01/app/oracle/test01.dbf' to '/u01/app/oracle/oradata/orcl/test001.dbf'...
- ORA-1525 signalled during: alter tablespace test rename datafile '/u01/app/oracle/test01.dbf' to '/u01/app/oracle/oradata/orcl/test001.dbf'...
- ORA-959 signalled during: alter database default tablespace test...
- ORA-959 signalled during: alter database default tablespace test1...
- ORA-3217 signalled during: alter tablespace temp offline...
- 已选择28行。
评论暂时关闭