当前位置:数据库 > Oracle >>

oracle导出数据到文本、从文本导入数据

oracle导出数据到文本、从文本导入数据
 
经常有需求向表中导入大量的数据,使用insert不靠谱,太慢
oracle提供了sqlldr的工具
 
也有时需要讲数据导入到文本,oracle的spool可以轻松实现
 
方便的实现oracle导出数据到txt、txt导入数据到oracle
 
一、导出数据到txt
 
这里用all_objects表做测试
 
SQL> desc all_objects;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                   NOT NULL DATE
 LAST_DDL_TIME                             NOT NULL DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 
拿object_id,object_name做导出、导入测试
这里需要一些设置满足数据导出的样式
 
vi exp_table.sql
 
set line 1000         --设置行的长度
set pagesize 0        --输出不换页
set feedback off      --默认的当一条sql发出的时候,oracle会给一个反馈,比如说创建表的时候,如果成功命令行会返回类似:Table created的反馈,off后不显示反馈
set heading off       --不显示表头信息
set trimspool on      --如果trimspool设置为on,将移除spool文件中的尾部空
set trims on          --去掉空字符
set echo off;       --显示start启动的脚本中的每个sql命令,缺省为on
set colsep '|'         --设置分隔符
set termout off        --不在屏幕上显示结果
spool db1.txt          --记录数据到db1.txt
select object_id,object_name from all_objects;  --导出数据语句
spool off              --收集完毕
exit
 
一切就绪后导出数据
 
[oracle@centos5 ~]$ sqlplus test/test @exp_table.sql 
 
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 13 16:35:14 2013
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@centos5 ~]$ sed -i 's/ //g' db1.txt  --可选,去除每行开头部分的空格
[oracle@centos5 ~]$ more db1.txt 20|ICOL$
44|I_USER1
28|CON$
15|UNDO$
29|C_COBJ#
3|I_OBJ#
25|PROXY_ROLE_DATA$
 
导出后检查数据的记录数是否正确
 
[oracle@centos5 ~]$ cat db1.txt |wc -l
49988
[oracle@centos5 ~]$ sqlplus test/test
 
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 13 16:36:21 2013
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select count(*) from all_objects;
 
  COUNT(*)
----------
     49988  --数据正确
 
二、从txt导入数据到oracle
 
sqlldr是通过一个control文件设定后,从文本导入数据
 
建立一张测试表
 
SQL> create table tb_sqlldr (id number,name varchar2(50));
 
Table created.
 
建立一个control文件
vi tb_sqlldr.ctl
 
load data                 
infile 'db1.txt'            --数据来源文本
append into table tb_sqlldr    --数据导入到表tb_sqldr中,导入方式为追加,如果想覆盖
fields terminated by "|"    --4、字段终止于X'09',是一个制表符(tab)
(id,name)                    --定义对应的字段名称,注意顺序
 
导入数据分成四种模式,可以根据需求选择:
 
APPEND // 原先的表有数据 就加在后面
INSERT // 装载空表 如果原先的表有数据 sqlloader会停止 默认值
REPLACE // 原先的表有数据 原先的数据会全部删除
TRUNCATE // 指定的内容和replace的相同 会用truncate语句删除现存数据 
 
执行导入操作
 
sqlldr userid=test/test control=tb_sqlldr.ctl
 
差不多5w的数据短短2s解决
 
执行导入后验证数据
 
SQL> select count(*) from tb_sqlldr;
 
  COUNT(*)
----------
     49988
 
    
导入成功
 
再执行一次导入操作,由于设置为追加
 
SQL> select count(*) from tb_sqlldr;
 
  COUNT(*)
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,