批量生成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、单表卸载数据
1. --首先查看你的数据库是否存在相应的dump目录，如果没有，则应先使用create or replace directory dir_name as '/yourpath'创建  
2. scott@SYBO2SZ> @dba_directories  
3.  
4. Owner      Directory Name                 Directory Path  
5. ---------- ------------------------------ -------------------------------------------------  
6. SYS        DB_DUMP_DIR                    /u02/database/SYBO2SZ/BNR/dump  
7.  
8. --下面是用匿名的pl/sql块来卸载单表数据  
9. DECLARE  
10.   l_rows   NUMBER;  
11. BEGIN  
12.   l_rows :=  
13.      unloader.run (p_query        => 'select * from scott.emp order by empno',    --->定义你的查询  
14.                    p_tname        => 'emp',                                       --->定义放入控制文件的表名  
15.                    p_mode         => 'replace',                                   --->定义装载到目标表时使用的方式     
16.                    p_dir          => 'DB_DUMP_DIR',                               --->定义卸载数据存放目录  
17.                    p_filename     => 'emp',                                       --->定义生成的文件名  
18.                    p_separator    => ',',                                         --->字段分隔符  
19.                    p_enclosure    => '"',                                         --->封装每个字段的符合  
20.                    p_terminator   => '~');                                        --->行终止符  
21.  
22.   DBMS_OUTPUT.put_line (TO_CHAR (l_rows) || ' rows extracted to ascii file');  
23. END;  
24. /  
25.  
26. 14 rows extracted to ascii file  
27.  
28. PL/SQL procedure successfully completed.  
29.  
30. --查看刚刚卸载数据生成的文件  
31. scott@SYBO2SZ> ho ls -hltr /u02/database/SYBO2SZ/BNR/dump  
32. total 8.0K  
33. -rw-r--r-- 1 oracle oinstall  913 2014-01-14 15:04 emp.dat  
34. -rw-r--r-- 1 oracle oinstall  261 2014-01-14 15:04 emp.ctl  
35.  
36. --查看卸载文件的内容   
37. scott@SYBO2SZ> ho more /u02/database/SYBO2SZ/BNR/dump/emp.dat  
38. "7369","SMITH","CLERK","7902","17121980000000","800","","20"~  
39. "7499","ALLEN","SALESMAN","7698","20021981000000","1600","300","30"~  
40. "7521","WARD","SALESMAN","7698","22021981000000","1250","500","30"~  
41. "7566","JONES","MANAGER","7839","02041981000000","2975","","20"~  
42. "7654","MARTIN","SALESMAN","7698","28091981000000","1250","1400","30"~  
43. "7698","BLAKE","MANAGER","7839","01051981000000","2850","","30"~  
44. "7782","CLARK","MANAGER","7839","09061981000000","2650","","10"~  
45. "7788","SCOTT","ANALYST","7566","19041987000000","3000","","20"~  
46. "7839","KING","PRESIDENT","","17111981000000","5200","","10"~  
47. "7844","TURNER","SALESMAN","7698","08091981000000","1500","0","30"~  
48. "7876","ADAMS","CLERK","7788","23051987000000","1100","","20"~  
49. "7900","JAMES","CLERK","7698","03121981000000","950","","30"~  
50. "7902","FORD","ANALYST","7566","03121981000000","3000","","20"~  
51. "7934","MILLER","CLERK","7782","23011982000000","1500","","10"~  
52.  
53. --下面是生成的控制文件，有了数据文件和控制文件可以直接进行导入目标表  
54. scott@SYBO2SZ> ho more /u02/database/SYBO2SZ/BNR/dump/emp.ctl  
55. load data  
56. infile 'emp.dat' "str x'7E0A'"  
57. into table emp  
58. replace  
59. fields terminated by X'2c' enclosed by X'22'   
60. (  
61. EMPNO char(44 ),  
62. ENAME char(20 ),  
63. JOB char(18 ),  
64. MGR char(44 ),  
65. HIREDATE date 'ddmmyyyyhh24miss' ,  
66. SAL char(44 ),  
67. COMM char(44 ),  
68. DEPTNO char(44 )  
69. )  
70.  
71. --下面我们先truncate表emp，然后尝试使用sqlldr来装载数据  
72. scott@SYBO2SZ> truncate table emp;  
73.  
74. Table truncated.  
75.  
76. --装载数据到emp  
77. robin@SZDB:/u02/database/SYBO2SZ/BNR/dump> sqlldr scott/tiger control=emp.ctl data=emp.dat direct=true  
78.  
79. SQL*Loader: Release 10.2.0.3.0 - Production on Tue Jan 14 15:45:39 2014  
80.  
81. Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
82.  
83. Load completed - logical record count 14.  
2、批量卸载数据
1. --使用下面的匿名pl/sql块可以实现批量卸载数据，此处不演示  
2. DECLARE  
3.   l_rows   NUMBER;  
4.   v_sql    VARCHAR2 (200);  
5.  
6.   CURSOR cur_tab  
7.   IS  
8.      SELECT table_name FROM user_tables;-->这里定义需要卸载的表，可以单独指定一个表用于存放需要卸载的对象，此处直接查询数据字典  
9. BEGIN  
10.   FOR tab_name IN cur_tab  
11.   LOOP  
12.      v_sql := 'select * from ' || tab_name.table_name;  
13.      l_rows :=  
14.         unloader.run (p_query        => v_sql,  
15.                       p_tname        => tab_name.table_name,  
16.                       p_mode         => 'replace',  
17.                       p_dir          => 'DB_DUMP_DIR',  
18.                       p_filename     => tab_name.table_name,  
19.                       p_separator    => ',',  
20.                       p_enclosure    => '"',  
21.                       p_terminator   => '~');  
22. -- Author : Leshami  
23. -- Blog   : http://blog.csdn.net/leshami  
24.  
25.      DBMS_OUTPUT.put_line (TO_CHAR (l_rows) || ' rows extracted to ascii file');  
26.   END LOOP;  
27. END;  
28. /  
3、卸载数据原始脚本
1. robin@SZDB:~/dba_scripts/custom/tom> more unloader_pkg.sql   
2. CREATE OR REPLACE PACKAGE unloader  
3.   AUTHID CURRENT_USER  
4. AS  
5.   /* Function run -- unloads data from any query into a file  
6.   and creates a control file to reload that  
7.   data into another table  
8.   --注释信息给出了比较详细的描述  
9.   p_query = SQL query to "unload". May be virtually any query.  
10.   p_tname = Table to load into. Will be put into control file.  
11.    p_mode = REPLACE|APPEND|TRUNCATE -- how to reload the data  
12.    p_dir = directory we will write the ctl and dat file to.  
13.    p_filename = name of file to write to. I will add .ctl and .dat  
14.    to this name  
15.    p_separator = field delimiter. I default this to a comma.  
16.    p_enclosure = what each field will be wrapped in  
17.    p_terminator = end of line character. We use this so we can unload  
18.    and reload data with newlines in it. I default to  
19.    "|\n" (a pipe and a newline together) and "|\r\n" on NT.  
20.    You need only to override this if you believe your  
21.    data will have that sequence in it. I ALWAYS add the  
22.    OS "end of line" marker to this sequence, you should not  
23.    */  
24.   FUNCTION run (p_query        IN VARCHAR2,  
25.                 p_tname        IN VARCHAR2,  
26.                 p_mode         IN VARCHAR2 DEFAULT 'REPLACE',  
27.                 p_dir          IN VARCHAR2,  
28.                 p_filename     IN VARCHAR2,  
29.                 p_separator    IN VARCHAR2 DEFAULT ',',  
30.                 p_enclosure    IN VARCHAR2 DEFAULT '"',  
31.                 p_terminator   IN VARCHAR2 DEFAULT '|')  
32.      RETURN NUMBER;  
33. END;  
34. /  
35.  
36. CREATE OR REPLACE PACKAGE BODY unloader  
37. AS  
38.   g_thecursor   INTEGER DEFAULT DBMS_SQL.open_cursor;  
39.   g_desctbl     DBMS_SQL.desc_tab;  
40.   g_nl          VARCHAR2 (2) DEFAULT CHR (10);  
41.  
42.   FUNCTION to_hex (p_str IN VARCHAR2)  
43.      RETURN VARCHAR2  
44.   IS  
45.   BEGIN  
46.      RETURN TO_CHAR (ASCII (p_str), 'fm0x');  
47.   END;  
48.  
49.   FUNCTION is_windows  
50.      RETURN BOOLEAN  
51.   IS  
52.      l_cfiles   VARCHAR2 (4000);  
53.      l_dummy    NUMBER;  
54.   BEGIN  
55.      IF (DBMS_UTILITY.get_parameter_value ('control_files', l_dummy, l_cfiles) > 0)  
56.      THEN  
57.         RETURN INSTR (l_cfiles, '\') > 0;  
58.      ELSE  
59.         RETURN FALSE;  
60.      END IF;  
61.   END;  
62.  
63.   PROCEDURE dump_ctl (p_dir          IN VARCHAR2,  
64.                       p_filename     IN VARCHAR2,  
65.                       p_tname        IN VARCHAR2,  
66.                       p_mode         IN VARCHAR2,  
67.                       p_separator    IN VARCHAR2,  
68.                       p_enclosure    IN VARCHAR2,  
69.                       p_terminator   IN VARCHAR2)  
70.   IS  
71.      l_output   UTL_FILE.file_type;  
72.      l_sep      VARCHAR2 (5);  
73.      l_str      VARCHAR2 (5) := CHR (10);  
74.   BEGIN  
75.      IF (is_windows)  
76.      THEN  
77.         l_str := CHR (13) || CHR (10);  
78.      END IF;  
79.  
80.      l_output := UTL_FILE.fopen (p_dir, p_filename || '.ctl', 'w');  
81.  
82.      UTL_FILE.put_line (l_output, 'load data');  
83.      UTL_FILE.put_line (l_output, 'infile ''' || p_filename || '.dat'' "str x''' || UTL_RAW.cast_to_raw (p_terminator || l_str) || '''"');  
84.      UTL_FILE.put_line (l_output, 'into table ' || p_tname);  
85.      UTL_FILE.put_line (l_output, p_mode);  
86.      UTL_FILE.put_line (l_output, 'fields terminated by X''' || to_hex (p_separator) || ''' enclosed by X''' || to_hex (p_enclosure) || ''' ');  
87.      UTL_FILE.put_line (l_output, '(');  
88.  
89.      FOR i IN 1 .. g_desctbl.COUNT  
90.      LOOP  
91.         IF (g_desctbl (i).col_type = 12)  
92.         THEN  
93.            UTL_FILE.put (l_output, l_sep || g_desctbl (i).col_name || ' date ''ddmmyyyyhh24miss'' ');  
94.         ELSE  
95.            UTL_FILE.put (l_output, l_sep || g_desctbl (i).col_name || ' char(' || TO_CHAR (g_desctbl (i).col_max_len * 2) || ' )');  
96.         END IF;  
97.  
98.         l_sep := ',' || g_nl;  
99.      END LOOP;  
100.  
101.      UTL_FILE.put_line (l_output, g_nl || ')');  
102.      UTL_FILE.fclose (l_output);  
103.   END;  
104.  
105.   FUNCTION quote (p_str IN VARCHAR2, p_enclosure IN VARCHAR2)  
106.      RETURN VARCHAR2  
107.   IS  
108.   BEGIN  
109.      RETURN p_enclosure || REPLACE (p_str, p_enclosure, p_enclosure || p_enclosure) || p_enclosure;  
110.   END;  
111.  
112.   FUNCTION run (p_query        IN VARCHAR2,  
113.                 p_tname        IN VARCHAR2,  
114.                 p_mode         IN VARCHAR2 DEFAULT 'REPLACE',  
115.                 p_dir          IN VARCHAR2,  
116.                 p_filename     IN VARCHAR2,  
117.                 p_separator    IN VARCHAR2 DEFAULT ',',  
118.                 p_enclosure    IN VARCHAR2 DEFAULT '"',  
119.                 p_terminator   IN VARCHAR2 DEFAULT '|')  
120.      RETURN NUMBER  
121.   IS  
122.      l_output        UTL_FILE.file_type;  
123.      l_columnvalue   VARCHAR2 (4000);  
124.      l_colcnt        NUMBER DEFAULT 0;  
125.      l_separator     VARCHAR2 (10) DEFAULT '';  
126.      l_cnt           NUMBER DEFAULT 0;  
127.      l_line          LONG;  
128.      l_datefmt       VARCHAR2 (255);  
129.      l_desctbl       DBMS_SQL.desc_tab;  
130.   BEGIN  
131.      SELECT VALUE  
132.        INTO l_datefmt  
133.        FROM nls_session_parameters  
134.       WHERE parameter = 'NLS_DATE_FORMAT';  
135.  
136.      /*  
137.      Set the date format to a big numeric string. Avoids  
138.      all NLS issues and saves both the time and date.  
139.      */  
140.      EXECUTE IMMEDIATE 'alter session set nls_date_format=''ddmmyyyyhh24miss'' ';  
141.  
142.      /*  
143.      Set up an exception block so that in the event of any  
144.      error, we can at least reset the date format.  
145.      */  
146.      BEGIN  
147.         /*  
148.         Parse and describe the query. We reset the  
149.         descTbl to an empty table so .count on it  
150.         will be reliable.  
151.         */  
152.         DBMS_SQL.parse (g_thecursor, p_query, DBMS_SQL.native);  
153.         g_desctbl := l_desctbl;  
154.         DBMS_SQL.describe_columns (g_thecursor, l_colcnt, g_desctbl);  
155.  
156.         /*  
157.         Create a control file to reload this data  
158.         into the desired table.  
159.         */  
160.         dump_ctl (p_dir,  
161.                   p_filename,  
162.                   p_tname,  
163.                   p_mode,  
164.                   p_separator,  
165.                   p_enclosure,  
166.                   p_terminator);  
167.  
168.         /*  
169.         Bind every single column to a varchar2(4000). We don't care  
170.         if we are fetching a number or a date or whatever.  
171.         Everything can be a string.  
172.         */  
173.         FOR i IN 1 .. l_colcnt  
174.         LOOP  
175.            DBMS_SQL.define_column (g_thecursor,  
176.                                    i,  
177.                                    l_columnvalue,  
178.                                    4000);  
179.         END LOOP;  
180.  
181.         /*  
182.         Run the query - ignore the output of execute. It is only  
183.         valid when the DML is an insert/update or delete.  
184.         */  
185.         l_cnt := DBMS_SQL.execute (g_thecursor);  
186.  
187.         /*  
188.         Open the file to write output to and then write the  
189.         delimited data to it.  
190.         */  
191.         l_output :=  
192.            UTL_FILE.fopen (p_dir,  
193.                            p_filename || '.dat',  
194.                            'w',  
195.                            32760);  
196.  
197.         LOOP  
198.            EXIT WHEN (DBMS_SQL.fetch_rows (g_thecursor) <= 0);  
199.            l_separator := '';  
200.            l_line := NULL;  
201.  
202.            FOR i IN 1 .. l_colcnt  
203.            LOOP  
204.               DBMS_SQL.COLUMN_VALUE (g_thecursor, i, l_columnvalue);  
205.               l_line := l_line || l_separator || quote (l_columnvalue, p_enclosure);  
206.               l_separator := p_separator;  
207.            END LOOP;  
208.  
209.            l_line := l_line || p_terminator;  
210.            UTL_FILE.put_line (l_output, l_line);  
211.            l_cnt := l_cnt + 1;  
212.         END LOOP;  
213.  
214.         UTL_FILE.fclose (l_output);  
215.  
216.         /*  
217.         Now reset the date format and return the number of rows  
218.         written to the output file.  
219.         */  
220.         EXECUTE IMMEDIATE 'alter session set nls_date_format=''' || l_datefmt || '''';  
221.  
222.         RETURN l_cnt;  
223.      EXCEPTION  
224.         /*  
225.         In the event of ANY error, reset the data format and  
226.         re-raise the error.  
227.         */  
228.         WHEN OTHERS  
229.         THEN  
230.            EXECUTE IMMEDIATE 'alter session set nls_date_format=''' || l_datefmt || '''';  
231.  
232.            RAISE;  
233.      END;  
234.   END run;  
235. END unloader;  
236. /  


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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31448824/viewspace-2139356/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31448824/viewspace-2139356/