2. Use external table to view the oracle alarm log
Since a user has been established in the above experiment and has the corresponding permissions, and an OS file (i.e. alarm file alert_SID.log), you can directly create a directory object and create an external table here.
1. Create a directory object
|
SQL> conn test / 123 Connected. SQL> create directory bdump as '/oracle/u01/app/oracle/admin/db2/bdump'; Directory created. |
2. Create an external table
|
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') ); |
First check whether you can find the content of 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 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 in search paths Unable to find dynamic library in search paths 9 rows selected. |
Test successfully
Then we test the alarm information '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/ 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 |
The test was successful,
It can be seen that we can use external tables to easily view ORACLE's alarm information.