oracle ctl文件7c1b,批量生成控制文件，用sqlldr自动导入多个表的数据

生成控制文件

准备工作：

SQL>set colsep ' ';　　　　//-域输出分隔符SQL>set echo off;　　　　 //显示start启动的脚本中的每个sql命令，缺省为onSQL> set echo on//设置运行命令是是否显示语句SQL> set feedback on;//设置显示“已选择XX行”SQL>set feedback off;//回显本次sql命令处理的记录条数，缺省为onSQL>set heading off;　　 //输出域标题，缺省为onSQL>set pagesize 0;　　 //输出每页行数，缺省为24,为了避免分页，可设定为0。SQL>set linesize 80;　　 //输出一行字符个数，缺省为80SQL>set numwidth 12;　 //输出number类型域长度，缺省为10SQL>set termout off;　　 //显示脚本中的命令的执行结果，缺省为onSQL>set trimout on;　　　//去除标准输出每行的拖尾空格，缺省为offSQL>set trimspool on;　　//去除重定向(spool)输出每行的拖尾空格，缺省为offSQL>set serveroutput on; //设置允许显示输出类似dbms_output

1、spool control.all;

select ctl_name from (

select 'spool ctl/'||lower(table_name)||'.ctl' ctl_name,table_name, 0 cid from user_tables a

union all

select 'select ''LOAD DATA TRUNCATE into table '||table_name,table_name,0.1 cid from user_tables a

union all

select 'FIELDS TERMINATED BY  x'||chr(39)||'03'||chr(39)||' OPTIONALLY ENCLOSED BY '||chr(39)||'"'||chr(39),table_name, 0.2 cid from user_tables a

union all

select 'TRAILING NULLCOLS',table_name,0.3 cid from user_tables a

union all

select '(' ,table_name,0.4 cid from user_tables a

union all

select b.tab_column_ora,a.table_name,0.5 cid from user_tables a,tab_column_union b where a.table_name=b.TABLE_NAME

union all

select ') '' from dual;' ,table_name,0.6 cid from user_tables a

union all

select 'spool off',table_name,0.7 cid from user_tables a

) aa order by table_name,cid;

spool off;

-----------生成一个新表tab_column_union，tab_column_ora字段对时间类型的col字段进行处理，用gy_etl_download存储过程处理。cid 伪列控制输出顺序。

-----------chr(39)是'的意思，两个之间的部分有啥显示啥。

-----------------------------------------

备注：生成tab_column_union的存储过程 。

备注：

CREATE OR REPLACE PROCEDURE GY_ETL_DOWNLOAD as

n_count integer;

v_column_ora varchar2(4000);

begin

update tab_column_union set tab_column_db2=null;

for c1 in (select column_name,a.table_name,column_id,data_type from user_tables a,user_tab_columns b where a.table_name=b.TABLE_NAME order by a.table_name,column_id)

loop

n_count:=0;

v_column_ora:=null;

select count(*) into n_count from tab_column_ex where table_name=c1.table_name and tab_column=c1.column_name;

if n_count> 0 then

select 'to_date'||'('||c1.column_name||','||chr(39)||'yyyy-mm-dd-hh24.mi.ss'||chr(39)||')' into v_column_ora from dual;

update tab_column_union set tab_column_ora=tab_column_ora||v_column_ora||',' where table_name=c1.table_name;

else

if c1.data_type='DATE' then

select c1.column_name||'"to_date(:'||c1.column_name||','||chr(39)||'yyyymmdd'||chr(39)||')"' into v_column_ora from dual;

update tab_column_union set tab_column_ora=tab_column_ora||v_column_ora||',' where table_name=c1.table_name;

else

update tab_column_union set tab_column_ora=tab_column_ora||c1.column_name||',' where table_name=c1.table_name;

end if;

end if;

END LOOP;

update tab_column_union t set tab_column_db2=substr(t.tab_column_db2,1,length(t.tab_column_db2)-1);

update tab_column_union t set tab_column_ora=substr(t.tab_column_ora,1,length(t.tab_column_ora)-1);

COMMIT;

end GY_ETL_DOWNLOAD;

----------------------------------------

2、在sqlplus里运行control.all脚本

SQL>@control.all

批量生成控制文件

3、执行命令脚本,脚本保存为impdata.sh

#!/bin/sh

#获取当前目录

currDir=`dirname $0`

cd $currDir

currDir=`pwd`

#数据库连接串

dbConnStr=joey/jy_123orcl11g

#数据文件保存目录

dataDir=$currDir/data

#控制文件

controlFile=$currDir/ctl/

#设置环境变量

#ORACLE_HOME=/opt/oracle/product/10201

#PATH=$PATH:$ORACLE_HOME/bin

#日志根目录

baseLogDir=$currDir/log

#插入失败数据的记录的目录

badLogDir=$baseLogDir/bad

#执行日志目录  (导入日志)

impLogDir=$baseLogDir/imp

#命令执行日志目录 (错误记录)

cmdLogDir=$baseLogDir/cmd

#创建日志目录

mkdir -p $badLogDir 2> /dev/null

mkdir -p $impLogDir 2> /dev/null

mkdir -p $cmdLogDir 2> /dev/null

for dataFile in `ls ${dataDir}/*.del`

do

logDateSuffix=`date "+%Y%m%d%H%M%S"`

sqlldr $dbConnStr \

silent=all errors=10000 \

data=$dataFile \

log=$impLogDir/`basename ${dataFile}`.$logDateSuffix.imp \

bad=$badLogDir/`basename ${dataFile}`.$logDateSuffix.bad \

control= $controlFile/`basename ${dataFile}`.ctl \

> /dev/null \

2>> $cmdLogDir/`basename ${dataFile}`.${logDateSuffix}.log

done

运行脚本  $./impdata.sh