oracle数据库SPOOL缓冲池导出数据，SQLLDR-导入数据

SPOOL缓冲池导出数据;

1.数据泵虽然功能强大;但有一点非常尴尬;就是它面向的对象仍为Oracle;
导出的文件不能直接导入到其他种类的数据;且导出文件为二进制文件;
也不能直接查看导出内容。为了给下游系统提供数据;或某些时候以表格形式为业务人员提供大量数据;可以采用SPOOL缓冲池技术。
2.SPOOL缓冲池需要在SQLPLUS工具中使用;为了导出格式正确;需要提前设置一些参数

;数据泵导入导出可以在我的博客里面看;

--设置SQLPLUS参数 只在当前SQLPLUS工具中有效
set trimspool on --是否删除每行后的空白字符;建议on
set trimout on --是否允许每行后的空格;建议on
set echo off --是否显示执行命令的内容;建议off
set newpage none --一页中空行的数量;建议无空行
set feedback off --是否显示“返回n行”;建议off
set linesize 200 --每行的字符长度;一定要根据数据行的长度设置;过少自动换行;过长文件过大 
set pagesize 0 --每页的间隔;建议无间隔
set heading off --是否打印列标题;建议off
set term off --控制当使用;、;;或START命令执行一个命令文件时;是否显示中间结果;建议off
set timing off --是否显示命令时间;建议off

--启动spool 并设置导出路径和文件名称及类型(常用;.txt或.csv或.dat)
spool D:pathfilename.txt

--设置查询内容 
SELECT col1||;|;||col2||;|;||col3||;|;||col4... FROM tb_name;

--结束spool
spool off

我们导出还是以emp表为例;写一个.sql脚本;脚本内容如下;并将其封装。

set trimspool on 

set trimout on 

set echo off 

set newpage none 

set feedback off 

set linesize 200 

set pagesize 0 

set heading off 

set term off 

set timing off 

SPOOL D:DIR1EMP.DAT

SELECT EMPNO||;|;||ENAME||;|;||JOB||;|;||MGR||;|;||TO_CHAR(HIREDATE,;YYYY-MM-DD;)||;|;||SAL||;|;||COMM||;|;||DEPTNO FROM EMP;

spool off

==========================================

SPOOL D:DIR1EMP.DAT  --导出为EMP.DAT文件;存放在D:DIR87里

我是把脚本放在D盘的dir1文件里;D:DIR1;;这个是实际路径

执行导出命令;导出为一个.DAT文件;调用脚本;

;进入SQL的方法;cmd打开命令行窗口;输入sqlplus / as sysdba 登录数据库;

输入conn scott/scott --连接scott用户;我是在scott用户下执行的;;

SQL>;D:DIR1EMP.SQL

这是执行成功了;如果失败会报错。

然后我们去路径 ;D:DIR1;看一下

 我们可以发现EMP.sql就是用来导出数据的数据库脚本;内容与上面的一致;而EMP.DAT就是我们导出的数据文件;其内容如下

这是根据我们的查询语句导出的数据;一会导入的时候可以以这个文件为数据文件导入数据

SQLLDR导入数据

介绍;

SQLLDR是ORACLE另一项非常方便的工具;它常用来接收其它种类数据库导出的文本文件。
它的命令组成中常包括以下几项内容;
数据文件;即接收到的文本文件;常见.dat.txt;
控制文件;一种控制数据如何加载的控制文件;.ctl;
日志文件;记录sqlldr的加载过程、执行情况、报错信息等;.log;
错误文件;记录数据加载过程中因各种问题导入失败的数据;.bad;

SQLLDR导入数据必须的文件是.dat数据文件和.ctl控制文件;而日志文件和错误文件是在执行时创建的

数据文件我们用上面导出的EMP.DAT文件就行;现在就需要编写一个控制文件

语法如下;

--ctl文件配置;
OPTIONS (skip=1,rows=128) --skip决定跳过的行;rows决定每加载多少行提交一次数据
LOAD DATA --数据加载
CHARACTERSET ZHS16GBK --设置编码 ZHS16GBK或 ;UTF8;
INTO TABLE tb_name --目标表
INSERT|APPEND|REPLACE|TRUNCATE --数据加载模式
FIELDS TERMINATED BY ;|; --字段间分隔符设置
OPTIONALLY ENCLOSED BY ;;; --数据括起方式;没有可以忽略
TRAILING NULLCOLS --字段无对应数据时允许为空
( col_1  ;trim(:col_1); ,
  col_2  ;trim(:col_2); ,
  col_3  ;trim(:col_3); ,
  col_4  ;to_date(:col_4,;YYYY-MM-DD;); ,
  TS       ;to_char(sysdate,;YYYY-MM-DD HH24:MI:SS;) )

--数据加载模式
类似于IMPDP功能;SQLLDR进行数据加载时也提供了不同的加载方式
1;INSERT;直接插入数据;但是要求目标表中不能有数据存在;该方式也是默认方式;--;一般不用;
2;APPEND;增量加载数据;表中原数据保留;将新数据加载到目标表;
3;REPLACE;覆盖旧数据;类似于加载数据前对目标表执行了删除数据操作;DELETE FROM tb_name;
4;TRUNCATE;覆盖旧数据;类似于加载数据前对目标表执行了清空数据操作;TRUNCATE TABLE tb_name; 这种方式是最常用的一种。

编写控制文件;

OPTIONS (skip=0,rows=128)
LOAD DATA
CHARACTERSET ZHS16GBK
INTO TABLE EMP
TRUNCATE
FIELDS TERMINATED BY ;|;
TRAILING NULLCOLS
(EMPNO    ; TRIM(:EMPNO   );,                               
ENAME    ; TRIM(:ENAME   );,                               
JOB      ; TRIM(:JOB     );,                               
MGR      ; TRIM(:MGR     );,                               
HIREDATE ; TO_DATE(TRIM(:HIREDATE),;YYYY-MM-DD;);  ,       
SAL      ; TRIM(:SAL     );,                               
COMM     ; TRIM(:COMM    );,                               
DEPTNO   ; TRIM(:DEPTNO  );
)

--将其封装成.CTL文件放到;D:DIR1;路径下

-sqlldr命令;--不需要连接数据库用户;但需要进入安装了数据库的计算机用户

语法;
sqlldr --sqlldr启动
user_name/pwd;ip:1521/db_name --指定用户名、密码、IP和实例名称
data=pathfilename.dat --数据文件路径;可txt、dat、csv等文本文件
control=pathfilename.ctl --控制文件路径
bad=pathfilename.bad --错误文件路径;存储导入失败的数据
log=pathfilename.log --日志文件路径;记录导入过程
errors=0 --允许失败数据量;一般设为0;即一旦出现失败即停止

--在cmd窗口运行以下命令导入数据 (以下命令是一行;不可以分行)

sqlldr SCOTT/scott;192.168.2.72:1521/orcl data=D:DIR1EMP.DAT control=D:DIR1EMP.ctl bad=D:DIR1EMP.bad log=D:DIR1EMP1.log errors=0

执行结果;

 

 运行成功;提交了14条数据

我们再来看看DIR1文件夹里都有什么现在

 ;里头EMP.DMP和EMP.LOG是我之前用数据泵导出的数据文件和日志文件;

所以我们可以发现EMP.sql是我们编写用来导出EMP.DAT;数据文件;的脚本;而EMP.CTL则是上面编写的控制文件;EMP1.LOG是导入数据时生成的日志文件;因为没有错误;所以没有错误文件。

我们可以看一下导入数据时生成的日志文件

里面就是一些信息和运行的时间。

至此;数据的导出导入就是成功了。