Access2000速成教程－－2.2 定义与使用交叉表查询

核心提示： 从Oracle(大型网站数据库平台)9i开始，Oracle(大型网站数据库平台)的外部表技术（Oracle(大型网站数据库平台) External Tables）被极大的增强，Access2000速成教程－－2.2 定义与使用交叉表查询(2)，通过外部表访问外部数据增强了Oracle(大型网站数据库平台)数据库和

　  从Oracle(大型网站数据库平台)9i开始，Oracle(大型网站数据库平台)的外部表技术（Oracle(大型网站数据库平台) External Tables）被极大的增强，通过外部表访问外部数据增强了Oracle(大型网站数据库平台)数据库和外部数据源进行数据交互的能力，对于数据仓库和ETL来说，这些增强极大的方便了数据访问。

对于DBA来说，最常见一个例子是可以使用外部表来访问警告日志文件或其他跟踪文件.
以下一个例子用来说明外部表的用途。

首先需要创建一个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 t
Oracle(大型网站数据库平台)9i 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

然后创建一个外部表:

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 )
11 location('alert_eygle.log')
12 )
13 reject limit unlimited
14 /

Table created.

然后我们就可以通过外部表进行查询警告日志的内容:

SQL> select * from alert_log where rownum < 51;

TEXT
-----------------------------------------------------------------------------------------
Mon Jun 26 12:00:24 2006
Starting Oracle(大型网站数据库平台) instance (normal)
Mon Jun 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
background_dump_dest = /opt/Oracle(大型网站数据库平台)/admin/eygle/bdump
user_dump_dest = /opt/Oracle(大型网站数据库平台)/admin/eygle/udump
core_dump_dest = /opt/Oracle(大型网站数据库平台)/admin/eygle/cdump
sort_area_size = 524288
db_name = eygle
open_cursors = 500
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 52428800
aq_tm_processes = 0
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7

50 rows selected.

SQL>