批量生成sqlldr文件，高速卸载数据

      SQL*Loader 是用于将外部数据进行批量高速加载的数据库的最高效工具，可用于将多种平面格式文件加载到Oracle数据库。SQL*Loader支持传统路径模式以及直接路径这两种加载模式。关于SQL*Loader的具体用法可以参考Oracle Utilities 手册或者SQL*Loader使用方法。那么如何以SQL*Loader能识别的方式高效的卸载数据呢? Tom大师为我们提供了一个近乎完美的解决方案，是基于exp/imp，Datapump方式迁移数据的有力补充。本文基于此给出描述，并通过批量的方式来卸载数据。

有关本文涉及到的参考链接:
    SQL*Loader使用方法
    数据泵 EXPDP 导出工具的使用
    数据泵IMPDP 导入工具的使用
    PL/SQL-->UTL_FILE包的使用介绍

 

1、单表卸载数据

--首先查看你的数据库是否存在相应的dump目录，如果没有，则应先使用create or replace directory dir_name as '/yourpath'创建scott@SYBO2SZ> @dba_directoriesOwner      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 filePL/SQL procedure successfully completed.--查看刚刚卸载数据生成的文件scott@SYBO2SZ> ho ls -hltr /u02/database/SYBO2SZ/BNR/dumptotal 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.ctlload datainfile 'emp.dat' "str x'7E0A'"into table empreplacefields 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.--装载数据到emprobin@SZDB:/u02/database/SYBO2SZ/BNR/dump> sqlldr scott/tiger control=emp.ctl data=emp.dat direct=trueSQL*Loader: Release 10.2.0.3.0 - Production on Tue Jan 14 15:45:39 2014Copyright (c) 1982, 2005, Oracle.  All rights reserved.Load completed - logical record count 14.

2、批量卸载数据

--使用下面的匿名pl/sql块可以实现批量卸载数据，此处不演示DECLARE   l_rows   NUMBER;   v_sql    VARCHAR2 (200);   CURSOR cur_tab   IS      SELECT table_name FROM user_tables;-->这里定义需要卸载的表，可以单独指定一个表用于存放需要卸载的对象，此处直接查询数据字典BEGIN   FOR tab_name IN cur_tab   LOOP      v_sql := 'select * from ' || tab_name.table_name;      l_rows :=         unloader.run (p_query        => v_sql,                       p_tname        => tab_name.table_name,                       p_mode         => 'replace',                       p_dir          => 'DB_DUMP_DIR',                       p_filename     => tab_name.table_name,                       p_separator    => ',',                       p_enclosure    => '"',                       p_terminator   => '~');-- Author : Leshami-- Blog   : http://blog.csdn.net/leshami      DBMS_OUTPUT.put_line (TO_CHAR (l_rows) || ' rows extracted to ascii file');   END LOOP;END;/

3、卸载数据原始脚本

