备份并删除DAYS_RESERVED天以前的表，表名为table_name_date. 使用时要保证表的数据不会发生变化。


#!/usr/bin/bash
export ORACLE_HOME=/app/oracle/product/server_rac/10.2.0.3
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=BEICENP3
LOGFILE=/app/oracle/log/expdp_droptable_olap.log
TDATE=`/usr/bin/date '+%Y%m%d'`
DAYS_RESERVED=180

date2julian() #  day month year
{
  day=$1;  month=$2;  year=$3
  tmpmonth=10#$((12 * 10#$year + 10#$month - 3))
  tmpyear=10#$(($tmpmonth / 12))
  echo $(( (734 * $tmpmonth + 15) / 24 -  2 * $tmpyear + \
    $tmpyear/4 - $tmpyear/100 + $tmpyear/400 + 10#$day + 1721119 ))
}

julian2date() # julianday
{
  tmpday=$(($1 - 1721119))
  centuries=$(( (4 * tmpday - 1) / 146097))
  tmpday=$((tmpday + centuries - centuries/4))
  year=$(( (4 * tmpday - 1) / 1461))
  tmpday=$((tmpday - (1461 * year) / 4))
  month=$(( (10 * tmpday - 5) / 306))
  day=$((tmpday - (306 * month + 5) / 10))
  month=$((month + 2))
  year=$((year + month/12))
  month=$((month % 12 + 1))
  echo "${year}-${month}-${day}"
}

date_format() #change date format. eg: change date from 2008-7-5 to 20080705
{
 
  year1=$( echo $1 | awk -F- '{print $1}' )
  month1=$( echo $1 | awk -F- '{print $2}' )
  day1=$( echo $1 | awk -F- '{print $3}' )
  if [ ${#month1} -lt 2 ] ; then
    month1="0"$month1
  fi
  if [ ${#day1} -lt 2 ] ; then
   day1="0"$day1
  fi
  echo $year1$month1$day1 
}

# expdp table and gzip dumpfile
expdp_gzip_tab()
{
  WNER=$1
  TABNAME=$2
  EXPDP=${ORACLE_HOME}/bin/expdp
  GZIP=/usr/bin/gzip
  GREP=/usr/bin/grep
  RADIR="/oracle/userdata/expdpdir"
  DUMPNAME=${TABNAME}.dmp
  EXPDPLOGNAME=expdp_${TABNAME}.log
  EXPDPLOG="${ORADIR}/${EXPDPLOGNAME}"
  DUMPFILE="${ORADIR}/${DUMPNAME}"
 
  if [ -f $DUMPFILE -o -f ${DUMPFILE}.gz ] ; then
    echo "Dumpfile $DUMPFILE or ${DUMPFILE}.gz exist! Please delete it!"
    echo
    return 100
  fi
 
  $EXPDP system/sys_beicenp directory=EXPDPDIR dumpfile=${DUMPNAME} logfile=${EXPDPLOGNAME} tables=${OWNER}.${TABNAME} > /dev/null 2
>&1
  ### does not use expdp exit code for judgement because of oracle bug
  MESG=$($GREP "successfully completed" ${EXPDPLOG})
  if [ $? -eq 0 ] ; then
    echo "expdp successfully completed for table ${OWNER}.${TABNAME}!"
    echo
  else
    echo "expdp failed for table ${OWNER}.${TABNAME}!"
    echo
    echo "please check the expdp logfile ${EXPDPLOG}!"
    echo
    return 101
  fi
 
  ERROR=$($GZIP ${DUMPFILE} 2>&1)
  if [ $? -eq 0 ] ; then
    echo "gzip successfully completed for file ${DUMPFILE}!"
    echo
  else
    echo "gzip failed for file ${DUMPFILE}!"
    echo
    echo $ERROR
    echo
    return 102
  fi 
}

# drop table
drop_tab()  # owner tabname
{
OWNER=$1
TABNAME=$2
SQLPLUS=${ORACLE_HOME}/bin/sqlplus
$SQLPLUS -s / as sysdba <whenever sqlerror exit 1;
drop table ${OWNER}.${TABNAME} purge;
exit
EOF
}

exec 1>>$LOGFILE 2>&1

# get the date DAYS_RESERVED ago
day=${TDATE:6:2}; month=${TDATE:4:2}; year=${TDATE:0:4}
ODATE=$(julian2date $(( $(date2julian $day $month $year) - $DAYS_RESERVED )))

# change date format to yyyymmdd
DATESTR=$(date_format $ODATE)

# table name
TABOWNER="OLAP_ACC"
TABNAME1="OLAP_ENTRY_"$DATESTR
TABNAME2="OLAP_EXIT_"$DATESTR
TABNAME3="OLAP_SALE_"$DATESTR
TABNAME4="FACT_EE_"$DATESTR
TABNAME5="FACT_EEOD_"$DATESTR

# expdp table and gzip file
echo
echo
echo
date
echo "-----------------------------------------------------------------------------------------------------"
echo
echo -e "\t$TABNAME1"
echo -e "\t----------------------"

MSG_EXPDP1=$(expdp_gzip_tab $TABOWNER $TABNAME1)
if [ $? -eq 0 ] ; then
  echo "$MSG_EXPDP1"
  echo
  MSG_DROP1=$(drop_tab $TABOWNER $TABNAME1)
  if [ $? -eq 0 ] ; then
    echo "drop table ${TABOWNER}.${TABNAME1} successfully. "
    echo
    echo "$MSG_DROP1"
    echo
  else
    echo "drop table ${TABOWNER}.${TABNAME1} failed. "
    echo
    echo "$MSG_DROP1"
    echo
  fi 
else
  echo "$MSG_EXPDP1"
  echo
fi
echo
echo -e "\t-------------------------------------------------------------------------------------"
echo -e "\t-------------------------------------------------------------------------------------"
echo
echo -e "\t$TABNAME2"
echo -e "\t----------------------"

MSG_EXPDP2=$(expdp_gzip_tab $TABOWNER $TABNAME2)
if [ $? -eq 0 ] ; then
  echo "$MSG_EXPDP2"
  echo
  MSG_DROP2=$(drop_tab $TABOWNER $TABNAME2)
  if [ $? -eq 0 ] ; then
    echo "drop table ${TABOWNER}.${TABNAME2} successfully. "
    echo
    echo "$MSG_DROP2"
    echo
  else
    echo "drop table ${TABOWNER}.${TABNAME2} failed. "
    echo
    echo "$MSG_DROP2"
    echo
  fi
else
  echo "$MSG_EXPDP2"
  echo
fi
echo
echo -e "\t-------------------------------------------------------------------------------------"
echo -e "\t-------------------------------------------------------------------------------------"
echo
echo -e "\t$TABNAME3"
echo -e "\t----------------------"

MSG_EXPDP3=$(expdp_gzip_tab $TABOWNER $TABNAME3)
if [ $? -eq 0 ] ; then
  echo "$MSG_EXPDP3"
  echo
  MSG_DROP3=$(drop_tab $TABOWNER $TABNAME3)
  if [ $? -eq 0 ] ; then
    echo "drop table ${TABOWNER}.${TABNAME3} successfully. "
    echo
    echo "$MSG_DROP3"
    echo
  else
    echo "drop table ${TABOWNER}.${TABNAME3} failed. "
    echo
    echo "$MSG_DROP3"
    echo
  fi
else
  echo "$MSG_EXPDP3"
  echo
fi
echo
echo -e "\t-------------------------------------------------------------------------------------"
echo -e "\t-------------------------------------------------------------------------------------"
echo
echo -e "\t$TABNAME4"
echo -e "\t----------------------"

MSG_EXPDP4=$(expdp_gzip_tab $TABOWNER $TABNAME4)
if [ $? -eq 0 ] ; then
  echo "$MSG_EXPDP4"
  echo
  MSG_DROP4=$(drop_tab $TABOWNER $TABNAME4)
  if [ $? -eq 0 ] ; then
    echo "drop table ${TABOWNER}.${TABNAME4} successfully. "
    echo
    echo "$MSG_DROP4"
    echo
  else
    echo "drop table ${TABOWNER}.${TABNAME4} failed. "
    echo
    echo "$MSG_DROP4"
    echo
  fi
else
  echo "$MSG_EXPDP4"
  echo
fi
echo
echo -e "\t-------------------------------------------------------------------------------------"
echo -e "\t-------------------------------------------------------------------------------------"
echo
echo -e "\t$TABNAME5"
echo -e "\t----------------------"

MSG_EXPDP5=$(expdp_gzip_tab $TABOWNER $TABNAME5)
if [ $? -eq 0 ] ; then
  echo "$MSG_EXPDP5"
  echo
  MSG_DROP5=$(drop_tab $TABOWNER $TABNAME5)
  if [ $? -eq 0 ] ; then
    echo "drop table ${TABOWNER}.${TABNAME5} successfully. "
    echo
    echo "$MSG_DROP5"
    echo
  else
    echo "drop table ${TABOWNER}.${TABNAME5} failed. "
    echo
    echo "$MSG_DROP5"
    echo
  fi
else
  echo "$MSG_EXPDP5"
  echo
fi
echo
echo "-----------------------------------------------------------------------------------------------------"