ORACLE--数据导入导出

1 导出INSERT语句，通过SQL来导入

使用insert插入大量数据的个人经验总结在很多时候，我们会需要对一个表进行插入大量的数据，并且希望在尽可能短的时间内完成该工作，这里，和大家分享下我平时在做大量数据insert的一些经验。 前提：在做insert数据之前，如果是非生产环境，请将表的索引和约束去掉，待insert完成后再建索引和约束。
1.insert into tab1 select * from tab2;
commit;
这是最基础的insert语句，我们把tab2表中的数据insert到tab1表中。根据经验，千万级的数据可在1小时内完成。但是该方法产生的arch会非常快，需要关注归档的产生量，及时启动备份软件，避免arch目录撑爆（ 归档进程 (ARCH):在每次日志切换时把已满的日志组进行备份或归档）。
2.alter table tab1 nologging;
insert /*+ append */ into tab1 select * from tab2;
commit;
alter table tab1 logging;
该方法会使得产生arch大大减少，并且在一定程度上提高时间，根据经验，千万级的数据可在45分钟内完成。但是请注意，该方法适合单进程的串行方式，如果当有多个进程同时运行时，后发起的进程会有enqueue的等待。
注意此方法千万不能dataguard上用（不过要是在database已经force logging那也是不怕的，呵呵）！！
3.insert into tab1 select /*+ parallel */ * from tab2;
commit;
对于select之后的语句是全表扫描的情况，我们可以加parallel的hint来提高其并发，这里需要注意的是最大并发度受到初始化参数parallel_max_servers的限制，并发的进程可以通过v$px_session查看，或者ps -ef |grep ora_p查看。
4.alter session enable parallel dml;
insert /*+ parallel */ into tab1 select * from tab2;
commit;
：/*+parallel(t,n)*/中，t代表表别名或者表明（没有起别名情况）；n代表进程数量，一般值为：cpu数量-1。
一般而言主要在如下情况使用parallel HINT：
1.表的数据量很大,超过一千万; 
2.数据库主机是多个CPU;
3.系统的当前负载较低;

与方法2相反，并发的insert，尚未比较和方法2哪个效率更高（偶估计是方法2快）。
5.insert into tab1 select * from tab2 partition (p1);
insert into tab1 select * from tab2 partition (p2);
insert into tab1 select * from tab2 partition (p3);
insert into tab1 select * from tab2 partition (p4);
对于分区表可以利用tab1进行多个进程的并发insert，分区越多，可以启动的进程越多。
我曾经试过insert 2.6亿行记录的一个表，8个分区，8个进程，
如果用方法2，单个进程完成可能要40分钟，
但是由于是有8个分区8个进程，后发进程有enqueue，
所以因此需要的时间为40分钟×8；但是如果用方法5,虽然单个进程需要110分钟，
但是由于能够并发进程执行，所以总共需要的时间就约为110分钟了。

2 通过PL/SQL Developer工具进行导出导入

3 EXP/IMP工具

客户端工具

4 EXPDP/IMPDP数据泵(CMD窗口运行)

服务端工具 效率高

数据泵牵扯到一个逻辑概念：路径名称DIRECTORY，它在物理上对应一个实际路径，当使用数据泵时，路径名称用于指明文件所在路径。

1）路径相关内容

• 语法

SELECT * FROM DBA_DIRECTORIES;-- 查看所有路径  需要DBA权限
CREATE OR REPLACE DIRECTORY 路径名字 AS '实际路径';
GRANT READ,WRITE ON DIRECTORY DIR_NAME TO 用户; -- 将存放路径的读写权限赋予给某用户
GRANT EXP_FULL_DATABASE,IMP_FULL_DATABASE TO 用户; -- 最好有这俩权限或者DBA权限
DROP DIRECTORY 路径名字;--删除路径

• 例子

CREATE OR REPLACE DIRECTORY ODATA AS 'E:\ORCLDATA'; -- 创建一个叫ODATA的路径名字
GRANT READ,WRITE ON DIRECTORY ODATA TO SCOTT;  --赋给用户SCOTT读写该路径的权限
GRANT EXP_FULL_DATABASE,IMP_FULL_DATABASE TO SCOTT;--赋给SCOTT导入和导出的权限

2）数据泵导出数据

• 参数

directory                             -- 导出路径该名称指向数据字典DBA_DIRECTORIES中的路径
dumpfile                              -- 导出文件的名称 .dmp
logfile                               -- 记录导出过程日志 .log
tables|schemas|tablesplaces|full      -- 分别表示导出表、用户、表空间、整个数据库
content={all|data_only|metadata_only} -- 分别表示导出数据和表结构(元数据)、数据、表结构
query=[表名:]\"条件\",...              -- 导出表指定WHERE字句进行过滤
exclude -- 过滤掉
include -- 仅包含

-- exclude和include参数举例  
exclude=sequence,view              
exclude=table:\"in ('tb_name1','tb_name2')\"            
exclude=sequence,view,table:"IN ('tb_name1','tb_name2')"
exclude=index:"='ind_name'"                  
include=procedure:"like 'pro_%'"                             
其它常用操作符 not in, not like, <, != 

• 导出命令

