Oracle创建外部表，再由外部表创建实体表-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_11961/article/details/81933761

外部表：

这些表并不存储在数据库本身中，而是放在数据库之外，即放在平常的操作系统文件中。在Oracle9i
及以上版本中，利用外部表可以查询数据库之外的一个文件，就好像这个文件也是数据库中平常的表一样。外部表对于向数据
库加载数据最有用（外部表是非常强大的数据加载工具）

创建的语法类似于： "CREATE TABLE ... ORGANIZATION EXTERNAL"
数据在数据库的外部组织，是操作系统文件。
操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。
数据是只读的。（外部表相当于一个临时的只读的虚表）
不可以在上面运行任何 DML 操作，不可以创建索引。
可以查询操作和连接。可以并行操作。

例子：

1、有以下文件demo.txt：

20100830000000,0830,10086,01,0551,40,2,11,100,20100830000005,11,
20100830000000,0830,10086,01,0551,40,2,11,100,20100830000008,11,
20100830000000,0830,10086,08,0558,32,3,11,100,20100830000009,11,
20100830000000,0830,10086,17,0567,32,3,11,100,20100830000313,11,
20100830000001,0830,10086,01,0551,32,3,1911,100,201008300000,11,
20100830000001,0830,10086,01,0551,32,3,11,100,20100830000011,11,
20100830000001,0830,10086,08,0558,32,3,11,100,20100830000013,11,
20100830000001,0830,10086,08,0558,32,3,11,100,20100830000015,11,
20100830000001,0830,10086,03,0553,40,2,11,100,20100830000021,11,
20100830000001,0830,10086,03,0553,40,2,10,80,20100830000145,11,
20100830000001,0830,10086,08,0558,32,3,11,100,20100830000306,11,
20100830000001,0830,10086,11,0561,32,3,11,100,20100830000311,11,
20100830000001,0830,10086,07,0557,32,3,11,100,20100830000313,11,
20100830000001,0830,10086,14,0564,52,1,12,100,20100830000328,12,
20100830000002,0830,10086,10,0550,40,2,11,100,20100830000006,11,
20100830000002,0830,10086,14,0564,40,2,11,100,20100830000007,11,
20100830000002,0830,10086,10,0550,40,2,11,100,20100830000011,11,
20100830000002,0830,10086,01,0551,40,2,10,80,20100830000017,10,

2、创建目录，并赋予权限

SQL> CREATE DIRECTORY testdir AS 'd:\temp\'; 
Directory created 
SQL> GRANT ALL ON DIRECTORY testdir TO scott; 
Grant succeeded

注意：接着把student.txt文件拷贝到d:\temp下面

3、创建外部表

CREATE TABLE demo_ext( 
		LogDate VARCHAR2(20), 
		PartID CHAR(4), 
		CalleeNo VARCHAR2(20), 
		CityID VARCHAR2(10), 
		CityCode VARCHAR2(5), 
		UserGrade NUMBER(4), 
		UserType NUMBER(4), 
		OperateTrace VARCHAR2(30), 
		OperateResult NUMBER(4), 
		PressDate VARCHAR2(20), 
		KeyTrace VARCHAR2(10), 
		ExitDate VARCHAR2(20) 
) 
ORGANIZATION EXTERNAL( 
	TYPE ORACLE_LOADER 
	DEFAULT DIRECTORY testdir 
	ACCESS PARAMETERS ( 
	RECORDS DELIMITED BY NEWLINE 
	BADFILE 'bad.txt' 
	LOGFILE 'log.txt' 
	READSIZE 10485760 
	FIELDS TERMINATED BY ',' 
	LDRTRIM 
	MISSING FIELD VALUES ARE NULL 
	REJECT ROWS WITH ALL NULL FIELDS 
	(LogDate,PartID,CalleeNo,CityID,CityCode,UserGrade,UserType,OperateTrace,OperateResult,PressDate,KeyTrace,ExitDate) 
) 
	LOCATION('demo.txt') 
)reject limit unlimited 
/

3、查看外部表

SQL> SELECT * FROM demo_ext;
LOGDATE PARTID CALLEENO CITYID CITYCODE USERGRADE USERTYPE OPERATETRACE OPERATERESULT PRESSDATE KEYTRACE EXITDATE 
-------------------- ------ -------------------- ---------- -------- --------- -------- ------------------------------ ------------- -------------------- ---------- -------------------- 
20100830000000 0830 10086 01 0551 40 2 11 100 20100830000005 11 
20100830000000 0830 10086 01 0551 40 2 11 100 20100830000008 11 
20100830000000 0830 10086 08 0558 32 3 11 100 20100830000009 11 
20100830000000 0830 10086 17 0567 32 3 11 100 20100830000313 11 
20100830000001 0830 10086 01 0551 32 3 1911 100 201008300000 11 
20100830000001 0830 10086 01 0551 32 3 11 100 20100830000011 11 
20100830000001 0830 10086 08 0558 32 3 11 100 20100830000013 11 
20100830000001 0830 10086 08 0558 32 3 11 100 20100830000015 11 
20100830000001 0830 10086 03 0553 40 2 11 100 20100830000021 11 
20100830000001 0830 10086 03 0553 40 2 10 80  20100830000145 11 
20100830000001 0830 10086 08 0558 32 3 11 100 20100830000306 11 
20100830000001 0830 10086 11 0561 32 3 11 100 20100830000311 11 
20100830000001 0830 10086 07 0557 32 3 11 100 20100830000313 11 
20100830000001 0830 10086 14 0564 52 1 12 100 20100830000328 12 
20100830000002 0830 10086 10 0550 40 2 11 100 20100830000006 11 
20100830000002 0830 10086 14 0564 40 2 11 100 20100830000007 11 
20100830000002 0830 10086 10 0550 40 2 11 100 20100830000011 11 
20100830000002 0830 10086 01 0551 40 2 10 80  20100830000017 10 
18 rows selected

