数据库导入导出
导入导出介绍
在实际项目中,数据库的导出与导入是一项非常重要的工作,它常常用于数据库的备份及恢复,或是用于数据的接收(来自上游系统)和下发(给予下游系统)。
ORACLE中导出导入的方法有很多,主要有以下几项:
1)导出INSERT语句,通过执行SQL来导入
2)通过PL/SQL Developer工具进行导出导入
3)EXP/IMP工具
4)EXPDP/IMPDP数据泵
5)SPOOL/SQLLDR
方法1适用于数据量比较小的导出导入,操作还算简单,数据量比较大时不适用,效率会非常低;
方法2也是适用数据量比较小的导出导入,同样数据量大时效率会骤降;
方法3及方法4都是ORACLE自带的导出导入专用工具,适用于大量数据的导出导入,同时也支持用户、表空间和数据库的导出导入,区别在于EXP/IMP是客户端工具,EXPDP/IMPDP是服务端工具,另外EXPDP/IMPDP的效率要比EXP/IMP还要高一些;
方法5也是ORACLE自带的工具,它是为解决导出数据到非ORACLE数据库或接收非ORACLE数据库数据而存在的,效率也非常可观。
我们主要介绍上述提到的数据泵EXPDP/IMPDP、SPOOL和SQLLDR。
需要特别注意的是,实际项目中也有可能用到EXP/IMP工具,当由EXPDP导出时,相应的导入一定要通过IMPDP进行;而由EXP导出时,相应的导入一定要通过IMP进行。
数据泵导出导入分四种情况,对应导出不同内容:表、模式(即用户)、表空间、数据库。表方面常用来备份数据或传递数据,模式和数据库方面常用来备份或搭建测试环境。表空间的导出导入很少使用。
数据泵
数据泵中牵扯到一个逻辑概念:路径名称(DIRECTORY),它在物理上对应一个实际路径,当使用数据泵时,路径名称用于指明导出文件所在路径。
EXPDP-数据泵导出命令
EXPDP-参数
IMPDP-数据泵导出命令
IMPDP-参数
exclude和include参数举例
SPOOL缓冲池
数据泵虽然功能强大,但有一点非常尴尬,就是它面向的对象仍为ORACLE,导出的文件不能直接导入到其他种类的数据,且导出文件为二进制文件,也不能直接查看导出内容。为了给下游系统提供数据,或某些时候以表格形式为业务人员提供大量数据,可以采用SPOOL缓冲池技术。
SPOOL缓冲池需要在SQLPLUS工具中使用,为了导出格式正确,需要提前设置一些参数
设置SQLPLUS参数
封装SQL文件并调用
SQLPLUS中的各项参数都有其默认值,而且每次重新登入SQLPLUS都会重置参数为默认值,鉴于每次使用SPOOL都需要设定参数,我们可以将SPOOL命令封装在一个.sql文件中,每次直接调用该文件即可。
SQLLDR
SQLLDR是ORACLE另一项非常方便的工具,它常用来接收其它种类数据库导出的文本文件。
它的命令组成中常包括以下几项内容:
数据文件:即接收到的文本文件(常见.dat.txt)
控制文件:一种控制数据如何加载的控制文件(.ctl)
日志文件:记录sqlldr的加载过程、执行情况、报错信息等(.log)
错误文件:记录数据加载过程中因各种问题导入失败的数据(.bad)
ctl文件配置
数据加载模式
类似于IMPDP功能,SQLLDR进行数据加载时也提供了不同的加载方式
1)INSERT:直接插入数据,但是要求目标表中不能有数据存在,该方式也是默认方式;
2)APPEND:增量加载数据,表中原数据保留,将新数据加载到目标表;
3)REPLACE:覆盖旧数据,类似于加载数据前对目标表执行了删除数据操作:DELETE FROM tb_name;
4)TRUNCATE:覆盖旧数据,类似于加载数据前对目标表执行了清空数据操作:TRUNCATE TABLE tb_name; 这种方式是最常用的一种。