SPOOL+SQLLDR数据导出导入

SPOOL导出:

$cat spool.sql 
set echo off
set feedback off
set heading off
set verify off
set trimspool on
set pagesize 0
set term off
set linesize 150
spool /ocfs/sql/sqlldrdata/test_spool_file.txt
select
 empno||'       '||ename||'     '||job||'       '||mgr||'       '||to_char(hiredate,'yyyy-mm-dd')||'||sal||'        '||comm||'      '||deptno
from emp;
spool off

$cat test_spool.sh 
#!/bin/bash
sqlplus -s yp/yp < @/ocfs/sql/sqlldr/spool.sql
exit;
EOF

SQLLDR导入:

sqlldr参数：

userid -- Oracle 的 username/password[@servicename]
control -- 控制文件，可能包含表的数据
log -- 记录导入时的日志文件，默认为 控制文件(去除扩展名).log
bad -- 坏数据文件，默认为 控制文件(去除扩展名).bad
data -- 数据文件，一般在控制文件中指定。用参数控制文件中不指定数据文件更适于自动操作
errors -- 允许的错误记录数，可以用他来控制一条记录都不能错
rows -- 多少条记录提交一次，默认为 64
skip -- 跳过的行数，比如导出的数据文件前面几行是表头或其他描述

$cat test_sqlldr.ctl 
LOAD DATA
infile "/ocfs/sql/sqlldrdata/test_spool_file.txt"
into table emp_test append
fields terminated by X'09' 
(empno,ename,job,mgr,hiredate date 'yyyy-mm-dd',sal,comm,deptno)

//以下是4种装入表的方式 
//APPEND // 原先的表有数据就加在后面 
// INSERT // 装载空表,如果原先的表有数据SQLLOADER会停止默认值 
// REPLACE // 删除旧记录(用 delete from table 语句)，替换成新装载的记录
// TRUNCATE // 删除旧记录(用 truncate table 语句)，替换成新装载的记录

// TERMINATED BY X'09' // 以十六进制格式'09'表示文本文件用TAB键分隔

$sqlldr yp/yp control='/ocfs/sql/sqlldr/test_sqlldr.ctl' log='/ocfs/sql/sqlldr/test_sqlldr.log' bad='/ocfs/sql/sqlldr/test_sqlldr.bad'

=====================详细介绍sqlldr===================

关于 Oracle 的数据导入导出及 Sql Loader (sqlldr) 的用法：

在命令行下执行 Oracle  的 sqlldr 命令，可以看到它的详细参数说明，要着重关注以下几个参数：

userid -- Oracle 的 username/password[@servicename]
control -- 控制文件，可能包含表的数据
log -- 记录导入时的日志文件，默认为 控制文件(去除扩展名).log
bad -- 坏数据文件，默认为 控制文件(去除扩展名).bad
data -- 数据文件，一般在控制文件中指定。用参数控制文件中不指定数据文件更适于自动操作
errors -- 允许的错误记录数，可以用他来控制一条记录都不能错
rows -- 多少条记录提交一次，默认为 64
skip -- 跳过的行数，比如导出的数据文件前面几行是表头或其他描述

discard – 丢弃的数据文件

discardmax – 允许丢弃数据的最大值 (全部默认)

skip -- Number of logical records to skip (默认0)

load -- Number of logical records to load (全部默认)

errors – 允许的错误记录数 (默认50)

rows -- Number of rows in conventional path bind array or between direct path data saves（每次提交的记录数，默认: 常规路径 64, 所有直接路径）

bindsize -- Size of conventional path bind array in bytes(默认256000)

每次提交记录的缓冲区的大小(字节为单位，默认256000)

silent --禁止输出信息 (header,feedback,errors,discards,partitions)

direct – 使用直通路径方式导入 (默认FALSE)

parfile -- parameter file: name of file that contains parameter specifications

parallel -- 并行导入 (默认FALSE)

file -- File to allocate extents from

skip_unusable_indexes -- disallow/allow unusable indexes or index partitions(默认FALSE)

skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable(默认FALSE)

readsize -- Size of Read buffer (默认1048576)

与bindsize成对使用，其中较小者会自动调整到较大者。sqlldr先计算单条记录长度，乘以rows，如小于bindsize，不会试图扩张rows以填充bindsize；如超出，则以bindsize为准。

external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE(默认NOT_USED)

columnarrayrows -- Number of rows for direct path column array(默认5000)

streamsize -- Size of direct path stream buffer in bytes(默认256000)

multithreading -- use multithreading in direct path

resumable -- enable or disable resumable for current session(默认FALSE)

resumable_name -- text string to help identify resumable statement

resumable_timeout -- wait time (in seconds) for RESUMABLE(默认7200)

date_cache -- size (in entries) of date conversion cache(默认1000)

用例子来演示 sqlldr 的使用:
首先，假定有这么一个表 users，并插入几条记录：

create table users(   
    user_id number,
    user_name varchar2(50),
    login_times number,
    last_login date 
)   
1) 建立数据文件，这里用 PL/SQL Developer 导出表 users 的记录为 users_data.csv 文件

2) 建立一个控制文件 users.ctl，内容如下：

OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行   
LOAD DATA   
INFILE "users_data.csv" --指定外部数据文件，可以写多个 INFILE "another_data_file.csv" 指定多个数据文件   
--这里还可以使用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件，   
truncate --操作类型，用 truncate table 来清除表中原有记录   
INTO TABLE users -- 要插入记录的表   
Fields terminated by "," -- 数据中每行记录用 "," 分隔   
Optionally enclosed by '"' -- 数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时   
trailing nullcols --表的字段没有对应的值时允许为空   
(   
  virtual_column FILLER, --这是一个虚拟字段，用来跳过由 PL/SQL Developer 生成的第一列序号   
  user_id number, --字段可以指定类型，否则认为是 CHARACTER 类型, log 文件中有显示   
  user_name,   
  login_times,   
  last_login DATE "YYYY-MM-DD HH24:MI:SS" -- 指定接受日期的格式，相当用 to_date() 函数转换   
)  

说明：在操作类型 truncate 位置可用以下中的一值：
1) insert     --为缺省方式，在数据装载开始时要求表为空
2) append  --在表中追加新记录
3) replace  --删除旧记录(用 delete from table 语句)，替换成新装载的记录
4) truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录

3) 执行命令：
sqlldr  control=users.ctl

在 dbservice 指示的数据库的表 users 中记录就和数据文件中的一样了。
执行完 sqlldr 后希望能留意一下生成的几个文件，如 users.log 日志文件、users.bad 坏数据文件等。特别是要看看日志文件，从中可让你更好的理解 Sql Loader，里面有对控制文件的解析、列出每个字段的类型、加载记录的统计、出错原因等信息。

有关 SQL *Loader 的性能与并发操作
1) ROWS 的默认值为 64，你可以根据实际指定更合适的 ROWS 参数来指定每次提交记录数。(体验过在 PL/SQL Developer 中一次执行几条条以上的 insert 语句的情形吗？)
2）常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中，可以提高导入数据的性能。当然，在很多情况下，不能使用此参数(如果主键重复的话会使索引的状态变成UNUSABLE!)。 
3) 通过指定 UNRECOVERABLE选项，可以关闭数据库的日志(是否要 alter table table1 nologging 呢?)。这个选项只能和 direct 一起使用。
4) 对于超大数据文件的导入就要用并发操作了，即同时运行多个导入任务.
  sqlldr   userid=/   control=result1.ctl   direct=true   parallel=true   
  sqlldr   userid=/   control=result2.ctl   direct=true   parallel=true   
  sqlldr   userid=/   control=result2.ctl   direct=true   parallel=true  

  当加载大量数据时（大约超过10GB），最好抑制日志的产生：   
  SQL>ALTER   TABLE   RESULTXT   nologging; 
  这样不产生REDO LOG，可以提高效率。然后在 CONTROL 文件中 load data 上面加一行：unrecoverable，  此选项必须要与DIRECT共同应用。   
  在并发操作时，ORACLE声称可以达到每小时处理100GB数据的能力！其实，估计能到 1－10G 就算不错了，开始可用结构 相同的文件，但只有少量数据，成功后开始加载大量数据，这样可以避免时间的浪费。

--以下过程是eygle编写的，目的是生成csv文件，做了少许修改

--使用方法：
sqlplus / as sysdba
create or replace directory prt as 'c:\data';
grant read,write on directory prt to username;
exec dump_csv(p_qurey=>'select * from yhjb',p_dir=>'PRT',p_file='prt.bat');
--注意，p_dir的参数必须大写，否则会报目录无效的错误。

create or replace procedure dump_csv(p_query     in varchar2,
                                     p_separator in varchar2 default ',',
                                     p_dir       in varchar2,
                                     p_filename  in varchar2) AUTHID CURRENT_USER is
  l_output      utl_file.file_type;
  l_theCursor   integer default dbms_sql.open_cursor;
  l_columnValue varchar2(2000);
  l_status      integer;
  l_colCnt      number default 0;
  l_separator   varchar2(10) default '';
  l_cnt         number default 0;
begin
  l_output := utl_file.fopen(p_dir, p_filename, 'w');

  dbms_sql.parse(l_theCursor, p_query, dbms_sql.native);

  for i in 1 .. 255 loop
    begin
      dbms_sql.define_column(l_theCursor, i, l_columnValue, 2000);
      l_colCnt := i;
    exception
      when others then
        if (sqlcode = -1007) then
          exit;
        else
          raise;
        end if;
    end;
  end loop;

  dbms_sql.define_column(l_theCursor, 1, l_columnValue, 2000);

  l_status := dbms_sql.execute(l_theCursor);

  loop
    exit when(dbms_sql.fetch_rows(l_theCursor) <= 0);
    l_separator := '';
    for i in 1 .. l_colCnt loop
      dbms_sql.column_value(l_theCursor, i, l_columnValue);
      utl_file.put(l_output, l_separator || l_columnValue);
      l_separator := p_separator;
    end loop;
    utl_file.new_line(l_output);
    l_cnt := l_cnt + 1;
  end loop;
  dbms_sql.close_cursor(l_theCursor);

  utl_file.fclose(l_output);
end ;
/