How to access oracle alarm logs using external tables

Source: Internet
Author: User
Tags oracle materialized view

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

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

2. Create a directory object

  1. SQL>show parameter background_dump_dest;  
  3. NAME                                 TYPE        VALUE  
  4. ------------------------------------ ----------- ------------------------------  
  5. background_dump_dest                 string      /u01/app/oracle/orcl/bdump  
  7. SQL> conn  checker/password  
  9. SQL>create directory  bdump as '/u01/app/oracle/orcl/bdump';  

3. Create an External table

  1. SQL> create table alert_log( text varchar2(400) )  
  2. organization external (type oracle_loader  
  3. default directory bdump  
  4. access parameters (records delimited by newline)  
  5. location('alert_orcl.log')); 

4. Use an External table to view oracle alarm logs

  1. SQL> select text from alert_log; // view all alarm logs
  3. SQL> select text from alert_log where text like 'ora-% '; // view error information in the alarm log
  5. 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.