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

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

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) 建立目錄（警告日誌相應目錄）

[oracle@wang trace]$ pwd

/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/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

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

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',

'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，意為授予所有人使用),所以，如果想在其他使用者下建立外部表，先將目錄物件的讀許可權賦予某使用者

【移動資料】External Table 外部表

相關文章