保健品网站可以做网站,wordpress中文免费电商模板,南昌正规网站公司吗,蓝天下品牌运营业务展示-- -- Oracle 外部表 -- 外部表只能在Oracle 9i 之后来使用。简单地说#xff0c;外部表#xff0c;是指不存在于数据库中的表。通过向Oracle提供描述外部表的元数据#xff0c;我们 可以把一个操作系统文件当成一个只读的数据库表#xff0c;就像这些数据存储在一个普通数…-- -- 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.外部表的创建语法 create table table_name (col1 datatype1,col2 datatype2 ,col3 datatype3) organization exteneral (.....) 2.由查询结果集使用Oracle_datapump来填充数据来生成外部表 a.创建系统目录以及Oracle数据目录名来建立对应关系同时授予权限 [oracleoradb ~]$ mkdir -p /home/oracle/external_tb/data sysORCL create or replace directory dat_dir as /home/oracle/external_tb/data/; sysORCL grant read,write on directory dat_dir to scott; b.创建外部表 scottORCL create table ex_tb1 --创建外部表 2 (ename,job,sal,dname) --表列描述注意未指定数据类型 3 organization external 4 ( 5 type oracle_datapump --使用datapump将查询结果填充到外部表,注,此处由select生成故不支持oracle_loader 6 default directory dat_dir --指定外部表的存放目录 7 location(tb1.exp,tb2.exp) --产生外部表的内容将填充到这些文件中 8 ) 9 parallel --按并行方式来填充 10 as 11 select ename,job,sal,dname -填充使用的原始数据 12 from emp join dept 13 on emp.deptnodept.deptno; c.--验证外部表 scottORCL select * from ex_tb1; ENAME JOB SAL DNAME ---------- --------- ---------- -------------- SMITH CLERK 800 RESEARCH ALLEN SALESMAN 1600 SALES WARD SALESMAN 1250 SALES JONES MANAGER 2975 RESEARCH .......... 对于使用上述方式创建的外部表可以将其复制到其他路径作为外部表的原始数据来生成新的外部表用于转移数据。 3.使用SQLLDR提供外部表的定义并创建外部表 关于SQL*Loader的使用请参照SQL*Loader使用方法 我们使用SQL*Loader和下面的这个控制文件来生成外部表的定义 [oracleoradb ~]$ cat demo1.ctl LOAD DATA INFILE * INTO TABLE DEPT_NEW FIELDS TERMINATED BY , (DEPTNO, DNAME, LOC ) BEGINDATA 10,Sales,Virginia 20,Accounting,Virginia 30,Consulting,Virginia 40,Finance,Virginia [oracleoradb ~]$ sqlldr scott/tiger controldemo1.ctl external_tablegenerate_only EXTERNAL_TABLE 参数有以下三个值 NOT_USED默认值。 EXECUTE这个值说明SQLLDR不会生成并执行一个SQL INSERT语句而是会创建一个外部表且使用一个批量SQL语句来加载。 GENERATE_ONLY使SQLLDR 并不具体加载任何数据而只是会生成所执行的SQL DDL 和DML 语句并放到它创建的日志文件中。 注DIRECTTRUE 覆盖EXTENAL_TABLEGENERATE_ONLY。如果指定了DIRECTTRUE则会加载数据而不会生成外部表。 [oracleoradb ~]$ cat demo1.log --查看sqlldr产生的日志文件 Table DEPT_NEW, loaded from every logical record. Insert option in effect for this table: INSERT Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- DEPTNO FIRST * , CHARACTER DNAME NEXT * , CHARACTER LOC NEXT * , CHARACTER CREATE DIRECTORY statements needed for files --创建一个目录 ------------------------------------------------------------------------ CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS /home/oracle/ CREATE TABLE statement for external table: --生成创建外部表的命令 ------------------------------------------------------------------------ CREATE TABLE SYS_SQLLDR_X_EXT_DEPT_NEW ( DEPTNO NUMBER(2), DNAME VARCHAR2(20), LOC VARCHAR2(20) ) ORGANIZATION external --该子句表明是一个外部表 heap 对应普通表index 对应iotexternal 对应外部表 ( TYPE oracle_loader --说明外部文件访问方式oracle_loader或oracle_datapump(9i不支持) DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 --指定外部文件的缺省目录 ACCESS PARAMETERS --这个访问参数有些类似于sqlldr中控制文件中的描述信息 ( --系统根据这些描述信息来生成外部表的格式 RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII --记录默认以换行符结束 BADFILE SYS_SQLLDR_XT_TMPDIR_00000:demo1.bad --存放处理失败的记录文件描述 LOGFILE demo1.log_xt --日志文件 READSIZE 1048576 --Oracle读取输入数据文件所用的默认缓冲区,此处为MB,如专用模式则从PGA分配,如共享模式则从SGA分配 SKIP 6 --跳过的记录数因为我们使用了控制文件所以前面的控制信息需要跳过 FIELDS TERMINATED BY , LDRTRIM --描述字段的终止符 REJECT ROWS WITH ALL NULL FIELDS --所有为空值的行被跳过并且记录到bad file. ( --下面是描述外部文件各个列的定义 DEPTNO CHAR(255) TERMINATED BY ,, DNAME CHAR(255) TERMINATED BY ,, LOC CHAR(255) TERMINATED BY , ) ) location ( demo1.ctl --描述外部文件的文件名 ) )REJECT LIMIT UNLIMITED --描述允许的错误数此处为无限制 INSERT statements used to load internal tables: --用于将数据填充到表使用append方式 ------------------------------------------------------------------------ INSERT /* append */ INTO DEPT_NEW ( DEPTNO, DNAME, LOC ) SELECT DEPTNO, DNAME, LOC FROM SYS_SQLLDR_X_EXT_DEPT statements to cleanup objects created by previous statements: --用于删除目录和外部表的定义信息 ------------------------------------------------------------------------ DROP TABLE SYS_SQLLDR_X_EXT_DEPT_NEW DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 ----------------------------------------------------------------------------------------------------------------------- sysORCL grant create any directory to scott; sysORCL grant drop any directory to scott; scottORCL create table dept_new 2 (deptno number,dname varchar2(20),loc varchar2(25)); scottORCL select * from dept_new; no rows selected [oracleoradb ~]$ sqlldr scott/tiger controldemo1.ctl external_tableexecute scottORCL select * from dept_new; DEPTNO DNAME LOC ---------- -------------------- ------------------------- 10 Sales Virginia 20 Accounting Virginia 30 Consulting Virginia 40 Finance Virginia 4.使用平面文件定义并生成外部表 a.平面文件数据 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.继续使用前面创建的目录/home/oracle/external_tb/data 来存放数据文件 sysORCL select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH --------------- --------------- --------------------------------------------- SYS DATA_PUMP_DIR /u01/oracle/10g/rdbms/log/ SYS DAT_DIR /home/oracle/external_tb/data/ scottORCL ho ls /home/oracle/external_tb/data/ 1.dat 2.dat tb1.exp tb2.exp c.创建外部表 scottORCL get /u01/bk/scripts/tb.emp_new 1 create table emp_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 organization external 13 ( 14 type oracle_loader 15 default directory dat_dir 16 access parameters 17 ( 18 records delimited by newline 19 fields terminated by , 20 ) 21 location 22 (1.dat,2.dat) 23* ); scottORCL start /u01/bk/scripts/tb.emp_new d.验证外部表 scottORCL select * from emp_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 ............................ scottORCL delete from emp_new where enameSMITH; --外部表不能执行DML delete from emp_new where enameSMITH * ERROR at line 1: ORA-30657: operation not supported on external organized table scottORCL insert into emp_new(emp_id,ename) select 8888,Robinson from dual; insert into emp_new(emp_id,ename) select 8888,Robinson from dual * ERROR at line 1: ORA-30657: operation not supported on external organized table e.获得外部表的有关信息 scottORCL col access_parameters format a35 scottORCL select owner,table_name,type_name,default_directory_name,access_parameters 2 from dba_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 delimited by newline fields terminated by , SCOTT EMP_PUMP ORACLE_DATAPUMP DAT_DIR records delimited by newline fields terminated by , f.获得平面文件的位置使用如下的查询 scottORCL select * from dba_external_locations order by table_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.外部表定义的进一步分析 CREATE TABLE external_table ( COL01 VARCHAR2(100), COL02 NUMBER, ...... ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY XXX ACCESS PARAMETERS ( RECORDS DELIMITED BY 0X0A SKIP 1 BADFILE bad.txt FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LRTRIM MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS ) LOCATION (CJ_DIR:data.txt) )REJECT LIMIT UNLIMITED; 外部表定义的几个重点 a.ORGANIZATION EXTERNAL 关键字必须要有。以表明定义的表为外部表。 b.重要参数外部表的类型 ORACLE_LOADER 定义外部表的缺省方式只能只读方式实现文本数据的装载。 ORACLE_DATAPUMP 支持对数据的装载与卸载数据文件必须为二进制dump文件。可以从外部表提取数据装载到内部表也 可以从内部表卸载数据作为二进制文件填充到外部表。 c.DEFAULT DIRECTORY 缺省的目录指明了外部文件所在的路径 d.LOCATION 定义了外部表的位置 f.ACCESS PARAMETERS 描述如何对外部表进行访问 RECORDS关键字后定义如何识别数据行 DELIMITED BY XXX ——换行符常用newline定义换行并指明字符集。对于特殊的字符则需要单独定义 如特殊符号可以使用OX十六位值例如tab(/t)的十六位是9则DELIMITED BY 0X09 cr(/r)的十六位是d那么就是DELIMITED BY 0X0D。 SKIP X ——跳过X行数据有些文件中第一行是列名需要跳过第一行则使用SKIP 1。 FIELDS关键字后定义如何识别字段常用的如下 FIELDSTERMINATED BY x ——字段分割符。 ENCLOSED BY x ——字段引用符包含在此符号内的数据都当成一个字段。 例如一行数据格式如abc,ab,c,。使用参数TERMINATED BY , ENCLOSED BY 后系统会读到两个字段 第一个字段的值是abc第二个字段值是ab,c,。 LRTRIM ——删除首尾空白字符。 MISSING FIELD VALUES ARE NULL ——某些字段空缺值都设为NULL。 对于字段长度和分割符不确定且准备用作外部表文件可以使用UltraEdit、Editplus等来进行分析测试如果文件较 大则需要考虑将文件分割成小文件并从中提取数据进行测试。 外部表对错误的处理 REJECT LIMIT UNLIMITED 在创建外部表时最后加入LIMIT子句表示可以允许错误的发生个数。默认值为零。设定为UNLIMITED则错误不受限制 BADFILE 和NOBADFILE 子句 用于指定将捕获到的转换错误存放到哪个文件。如果指定了NOBADFILE则表示忽略转换期间的错误 如果未指定该参数则系统自动在源目录下生成与外部表同名的.BAD文件 BADFILE记录本次操作的结果下次将会被覆盖 LOGFILE 和NOLOGFILE 子句 同样在accessparameters中加入LOGFILE LOG_FILE.log子句则所有Oracle的错误信息放入LOG_FILE.log中 而NOLOGFILE子句则表示不记录错误信息到log中如忽略该子句系统自动在源目录下生成与外部表同名的.LOG文件 注意以下几个常见的问题 1.外部表经常遇到BUFFER不足的情况因此尽可能的增大READ SIZE 2.换行符不对产生的问题。在不同的操作系统中换行符的表示方法不一样碰到错误日志提示如是换行符问题可以使用 UltraEdit打开直接看十六进制 3.特定行报错时查看带有BAD的日志文件,其中保存了出错的数据,用记事本打开看看那里出错,是否存在于外部表定义相冲突 四、更多参考 Oracle 分区表 SQL*Loader使用方法 数据泵 IMPDP 导入工具的使用 数据泵 EXPDP 导出工具的使用
