自动生成sqlloader控制文件并导入数据一例
---------- 说明 ---------- 先使用sql脚本，自动生成sqlloader的控制文件，然后将源表使用查询语句，生成数据文件，并获取源表的创建语句，将这些信息全部ftp到目标数据库服务器上，创建新表，并使用sqlldr将数据插入新表，可以通过这种方式，实现对小表的移植。具有可跨平台、跨版本的好处。 环境简单说明 HP-UX zw01 B.11.11 U 9000/800 oracle rdbms 9206 windowXP hxc oracle rdbms 9201 将 zw01 上的sys.uni_long_time_t 表，通过sql*load的方式，移植到hxc上的system用户下。 ---------- 主要步骤 ---------- 1. 自动生成control.sql生成sql*load控制文件 2. 在hxc上的system用户下创建表，表的脚本来自zw01 上的sys.uni_long_time_t 表 3. 使用查询语句，生成数据文件 4. 执行导入 ------------- step1. 编辑脚本 control.sql ------------- set echo off set heading off set verify off set feedback off set show off set trim off set pages 0 set concat on set lines 300 set trimspool on set trimout on spool &1..ctl select 'LOAD DATA'||chr (10)|| 'INFILE '''||lower (table_name)||'.dat'''||chr (10)|| 'INTO TABLE '||table_name||chr (10)|| 'FIELDS TERMINATED BY '','''||chr (10)|| 'TRAILING NULLCOLS'||chr (10)||'(' from all_tables where table_name = upper ('&1'); select decode (rownum, 1, ' ', ' , ')|| rpad (column_name, 33, ' ')|| decode (data_type, 'VARCHAR2', 'CHAR NULLIF ('||column_name||'=BLANKS)', 'FLOAT', 'DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)', 'NUMBER', decode (data_precision, 0, 'INTEGER EXTERNAL NULLIF ('||column_name|| '=BLANKS)', decode (data_scale, 0, 'INTEGER EXTERNAL NULLIF ('|| column_name||'=BLANKS)', 'DECIMAL EXTERNAL NULLIF ('|| column_name||'=BLANKS)')), 'DATE', 'DATE "MM/DD/YY" NULLIF ('||column_name||'=BLANKS)', null) from user_tab_columns where table_name = upper ('&1') order by column_id; select ')' from sys.dual; spool off ------------- step2. 运行control.sql生成控制文件------------- sqlplus "/as sysdba" Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning option JServer Release 9.2.0.6.0 - Production SQL> start control.sql uni_long_time_t LOAD DATA INFILE 'uni_long_time_t.dat' INTO TABLE UNI_LONG_TIME_T FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( IPDET CHAR NULLIF (IPDET=BLANKS) , USERNAME CHAR NULLIF (USERNAME=BLANKS) , ONTIME DATE "MM/DD/YYYY" NULLIF (ONTIME=BLANKS) , OFFTIME DATE "MM/DD/YYYY" NULLIF (OFFTIME=BLANKS) , FLAG INTEGER EXTERNAL NULLIF (FLAG=BLANKS) , NOTE CHAR NULLIF (NOTE=BLANKS) ) ------------- step3. 获取创建表的脚本 ------------- create table UNI_LONG_TIME_T ( IPDET VARCHAR2(15) not null, USERNAME VARCHAR2(15) not null, ONTIME DATE not null, OFFTIME DATE not null, FLAG INTEGER not null, NOTE VARCHAR2(50) ); ------------- step4. 生成数据文件 ------------- set lines 1000 set pages 1000 spool uni_long_time_t.dat select a.ipdet || ',' || a.username || ',' || to_char(a.ontime, 'mm/dd/yy') || ',' || to_char(a.offtime, 'mm/dd/yy') || ',' || a.flag || ',' || a.note from sys.uni_long_time_t a; spool off ------------- step5. 运行sqlldr------------- sqlldr userid=system/system control=a.ctl C:/>sqlldr userid=system/system control=a.ctl SQL*Loader: Release 9.2.0.1.0 - Production on 星期二 10月 21 09:50:24 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 达到提交点，逻辑记录计数64 达到提交点，逻辑记录计数128 达到提交点，逻辑记录计数192 达到提交点，逻辑记录计数256 达到提交点，逻辑记录计数320 达到提交点，逻辑记录计数384 达到提交点，逻辑记录计数448 达到提交点，逻辑记录计数512 达到提交点，逻辑记录计数576 达到提交点，逻辑记录计数640 达到提交点，逻辑记录计数704 达到提交点，逻辑记录计数768 达到提交点，逻辑记录计数804