自动导出数据库分区表数据

来源：互联网发布：手机维修网络教学编辑：程序博客网时间：2024/05/23 15:49

2. 基本思路

2.1 通过定制任务调用LINUX脚本实现分区表的定时导出并记录导出状态
crontab -l
30 10 * * * sh /data/run/exp_partition.sh 1>>/data/run/log/exp_partition.log 2>>/data/run/log/exp_partition.bad

2.2 LINUX脚本实现分区表的定时导出并记录导出状态，每天定时导出5天前的分区，若成功完成，则记录为1，否则记录为0;脚本代码如下：

#!/bin/sh
cd /data/dump_dir
find /data/dump_dir/partition* -mtime +15 -exec rm -f {} \;
ORACLE_HOME=/u01/product/oracle/11.2.0/db_1;export ORACLE_HOME
ORACLE_SID=szxddb;export ORACLE_SID
FILENAME=`date +%Y%m%d%H`
PARTNAME=P`date -d "5 days ago" +%Y%m%d`
OWNER=TOPUP
RECORDTAB=TAB_PARTITION_MANTAIN_RECORD
TABNAME=`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

for tablename in $TABNAME
do

#echo $tablename
#done

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

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
/u01/product/oracle/11.2.0/db_1/bin/exp 'system/"system#test"' file=/data/dump_dir/$TAB_B.dmp log=/data/dump_dir/$TAB_B.log tables=$TAB_A

flag=successfully
sflag=`tail -1 /data/dump_dir/$TAB_B.log | awk '{print $3}'`
#createdate=`date +%Y%m%d`
createdate=`date -d "5 days ago" +%Y%m%d`

if [ "$sflag" = "$flag" ];then

/u01/product/oracle/11.2.0/db_1/bin/sqlplus /nolog <<eof
connect / as sysdba;
insert into $OWNER.$RECORDTAB values('$createdate','$tablename','$PARTNAME','1');
commit;
exit;
EOF

else

/u01/product/oracle/11.2.0/db_1/bin/sqlplus /nolog <<eof
connect / as sysdba;
insert into $OWNER.$RECORDTAB values('$createdate','$tablename','$PARTNAME',0);
commit;
exit;
EOF

fi;

done

2.3 导出状态记录到记录表

CREATE TABLE TAB_PARTITION_MANTAIN_RECORD
(
CREATEDATE     VARCHAR2(100 BYTE),
TABLENAME      VARCHAR2(100 CHAR),
PARTITIONNAME VARCHAR2(50 BYTE),
FLAG           VARCHAR2(1 CHAR)
)
TABLESPACE BASE_DATA
PCTUSED    0
PCTFREE    20
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          50M
            NEXT             100M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

0 0