Oracle 控制文件自动生成

自动生成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