linux脚本练习之将数据导入oracle表

　　脚本记录一下执行shell，向oracle数据库的某个表导入数据，熟系一下shell脚本和oracle的导入

　　1、envconfig，数据库配置

export DBUSER=c##sl
export DBPASSWD=123456

　　2、importdata.sh，导入脚本

#!/bin/sh

. /usr/local/myroom/script/envconfig

#检查输入参数
if [ $# -ne 1 ];then
  echo "输入参数错误，格式应为importdata.sh YYYYMMDD" 2>&1
  exit 1
fi

opdate=$1

LOGPATH=/usr/local/myroom/script/log/`echo ${opdate}|cut -c 1-6`
LOGFILE=$LOGPATH/importdata_${opdate}.log
DATAPATH=/usr/local/myroom/script/data

echo "程序执行开始！`date`" 2>&1|tee -a ${LOGFILE}

lastdate=`sqlplus -S ${DBUSER}/${DBPASSWD} <<eof
set heading off
set feedback off
set pagesize 0
set verify off
set echo off
select TO_CHAR(SYSDATE-1,'YYYYMMDD') from dual;
exit
eof`
if [ ! -d $DATAPATH -o ! -d $DATAPATH/tmp ];then
  echo "环境异常，文件路径不存在" 2>&1|tee -a ${LOGFILE}
  exit 1
fi

echo "opdate="[$opdate] 2>&1|tee -a ${LOGFILE}
echo "lastdate="[$lastdate] 2>&1|tee -a ${LOGFILE}

echo "清理临时工作区" 2>&1|tee -a ${LOGFILE}
rm -f ${DATAPATH}/tmp/student* 1>/dev/null 2>/dev/null

STUDENTFILE=student-${lastdate}.dmp
STOPTIME="230000"

if [ "${opdate}" = "${lastdate}" ];then
  while true
  do
    if [ `date "+%H%M%S"` -gt ${STOPTIME} ];then
      echo "`date +%T` 已到截止时间,ctl未到！" 2>&1|tee -a ${LOGFILE}
      exit 1
    fi
    if [ -f ${DATAPATH}/${STUDENTFILE}.gz.ctl ];then
      while true
      do    
    if [ `date "+%H%M%S"` -gt ${STOPTIME} ];then
          echo "`date +%T` 已到截止时间,gz未到！" 2>&1|tee -a ${LOGFILE}
          exit 1
        fi  
        if [ -f ${DATAPATH}/${STUDENTFILE}.gz ];then
          cp ${DATAPATH}/${STUDENTFILE}.gz ${DATAPATH}/tmp
          gunzip ${DATAPATH}/tmp/${STUDENTFILE}.gz
          break
        else
          echo "gz包还未到" 2>&1|tee -a ${LOGFILE}
          sleep 2
        fi
      done
      break
    else
      echo "ctl还未到" 2>&1|tee -a ${LOGFILE}
      sleep 5
    fi
  done
      
  echo "drop student表" 2>&1|tee -a ${LOGFILE}
  `sqlplus -S ${DBUSER}/${DBPASSWD} <<eof
   set heading off
  set feedback off
  set pagesize 0
  set verify off
  set echo off
  drop table student;
  exit
eof`
    
  echo "开始导入student数据" 2>&1|tee -a ${LOGFILE}
  imp ${DBUSER}/${DBPASSWD} buffer=64000 file=${DATAPATH}/tmp/${STUDENTFILE} tables=student 2>&1|tee -a ${LOGFILE}
  if [ $? -ne 0 ]; then
    echo "导入失败！" 2>&1|tee -a ${LOGFILE}
    exit 1
  else
    echo "导入成功！" 2>&1|tee -a ${LOGFILE}
  fi
  echo "导入数据完毕！" 2>&1|tee -a ${LOGFILE}
else
  echo "不需要导入数据" 2>&1|tee -a ${LOGFILE}
fi

echo "删除一个月前的日志文件" 2>&1|tee -a ${LOGFILE}
find $LOGPATH -mtime +30 -name "*student*.*" -exec rm -rf {} \;

echo "删除一周前的数据文件" 2>&1|tee -a ${LOGFILE}
find $DATAPATH -mtime +7 -name "*student*.*" -exec rm -rf {} \;

echo "程序执行完成！`date`" 2>&1|tee -a ${LOGFILE}
exit 0