External Table
一、 Oracle 提供了两种外部表访问驱动：
?ORACLE_LOADER/? ORACLE_DATAPUMP.
ORACLE_LOADER: 它利用 oracle loader 技术从外部表读取数据. 它还具有类似 SQL*Loader 工具控制文件语法的数据映射能力.
ORACLE_DATAPUMP: 它提供卸载数据的能力,即把数据从数据库里导入一个外部表,再由一个或几个外部表导入到数据库里. 对于 AS subquery 的子句必须使用 ORACLE_DATAPUMP.
二、 错误处理
1) REJECT LIMIT 子句
在创建外部表时最后加入 LIMIT 子句，表示可以允许错误的发生个数。
- 默认的 REJECT LIMIT 值为 0
- REJECT LIMIT UNLIMITED 则不会报错
2) BADFILE 和 NOBADFILE 子句
在 accessparameters中加入 BADFILE 'BAD_FILE.txt'子句，则所有数据转换错误的值会被放入'BAD_FILE.txt'中,使用 NOBADFILE 子句则表示忽略转换错误的数据
- 如果不写 BADFILE 或 NOBADFILE，则系统自动在源目录下生成与外部表同名的.BAD 文件
- BADFILE 只能记录前 1 次操作的结果，他会被第 2 次操作所覆盖。
3) LOGFILE 和 NOLOGFILE 子句
在 accessparameters中加入 LOGFILE'LOG_FILE.log'子句，则所有 Oracle 的错误信息放入'LOG_FILE.log'中使用 NOLOGFILE 子句则表示不记录错误信息到 log 中
- 如果不写 LOGFILE 或 NOLOGFILE，则系统自动在源目录下生成与外部表同名的.LOG 文件
三、 外部表修改
1) 更改拒绝限制
ALTER TABLE tab_name LIMIT 100;
2) 更改默认目录说明
ALTER TABLE tab_name DIRECTORY DEFAULT DIRECTORY bdump;
3) 修改访问参数,如分隔符由","变为"|"
ALTER TABLE tab_name PARAMETERS ACCESS PARAMETERS (FIELDS TERMINATED BY '|');
4) 修改文件位置:
ALTER TABLE tab_name LOCATION('aaa.txt');
5) 删除表
drop table tab_name ;
6) 删除目录
drop DIRECTORY bdump;
1.ORACLE_DATAPUMP 驱动
利用 DATAPUMP 卸载 scott 下的表 dept
1) 创建目录
[oracle@wang ~]$ mkdir ext
[oracle@wang ~]$ ls
base.ctl base_data.bad base_data.dat base.log datadump ext
[oracle@wang ~]$ cd ext/
[oracle@wang ext]$ pwd
/home/oracle/ext
[oracle@wang ext]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 24 12:12:56 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
——创建目录对象：(并授权）
SYS@ORA11GR2>create or replace directory dir_ext as '/home/oracle/ext';
Directory created.
SYS@ORA11GR2>grant read,write on directory dir_ext to public;
Grant succeeded.
2) 用创建外部表的方式卸载表到目录对象 dir_ext指定目录下
SYS@ORA11GR2>conn scott/tiger
Connected.
SCOTT@ORA11GR2>create table ext_dept(deptno,dname,loc)
2 organization external
3 (
4 type oracle_datapump
5 default directory dir_ext
6 location ('ext1.dat','ext2.dat')
7 )
8 parallel 2
9 as select * from scott.dept;
Table created.
3) 查看卸载文件
SCOTT@ORA11GR2>host ls ext*
ext1.dat ext2.dat
4) 查看外部表字典表
SCOTT@ORA11GR2>select table_name,type_name,default_directory_name from user_external_tables;
TABLE_NAME TYPE_NAME DEFAULT_DIRECTORY_NAME
---------- -------------------- ------------------------------
EXT_DEPT ORACLE_DATAPUMP DIR_EXT
2. ORACLE_LOADER 驱动
1) 创建目录
[oracle@wang ~]$ mkdir data
[oracle@wang ~]$ mkdir log
[oracle@wang ~]$ mkdir bad
[oracle@wang ~]$
[oracle@wang ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 24 12:34:20 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
——创建目录对象：
SYS@ORA11GR2>create or replace directory dir_log as '/home/oracle/log';
Directory created.
SYS@ORA11GR2>create or replace directory dir_bad as '/home/oracle/bad';
Directory created.
SYS@ORA11GR2>create or replace directory dir_data as '/home/oracle/data';
Directory created.
——授权：
SYS@ORA11GR2>grant read,write on directory dir_data to public;
Grant succeeded.
SYS@ORA11GR2>grant read,write on directory dir_log to public;
Grant succeeded.
SYS@ORA11GR2>grant read,write on directory dir_bad to public;
Grant succeeded.
2) 创建数据文件
SYS@ORA11GR2>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@wang ~]$ cd data
[oracle@wang data]$ vi d1.dat
10,accounting,new york
~
"d1.dat" [New] 2L, 25C written
[oracle@wang data]$ vi d2.dat
20,research,dallas
~
"d2.dat" [New] 1L, 19C written
[oracle@wang data]$
[[oracle@wang data]$ vi d3.dat
30,sales,chicago
40,operations,boston
~
"d3.dat" 2L, 38C written
[oracle@wang data]$
[oracle@wang data]$ ls
d1.dat d2.dat d3.dat
3) 创建外部表
SCOTT@ORA11GR2>create table extab_ldr
2 (
3 deptno number(2),
4 dname varchar2(14),
5 loc varchar2(13)
6 )
7 organization external
8 (
9 type oracle_loader
10 default directory dir_data
11 access parameters
12 (
13 records delimited by newline
14 badfile dir_bad:'ext%a_%p.bad'
15 logfile dir_log:'ext%a_%p.log'
16 fields terminated by ','
17 missing fields values are null
18 )
19 location ('d1.dat','d2.dat','d3.dat')
20 )
21 parallel
22 reject limit unlimited;
Table created.
4) 查看外部表
SCOTT@ORA11GR2>select table_name,type_name,default_directory_name from user_external_tables;
TABLE_NAME TYPE_NAME DEFAULT_DIRECTO
---------- ------------------------- ---------------
EXT_DEPT ORACLE_DATAPUMP DIR_EXT
EXTAB_LDR ORACLE_LOADER DIR_DATA
SCOTT@ORA11GR2>select TABLE_NAME,TABLESPACE_NAME from user_tables;
TABLE_NAME TABLESPACE_NAME
---------- ------------------------------
DEPT USERS
EMP USERS
BONUS USERS
SALGRADE USERS
SL_BASE USERS
EXT_DEPT （没有表空间属性，即为外部表）
EXTAB_LDR （没有表空间属性，即为外部表）
7 rows selected.
3.外部表查看警告日志
1) 创建目录（警告日志相应目录）
/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace
[oracle@wang trace]$
[oracle@wang trace]$ ls alert_ORA11GR2.log
alert_ORA11GR2.log
——创建目录对象及授权：
SYS@ORA11GR2>create or replace directory dir_log as '/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace';
Directory created.
SYS@ORA11GR2>grant read on directory dir_log to public;
Grant succeeded.
2) 创建外部表
SCOTT@ORA11GR2>create table alert_log (text varchar2(4000))
2 organization external (
3 type oracle_loader
4 default directory dir_log
5 access parameters (
6 records delimited by newline
7 nobadfile
8 nodiscardfile
9 nologfile
10 )
11 location ('alert_ORA11GR2.log')
12 ) reject limit unlimited
13 /
Table created.
3) 查看日志外部表
SCOTT@ORA11GR2>select count(*) from alert_log;
COUNT(*)
----------
4513
——查看外部表alert_log：
SCOTT@ORA11GR2>select * from alert_log where text like'ORA-%' and rownum=1;
TEXT
--------------------------------------------------------------------------------
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
4.利用 SQL*Loader 生成创建外部表的命令
1) 创建控制文件
[oracle@wang ~]$ vi t.ctl
load data
infile '/home/oracle/data/d1.dat'
infile '/home/oracle/data/d1.dat'
infile '/home/oracle/data/d3.dat'
badfile '/home/oracle/bad/t.bad'
append
into table scott.extab_ldr
fields terminated by ',' optionally encolsed by '"'
trailing nullcols
(deptno,dname,loc)
~
"t.ctl" [New] 10L, 268C written
2) 通过 sqlldr 生成创建外部表脚本
[oracle@wang ~]$ sqlldr scott/tiger control=t.ctl external_table=generate_only log=t.log
SQL*Loader: Release 11.2.0.4.0 - Production on Sat Sep 24 13:33:28 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-284: Warning: Input data file /home/oracle/data/d1.dat specified multiple times.
[oracle@wang ~]$ ls t*
t.ctl t.log
3) 查看 t.log，日志中详细的记录了通过 ORACLE_LOADER 驱动创建外部表的步骤
[oracle@wang ~]$ cat t.log
SQL*Loader: Release 11.2.0.4.0 - Production on Sat Sep 24 13:33:28 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-284: Warning: Input data file /home/oracle/data/d1.dat specified multiple times.
Control File: t.ctl
There are 3 data files:
Data File: /home/oracle/data/d1.dat
Bad File: d1.bad
Discard File: none specified
(Allow all discards)
Data File: /home/oracle/data/d1.dat
Bad File: d1.bad
Discard File: none specified
(Allow all discards)
Data File: /home/oracle/data/d3.dat
Bad File: /home/oracle/bad/t.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table
Table SCOTT.EXTAB_LDR, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , O(") CHARACTER
DNAME NEXT * , O(") CHARACTER
LOC NEXT * , O(") CHARACTER
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/data/'
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_EXTAB_LDR"
(
"DEPTNO" NUMBER(2),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'DIR_DT':'d1.bad'
LOGFILE 't.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"DEPTNO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"DNAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"LOC" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'd1.dat',
'd1.dat',
'd3.dat'
)
)REJECT LIMIT UNLIMITED
INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO SCOTT.EXTAB_LDR
(
DEPTNO,
DNAME,
LOC
)
SELECT
"DEPTNO",
"DNAME",
"LOC"
FROM "SYS_SQLLDR_X_EXT_EXTAB_LDR"
statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_EXTAB_LDR"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
Run began on Sat Sep 24 13:33:28 2016
Run ended on Sat Sep 24 13:33:28 2016
Elapsed time was: 00:00:00.19
CPU time was: 00:00:00.03
[oracle@wang ~]$
4) 按照日志中的步骤创建外部表
SYS@ORA11GR2>create directory sys_sqlldr_xt_tmpdir_00000 as '/home/oracle/data/' ;
Directory created.
SYS@ORA11GR2>CREATE TABLE "SYS_SQLLDR_X_EXT_EXTAB_LDR"
2 (
3 "DEPTNO" NUMBER(2),
4 "DNAME" VARCHAR2(14),
5 "LOC" VARCHAR2(13)
6 )
7 ORGANIZATION external
8 (
9 TYPE oracle_loader
10 DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
11 ACCESS PARAMETERS
12 (
13 RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
14 BADFILE 'DIR_DT':'d1.bad'
15 LOGFILE 't.log_xt'
16 READSIZE 1048576
17 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
18 MISSING FIELD VALUES ARE NULL
19 REJECT ROWS WITH ALL NULL FIELDS
20 (
21 "DEPTNO" CHAR(255)
22 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
23 "DNAME" CHAR(255)
24 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
25 "LOC" CHAR(255)
26 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
27 )
28 )
29 location
30 (
31 'd1.dat',
32 'd1.dat',
33 'd3.dat'
34 )
35 )REJECT LIMIT UNLIMITED
36 ;
Table created.
——验证：
SYS@ORA11GR2>select table_name,type_name,default_directory_name from user_external_tables;
TABLE_NAME TYPE_NAME DEFAULT_DIRECTORY_NAME
------------------------------ -------------------- ------------------------------
SYS_SQLLDR_X_EXT_EXTAB_LDR ORACLE_LOADER SYS_SQLLDR_XT_TMPDIR_00000
5) 小结
- 创建目录对象要有相应的权限，本次测试是利用 sys 用户完成的
- 生成的日志中，并没有将目录对象赋予给某用户的步骤（public，意为授予所有人使用),所以，如果想在其他用户下创建外部表，先将目录对象的读权限赋予某用户
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2126621/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31397003/viewspace-2126621/
1346
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
