oracle 外部表

-- Oracle外部表--=================外部表只能在Oracle 9i之后来使用。

简单地说，外部表，是指不存在于数据库中的表。

通过向Oracle提供描述外部表的元数据，我们可以把一个操作系统文件当成一个只读的数据库表，就像这些数据存储在一个普通数据库表中一样来进行访问。

外部表是对数据库表的延伸。

一、外部表的特性位于文件系统之中，按一定格式分割，如文本文件或者其他类型的表可以作为外部表。

对外部表的访问可以通过SQL语句来完成，而不需要先将外部表中的数据装载进数据库中。

外部数据表都是只读的，因此在外部表不能够执行DML操作，也不能创建索引。

ANALYZE语句不支持采集外部表的统计数据，应该使用DMBS_STATS包来采集外部表的统计数据。

二、创建外部表的注意事项1.需要先建立目录对象2.对于操作系统文件的要求文件要有固定的格式、不能有标题列、访问时会自动创建一个日志文件3.在建立临时表时的相关限制对表中字段的名称存在特殊字符的情况下，必须使用英文状态的下的双引号将该表列名称连接起来。

如采用”SalseID#”。

对于列名字中特殊符号未采用双引号括起来时，会导致无法正常查询数据。

建议不用使用特殊的列标题字符在创建外部表的时候，并没有在数据库中创建表，也不会为外部表分配任何的存储空间。

创建外部表只是在数据字典中创建了外部表的元数据，以便对应访问外部表中的数据，而不在数据库中存储外部表的数据。

简单地说，数据库存储的只是与外部文件的一种对应关系，如字段与字段的对应关系。

而没有存储实际的数据。

由于存储实际数据，故无法为外部表创建索引，同时在数据使用DML时也不支持对外部表的插入、更新、删除等操作。

4.删除外部表或者目录对象一般情况下，先删除外部表，然后再删除目录对象，如果目录对象中有多个表，应删除所有表之后再删除目录对象。

如果在未删除外部表的情况下，强制删除了目录，在查询到被删除的外部表时，将收到"对象不存在"的错误信息。

查询dba_external_locations来获得当前所有的目录对象以及相关的外部表，同时会给出这些外部表所对应的操作系统文件的名字。

5.对于操作系统平台的限制不同的操作系统对于外部表有不同的解释和显示方式如在Linux操作系统中创建的文件是分号分隔且每行一条记录，但该文件在Windows操作系统上打开则并非如此。

建议避免不同操作系统以及不同字符集所带来的影响三、创建外部表使用CREATE TABLE语句的ORGANIZATION EXTENERAL子句来创建外部表。

外部表不分配任何盘区，因为仅仅是在数据字典中创建元数据。

1.外部表的创建语法createtabletable_name(col1 datatype1,col2 datatype2,col3 datatype3)organization exteneral(.....)2.由查询结果集，使用Oracle_datapump来填充数据来生成外部表a.创建系统目录以及Oracle数据目录名来建立对应关系，同时授予权限[oracle@oradb ~]$ mkdir-p/home/oracle/external_tb/datasys@ORCL>createorreplacedirectory dat_diras'/home/oracle/external_tb/data/';sys@ORCL>grantread,writeondirectory dat_dirtoscott;b.创建外部表scott@ORCL>createtableex_tb1 --创建外部表2 (ename,job,sal,dname) --表列描述，注意未指定数据类型3 organizationexternal4 (5 typeoracle_datapump --使用datapump将查询结果填充到外部表,注,此处由select 生成，故不支持oracle_loader6 defaultdirectory dat_dir --指定外部表的存放目录7 location('tb1.exp','tb2.exp') --产生外部表的内容将填充到这些文件中8 )9 parallel --按并行方式来填充10 as11 selectename,job,sal,dname -填充使用的原始数据12 fromempjoindept13 onemp.deptno=dept.deptno;c.--验证外部表scott@ORCL>select*fromex_tb1;ENAME JOB SAL DNAME---------- --------- ---------- --------------SMITH CLERK 800 RESEARCHALLEN SALESMAN 1600 SALESWARD SALESMAN 1250 SALESJONES MANAGER 2975 RESEARCH..........对于使用上述方式创建的外部表可以将其复制到其他路径作为外部表的原始数据来生成新的外部表，用于转移数据。

