SQL*Loader 是用于将外部数据进行批量高速加载的数据库的最高效工具，可用于将多种平面格式文件加载到Oracle数据库。SQL*Loader支持传统路径模式以及直接路径这两种加载模式。关于SQL*Loader的具体用法可以参考Oracle Utilities 手册或者SQL*Loader使用方法。那么如何以SQL*Loader能识别的方式高效的卸载数据呢 Tom大师为我们提供了一个近乎完美的解决方案，是基于exp/imp，Datapump方式迁移数据的有力补充。本文基于此给出描述，并通过批量的方式来卸载数据。
有关本文涉及到的参考链接:
1、单表卸载数据
--首先查看你的数据库是否存在相应的dump目录，如果没有，则应先使用create or replace directory dir_name as '/yourpath'创建
scott@SYBO2SZ> @dba_directories
Owner Directory Name Directory Path
---------- ------------------------------ -------------------------------------------------
SYS DB_DUMP_DIR /u02/database/SYBO2SZ/BNR/dump
--下面是用匿名的pl/sql块来卸载单表数据
DECLARE
l_rows NUMBER;
BEGIN
l_rows :=
unloader.run (p_query => 'select * from scott.emp order by empno', --->定义你的查询
p_tname => 'emp', --->定义放入控制文件的表名
p_mode => 'replace', --->定义装载到目标表时使用的方式
p_dir => 'DB_DUMP_DIR', --->定义卸载数据存放目录
p_filename => 'emp', --->定义生成的文件名
p_separator => ',', --->字段分隔符
p_enclosure => '"', --->封装每个字段的符合
p_terminator => '~'); --->行终止符
DBMS_OUTPUT.put_line (TO_CHAR (l_rows) || ' rows extracted to ascii file');
END;
/
14 rows extracted to ascii file
PL/SQL procedure successfully completed.
--查看刚刚卸载数据生成的文件
scott@SYBO2SZ> ho ls -hltr /u02/database/SYBO2SZ/BNR/dump
total 8.0K
-rw-r--r-- 1 oracle oinstall 913 2014-01-14 15:04 emp.dat
-rw-r--r-- 1 oracle oinstall 261 2014-01-14 15:04 emp.ctl
--查看卸载文件的内容
scott@SYBO2SZ> ho more /u02/database/SYBO2SZ/BNR/dump/emp.dat
"7369","SMITH","CLERK","7902","17121980000000","800","","20"~
"7499","ALLEN","SALESMAN","7698","20021981000000","1600","300","30"~
"7521","WARD","SALESMAN","7698","22021981000000","1250","500","30"~
"7566","JONES","MANAGER","7839","02041981000000","2975","","20"~
"7654","MARTIN","SALESMAN","7698","28091981000000","1250","1400","30"~
"7698","BLAKE","MANAGER","7839","01051981000000","2850","","30"~
"7782","CLARK","MANAGER","7839","09061981000000","2650","","10"~
"7788","SCOTT","ANALYST","7566","19041987000000","3000","","20"~
"7839","KING","PRESIDENT","","17111981000000","5200","","10"~
"7844","TURNER","SALESMAN","7698","08091981000000","1500","0","30"~
"7876","ADAMS","CLERK","7788","23051987000000","1100","","20"~
"7900","JAMES","CLERK","7698","03121981000000","950","","30"~
"7902","FORD","ANALYST","7566","03121981000000","3000","","20"~
"7934","MILLER","CLERK","7782","23011982000000","1500","","10"~
--下面是生成的控制文件，有了数据文件和控制文件可以直接进行导入目标表
scott@SYBO2SZ> ho more /u02/database/SYBO2SZ/BNR/dump/emp.ctl
load data
infile 'emp.dat' "str x'7E0A'"
into table emp
replace
fields terminated by X'2c' enclosed by X'22'
(
EMPNO char(44 ),
ENAME char(20 ),
JOB char(18 ),
MGR char(44 ),
HIREDATE date 'ddmmyyyyhh24miss' ,
SAL char(44 ),
COMM char(44 ),
DEPTNO char(44 )
)
--下面我们先truncate表emp，然后尝试使用sqlldr来装载数据
scott@SYBO2SZ> truncate table emp;
Table truncated.
--装载数据到emp
robin@SZDB:/u02/database/SYBO2SZ/BNR/dump> sqlldr scott/tiger control=emp.ctl data=emp.dat direct=true
SQL*Loader: Release 10.2.0.3.0 - Production on Tue Jan 14 15:45:39 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 14.
2、批量卸载数据
-