2. 使用外部表查看oracle报警日志
由于在上面实验中已建立了一个用户,并赋相应的权限,而且也有了os文件(即报警文件alert_sid.log),所以在此直接建立目录对象并建立外部表就可以了。
1.建立目录对象
sql> conn test / 123 connected. sql> create directory bdump as '/oracle/u01/app/oracle/admin/db2/bdump'; directory created. |
2.建立外部表
sql> create table alert_log( text varchar2(400) )organization external (type oracle_loader default directory bdump access parameters (records delimited by newline )location('alert_db2.log') ); |
首先查看能否查到alert_db2.log的内容
sql> select * from alert_log where rownum < 10; text -------------------------------------------------------------------------------- thu jun 11 00:51:46 2009 starting oracle instance (normal) cannot determine all dependent dynamic libraries for /proc/self/exe unable to find dynamic library libocr10.so in search paths rpath = /ade/aime1_build2101/oracle/has/lib/:/ade/aime1_build2101/oracle/lib/:/a de/aime1_build2101/oracle/has/lib/: ld_library_path is not set! the default library directories are /lib and /usr/lib unable to find dynamic library libocrb10.so in search paths unable to find dynamic library libocrutl10.so in search paths 9 rows selected. |
测试成功
然后我们测试查报警信息'ora-%'
sql> select * from alert_log where text like 'ora-%'; text -------------------------------------------------------------------------------- ora-00202: control file: '/oracle/u01/app/oracle/product/10.2.0/db2/dbs/cntrldb2 .dbf' ora-27037: unable to obtain file status ora-205 signalled during: alter database mount... ora-00301: error in adding log file '/home/oracle/oracle/oradata/testdb/redo01.l og' - file cannot be created ora-27040: file create error ora-1501 signalled during: create database db2 ora-00200: control file could not be created text -------------------------------------------------------------------------------- ora-00202: control file: '/oracle/u01/app/oracle/product/10.2.0/db2/dbs/cntrldb2 .dbf' ora-27038: created file already exists ora-1501 signalled during: create database db2 ora-00200: control file could not be created ora-00202: control file: '/oracle/u01/app/oracle/product/10.2.0/db2/dbs/cntrldb2 .dbf' ora-27038: created file already exists ora-1501 signalled during: create database db2 |
测试成功,
可见我们可以使用外部表来方便的查看oracle的报警信息.
如对本文有疑问, 点击进行留言回复!!
Oracle查询用户,表结构,表索引,存储过程,触发器等信息整理
oracle的自连接、内连接、外连接、交叉连接、自然连接、等值连接、不等值连接
Oracle数据库使用replace函数批量删除数据中的双引号
网友评论