robin@SZDB:~/dba_scripts/custom/tom> more unloader_pkg.sql CREATE OR REPLACE PACKAGE unloader   AUTHID CURRENT_USERAS   /* Function run -- unloads data from any query into a file   and creates a control file to reload that   data into another table   --注释信息给出了比较详细的描述   p_query = SQL query to "unload". May be virtually any query.   p_tname = Table to load into. Will be put into control file.    p_mode = REPLACE|APPEND|TRUNCATE -- how to reload the data    p_dir = directory we will write the ctl and dat file to.    p_filename = name of file to write to. I will add .ctl and .dat    to this name    p_separator = field delimiter. I default this to a comma.    p_enclosure = what each field will be wrapped in    p_terminator = end of line character. We use this so we can unload    and reload data with newlines in it. I default to    "|\n" (a pipe and a newline together) and "|\r\n" on NT.    You need only to override this if you believe your    data will have that sequence in it. I ALWAYS add the    OS "end of line" marker to this sequence, you should not    */   FUNCTION run (p_query        IN VARCHAR2,                 p_tname        IN VARCHAR2,                 p_mode         IN VARCHAR2 DEFAULT 'REPLACE',                 p_dir          IN VARCHAR2,                 p_filename     IN VARCHAR2,                 p_separator    IN VARCHAR2 DEFAULT ',',                 p_enclosure    IN VARCHAR2 DEFAULT '"',                 p_terminator   IN VARCHAR2 DEFAULT '|')      RETURN NUMBER;END;/CREATE OR REPLACE PACKAGE BODY unloaderAS   g_thecursor   INTEGER DEFAULT DBMS_SQL.open_cursor;   g_desctbl     DBMS_SQL.desc_tab;   g_nl          VARCHAR2 (2) DEFAULT CHR (10);   FUNCTION to_hex (p_str IN VARCHAR2)      RETURN VARCHAR2   IS   BEGIN      RETURN TO_CHAR (ASCII (p_str), 'fm0x');   END;   FUNCTION is_windows      RETURN BOOLEAN   IS      l_cfiles   VARCHAR2 (4000);      l_dummy    NUMBER;   BEGIN      IF (DBMS_UTILITY.get_parameter_value ('control_files', l_dummy, l_cfiles) > 0)      THEN         RETURN INSTR (l_cfiles, '\') > 0;      ELSE         RETURN FALSE;      END IF;   END;   PROCEDURE dump_ctl (p_dir          IN VARCHAR2,                       p_filename     IN VARCHAR2,                       p_tname        IN VARCHAR2,                       p_mode         IN VARCHAR2,                       p_separator    IN VARCHAR2,                       p_enclosure    IN VARCHAR2,                       p_terminator   IN VARCHAR2)   IS      l_output   UTL_FILE.file_type;      l_sep      VARCHAR2 (5);      l_str      VARCHAR2 (5) := CHR (10);   BEGIN      IF (is_windows)      THEN         l_str := CHR (13) || CHR (10);      END IF;      l_output := UTL_FILE.fopen (p_dir, p_filename || '.ctl', 'w');      UTL_FILE.put_line (l_output, 'load data');      UTL_FILE.put_line (l_output, 'infile ''' || p_filename || '.dat'' "str x''' || UTL_RAW.cast_to_raw (p_terminator || l_str) || '''"');      UTL_FILE.put_line (l_output, 'into table ' || p_tname);      UTL_FILE.put_line (l_output, p_mode);      UTL_FILE.put_line (l_output, 'fields terminated by X''' || to_hex (p_separator) || ''' enclosed by X''' || to_hex (p_enclosure) || ''' ');      UTL_FILE.put_line (l_output, '(');      FOR i IN 1 .. g_desctbl.COUNT      LOOP         IF (g_desctbl (i).col_type = 12)         THEN            UTL_FILE.put (l_output, l_sep || g_desctbl (i).col_name || ' date ''ddmmyyyyhh24miss'' ');         ELSE            UTL_FILE.put (l_output, l_sep || g_desctbl (i).col_name || ' char(' || TO_CHAR (g_desctbl (i).col_max_len * 2) || ' )');         END IF;         l_sep := ',' || g_nl;      END LOOP;      UTL_FILE.put_line (l_output, g_nl || ')');      UTL_FILE.fclose (l_output);   END;   FUNCTION quote (p_str IN VARCHAR2, p_enclosure IN VARCHAR2)      RETURN VARCHAR2   IS   BEGIN      RETURN p_enclosure || REPLACE (p_str, p_enclosure, p_enclosure || p_enclosure) || p_enclosure;   END;   FUNCTION run (p_query        IN VARCHAR2,                 p_tname        IN VARCHAR2,                 p_mode         IN VARCHAR2 DEFAULT 'REPLACE',                 p_dir          IN VARCHAR2,                 p_filename     IN VARCHAR2,                 p_separator    IN VARCHAR2 DEFAULT ',',                 p_enclosure    IN VARCHAR2 DEFAULT '"',                 p_terminator   IN VARCHAR2 DEFAULT '|')      RETURN NUMBER   IS      l_output        UTL_FILE.file_type;      l_columnvalue   VARCHAR2 (4000);      l_colcnt        NUMBER DEFAULT 0;      l_separator     VARCHAR2 (10) DEFAULT '';      l_cnt           NUMBER DEFAULT 0;      l_line          LONG;      l_datefmt       VARCHAR2 (255);      l_desctbl       DBMS_SQL.desc_tab;   BEGIN      SELECT VALUE        INTO l_datefmt        FROM nls_session_parameters       WHERE parameter = 'NLS_DATE_FORMAT';      /*      Set the date format to a big numeric string. Avoids      all NLS issues and saves both the time and date.      */      EXECUTE IMMEDIATE 'alter session set nls_date_format=''ddmmyyyyhh24miss'' ';      /*      Set up an exception block so that in the event of any      error, we can at least reset the date format.      */      BEGIN         /*         Parse and describe the query. We reset the         descTbl to an empty table so .count on it         will be reliable.         */         DBMS_SQL.parse (g_thecursor, p_query, DBMS_SQL.native);         g_desctbl := l_desctbl;         DBMS_SQL.describe_columns (g_thecursor, l_colcnt, g_desctbl);         /*         Create a control file to reload this data         into the desired table.         */         dump_ctl (p_dir,                   p_filename,                   p_tname,                   p_mode,                   p_separator,                   p_enclosure,                   p_terminator);         /*         Bind every single column to a varchar2(4000). We don't care         if we are fetching a number or a date or whatever.         Everything can be a string.         */         FOR i IN 1 .. l_colcnt         LOOP            DBMS_SQL.define_column (g_thecursor,                                    i,                                    l_columnvalue,                                    4000);         END LOOP;         /*         Run the query - ignore the output of execute. It is only         valid when the DML is an insert/update or delete.         */         l_cnt := DBMS_SQL.execute (g_thecursor);         /*         Open the file to write output to and then write the         delimited data to it.         */         l_output :=            UTL_FILE.fopen (p_dir,                            p_filename || '.dat',                            'w',                            32760);         LOOP            EXIT WHEN (DBMS_SQL.fetch_rows (g_thecursor) <= 0);            l_separator := '';            l_line := NULL;            FOR i IN 1 .. l_colcnt            LOOP               DBMS_SQL.COLUMN_VALUE (g_thecursor, i, l_columnvalue);               l_line := l_line || l_separator || quote (l_columnvalue, p_enclosure);               l_separator := p_separator;            END LOOP;            l_line := l_line || p_terminator;            UTL_FILE.put_line (l_output, l_line);            l_cnt := l_cnt + 1;         END LOOP;         UTL_FILE.fclose (l_output);         /*         Now reset the date format and return the number of rows         written to the output file.         */         EXECUTE IMMEDIATE 'alter session set nls_date_format=''' || l_datefmt || '''';         RETURN l_cnt;      EXCEPTION         /*         In the event of ANY error, reset the data format and         re-raise the error.         */         WHEN OTHERS         THEN            EXECUTE IMMEDIATE 'alter session set nls_date_format=''' || l_datefmt || '''';            RAISE;      END;   END run;END unloader;/

4、小结
a、本文描述了单表以及多表如何高速卸载数据，并且批量生成sqlldr的控制文件及数据文件
b、包调用者应该对unloader其具有execute权限以及表上的select权限
c、包主要是通过utl_file来写出到控制文件和数据文件，有关utl_file用法可参考：PL/SQL-->UTL_FILE包的使用介绍
d、Tom大师的这个包支持lob数据类型，但其字节不能大于4000，以及不支持long raw

 

   

更多参考

使用 DBMS_PROFILER 定位 PL/SQL 瓶颈代码

使用PL/SQL Developer剖析PL/SQL代码

对比 PL/SQL profiler 剖析结果

PL/SQL Profiler 剖析报告生成html

DML Error Logging 特性 

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划