外部表在Oracle 9i中引入，外部表的特性是對現有SQL*Loader功能的補充，它能夠使您訪問外部資料源中的資料，就像訪問資料庫中的表一樣，本篇就Oracle 12c中的外部表進行總結和演示，演示環境為Oracle Database 12c Enterprise Edition Release 12.1.0.2.0，
1 外部表介紹
外部表是通過create table ... organization external陳述句創建的，當創建外部表時，需指定如下屬性：
- TYPE：指定外部表的型別，即訪問驅動，訪問驅動運行在服務器內部的，這點和SQL*Loader不同，有兩種可用的型別，分別為：
- ORACLE_LOADER：默認的訪問驅動，它可以將資料從外部表加載到內部表，資料必須來自文本資料檔案，但它不能將資料從內部表卸載到外部表；
- ORACLE_DATAPUMP：該訪問驅動可以執行資料的加載和卸載，資料必須來自二進制轉儲檔案，從外部表加載到內部表是通過從二進制轉儲檔案中提取來完成，而從內部表卸載到外部表是通過填充外部表的二進制轉儲檔案來完成的，轉儲檔案一旦創建，可以讀取任意次數，但不能被修改，也就是說，不能執行任何DML陳述句；
- DEFAULT DIRECTORY：指定所有輸入輸出檔案使用的默認目錄，位置是一個目錄物件，而不是一個目錄路徑，在創建外部表前必須先創建目錄物件，否則，會出錯；
- ACCESS PARAMETERS：描述外部資料源，并實作指定的外部表的型別，每一種型別的外部表都有自己的訪問驅動程式，該驅動程式為該型別的外部表提供唯一的訪問引數；
- LOCATION：指定外部表的資料檔案，檔案命名方式為directory:file，其中，目錄是可選的，若不指定，默認為默認目錄，
2 外部表創建
外部表的創建可以通過兩種訪問驅動實作，下面將分別介紹，
2.1 創建目錄物件
SQL> ho mkdir /home/oracle/ext
SQL> create directory ext_dir as '/home/oracle/ext';
Directory created.
SQL> grant read,write on directory ext_dir to alen;
Grant succeeded.2.2 ORACLE_LOAD訪問驅動
2.2.1 手動創建外部表
創建外部表時如果資料檔案不是文本檔案，則需要使用preprocessor子句，
1）創建測驗資料
[oracle@odd ext]$ cat emp.dat
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
"7369","SMITH","CLERK","7902","1980/12/17","800.00","","20"
"7499","ALLEN","SALESMAN","7698","1981/2/20","1600.00","300.00","30"
"7521","WARD","SALESMAN","7698","1981/2/22","1250.00","500.00","30"
"7566","JONES","MANAGER","7839","1981/4/2","2975.00","","20"
"7654","MARTIN","SALESMAN","7698","1981/9/28","1250.00","1400.00","30"
"7698","BLAKE","MANAGER","7839","1981/5/1","2850.00","","30"
"7782","CLARK","MANAGER","7839","1981/6/9","2450.00","","10"
"7788","SCOTT","ANALYST","7566","1987/4/19","3000.00","","20"
"7839","KING","PRESIDENT","","1981/11/17","5000.00","","10"
"7844","TURNER","SALESMAN","7698","1981/9/8","1500.00","0.00","30"
"7876","ADAMS","CLERK","7788","1987/5/23","1100.00","","20"
"7900","JAMES","CLERK","7698","1981/12/3","950.00","","30"
"7902","FORD","ANALYST","7566","1981/12/3","3000.00","","20"
"7934","MILLER","CLERK","7782","1982/1/23","1300.00","","10"2）創建外部表
SQL> conn alen/alen@odd
Connected.
SQL> ho cat /home/oracle/ext/emp.sql
create table emp
(
empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
)
organization external --指定創建的表是外部表
(
type oracle_loader --外部表訪問驅動型別
default directory ext_dir --默認目錄，需提前創建
access parameters --設定訪問引數，注意先后順序，否則出錯
(
records delimited by newline
badfile ext_dir:'emp.bad'
discardfile ext_dir:'emp.dsc'
logfile ext_dir:'emp.log'
field names all files ignore
fields terminated by ',' optionally enclosed by '"' ldrtrim
reject rows with all null fields
(empno,
ename,
job,
mgr,
hiredate char(10) date_format date mask 'yyyy-mm-dd',
sal,
comm,
deptno
))
location('emp.dat')
)reject limit unlimited;
SQL> @emp.sql
Table created.
SQL> set linesize 200
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.2.2.2 通過sqlldr創建外部表
外部表可以手動寫SQL，也可以使用sqlldr工具生成外部表，下面是具體創建程序，
1）創建控制檔案
[oracle@odd ext]$ cat alen.ctl
load data
field names all files ignore
infile 'emp.dat'
badfile 'emp.bad'
discardfile 'emp.dsc'
into table alen
fields terminated by ',' optionally enclosed by '"'
(empno,
ename,
job,
mgr,
hiredate date 'yyyy-mm-dd',
sal,
comm,
deptno )2）sqlldr利用generate_only生成外部表
[oracle@odd ext]$ sqlldr alen/alen@odd control=alen.ctl external_table=generate_only log=alen.log
SQL*Loader: Release 12.1.0.2.0 - Production on Wed May 13 22:08:47 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: External Table3）查看日志
[oracle@odd ext]$ cat alen.log
SQL*Loader: Release 12.1.0.2.0 - Production on Wed May 13 22:08:47 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Control File: alen.ctl
Data File: emp.dat
Bad File: emp.bad
Discard File: emp.dsc
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table
Table ALEN, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO FIRST * , O(") CHARACTER
ENAME NEXT * , O(") CHARACTER
JOB NEXT * , O(") CHARACTER
MGR NEXT * , O(") CHARACTER
HIREDATE NEXT * , O(") DATE 'yyyy-mm-dd'
SAL NEXT * , O(") CHARACTER
COMM NEXT * , O(") CHARACTER
DEPTNO NEXT * , O(") CHARACTER
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_ALEN"
(
"EMPNO" NUMBER(4),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'EXT_DIR':'emp.bad'
DISCARDFILE 'EXT_DIR':'emp.dsc'
LOGFILE 'alen.log_xt'
READSIZE 1048576
FIELD NAMES ALL FILES IGNORE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPNO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"ENAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"JOB" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"MGR" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"HIREDATE" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
DATE_FORMAT DATE MASK 'yyyy-mm-dd',
"SAL" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"COMM" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"DEPTNO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'emp.dat'
)
)REJECT LIMIT UNLIMITED
INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO ALEN
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
)
SELECT
"EMPNO",
"ENAME",
"JOB",
"MGR",
"HIREDATE",
"SAL",
"COMM",
"DEPTNO"
FROM "SYS_SQLLDR_X_EXT_ALEN"
statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_ALEN"
Run began on Wed May 13 22:08:47 2020
Run ended on Wed May 13 22:08:47 2020
Elapsed time was: 00:00:00.47
CPU time was: 00:00:00.014）利用上述sql創建外部表SQL
[oracle@odd ext]$ cat alen.sql
CREATE TABLE alen
(
"EMPNO" NUMBER(4),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'EXT_DIR':'emp.bad'
DISCARDFILE 'EXT_DIR':'emp.dsc'
LOGFILE 'alen.log_xt'
READSIZE 1048576
FIELD NAMES ALL FILES IGNORE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPNO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"ENAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"JOB" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"MGR" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"HIREDATE" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
DATE_FORMAT DATE MASK 'yyyy-mm-dd',
"SAL" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"COMM" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"DEPTNO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'emp.dat'
)
)REJECT LIMIT UNLIMITED5）洗掉原alen表，創建外部表并驗證
SQL> drop table alen;
Table dropped.
SQL> @alen.sql
51 ;
Table created.
SQL> select count(1) from alen;
COUNT(1)
----------
142.3 ORACLE_DATAPUMP訪問驅動
創建外部表，除了使用ORACLE_LOADER驅動之外，還可以使用ORACLE_DATAPUMP，使用ORACLE_DATAPUMP可以實作資料的卸載和加載，通過create table as select 陳述句，ORACLE_DATAPUMP驅動可以將資料寫到一個二進制的dump檔案中，但該檔案只能通過ORACLE_DATAPUMP訪問驅動進行訪問，而且，該檔案一旦創建，不能被修改，可以被多次讀取，
2.3.1 卸載資料
1）創建測驗資料
SQL> conn alen/alen@odd
Connected.
SQL> create table dept as select * from scott.dept;
Table created.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON2）CTAS方式創建外部表
SQL> ho cat /home/oracle/ext/dept.sql
create table dept_xt
organization external
(
type oracle_datapump
default directory ext_dir
location('dept_xt.dump')
)
as select * from dept;
SQL> @/home/oracle/ext/dept.sql
Table created.3）查看表及dump檔案
SQL> select * from dept_xt;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>ho ls /home/oracle/ext/dept_xt.dump
/home/oracle/ext/dept_xt.dump4）修改外部表，出錯，即不能進行修改資料
SQL> update dept_xt set loc='SZ' where deptno=10;
update dept_xt set loc='SZ' where deptno=10
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table2.3.2 加載資料
可以利用上面創建的dump檔案創建新的外部表，
1）利用dump檔案創建外部表，location位置可以指定多個dump檔案
SQL> ho cat /home/oracle/ext/alen_xt.sql
create table alen_xt
(
deptno number(2),
dname varchar2(14),
loc varchar2(13)
)
organization external
(
type oracle_datapump
default directory ext_dir
location('dept_xt.dump')
)
;
SQL> @/home/oracle/ext/alen_xt.sql
Table created.2）查看資料
SQL> select * from alen_xt;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON通過上面的介紹和演示，可以看出，外部表在使用上有很大的優勢，尤其是在批量處理外部資料的時候，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/2490.html
標籤：Oracle