|
外部表只能在Oracle 9i之后來使用。簡(jiǎn)單地說，外部表，是指不存在于數(shù)據(jù)庫(kù)中的表。通過向Oracle提供描述外部表的元數(shù)據(jù)，我們 可以把一個(gè)操作系統(tǒng)文件當(dāng)成一個(gè)只讀的數(shù)據(jù)庫(kù)表，就像這些數(shù)據(jù)存儲(chǔ)在一個(gè)普通數(shù)據(jù)庫(kù)表中一樣來進(jìn)行訪問。外部表是對(duì)數(shù)據(jù)庫(kù)表的延伸。
一、外部表的特性 位于文件系統(tǒng)之中，按一定格式分割，如文本文件或者其他類型的表可以作為外部表。 對(duì)外部表的訪問可以通過SQL語句來完成，而不需要先將外部表中的數(shù)據(jù)裝載進(jìn)數(shù)據(jù)庫(kù)中。 外部數(shù)據(jù)表都是只讀的，因此在外部表不能夠執(zhí)行DML操作，也不能創(chuàng)建索引。 ANALYZE語句不支持采集外部表的統(tǒng)計(jì)數(shù)據(jù)，應(yīng)該使用DMBS_STATS包來采集外部表的統(tǒng)計(jì)數(shù)據(jù)。
二、創(chuàng)建外部表的注意事項(xiàng) 1.需要先建立目錄對(duì)象
2.對(duì)于操作系統(tǒng)文件的要求 文件要有固定的格式、不能有標(biāo)題列、訪問時(shí)會(huì)自動(dòng)創(chuàng)建一個(gè)日志文件
3.在建立臨時(shí)表時(shí)的相關(guān)限制 對(duì)表中字段的名稱存在特殊字符的情況下，必須使用英文狀態(tài)的下的雙引號(hào)將該表列名稱連接起來。如采用”SalseID#”。 對(duì)于列名字中特殊符號(hào)未采用雙引號(hào)括起來時(shí)，會(huì)導(dǎo)致無法正常查詢數(shù)據(jù)。 建議不用使用特殊的列標(biāo)題字符
在創(chuàng)建外部表的時(shí)候，并沒有在數(shù)據(jù)庫(kù)中創(chuàng)建表，也不會(huì)為外部表分配任何的存儲(chǔ)空間。 創(chuàng)建外部表只是在數(shù)據(jù)字典中創(chuàng)建了外部表的元數(shù)據(jù)，以便對(duì)應(yīng)訪問外部表中的數(shù)據(jù)，而不在數(shù)據(jù)庫(kù)中存儲(chǔ)外部表的數(shù)據(jù)。 簡(jiǎn)單地說，數(shù)據(jù)庫(kù)存儲(chǔ)的只是與外部文件的一種對(duì)應(yīng)關(guān)系，如字段與字段的對(duì)應(yīng)關(guān)系。而沒有存儲(chǔ)實(shí)際的數(shù)據(jù)。 由于存儲(chǔ)實(shí)際數(shù)據(jù)，故無法為外部表創(chuàng)建索引，同時(shí)在數(shù)據(jù)使用DML時(shí)也不支持對(duì)外部表的插入、更新、刪除等操作。
4.刪除外部表或者目錄對(duì)象 一般情況下，先刪除外部表，然后再刪除目錄對(duì)象，如果目錄對(duì)象中有多個(gè)表，應(yīng)刪除所有表之后再刪除目錄對(duì)象。 如果在未刪除外部表的情況下，強(qiáng)制刪除了目錄，在查詢到被刪除的外部表時(shí)，將收到"對(duì)象不存在"的錯(cuò)誤信息。 查詢dba_external_locations來獲得當(dāng)前所有的目錄對(duì)象以及相關(guān)的外部表，同時(shí)會(huì)給出這些外部表所對(duì)應(yīng)的操作系統(tǒng)文件的名字。
5.對(duì)于操作系統(tǒng)平臺(tái)的限制 不同的操作系統(tǒng)對(duì)于外部表有不同的解釋和顯示方式 如在Linux操作系統(tǒng)中創(chuàng)建的文件是分號(hào)分隔且每行一條記錄，但該文件在Windows操作系統(tǒng)上打開則并非如此。 建議避免不同操作系統(tǒng)以及不同字符集所帶來的影響
三、創(chuàng)建外部表 使用CREATE TABLE語句的ORGANIZATION EXTENERAL子句來創(chuàng)建外部表。外部表不分配任何盤區(qū)，因?yàn)閮H僅是在數(shù)據(jù)字典中創(chuàng)建元數(shù)據(jù)。 1.外部表的創(chuàng)建語法 createtabletable_name (col1 datatype1,col2 datatype2,col3 datatype3) organization exteneral (.....)
2.由查詢結(jié)果集，使用Oracle_datapump來填充數(shù)據(jù)來生成外部表 a.創(chuàng)建系統(tǒng)目錄以及Oracle數(shù)據(jù)目錄名來建立對(duì)應(yīng)關(guān)系，同時(shí)授予權(quán)限 [oracle@oradb ~]$ mkdir-p/home/oracle/external_tb/data
sys@ORCL>createorreplacedirectory dat_diras'/home/oracle/external_tb/data/';
sys@ORCL>grantread,writeondirectory dat_dirtoscott;
b.創(chuàng)建外部表
scott@ORCL>createtableex_tb1 --創(chuàng)建外部表 2 (ename,job,sal,dname) --表列描述，注意未指定數(shù)據(jù)類型 3 organizationexternal 4 ( 5 typeoracle_datapump --使用datapump將查詢結(jié)果填充到外部表,注,此處由select生成，故不支持oracle_loader 6 defaultdirectory dat_dir --指定外部表的存放目錄 7 location('tb1.exp','tb2.exp') --產(chǎn)生外部表的內(nèi)容將填充到這些文件中 8 ) 9 parallel --按并行方式來填充 10 as 11 selectename,job,sal,dname -填充使用的原始數(shù)據(jù) 12 fromempjoindept 13 onemp.deptno=dept.deptno;
c.--驗(yàn)證外部表 scott@ORCL>select*fromex_tb1;
ENAME JOB SAL DNAME ---------- --------- ---------- -------------- SMITH CLERK 800 RESEARCH ALLEN SALESMAN 1600 SALES WARD SALESMAN 1250 SALES JONES MANAGER 2975 RESEARCH ..........
對(duì)于使用上述方式創(chuàng)建的外部表可以將其復(fù)制到其他路徑作為外部表的原始數(shù)據(jù)來生成新的外部表，用于轉(zhuǎn)移數(shù)據(jù)。
3.使用SQLLDR提供外部表的定義并創(chuàng)建外部表 關(guān)于SQL*Loader的使用請(qǐng)參照：SQL*Loader使用方法 我們使用SQL*Loader和下面的這個(gè)控制文件來生成外部表的定義 [oracle@oradb ~]$ cat demo1.ctl LOADDATA INFILE* INTOTABLEDEPT_NEW FIELDS TERMINATEDBY',' (DEPTNO,DNAME,LOC) BEGINDATA 10,Sales,Virginia 20,Accounting,Virginia 30,Consulting,Virginia 40,Finance,Virginia
[oracle@oradb ~]$ sqlldr scott/tiger control=demo1.ctl external_table=generate_only
EXTERNAL_TABLE參數(shù)有以下三個(gè)值： NOT_USED：默認(rèn)值。 EXECUTE：這個(gè)值說明SQLLDR不會(huì)生成并執(zhí)行一個(gè)SQLINSERT語句；而是會(huì)創(chuàng)建一個(gè)外部表，且使用一個(gè)批量SQL語句來加載。 GENERATE_ONLY：使SQLLDR并不具體加載任何數(shù)據(jù)，而只是會(huì)生成所執(zhí)行的SQL DDL和DML語句，并放到它創(chuàng)建的日志文件中。
注：DIRECT=TRUE覆蓋EXTENAL_TABLE=GENERATE_ONLY。如果指定了DIRECT=TRUE，則會(huì)加載數(shù)據(jù)，而不會(huì)生成外部表。
[oracle@oradb ~]$ cat demo1.log --查看sqlldr產(chǎn)生的日志文件
TableDEPT_NEW,loadedfromevery logical record. Insertoptionineffectforthistable:INSERT
ColumnName Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- DEPTNO FIRST * , CHARACTER DNAME NEXT * , CHARACTER LOC NEXT * , CHARACTER
CREATEDIRECTORY statements neededforfiles --創(chuàng)建一個(gè)目錄 ------------------------------------------------------------------------ CREATEDIRECTORY SYS_SQLLDR_XT_TMPDIR_00000AS'/home/oracle/'
CREATETABLEstatementforexternaltable: --生成創(chuàng)建外部表的命令 ------------------------------------------------------------------------ CREATETABLE"SYS_SQLLDR_X_EXT_DEPT_NEW" ( "DEPTNO" NUMBER(2), "DNAME" VARCHAR2(20), "LOC" VARCHAR2(20) ) ORGANIZATIONexternal --該子句表明是一個(gè)外部表heap對(duì)應(yīng)普通表，index對(duì)應(yīng)iot，external對(duì)應(yīng)外部表 ( TYPEoracle_loader --說明外部文件訪問方式：oracle_loader或oracle_datapump(9i不支持) DEFAULTDIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 --指定外部文件的缺省目錄 ACCESS PARAMETERS --這個(gè)訪問參數(shù)有些類似于sqlldr中控制文件中的描述信息 ( --系統(tǒng)根據(jù)這些描述信息來生成外部表的格式 RECORDS DELIMITEDBYNEWLINE CHARACTERSET US7ASCII --記錄默認(rèn)以換行符結(jié)束 BADFILE'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad' --存放處理失敗的記錄文件描述 LOGFILE'demo1.log_xt' --日志文件 READSIZE 1048576--Oracle讀取輸入數(shù)據(jù)文件所用的默認(rèn)緩沖區(qū),此處為MB,如專用模式則從PGA分配,如共享模式則從SGA分配 SKIP 6 --跳過的記錄數(shù)，因?yàn)槲覀兪褂昧丝刂莆募?，所以前面的控制信息需要跳過 FIELDS TERMINATEDBY"," LDRTRIM --描述字段的終止符 REJECT ROWSWITHALLNULLFIELDS --所有為空值的行被跳過并且記錄到bad file. ( --下面是描述外部文件各個(gè)列的定義 "DEPTNO"CHAR(255) TERMINATEDBY",", "DNAME"CHAR(255) TERMINATEDBY",", "LOC"CHAR(255) TERMINATEDBY"," ) ) location ( 'demo1.ctl' --描述外部文件的文件名 ) )REJECT LIMIT UNLIMITED --描述允許的錯(cuò)誤數(shù)，此處為無限制
INSERTstatements usedtoloadinternal tables: --用于將數(shù)據(jù)填充到表，使用append方式 ------------------------------------------------------------------------ INSERT/*+ append */INTODEPT_NEW ( DEPTNO, DNAME, LOC ) SELECT "DEPTNO", "DNAME", "LOC" FROM"SYS_SQLLDR_X_EXT_DEPT"
statementstocleanup objects createdbyprevious statements: --用于刪除目錄和外部表的定義信息 ------------------------------------------------------------------------ DROPTABLE"SYS_SQLLDR_X_EXT_DEPT_NEW" DROPDIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
----------------------------------------------------------------------------------------------------------------------- sys@ORCL>grantcreateanydirectorytoscott;
sys@ORCL>grantdropanydirectorytoscott;
scott@ORCL>createtabledept_new 2 (deptno number,dname varchar2(20),loc varchar2(25));
scott@ORCL>select*fromdept_new;
norows selected
[oracle@oradb ~]$ sqlldr scott/tiger control=demo1.ctl external_table=execute
scott@ORCL>select*fromdept_new;
DEPTNO DNAME LOC ---------- -------------------- ------------------------- 10 Sales Virginia 20 Accounting Virginia 30 Consulting Virginia 40 Finance Virginia
4.使用平面文件定義并生成外部表 a.平面文件數(shù)據(jù) 1.dat： 7369,SMITH,CLERK,7902,17-DEC-80,100,0,20 7499,ALLEN,SALESMAN,7698,20-FEB-81,250,0,30 7521,WARD,SALESMAN,7698,22-FEB-81,450,0,30 7566,JONES,MANAGER,7839,02-APR-81,1150,0,20
2.dat： 7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,0,30 7698,BLAKE,MANAGER,7839,01-MAY-81,1550,0,30 7934,MILLER,CLERK,7782,23-JAN-82,3500,0,10
b.繼續(xù)使用前面創(chuàng)建的目錄/home/oracle/external_tb/data來存放數(shù)據(jù)文件：
sys@ORCL>select*fromdba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH --------------- --------------- --------------------------------------------- SYS DATA_PUMP_DIR /u01/oracle/10g/rdbms/log/ SYS DAT_DIR /home/oracle/external_tb/data/
scott@ORCL>ho ls/home/oracle/external_tb/data/ 1.dat 2.dat tb1.exp tb2.exp
c.創(chuàng)建外部表 scott@ORCL>get/u01/bk/scripts/tb.emp_new 1 createtableemp_new 2 ( 3 emp_id number(4), 4 ename varchar2(15), 5 job varchar2(12), 6 mgr_id number(4), 7 hiredate date, 8 salary number(8), 9 comm number(8), 10 dept_id number(2) 11 ) 12 organizationexternal 13 ( 14 typeoracle_loader 15 defaultdirectory dat_dir 16 access parameters 17 ( 18 records delimitedbynewline 19 fields terminatedby',' 20 ) 21 location 22 ('1.dat','2.dat') 23*);
scott@ORCL>start/u01/bk/scripts/tb.emp_new
d.驗(yàn)證外部表 scott@ORCL>select*fromemp_new;
EMP_ID ENAME JOB MGR_ID HIREDATE SALARY COMM DEPT_ID ---------- --------------- ------------ ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 100 0 20 7499 ALLEN SALESMAN 7698 20-FEB-81 250 0 30 ............................
scott@ORCL>deletefromemp_newwhereename='SMITH'; --外部表不能執(zhí)行DML deletefromemp_newwhereename='SMITH' * ERROR at line 1: ORA-30657:operationnotsupportedonexternalorganizedtable
scott@ORCL>insertintoemp_new(emp_id,ename)select8888,'Robinson'fromdual; insertintoemp_new(emp_id,ename)select8888,'Robinson'fromdual * ERROR at line 1: ORA-30657:operationnotsupportedonexternalorganizedtable
e.獲得外部表的有關(guān)信息： scott@ORCL>col access_parameters format a35 scott@ORCL>selectowner,table_name,type_name,default_directory_name,access_parameters 2 fromdba_external_tables;
OWNER TABLE_NAME TYPE_NAME DEFAULT_DIRECTO ACCESS_PARAMETERS ---------- --------------- ------------------------------ --------------- ----------------------------------- SCOTT EX_TB1 ORACLE_DATAPUMP DAT_DIR SCOTT EMP_NEW ORACLE_LOADER DAT_DIR records delimitedbynewline fields terminatedby','
SCOTT EMP_PUMP ORACLE_DATAPUMP DAT_DIR records delimitedbynewline fields terminatedby','
f.獲得平面文件的位置，使用如下的查詢： scott@ORCL>select*fromdba_external_locationsorderbytable_name;
OWNER TABLE_NAME LOCATION DIR DIRECTORY_NAME ---------- --------------- --------------- --- ------------------------------ SCOTT EMP_NEW 1.dat SYS DAT_DIR SCOTT EMP_NEW 2.dat SYS DAT_DIR SCOTT EMP_PUMP 1.dat SYS DAT_DIR SCOTT EMP_PUMP 2.dat SYS DAT_DIR SCOTT EX_TB1 tb2.exp SYS DAT_DIR SCOTT EX_TB1 tb1.exp SYS DAT_DIR
5.外部表定義的進(jìn)一步分析 CREATETABLEexternal_table ( COL01 VARCHAR2(100), COL02 NUMBER, ...... ) ORGANIZATIONEXTERNAL ( TYPEORACLE_LOADER DEFAULTDIRECTORY "XXX" ACCESS PARAMETERS ( RECORDS DELIMITEDBY0X'0A' SKIP 1 BADFILE'bad.txt' FIELDS TERMINATEDBY',' OPTIONALLY ENCLOSEDBY'"' LRTRIM MISSING FIELDVALUESARENULL REJECT ROWSWITHALLNULLFIELDS ) LOCATION ("CJ_DIR":'data.txt') )REJECT LIMIT UNLIMITED;
外部表定義的幾個(gè)重點(diǎn) a.ORGANIZATIONEXTERNAL關(guān)鍵字，必須要有。以表明定義的表為外部表。 b.重要參數(shù)外部表的類型 ORACLE_LOADER：定義外部表的缺省方式，只能只讀方式實(shí)現(xiàn)文本數(shù)據(jù)的裝載。 ORACLE_DATAPUMP：支持對(duì)數(shù)據(jù)的裝載與卸載，數(shù)據(jù)文件必須為二進(jìn)制dump文件?？梢詮耐獠勘硖崛?shù)據(jù)裝載到內(nèi)部表，也 可以從內(nèi)部表卸載數(shù)據(jù)作為二進(jìn)制文件填充到外部表。 c.DEFAULTDIRECTORY：缺省的目錄指明了外部文件所在的路徑 d.LOCATION：定義了外部表的位置 f.ACCESS PARAMETERS：描述如何對(duì)外部表進(jìn)行訪問 RECORDS關(guān)鍵字后定義如何識(shí)別數(shù)據(jù)行 DELIMITEDBY'XXX'——換行符，常用newline定義換行，并指明字符集。對(duì)于特殊的字符則需要單獨(dú)定義， 如特殊符號(hào)，可以使用OX'十六位值'，例如tab(/t)的十六位是9，則DELIMITEDBY0X'09'； cr(/r)的十六位是d，那么就是DELIMITEDBY0X'0D'。 SKIP X ——跳過X行數(shù)據(jù)，有些文件中第一行是列名，需要跳過第一行，則使用SKIP 1。
FIELDS關(guān)鍵字后定義如何識(shí)別字段，常用的如下： FIELDS：TERMINATEDBY'x'——字段分割符。 ENCLOSEDBY'x'——字段引用符，包含在此符號(hào)內(nèi)的數(shù)據(jù)都當(dāng)成一個(gè)字段。 例如一行數(shù)據(jù)格式如："abc","a""b,""c,"。使用參數(shù)TERMINATEDBY','ENCLOSEDBY'"'后，系統(tǒng)會(huì)讀到兩個(gè)字段， 第一個(gè)字段的值是abc，第二個(gè)字段值是a"b,"c,。 LRTRIM ——刪除首尾空白字符。 MISSING FIELDVALUESARENULL——某些字段空缺值都設(shè)為NULL。
對(duì)于字段長(zhǎng)度和分割符不確定且準(zhǔn)備用作外部表文件，可以使用UltraEdit、Editplus等來進(jìn)行分析測(cè)試，如果文件較 大，則需要考慮將文件分割成小文件并從中提取數(shù)據(jù)進(jìn)行測(cè)試。
外部表對(duì)錯(cuò)誤的處理 REJECT LIMIT UNLIMITED 在創(chuàng)建外部表時(shí)最后加入LIMIT子句，表示可以允許錯(cuò)誤的發(fā)生個(gè)數(shù)。默認(rèn)值為零。設(shè)定為UNLIMITED則錯(cuò)誤不受限制 BADFILE和NOBADFILE子句 用于指定將捕獲到的轉(zhuǎn)換錯(cuò)誤存放到哪個(gè)文件。如果指定了NOBADFILE則表示忽略轉(zhuǎn)換期間的錯(cuò)誤 如果未指定該參數(shù)，則系統(tǒng)自動(dòng)在源目錄下生成與外部表同名的.BAD文件 BADFILE記錄本次操作的結(jié)果，下次將會(huì)被覆蓋 LOGFILE和NOLOGFILE子句 同樣在accessparameters中加入LOGFILE'LOG_FILE.log'子句，則所有Oracle的錯(cuò)誤信息放入'LOG_FILE.log'中 而NOLOGFILE子句則表示不記錄錯(cuò)誤信息到log中，如忽略該子句，系統(tǒng)自動(dòng)在源目錄下生成與外部表同名的.LOG文件
注意以下幾個(gè)常見的問題 1.外部表經(jīng)常遇到BUFFER不足的情況，因此盡可能的增大READSIZE 2.換行符不對(duì)產(chǎn)生的問題。在不同的操作系統(tǒng)中換行符的表示方法不一樣，碰到錯(cuò)誤日志提示如是換行符問題，可以使用 UltraEdit打開，直接看十六進(jìn)制 3.特定行報(bào)錯(cuò)時(shí)，查看帶有"BAD"的日志文件,其中保存了出錯(cuò)的數(shù)據(jù),用記事本打開看看那里出錯(cuò),是否存在于外部表定義相沖突 |
|
|