-- 1.导出表
expdp user_name/pwd directory=路径名字 dumpfile=文件名.dmp logfile=文件名.log tables=表名字1[,表名2...]
-- 2.导出用户
expdp user_name/pwd directory=路径名字 dumpfile=文件名.dmp logfile=文件名.log schemas=用户名1[,用户名2...]
-- 3.导出表空间
expdp user_name/pwd directory=路径名字 dumpfile=文件名.dmp logfile=文件名.log tablespaces=表空间名1[,表空间名2...]
-- 4.导出数据库
expdp user_name/pwd directory=路径名字 dumpfile=文件名.dmp logfile=文件名.log full=y

• 例子

/*表*/
expdp scott/scott directory=ODATA dumpfile=emp.dmp logfile=emp.log tables=emp
impdp scott/scott directory=ODATA dumpfile=emp.dmp logfile=emp1.log tables=emp
--导出多个表
expdp scott/scott directory=ODATA dumpfile=emp.dmp logfile=emp.log tables=emp,dept content metadata_only
table_exists_action=truncate
expdp scott/scott directory=ODATA dumpfile=emp.dmp logfile=emp.log tables=scott.emp content=data_only

导出用户
expdp scott/scott directory=ODATA dumpfile=scott.dmp logfile=scott.log schemas=scott include=procedure:\"like 'SP_%'\"

expdp scott/scott directory=ODATA dumpfile=orcl1.dmp logfile=orcl1.log full=y

--导出用户的表
expdp scott/scott directory=ODATA dumpfile=test.dmp logfile=test.log schemas=scott include=table:\"IN('EMP','DEPT')\"

expdp scott/scott directory=ODATA dumpfile=test1.dmp logfile=test1.log schemas=scott include=table:\"IN(SELECT TNAME FROM TABLE_LISTS)\"

4）数据泵导入数据

• 参数

directory                             -- 导出路径该名称指向数据字典DBA_DIRECTORIES中的路径
dumpfile                              -- 导出文件的名称 .dmp
logfile                               -- 记录导出过程日志 .log
tables|schemas|tablesplaces|full      -- 分别表示导出表、用户、表空间、整个数据库
remap_schema=用户1:用户2                -- 用户1到用户2    改变用户的所属
remap_tablesplaces=用户1:用户2          -- 改变表空间
table_exists_action={skip|append|replace|truncate} --表存在的处理：跳过、补充、替换、清空
exclude -- 过滤掉
include -- 仅包含

• 导入命令

-- 1.导入表
impdp user_name/pwd directory=路径名字 dumpfile=文件名.dmp logfile=文件名.log tables=表名字1[,表名2...]
-- 2.导入用户
impdp user_name/pwd directory=路径名字 dumpfile=文件名.dmp logfile=文件名.log sechemas=用户名1[,用户名2...]
-- 3.导入表空间
impdp user_name/pwd directory=路径名字 dumpfile=文件名.dmp logfile=文件名.log tablespaces=表空间名1[,表空间名2...]
-- 4.导入数据库
impdp user_name/pwd directory=路径名字 dumpfile=文件名.dmp logfile=文件名.log full=y

• 例子

impdp scott/scott directory=ODATA dumpfile=emp.dmp logfile=emp.log tables=emp,dept
impdp scott/scott directory=ODATA dumpfile=emp.dmp logfile=emp1.log tables=emp 

5 SPOOL/SQLLDR

1）数据导出SPOOL (需要在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:\path\filename.txt
--设置查询内容
SELECT col1||'|'||col2||'|'||col3||'|'||col4... FROM tb_name;
--结束spool
spool off

• 例子

文件路径D:\ORCLDATA\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:\ORCLDATA\emp.csv
SELECT EMPNO||','||ENAME||','||JOB||','||MGR||','||TO_CHAR(HIREDATE,'YYYY-MM-DD')||','||SAL||','||COMM||','||DEPTNO FROM EMP;
spool off

• 调用

在SQLplus中
@D:\ORCLDATA\emp.sql

2）数据导入SQLLDR (不需要连接数据库用户，需要进入安装数据库的计算机用户)

它常用来接收其它种类数据库导出的文本文件。

它的命令组成中常包括以下几项内容：

​ 数据文件：即接收到的文本文件（常见.dat.txt）

​ 控制文件：一种控制数据如何加载的控制文件（.ctl）

​ 日志文件：记录sqlldr的加载过程、执行情况、报错信息等（.log）

​ 错误文件：记录数据加载过程中因各种问题导入失败的数据（.bad）

我们只需要配置.ctl文件即可。

• .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') )

• 数据加载模式

--四种数据加载模式：
1)INSERT  :直接插入，要求目标表为空（默认）
2)APPEND  :增量加载
3)REPLACE :覆盖旧数据(DELETE FROM tb_name)
4)TRUNCATE:覆盖旧数据(TRUNCATE TABLE tb_name)（建议）

• sqlldr命令

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

例子

ctl文件

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(:HIREDATE,'YYYYMMDD' )",
  SAL        "TRIM(:SAL      )",
  COMM       "TRIM(:COMM     )",
  DEPTNO     "TRIM(:DEPTNO   )" )

sqlldr scott/scott@localhost:1521/orcl data=E:\ORCLDATA\emp.dat control=E:\ORCLDATA\emp.ctl bad=E:\ORCLDATA\empsql.bad log=E:\ORCLDATA\empsql.log errors=0