使用spool和sqlldr导表的shell脚本

来源：互联网发布：熊出没知夏日连连看编辑：程序博客网时间：2024/05/19 02:45

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

#!/bin/shif [[ $# -ne 1 ]]then echo "usage: $0 TABLENAME>/dev/null" exitfiDBSCHEMA=from_user DBPWD=pwd_of_from_userDB=from_dbTODBSCHEMA=to_userTODBPWD=pwd_of_to_userTODB=to_dbSQLPATH=$HOME/dbdata/sql/DATAPATH=$HOME/dbdata/data/CTLPATH=$HOME/dbdata/ctl/LOGPATH=$HOME/dbdata/log/BADPATH=$HOME/dbdata/bad/TABLENAME=$1SQLNAME=$TABLENAME.sqlTXTNAME=$TABLENAME.txtCTLNAME=$TABLENAME.ctlLOGNAME=$TABLENAME.logBADNAME=$TABLENAME.badif [ -f $SQLPATH/$SQLNAME ]thenrm -f $SQLPATH/$SQLNAMEfiif [ -f $DATAPATH/$TXTNAME ]thenrm -f $DATAPATH/$TXTNAMEfiif [ -f $CTLPATH/$CTLNAME ]thenrm -f $CTLPATH/$CTLNAMEfisqlplus -s $DBSCHEMA/$DBPWD@$DB <<EOFSET HEADING OFF  SET ECHO OFF  SET FEEDBACK OFF  SET PAGES 0  SET LINESIZE 32766SET LONG 1999999SET TRIMOUT ONSET TRIMSPOOL ONSET NEWPAGE NONESET SQLBLANKLINES OFFSET TRIMS ONSET TIMING OFFSET SERVEROUTPUT ONSET VERIFY OFFCOLUMN SQL_TEXT FORMAT A32766 WORD WRAPPEDSPOOL $SQLPATH/$SQLNAMESELECT '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 offspool $CTLPATH/$CTLNAMEselect '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 offspool $DATAPATH/$TXTNAME@$SQLPATH/$SQLNAMEspool offexit;EOFsqlldr userid=$TODBSCHEMA/$TODBPWD@$TODB control=$CTLPATH/$CTLNAME log=$LOGPATH/$LOGNAME bad=$BADPATH/$BADNAME

0 0