使用spool和sqlldr导表的shell脚本

脚本可以一次将一张表导出到另一个数据库中的同一张表中，表名作为shell脚本的输入参数，在使用前需配置两个数据库的相关参数。不过spool对大数据的表执行速度较慢。

#!/bin/sh

if [[ $# -ne 1 ]]
then
 echo "usage: $0 TABLENAME>/dev/null"
 exit
fi

DBSCHEMA=from_user 
DBPWD=pwd_of_from_user
DB=from_db

TODBSCHEMA=to_user
TODBPWD=pwd_of_to_user
TODB=to_db

SQLPATH=$HOME/dbdata/sql/
DATAPATH=$HOME/dbdata/data/
CTLPATH=$HOME/dbdata/ctl/
LOGPATH=$HOME/dbdata/log/
BADPATH=$HOME/dbdata/bad/

TABLENAME=$1
SQLNAME=$TABLENAME.sql
TXTNAME=$TABLENAME.txt
CTLNAME=$TABLENAME.ctl
LOGNAME=$TABLENAME.log
BADNAME=$TABLENAME.bad


if [ -f $SQLPATH/$SQLNAME ]
then
rm -f $SQLPATH/$SQLNAME
fi

if [ -f $DATAPATH/$TXTNAME ]
then
rm -f $DATAPATH/$TXTNAME
fi

if [ -f $CTLPATH/$CTLNAME ]
then
rm -f $CTLPATH/$CTLNAME
fi



sqlplus -s $DBSCHEMA/$DBPWD@$DB <<EOF
SET HEADING OFF  
SET ECHO OFF  
SET FEEDBACK OFF  
SET PAGES 0  
SET LINESIZE 32766
SET LONG 1999999
SET TRIMOUT ON
SET TRIMSPOOL ON
SET NEWPAGE NONE
SET SQLBLANKLINES OFF
SET TRIMS ON
SET TIMING OFF
SET SERVEROUTPUT ON
SET VERIFY OFF
COLUMN SQL_TEXT FORMAT A32766 WORD WRAPPED
SPOOL $SQLPATH/$SQLNAME

SELECT 'SELECT ''"''||' ||
       REPLACE(REPLACE(wmsys.wm_concat(DECODE(DATA_TYPE,'DATE','TO_CHAR('||COLUMN_NAME||'~''YYYY/MM/DD HH24:MI:SS'''||')',COLUMN_NAME)), ',', '||''","''||' || chr(10)),'~',',') ||
       '||''"'' SQL_TEXT FROM $TABLENAME;' SQL_TEXT
  FROM (SELECT *
          FROM DBA_TAB_COLUMNS 
         WHERE TABLE_NAME = '$TABLENAME'
           AND OWNER = '$DBSCHEMA'
         order by column_id);

spool off

spool $CTLPATH/$CTLNAME

select 'load data' || chr(10) || 'infile ' ||
       '''$DATAPATH/$TXTNAME''' || chr(10) ||
       'append into table ' || '$TABLENAME' || chr(10) ||
       'fields terminated by '',''' || chr(10) || 'trailing nullcols' || chr(10) || 'optionally enclosed by ''"'''||
       chr(10) || '(' || chr(10) ||
       REPLACE(wmsys.wm_concat(decode(DATA_TYPE,
                              'DATE',
                              COLUMN_NAME || ' ' || DATA_TYPE || ' ' ||
                              '''YYYY/MM/DD HH24:MI:SS''',
                              COLUMN_NAME)),',',','||chr(10)) || ')'
  from (select *
          from dba_tab_columns a
         where a.TABLE_NAME = '$TABLENAME'
           and a.OWNER = '$DBSCHEMA'
         order by a.COLUMN_ID);

spool off

spool $DATAPATH/$TXTNAME
@$SQLPATH/$SQLNAME
spool off

exit;
EOF

sqlldr userid=$TODBSCHEMA/$TODBPWD@$TODB control=$CTLPATH/$CTLNAME log=$LOGPATH/$LOGNAME bad=$BADPATH/$BADNAME