第三版ORACLE 分区表定期自动分区导出及删除导出分区(三)

5. 操作系统脚本

cat expdp_partition.sh
#!/bin/sh

v_path=/data/dump_dir
b_path=/data/backup

cd $b_path
find $b_path/TAB* -mtime +30 -exec rm -f {} \;

cd $v_path
find $v_path/TAB* -mtime +15 -exec rm -f {} \;

ORACLE_HOME=/u01/product/oracle/11.2.0/db_1;export ORACLE_HOME
ORACLE_SID=szxonline;export ORACLE_SID

FILENAME=`date +%Y%m%d%H`
PARTNAME=P`date -d "7 days ago" +%Y%m%d`
OWNER=TOPUP
RECORDTAB=TAB_PARTITION_MANTAIN_RECORD
TABNAME=`$ORACLE_HOME/bin/sqlplus -silent "/ as sysdba" << END
set pagesize 0 feedback off verify off heading off echo off
select   table_name from   dba_part_tables  where   wner = '$OWNER';
exit;
END`
#echo $TABNAME

sh /data/run/disable_trigger.sh

for tablename in $TABNAME
do

#echo $tablename
#done

TAB_A=$OWNER.$tablename:$PARTNAME
TAB_B=$tablename$PARTNAME

#sh /data/run/enable_trigger.sh

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
$ORACLE_HOME/bin/expdp 'system/" system@dbsec"' directory=dump_dir dumpfile=$TAB_B.dmp logfile=$TAB_B.log  tables=$TAB_A

#sh /data/run/disable_trigger.sh

v_type=expdp_partition
v_log=ok
flag=successfully
sflag=`tail -1 $v_path/$TAB_B.log | awk '{print $3}'`
v_err_1=`cat $v_path/$TAB_B.log | grep err`
v_err_2=`cat $v_path/$TAB_B.log | grep ora`
v_err_3=`cat $v_path/$TAB_B.log | grep ora`
createdate=`date +%Y%m%d`

#createdate=`date -d "4 days ago" +%Y%m%d`
err_1=${v_err_1:=ok}
err_2=${v_err_2:=ok}
err_3=${v_err_3:=ok}

if [ "$sflag" = "$flag" ]&&[ "$err_1" = "$v_log" ]&&[ "$err_2" = "$v_log" ]&&[ "$err_3" = "$v_log" ];then
 
$ORACLE_HOME/bin/sqlplus /nolog <connect / as sysdba;
insert into $OWNER.$RECORDTAB values('$createdate','$v_type','$tablename','$PARTNAME','1');
commit;
exit;
EOF

cd $v_path
gzip $TAB_B.*
mv $TAB_B.* $b_path
 
else

$ORACLE_HOME/bin/sqlplus /nolog <connect / as sysdba;
insert into $OWNER.$RECORDTAB values('$createdate','$v_type','$tablename','$PARTNAME',0);
commit;
exit;
EOF

fi;

/u01/product/oracle/11.2.0/db_1/bin/sqlplus /nolog <connect / as sysdba;
exec TOPUP.SP_TAB_PARTITIONS_MAINTAIN('$OWNER','$tablename');
exit;
EOF

done

sh /data/run/enable_trigger.sh

6. 操作系统排程

crontab -l
0 10 * * * sh /data/run/expdp_partition.sh 1>>/data/run/log/expdp_partition.log 2>>/data/run/log/expdp_partition.bad

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25198367/viewspace-740472/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25198367/viewspace-740472/