首页 > 代码库 > 批量生成sqlldr文件,高速卸载数据
批量生成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、单表卸载数据
[sql] view plain copy print?
- --首先查看你的数据库是否存在相应的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=http://www.mamicode.com/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、批量卸载数据
[sql] view plain copy print?
- --使用下面的匿名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、卸载数据原始脚本
[sql] view plain copy print?
- 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
转:http://blog.csdn.net/leshami/article/details/18266003
批量生成sqlldr文件,高速卸载数据
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。