3.使用SQLLDR提供外部表的定义并创建外部表关于SQL*Loader的使用请参照：SQL*Loader使用方法我们使用SQL*Loader和下面的这个控制文件来生成外部表的定义[oracle@oradb ~]$ cat demo1.ctlLOADDATAINFILE*INTOTABLEDEPT_NEWFIELDS TERMINATEDBY','(DEPTNO,DNAME,LOC)BEGINDATA10,Sales,Virginia20,Accounting,Virginia30,Consulting,Virginia40,Finance,Virginia[oracle@oradb ~]$ sqlldr scott/tiger control=demo1.ctl external_table=generate_onlyEXTERNAL_TABLE参数有以下三个值：NOT_USED：默认值。

EXECUTE：这个值说明SQLLDR不会生成并执行一个SQLINSERT语句；而是会创建一个外部表，且使用一个批量SQL语句来加载。

GENERATE_ONLY：使SQLLDR并不具体加载任何数据，而只是会生成所执行的SQL DDL和DML 语句，并放到它创建的日志文件中。

注：DIRECT=TRUE覆盖EXTENAL_TABLE=GENERATE_ONLY。

如果指定了DIRECT=TRUE，则会加载数据，而不会生成外部表。

[oracle@oradb ~]$ cat demo1.log --查看sqlldr产生的日志文件TableDEPT_NEW,loadedfromevery logical record.Insertoptionineffectforthistable:INSERTColumnName Position Len Term Encl Datatype------------------------------ ---------- ----- ---- ---- ---------------------DEPTNO FIRST * , CHARACTERDNAME NEXT * , CHARACTERLOC NEXT * , CHARACTERCREATEDIRECTORY statements neededforfiles --创建一个目录------------------------------------------------------------------------CREATEDIRECTORY SYS_SQLLDR_XT_TMPDIR_00000AS'/home/oracle/'CREATETABLEstatementforexternaltable: --生成创建外部表的命令------------------------------------------------------------------------CREATETABLE"SYS_SQLLDR_X_EXT_DEPT_NEW"("DEPTNO" NUMBER(2),"DNAME" VARCHAR2(20),"LOC" VARCHAR2(20))ORGANIZATIONexternal --该子句表明是一个外部表heap对应普通表，index对应iot，external对应外部表(TYPEoracle_loader --说明外部文件访问方式：oracle_loader或oracle_datapump(9i不支持)DEFAULTDIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 --指定外部文件的缺省目录ACCESS PARAMETERS --这个访问参数有些类似于sqlldr中控制文件中的描述信息( --系统根据这些描述信息来生成外部表的格式RECORDS DELIMITEDBYNEWLINE CHARACTERSET US7ASCII --记录默认以换行符结束BADFILE'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad' --存放处理失败的记录文件描述LOGFILE'demo1.log_xt' --日志文件READSIZE 1048576--Oracle读取输入数据文件所用的默认缓冲区,此处为MB,如专用模式则从PGA分配,如共享模式则从SGA分配SKIP 6 --跳过的记录数，因为我们使用了控制文件，所以前面的控制信息需要跳过FIELDS TERMINATEDBY"," LDRTRIM --描述字段的终止符REJECT ROWSWITHALLNULLFIELDS --所有为空值的行被跳过并且记录到bad file.( --下面是描述外部文件各个列的定义"DEPTNO"CHAR(255)TERMINATEDBY",","DNAME"CHAR(255)TERMINATEDBY",","LOC"CHAR(255)TERMINATEDBY","))location('demo1.ctl' --描述外部文件的文件名))REJECT LIMIT UNLIMITED --描述允许的错误数，此处为无限制INSERTstatements usedtoloadinternal tables: --用于将数据填充到表，使用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_new2 (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=executescott@ORCL>select*fromdept_new;DEPTNO DNAME LOC---------- -------------------- -------------------------10 Sales Virginia20 Accounting Virginia30 Consulting Virginia40 Finance Virginia4.使用平面文件定义并生成外部表a.平面文件数据1.dat：7369,SMITH,CLERK,7902,17-DEC-80,100,0,207499,ALLEN,SALESMAN,7698,20-FEB-81,250,0,307521,WARD,SALESMAN,7698,22-FEB-81,450,0,307566,JONES,MANAGER,7839,02-APR-81,1150,0,202.dat：7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,0,307698,BLAKE,MANAGER,7839,01-MAY-81,1550,0,307934,MILLER,CLERK,7782,23-JAN-82,3500,0,10b.继续使用前面创建的目录/home/oracle/external_tb/data来存放数据文件：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.dat2.dat tb1.exp tb2.expc.创建外部表scott@ORCL>get/u01/bk/scripts/tb.emp_new1 createtableemp_new2 (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 organizationexternal13 (14 typeoracle_loader15 defaultdirectory dat_dir16 access parameters17 (18 records delimitedbynewline19 fields terminatedby','20 )21 location22 ('1.dat','2.dat')23*);scott@ORCL>start/u01/bk/scripts/tb.emp_newd.验证外部表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 207499 ALLEN SALESMAN 7698 20-FEB-81 250 0 30............................scott@ORCL>deletefromemp_newwhereename='SMITH'; --外部表不能执行DMLdeletefromemp_newwhereename='SMITH'*ERROR at line 1:ORA-30657:operationnotsupportedonexternalorganizedtablescott@ORCL>insertintoemp_new(emp_id,ename)select8888,'Robinson'fromdual;insertintoemp_new(emp_id,ename)select8888,'Robinson'fromdual*ERROR at line 1:ORA-30657:operationnotsupportedonexternalorganizedtablee.获得外部表的有关信息：scott@ORCL>col access_parameters format a35scott@ORCL>selectowner,table_name,type_name,default_directory_name,access_parameters2 fromdba_external_tables;OWNER TABLE_NAME TYPE_NAME DEFAULT_DIRECTO ACCESS_PARAMETERS---------- --------------- ------------------------------ --------------- -----------------------------------SCOTT EX_TB1 ORACLE_DATAPUMP DAT_DIRSCOTT EMP_NEW ORACLE_LOADER DAT_DIRrecords delimitedbynewlinefields terminatedby','SCOTT EMP_PUMP ORACLE_DATAPUMP DAT_DIR records delimitedbynewlinefields terminatedby','f.获得平面文件的位置，使用如下的查询：scott@ORCL>select*fromdba_external_locationsorderbytable_name;OWNER TABLE_NAME LOCATION DIR DIRECTORY_NAME---------- --------------- --------------- --- ------------------------------SCOTT EMP_NEW 1.dat SYS DAT_DIRSCOTT EMP_NEW 2.dat SYS DAT_DIRSCOTT EMP_PUMP 1.dat SYS DAT_DIRSCOTT EMP_PUMP 2.dat SYS DAT_DIRSCOTT EX_TB1 tb2.exp SYS DAT_DIRSCOTT EX_TB1 tb1.exp SYS DAT_DIR5.外部表定义的进一步分析CREATETABLEexternal_table(COL01 VARCHAR2(100),COL02 NUMBER,......)ORGANIZATIONEXTERNAL(TYPEORACLE_LOADERDEFAULTDIRECTORY "XXX"ACCESS PARAMETERS(RECORDS DELIMITEDBY0X'0A'SKIP 1BADFILE'bad.txt'FIELDS TERMINATEDBY','OPTIONALLY ENCLOSEDBY'"'LRTRIM MISSING FIELDVALUESARENULLREJECT ROWSWITHALLNULLFIELDS)LOCATION("CJ_DIR":'data.txt'))REJECT LIMIT UNLIMITED;外部表定义的几个重点ANIZATIONEXTERNAL关键字，必须要有。