批量生成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_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、批量卸载数据

--使用下面的匿名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_USER
AS
   /* 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 unloader
AS
   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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

聚簇因子和执行计划的联系(r3笔记第90天)

在平时的工作中,可能会碰到一种很奇怪的问题,本来在生产环境中有些sql语句执行没有问题,一个很普通的查询预期走了索引扫面,但是拷贝数据到其它环境之后,就发现却走...

29450
来自专栏杨建荣的学习笔记

基于DB time的调优分析 (r6笔记第79天)

继昨天使用DB time能够快速灵活的定位sql语句之后,发现分析问题更快捷,高效了。今天就牛刀小试,把一个数据库从500%的负载调到不到100%的负载。前提是...

31440
来自专栏杨建荣的学习笔记

关于索引和空值的讨论(r3笔记第80天)

在日常的工作中,空值总是有特殊的身份,对于它的处理有时候也是比较纠结。 有时候创建索引的时候会因为空值出现一些奇怪的结果。 有时候一个简单的查询因为空值却走不了...

28360
来自专栏杨建荣的学习笔记

关于all_procedures的问题分析 (r9笔记第61天)

今天快下班的时候有一个同事问我一个存储过程的权限是否做过修改。我简单看了下发现这个滚出过程已经是很久以前创建的了,一直没有做过修改,所以就反馈给 他了。但是他过...

35360
来自专栏杨建荣的学习笔记

关于奇怪的并行进程分析(三)(r6笔记第47天)

在前两篇的基础上,对于一个环境中存在的奇怪并行进程问题进行了初步的分析。 初步排除了是通过scheduler的job运行导致的,一方面因为运行的时间会有延迟,甚...

28940
来自专栏数据库新发现

Oracle诊断案例-Sql_trace之一

http://www.eygle.com/case/sql_trace_1.htm

12430
来自专栏数据和云

循序渐进:Oracle 12.2的Sharding基础概念解读

张大朋(Lunar)Oracle 工程师 Lunar 拥有超过十年的 ORACLE SUPPORT 从业经验,曾经服务于ORACLE ACS部门,现就职于 O...

31940
来自专栏乐沙弥的世界

Oracle大表清理truncate .. reuse storage

a、通过上述测试,当使用reuse storage与普通方式并无明显差异 b、truncate table 是ddl操作,无法回滚 c、尽管无明显性能差...

15340
来自专栏乐沙弥的世界

Oracle 全表扫描及其执行计划(full table scan)

    全表扫描是Oracle访问数据库表是较为常见的访问方式之一。很多朋友一看到SQL语句执行计划中的全表扫描,就要考虑对其进行修理一番。全表扫描的存在,的确...

26710
来自专栏乐沙弥的世界

Oracle自适应共享游标

    自适应游标共享Adaptive Cursor Sharing或扩展的游标共享(Extended Cursor Sharing)是Oracle 11g...

11920

扫码关注云+社区

领取腾讯云代金券