How to access oracle alarm logs using external tables

Oracle alarm logs are frequently seen. The following describes how to access oracle alarm logs by using oracle External tables.

1. Create a user and grant permissions

 
 

  
  
SQL>create user checker identified by password default tablespace users quota unlimited on users;  
  
  
 
  
  
SQL>grant connect,resource,create any directory to checker;  
 
 

2. Create a directory object

 
 

  
  
SQL>show parameter background_dump_dest;  
  
  
 
  
  
NAME                                 TYPE        VALUE  
  
  
------------------------------------ ----------- ------------------------------  
  
  
background_dump_dest                 string      /u01/app/oracle/orcl/bdump  
  
  
 
  
  
SQL> conn  checker/password  
  
  
 
  
  
SQL>create directory  bdump as '/u01/app/oracle/orcl/bdump';  
 
 

3. 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_orcl.log')); 
 
 

4. Use an External table to view oracle alarm logs

 
 

  
  
SQL> select text from alert_log; // view all alarm logs
  
  
 
  
  
SQL> select text from alert_log where text like 'ora-% '; // view error information in the alarm log
  
  
 
  
  
SQL> select * from (select text from alert_log order by rowid desc) where rownum <50; // view the last 50 lines of alarm logs.
 
 

Extended knowledge:

An External table is like a view created in a database. The database only stores the table metadata, and the real records are stored in the operating system. There are some restrictions on the use of external tables:

1. You can only read content from external tables. You cannot perform DML operations on External tables or create indexes for them.

2. Directory objects must be used to use external tables.

3. Only one separator can be used for data files in the same operating system, such as commas (,) or semicolons.

4. When deleting an External table, you should follow the principle of deleting all external tables first and then deleting the directory objects. If you delete the directory objects directly, the system will not automatically check the external tables on it, the External table may be unavailable. You can query dba_external_locations to query all directory objects and related external tables, and query the names of operating system files corresponding to these external tables.
 

Oracle Log File Management

Read and Write files in Oracle stored procedures

Entire Process of creating Oracle Materialized View

Minimum parameter requirements for Oracle instance Creation

Implementation of Oracle redo log