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