Oracle import export（7）
--用外部表（ORACLE_DATAPUMP）导出数据到文件再导入实验
--只有ORACLE_DATAPUMP引擎支持这种方式
--创建外部表来导出数据
SYS@PROD1& create table scott.dept_emp organization external
type oracle_datapump
default directory ext
location('dept_dp.data')
select * from scott.
Table created.
SYS@PROD1& select * from scott.dept_
DEPTNO DNAME
---------- -------------- -------------
10 ACCOUNTING
20 RESEARCH
40 OPERATIONS
[oracle@ocm1 ~]$ cd /home/oracle/ext/
[oracle@ocm1 ext]$ ls dept_dp.data
dept_dp.data--创建外部表来导入数据
SYS@PROD1& create table eoda.extbk
( deptno number(2),
varchar2(14),
varchar2(13)
organization external
(type oracle_datapump
default directory ext
location('dept_dp.data')
Table created.
SYS@PROD1& desc eoda.extbk
Null? Type
----------------------------------------------------- -------- ------------------------------------
VARCHAR2(14)
VARCHAR2(13)
SYS@PROD1& select * from eoda.
DEPTNO DNAME
---------- -------------- -------------
10 ACCOUNTING
20 RESEARCH
40 OPERATIONS
&&相关文章推荐
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：35998次
积分：2168
积分：2168
排名：第17465名
原创：178篇
转载：53篇
(13)(15)(5)(15)(25)(20)(57)(37)(47)同事最近在忙数据一致性比对工作，需要对不同文本文件中的数据进行比对，有的文件较大，记录较多，如果用普通的文本编辑器打开的话，很显然，会很卡，甚至打不开。
基于此，可将该文本文件的数据导入到数据库中，在集合的层面进行比对。
那么如何将文本文件的数据导入到数据库中呢？在这里，主要利用了Oracle的外部表特性。
Oracle外部表支持两种类型的驱动：一种是ORACLE_LOADER，外部表的数据必须来源于文件文件，另一种则是ORACLE_DATAPUMP，外部表的数据必须是二进制dump文件，该dump文件是先前将Oracle内部表的数据导入到外部表中填充的文件。很显然，Oracle希望将数据保留在数据库内部进行处理。
首先，我们来看一下该文本文件的大小及记录。
[oracle@node2 ~]$ du -sm P_00_3479598
P_00_3479598
[oracle@node2 ~]$ wc -l P_00_3479598
2899265 P_00_3479598
从上面的输出可以看出，该文件274M，有2899265条记录。
其次，构建创建外部表语句。
CREATE TABLE emp_load
(subsid number(18),
servnumber VARCHAR2(20 CHAR),
subsprodid NUMBER(18),
prodid VARCHAR2(32 CHAR),
startdate date,
owner VARCHAR2(4 CHAR))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY tmp
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY "|"
DECIMAL EXTERNAL,
servnumber
subsprodid
DECIMAL EXTERNAL,
startdate date "yyyymmddhh24miss",
enddate date "yyyymmddhh24miss",
LOCATION ('P_00_3479598')
注意，目录tmp必须存在，因为我是在scott用户下执行的，所以scott用户必须对该路径有读写权限。
第三、在scott用户下执行该建表语句。
第四、查看生成的外部表是否有问题
SQL& select count(*) from emp_
------------------
记录与wc-l查看的记录数吻合。
注意，建表过程中没有报错并不一定意味着数据已经成功加载在外部表中。必须通过查询外部表来判定数据是否已成功加载，倘若有错误提示，可参看当前目录下生成的日志文件，具体在本例中，是EMP_LOAD_2000.bad和EMP_LOAD_2000.log。
当然，外部表中的数据只能查询，不能做DML操作，譬如，随机删除表中的一条数据
SQL& delete from emp_load where rownum=1;
delete from emp_load where rownum=1
ERROR at line 1:
ORA-30657: operation not supported on external organized table
如果想对该外部表数据进行DML操作，可先将外部表的数据导入到内部表中。具体步骤如下：
SQL& create table test as select * from emp_load where 1=0;
Table created.
Elapsed: 00:00:00.26
SQL& INSERT /*+ APPEND */ INTO test select * from emp_
2899265 rows created.
Elapsed: 00:01:00.29
SQL& select * from test where rownum&=100;
select * from test where rownum&=100
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
Elapsed: 00:00:00.10
Commit complete.
Elapsed: 00:00:00.07
SQL& select * from test where rownum&=100;
在这里，为了节省时间，我用了直接路径插入，可以看出，插入近300万数据，只用了1分左右的时间，考虑到我虚拟机上的数据库，只给它分配了300M的内存，加载的效率还是相当可观的。
SQL& show parameter memory
------------------------------------ ----------- ------------------------------
hi_shared_memory_address
memory_max_target
big integer 300M
memory_target
big integer 300M
shared_memory_address
注意：在SQL*PLUS中，number字段的输出默认为10，这样会导致对于250300这样的数值，可能会显示为9.3712E+17，在这里，可通过set numwidth 18来显示完整的number字段的值。
阅读(...) 评论()您所在位置： &
&nbsp&&nbsp&nbsp&&nbsp
oracle外部表..doc 8页
本文档一共被下载：
次 ,您可全文免费在线阅读后下载本文档。
下载提示
1.本站不保证该用户上传的文档完整性，不预览、不比对内容而直接下载产生的反悔问题本站不予受理。
2.该文档所得收入(下载+内容+预览三)归上传者、原创者。
3.登录后可充值，立即自动返金币，充值渠道很便利
需要金币：150 &&
oracle外部表.
你可能关注的文档：
··········
··········
外部表介绍?ORACLE外部表用来存取数据库以外的文本文件（Text File）或ORACLE专属格式文件。因此，建立外部表时不会产生段、区、数据块等存储结构，只有与表相关的定义放在数据字典中。外部表，顾名思义，存储在数据库外面的表。当存取时才能从ORACLE专属格式文件中取得数据，外部表仅供查询，不能对外部表的内容进行修改（INSERT、UPDATE、DELETE操作）。不能对外部表建立索引。因为创建索引就意味着要存在对应的索引记录。而外部表其实在没有存储在数据库中。故在外部是无法建立索引的。如果硬要建立的话，则系统会提示“操作在外部组织表上不受支持”的错误提示。Notice: 外部表是ORACLE 9i后引入的。?外部表特征??? （1） 位于文件系统之中（一定要在数据库服务器中，而不是其它网络路径），按一定格式分割，????????? 例如@#$等，文本文件或者其他类型的文件可以作为外部表。　　（2） 对外部表的访问可以通过SQL语句来完成，而不需要先将外部表中的数据装载进数据库中。　　（3） 外部数据表都是只读的，因此在外部表不能够执行DML操作，也不能创建索引。　　（4） ANALYZE语句不支持采集外部表的统计数据，应该使用DMBS_STATS包来采集外部表的统计数据。　　（5） 可以查询操作和连接。也可以并行操作。　　（6） 数据在数据库的外部组织，是操作系统文件。　　（7） 操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。?外部表范例：?1：创建目录对象并授权从9i开始，ORACLE数据库若需要存取文件系统，就必须使用目录对象，以相对路径方式存取文件，强化数据库的安全性。建立目录对象、授予权限。SQL&CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/oradata/exterltab';给用户授予指定目录的操作权限SQL&GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO ETL;?2：创建外部表我创建了一个平面文件作为测试用例，如下所示，总共5条记录，其中一条错误记录[oracle@DB-Server exterltab]$ more student.data10001@#$kerry@#$male@#$28@#$110002@#$jimmy@#$male@#$22@#$110003@#$ken@#$male@#$21@#$110004@#$merry@#$femal@#$20@#$1this is a bad fileCREATE TABLE EXTER_TEST(
VARCHAR(12)
VARCHAR(8)
NUMBER(1)) ORGANIZATION EXTERNAL(
oracle_loader
default directory dump_dir
access parameters
records delimited by newline
fields terminated by '@#$'
location ('student.data'));?外部表语法也是蛮复杂的，参数选项非常多，这里不做过多解释。有兴趣自然可以翻阅官方文档。?SQL& select * from exter_select * from exter_test*ERROR at line 1:ORA-29913: error in executing ODCIEXTTABLEFETCH calloutORA-30653: reject limit reachedORA-06512: at &SYS.ORACLE_LOADER&, line 52?出现下面错误，是因为student.data文件中有不符合规范的记录，可以删除“this is a bad file”这一条记录，但是这是为了测试下面情况，所以可以通过执行 alter table exter_test re跳过一些限制。 SQL& alter table exter_test
正在加载中，请稍后...数据库应用程序如何利用外部文件呢？
全部答案（共1个回答）
Oracle9i有一个最引人注目的优点，这就是通过Oracle SQL访问非Oracle文件的能力。这 一新功能被称做外部表（external tables），它对某些系统具有非常重要的作用，可以令非 数据库应用程序利用外部文件，而且让这些文件被Oracle视作普通的数据表。 　　正如你所看到的那样，Oracle让数据库程序采用utl_file实用工具写普通文件。结合外部表的读取能力，这种新型拓扑不再需要所有的Oracle数据驻留在Oracle表内，也不需要为Oracle打开新的应用程序。下面我们就深入了解这种新特性的工作原理。 定义外部表 　　假设你要求Oracle引用以下逗号分隔的普通文本文件。
7369,SMITH,CLERK,7902,17-DEC-80,800,20 7499,ALLEN,SALESMAN,7698,20-FEB-81,521,WARD,SALESMAN,7698,22-FEB-81,566,JONES,MANAGER,7839,02-APR-81,,MARTIN,SALESMAN,7698,28-SEP-81,7698,BLAKE,MANAGER,7839,01-MAY-81,,CLARK,MANAGER,7839,09-JUN-81,,SCOTT,ANALYST,7566,19-APR-87,,KING,PRESIDENT,,17-NOV-81,4,TURNER,SALESMAN,7698,08-SEP-81,876,ADAMS,CLERK,7788,23-MAY-87,1100,,20以上文件包含一些职员信息： 　　职员工号 姓名 职务说明 经理的工号参加工作日期 薪水 任命 部门 　　那么该如何给Oracle定义这一文件呢？首先，我们必须在数据字典中创建一个Oracle目录入口，而数据字典则指向以上文件所驻留的Windows目录。在这个例子中我们把目录命名为 　　testdir并指向c:docspubsdbqueries目录： 　　SQL> create directory testdir as ‘c:docspubsdbqueries’; 　　Directory Created. 　　现在目录创建完毕，我们可以给Oracle定义外部表的结构了。代码如程序清单A 所示。 　　从语法上看，我们对外部表列的定义方式同内部Oracle表的定义方式是一样的。外部定义采用有住址的外部子句，如表A所示。 　　外部表定义好后就可以用SQL对外部表运行报告了，其工作方式就好象驻留在数据库内的普通数据表。查询示例如程序清单B所示，注意高级ROLLUP参数的复杂用法总计了部门和职位的薪水。结果如 程序清单C所示。 外部表的局限性 　　由于外部表是一种崭新的特性，Oracle还不能完美地充分利用这一功能。在Oracle9i中不支持DML。外部表都是只读的，但是原始数据可以用任何文本编辑器进行编辑。高强度查询的响应较为迟钝。外部表具有一定的处理负载，不太适合大型表的应用。
答: 求同志向摄友-10.06非洲
答: 硬盘安装(适合无光驱或者不想浪费光盘的用户，但是需要你有一个可以使用的Windows系统) 请提取ISO中的“WINXPSP3.GHO”和“GHO镜像安装器.E...
答: 保护系统安全，是指从整体电子商务系统或网络支付系统的角度进行安全防护，它与网络系统硬件平台、操作系统、各种应用软件等互相关联
大家还关注
确定举报此问题
举报原因(必选)：
广告或垃圾信息
激进时政或意识形态话题
不雅词句或人身攻击
侵犯他人隐私
其它违法和不良信息
报告，这不是个问题
报告原因(必选)：
这不是个问题
这个问题分类似乎错了
这个不是我熟悉的地区oracle 外部表导入时间日期类型数据
来源：易贤网&& 阅读：635 次&&日期： 09:57:32
温馨提示：易贤网小编为您整理了“oracle 外部表导入时间日期类型数据”,方便广大网友查阅！
1.建立外部表目录
create directory extdir as 'e:appextdir';
2.定义外部表
create table ext_test
rtype char(1)
organization external(
type oracle_loader
default directory extdir
access parameters(
fields terminated by ',' --- 字段间隔符为逗号
missing field values are null --- 外部文件行结束符为null
rt date "YYYY-MM-DD HH24:MI:SS", --- 定义时间字段格式类型
rtype char(1) ---定义其它字段(多个字段之间用逗号分隔)，长度同外部表定义
location('test_ext.txt'));
3.执行查询语句
SQL& select * from ext_
-------------- -
01-9月 -12 D
02-10月-12 D
19-10月-12 D
23-11月-12 D
20-11月-12 D
11-1月 -12 D
12-6月 -12 D
25-8月 -12 D
07-5月 -12 D
08-4月 -12 D
已选择10行。
外部文件示例：
test_ext.txt 文件内容：
更多信息请查看
更多信息请查看
【】&&&&&【点此处查询各地各类考试咨询QQ号码及交流群】
易贤网手机网站地址：
由于各方面情况的不断调整与变化，易贤网提供的所有考试信息和咨询回复仅供参考，敬请考生以权威部门公布的正式信息和咨询为准！
相关阅读 & & &
&&& &nbsp&nbsp&nbsp会员注册
本站不参与评论！（）
自觉遵守：爱国、守法、自律、真实、文明的原则
尊重网上道德，遵守中华人民共和国各项有关法律法规
严禁发表危害国家安全，破坏民族团结、国家宗教政策和社会稳定，含侮辱、诽谤、教唆、淫秽等内容的评论
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
您在本站发表的评论，本站有权保留、转载、引用或者删除
参与本评论即表明您已经阅读并接受上述条款