可见外部表已经创建成功！

接着用外部表的数据创建实体表数据：

1、创建实体表

CREATE TABLE t_demo( 
	LogDate VARCHAR2(20), 
	PartID CHAR(4), 
	CalleeNo VARCHAR2(20), 
	CityID VARCHAR2(10), 
	CityCode VARCHAR2(5), 
	UserGrade NUMBER(4), 
	UserType NUMBER(4), 
	OperateTrace VARCHAR2(30), 
	OperateResult NUMBER(4), 
	PressDate VARCHAR2(20), 
	KeyTrace VARCHAR2(10), 
	ExitDate VARCHAR2(20) 
) 
/

2、将外部表数据插入实体表

SQL> INSERT INTO t_demo SELECT * FROM demo_ext; 
18 rows inserted

3、查看实体表

SQL> SELECT * FROM t_demo;
LOGDATE PARTID CALLEENO CITYID CITYCODE USERGRADE USERTYPE OPERATETRACE OPERATERESULT PRESSDATE KEYTRACE EXITDATE 
-------------------- ------ -------------------- ---------- -------- --------- -------- ------------------------------ ------------- -------------------- ---------- -------------------- 
20100830000000 0830 10086 01 0551 40 2 11 100 20100830000005 11 
20100830000000 0830 10086 01 0551 40 2 11 100 20100830000008 11 
20100830000000 0830 10086 08 0558 32 3 11 100 20100830000009 11 
20100830000000 0830 10086 17 0567 32 3 11 100 20100830000313 11 
20100830000001 0830 10086 01 0551 32 3 1911 100 201008300000 11 
20100830000001 0830 10086 01 0551 32 3 11 100 20100830000011 11 
20100830000001 0830 10086 08 0558 32 3 11 100 20100830000013 11 
20100830000001 0830 10086 08 0558 32 3 11 100 20100830000015 11 
20100830000001 0830 10086 03 0553 40 2 11 100 20100830000021 11 
20100830000001 0830 10086 03 0553 40 2 10 80  20100830000145 11 
20100830000001 0830 10086 08 0558 32 3 11 100 20100830000306 11 
20100830000001 0830 10086 11 0561 32 3 11 100 20100830000311 11 
20100830000001 0830 10086 07 0557 32 3 11 100 20100830000313 11 
20100830000001 0830 10086 14 0564 52 1 12 100 20100830000328 12 
20100830000002 0830 10086 10 0550 40 2 11 100 20100830000006 11 
20100830000002 0830 10086 14 0564 40 2 11 100 20100830000007 11 
20100830000002 0830 10086 10 0550 40 2 11 100 20100830000011 11 
20100830000002 0830 10086 01 0551 40 2 10 80  20100830000017 10 
18 rows selected

*******有关建立外部表的参数说明：

create table extnl_table
(
col01 varchar2(100),
col02 number,
......
)
organization external
( type oracle_loader
default directory "xxx"
access parameters
(
records delimited by 0x'0a'
skip 1 fields
terminated by ','
enclosed by '"'
lrtrim
missing field values are null
reject rows with all null fields
(col01,col02,……)
)
location( 'data.txt'，……)
);

外部表的定义要害是oracle loader参数，records要害字后定义假如识别数据行，fields要害字后定义假如识别字段，常用的如下：

records:

delimited by 'xxx' —— 换行符，常用newline定义换行，假如文件中使用了非凡的字符就要另外定义了；假如是非凡符号，可以使用ox'十六位值'，例如tab(\t)的十六位是9，那么就是delimited by 0x'09'；cr(\r)的十六位是d，那么就是delimited by 0x'0d'。

skip x —— 跳过x行数据，有些文件中第一行是列名，需要跳过第一行，则使用skip 1。

fields：

terminated by 'x' —— 字段分割符。

enclosed by 'x' —— 字段引用符，包含在此符号内的数据都当成一个字段。例如一行数据格式如："abc","a""b,""c,"。使用参数terminated by ',' enclosed by '"'后，系统会读到两个字段，第一个字段的值是abc，第二个字段值是a"b,"c,。

lrtrim —— 删除空白字符。

missing field values are null —— 空缺值都设为null。

假如外部表文件的字段长度和分割符不确定，可以打开文件看看。但是假如文件非常大，比如用十几g，用ultraedit之类的工具打开也许会等十几个小时。那么可以使用分割文件的工具将文件切割成1m左右的小文件，再用记事本打开，复制几行数据出来作测试用。

读取外部表时最常见的问题就是报buffer不足了，假如增大read size还不能解决问题，可能就是断行错误，也许是换行符不对。因为有些换行符在记事本中看不出来，比如说windows的换行符是\n\r，而mac等系统中是line feed(lf, 0x0a)。假如在windows中打开带lf的文件，记事本中显示的是一个黑色的方块，而写字板中是能够显示正确的换行。所以最好使用ultraedit打开，直接看16进制代码，使用delimited by 0x'xx'断行。

还有可能在从外部表读取数据时，读取前面的数据没问题，读到一定的行数就报错。这可能是某行数据出问题导致。只是可以在外部文件目录下发现文件名带有"bad"的日志文件，其中就保存了出错的数据，有记事本打开看看那里出错，是否与外部表定义冲突。