sqlldr控制文件的使用
SQL*Loader详解
在 Oracle 数据库中，在不同数据库的表间记录进行复制或迁移时会用以下几种方法：
1.导出A表的记录，insert插入到B表中
2.建立dklink create table B from A@dblink where …
Insert into B select * from A@dblink where …
3.exp A, imp B.exp 可加查询条件(见附页)
4.程序实现select from A，再insert into B…，也要分批提交
5.Sqlldr
userid -- Oracle 的 username/password[@servicename]
control -- 控制文件，可能包含表的数据
log -- 记录导入时的日志文件 默认为 控制文件(去除扩展名).log
bad -- 坏数据文件 默认为 控制文件(去除扩展名).bad
data -- 数据文件，一般在控制文件中指定。用参数控制文件中不指定数据文件更适于自动操作
discardmax -- number of discards to allow (Default all)
errors -- 允许的错误记录数，可以用他来控制一条记录都不能错(Default 50)
rows -- 多少条记录提交一次，默认为 64
skip -- 跳过的行数，比如导出的数据文件前面几行是表头或其他描述
direct -- use direct path (Default FALSE -多文件插入)
parallel -- do parallel load (Default FALSE-并发操作)
------------------------------------------------------------------
- 并发操作
sqlldr userid=/ control=result1.ctl direct=true parallel=true
sqlldr userid=/ control=result2.ctl direct=true parallel=true
其中：
replace和truncate模式，均不能使用并发(par文件的parallel=true参数)
2.sqlldr 的使用，有两种使用方法：
只使用一个控制文件，控制文件中包含数据
使用一个控制文件(作为模板) 和一个数据文件。
一般为了利于模板和数据的分离，以及程序的不同分工会使用第二种方式。
3.数据文件：
（1）可以是 CSV 文件或者以其他分割符分隔的数据文件；
（2）可以用 PL/SQL Developer 或者 Toad 导出；
（3）也可以用 SQL *Plus 的 spool 格式化产出，或是 UTL_FILE 包生成；
#! /bin/sh
sqlplus -s hodba/hodba<<EOF
set head off
set linesize 20000
set echo off
set feedback off
set pagesize 0
set termout off
set trimout on
set trimspool on
spool /home/visitor/lxh/test.txt
select empno || ',' || ename || ',' || job || ',' ||
mgr || ',' || hiredate || ',' || sal || ',' ||
comm || ',' || deptno from emp;
spool off;
exit;
EOF
（4）另外，用 Toad 还能直接生成包含数据的控制文件。
Sqlldr步骤：
（1）创建表USER25
create table USER25(
user_id number, --用户 ID
user_name varchar2(50), --用户名
login_times number, --登陆次数
last_login date--最后登录日期
);
（2）插入数据，此时user25有数据了
INS2ERT INTO USER25 VALUES (1, 'Unmi', 3, SYSDATE);
INSERT INTO USER25 VALUES (2, NULL, 5, TO_DATE('2008-10-15', 'YYYY-MM-DD'));
INSERT INTO USER25 VALUES (3, '隔叶黄莺', 8, TO_DATE('2009-01-02', 'YYYY-MM-DD'));
INSERT INTO USER25 VALUES (4, 'Kypfos', NULL, NULL);
INSERT INTO USER25 VALUES (5, '不知秋', 1, TO_DATE('2008-12-23', 'YYYY-MM-DD'));
（3）查看数据，用plsql导出user25.csv
" ","USER_ID","USER_NAME","LOGIN_TIMES","LAST_LOGIN"
"1","1","Unmi","3","2016/5/25 11:39:02"
"2","2","","5","2008/10/15"
"3","3","隔叶黄莺","8","2009/1/2"
"4","4","Kypfos","",""
"5","5","不知秋","1","2008/12/23"
(4)把user25.csv复制到测试环境hodev下，这样才能找到和运行。
（5）在相同路径下创建user25.ctl，文件内容如下：
OPTIONS(SKIP=1,ROWS=128) --sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行
LOAD DATA
INFILE "user25.csv"
--可以infile多个文件，同时导入一张表，但后面的表格无法返回预定的bad文件
append --追加表user25数据到table use25
INTO TABLE user25
Fields terminated by "," --字段由,分隔
Optionally enclosed by '"' --数据中每个字段用”框起
trailing nullcols
(virtual_column FILLER,
user_id , -- user_id number，number报错，删掉编译通过
user_name,
login_times,
last_login DATE"YYYY-MM-DD HH24:MI:SS"
)
------------------------------------------------------
注：之前append处根据网上教程写的是truncate，可是报错，不能同时load和truncate，所以改成了append。
SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCA
TE keyword) for table USER25
ORA-00054: resource busy and acquire with NOWAIT specified
------------------------------------------------------
说明：在操作类型 truncate 位置可用以下中的一项：
1) insert --为缺省方式，在数据装载开始时要求表为空
2) append --在表中追加新记录
3) replace --删除旧记录(用 delete from table 语句)，替换成新装载的记录
4) truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录
其中：
replace和truncate模式，均不能使用并发(par文件的parallel=true参数)
------------------------------------------------------
如果没声明FIELDS TERMINATED BY ',' 时，可以用下面两种方式实现同样功能：
1．为每一列指定分隔符
(
col_1 [interger external] TERMINATED BY ',' ,
col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,
col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'
)
2．用位置告诉字段装载数据
(
col_1 position(1:2),
col_2 position(3:10),
col_3 position(*:16), // 这个字段的开始位置在前一字段的结束位置
col_4 position(1:16),
col_5 position(3:10) char(8) // 指定字段的类型
resultid char terminated by ',',
website char terminated by ',',
ipport char terminated by ',',
status char terminated by whitespace)
数据传输过程极大的依靠网络的稳定，假使中途断了零点几秒也会导致记录丢失，丢失的记录会在.bad文件中找到。
（6）执行命令导入数据
sqlldr hodba/hodba control=user25.ctl
SQL*Loader: Release 10.2.0.5.0 - Production on Wed May 25 11:59:38 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 5
（7）查看表数据
------------------------------------------------------
1 1 Unmi 3 2016/5/25 11:39:02
2 2 5 2008/10/15
3 3 隔叶黄莺 8 2009/1/2
4 4 Kypfos
5 5 不知秋 1 2008/12/23
6 1 Unmi 3 2016/5/25 11:11:05
7 2 5 2008/10/15
8 3 隔叶黄莺 8 2009/1/2
9 4 Kypfos
10 5 不知秋 1 2008/12/23
至此，把user25.csv的数据append（sqlldr）到表user25成功。
Way2:.ctl中加入data
OPTIONS(SKIP=1,ROWS=128)
LOAD DATA INFILE *
append
INTO TABLE user25
Fields terminated by ","
Optionally enclosed by '"' -- 数据中每个字段用”框起
trailing nullcols
(
virtual_column FILLER,
user_id,
user_name,
login_times,
last_login DATE"YYYY-MM-DD HH24:MI:SS"
)
BEGINDATA
" ","USER_ID","USER_NAME","LOGIN_TIMES","LAST_LOGIN"
"1","1","Unmi","3","2016/5/25 11:39:02"
"2","2","","5","2008/10/15"
"3","3","隔叶黄莺","8","2009/1/2"
"4","4","Kypfos","",""
"5","5","不知秋","1","2008/12/23"
-------------------------------------
"4","4","Kypfos","",""不能写成"4","4","Kypfos",NULL,NULL
否则，无法转化，该条数据不能插入表中
login_times terminated by",", NULLIF(login_times='NULL') --可为列单独指定分隔符
last_login DATE"YYYY-MM-DD HH24:MI:SS"NULLIF (last_login="NULL") -- 当字段为"NULL"时就是 NULL)
该函数无效
---------------------------------------------------------------------
impdp\expdp和imp\exp
EXP和IMP:客户端工具程序，可在客户端+服务端使用。缺点：速度慢
EXPDP和IMPDP:服务端的工具程序，只能在ORACLE服务端使用，不能在客户端使用。
IMP只适用于EXP导出的文件，不适用于EXPDP导出文件；IMPDP只适用于EXPDP导出的文件，而不适用于EXP导出文件。
expdp或impdp命令时，可暂不指出用户名/密码@实例名 as 身份，然后根据提示再输入，如：
expdp schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;
一、创建逻辑目录，该命令不会在操作系统创建真正的目录，最好以system等管理员创建。
create directory dpdata1 as 'd:\test\dump';
二、查看管理理员目录（同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错）
select * from dba_directories;
三、给scott用户赋予在指定目录的操作权限，最好以system等管理员赋予。
grant read,write on directory dpdata1 to scott;
四、导出数据
1)按用户导
expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;
2)并行进程parallel
expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3
3)按表名导
expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;
4)按查询条件导
expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';
5)按表空间导
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;
6)导整个数据库
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;
******************************************************************
$ORACLE_HOME/bin/expdp $1 full=y directory=nr_dump_dir ESTIMATE=STATISTICS dumpfile=$dmp
parallel=4 logfile=$log
导出整个数据库到directory这个目录下面
ESTIMATE=STATISTICS,dumpfile=$dmp,并行进程parallel,logfile=$log,directory=nr_dump_dir
> SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'NR_DUMP_DIR' ;
DIRECTORY_PATH：/u3/BACKUP/export
在使用expdp导出数据时，可以使用estimate参数控制并且估算导出文件大小,estimate取值分为blocks 和statistics两种
五、还原数据
1)导到指定用户下
impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;
2)改变表的owner
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept
REMAP_SCHEMA=scott:system;
3)导入表空间
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;
4)导入数据库
impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;
5)追加数据
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION
---------------------------------------------------------------------
posted on 2016-05-30 10:32 EchoLong333 阅读(5384) 评论(0) 编辑 收藏 举报
