Oracle Technology: Using Oracle's external Table query warning log file

Since Oracle9i, Oracle's external Table technology (Oracle External tables) has been greatly enhanced, with external tables accessing external data enhances the ability of Oracle databases and external data sources to interact with data, for data warehousing and ETL, These enhancements are greatly facilitated by data access.

One of the most common examples for DBAs is the use of external tables to access warning log files or other trace files.

The following example is used to illustrate the purpose of an external table.

First you need to create a directory:

[Oracle@jumper oracle]$ Sqlplus "/As SYSDBA"

Sql*plus:release 9.2.0.4.0-production on Sun Oct 15 21:42:28 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0-production

With the partitioning option

Jserver Release 9.2.0.4.0-production

sql> Create or replace directory Bdump

2 as '/opt/oracle/admin/eygle/bdump ';

Directory created.

Sql> Col Directory_path for A30

Sql> Col owner for A10

Sql> select * from Dba_directories;

OWNER Directory_name Directory_path

---------- ------------------------------ ------------------------------

SYS Bdump/opt/oracle/admin/eygle/bdump

Then create an external table:

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)

One location (' Alert_eygle.log ')

12)

Reject Limit Unlimited

14/

Table created.

We can then query the contents of the warning log through the external table:

Sql> SELECT * from Alert_log where rownum < 51;

TEXT

-----------------------------------------------------------------------------------------

Mon June 26 12:00:24 2006

Starting ORACLE instance (normal)

Mon June 26 12:00:25 2006

Warning:einval creating segment of size 0x0000000008c00000

Fix SHM parameters In/etc/system or equivalent

license_max_session = 0

license_sessions_warning = 0

SCN Scheme 2

Using log_archive_dest Parameter Default value

License_max_users = 0

SYS Auditing is disabled

Starting up ORACLE RDBMS version:9.2.0.4.0.

System parameters with Non-default values:

processes = 150

Timed_statistics = TRUE

Shared_pool_size = 104857600

Large_pool_size = 0

Java_pool_size = 0

Control_files =/opt/oracle/oradata/eygle/control01.ctl

Db_block_size = 8192

Db_cache_size = 16777216

Db_cache_advice = On

Compatible = 9.2.0.0.0

db_file_multiblock_read_count= 16

Fast_start_mttr_target = 300

Log_checkpoints_to_alert = TRUE

Undo_management = AUTO

Undo_tablespace = UNDOTBS1

Undo_retention = 10800

Remote_login_passwordfile= EXCLUSIVE

Db_domain =

instance_name = Eygle

Job_queue_processes = 10

hash_join_enabled = TRUE