oracle外部表 初探
外部表是指不在数据库中的表，如操作系统上的一个按一定格式分割的文本文件或者其他类型的表。这个外部表对于Oracle数据库来说，就好像是一张视图，
在数据库中可以像视图一样进行查询等操作。这个视图允许用户在外部数据上运行任何的SQL语句，而不需要先将外部表中的数据装载进数据库中。
不过需要注意是，外部数据表都是只读的，不能够更改。
外部表限制
1.在同一个操作系统文件中只能够使用一个分割符号
2.外部表中，不能够带有标题信息，否则会按第一条数据处理
3.列中有特殊符号时，如果不采用双引号括起来，虽然临时表可以正常创建，但是在采用的时候会出现错误
4.不能够为表创建索引
外部表用途：导入数据，节省空间
一、创建和删除
1: 数据文件的格式
bjuser.csv文件:
20080629,修改,1301110022,邹雪辉,01110022
20080629,修改,1302050023,王晓斌,02050023
20080629,修改,1306060130,邵静,06060130
20080629,修改,1304020386,张晋,04020386
20080629,修改,1301070082,许征,01070082
2:创建目录,并进行授权;
sql> create or replace directory out_tabdir as '/oradata';
sql>grant read,write on directory out_tabdir to users;
注意：创建完毕逻辑目录之后要把平面文件拷贝到该目录下，另外还要注意文件名字不要写错。
3:创建外部表:
Create table bjuser
(yyyymm varchar2(8),
pro_no varchar2(50),
user_id varchar2(20),
user_nm varchar2(20),
user_no varchar2(20)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY out_tabdir
ACCESS Parameters
(
RECORDS DELIMITED BY NEWLINE
badfile 'bad_bjuser.txt'
LOGFILE 'log_bjuser.txt'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(yyyymm,pro_no,user_id,user_nm,user_no)
)
LOCATION('bjuser.csv') ---这里可以多个文件'',''
)reject limit unlimited
表创建完成.
4:进行SELECT 操作看是否正确;
SQL>select * from bjuser
结果如下:
SQL> select * from bjuser;
5.卸载外部表
drop table bjuser
6.删除目录
drop DIRECTORY out_tabdir;
7.更换数据
ALTER TABLE bjuser LOCATION( 'test_main_c2.dat');
二、外部表导入正式表
从外部表提取数据到正式表方式很多:
insert /*+ append*/ 直接插入;
insert into ;create table as 等等.
insert into dx_sms_log as select * from external_dx_sms_bak0127;
注意:可以结合merge into 处理存在就更新不存在就插入的情况.
例子：SYS_SQLLDR_X_EXT_TEST_MAIN是外部表
SQL> MERGE INTO test_main
2 USING SYS_SQLLDR_X_EXT_TEST_MAIN
3 ON ( test_main.id =SYS_SQLLDR_X_EXT_TEST_MAIN.id ) -- 条件是 id 相同
4 WHEN MATCHED THEN UPDATE SET
5 test_main.value =SYS_SQLLDR_X_EXT_TEST_MAIN.value -- 匹配的时候，更新
6 WHEN NOT MATCHED THEN INSERTVALUES(
7 SYS_SQLLDR_X_EXT_TEST_MAIN.id, SYS_SQLLDR_X_EXT_TEST_MAIN.value) -- 源表有，目标表没有，插入
8 ;
三、相关信息
1.如何得到外部表路径的信息:
select * from DBA_EXTERNAL_LOCATIONS;
2.外表表信息
SELECT * from DBA_EXTERNAL_TABLES;
3。查看目录信息
select * from dba_directories;
