#!/bin/bash
###############################################################################################################
# Script name: truncate_data_365.sh
# Script description: truncation partition table remain 365 day.
# Current Release Version: 1.0.0
# Script Owner: hbhe0316
# Latest editor: hbhe0316
# Support platform:  Linux OS for Linux.
# Change log: None
# Description:Date 2022/3/2
#
#
###############################################################################################################

source /home/oracle/.bash_profile

out_log=/monitor/scripts/log/show_opration_record_365.out
scr_file=/monitor/scripts/truncate_data_365.sql
DATE_N=`date "+%Y-%m-%d-%H-%M"`
EXP_FILE=/monitor/scripts/expdp.sh
EXP_LOG=/monitor/scripts/log/expdp_${DATE_N}.log
echo > $EXP_FILE
echo > $scr_file
echo > $out_log


sqlplus -s system/"wwwwww"@IP:1521/ORCL >>$scr_file << EOF |sed 's/^[ /t]*//g'
set head off
set feed off
set lin 100 pages 2000
SELECT 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' DROP PARTITION '||PARTITION_NAME|| ' UPDATE GLOBAL INDEXES'||';' FROM   /
(SELECT DD.TABLE_OWNER,DD.TABLE_NAME,DD.PARTITION_NAME, /
       TO_DATE(SUBSTR(SYSTEM.LONG_TO_VARCHAR(DD.TABLE_OWNER,DD.TABLE_NAME,DD.PARTITION_NAME),11,11),'YYYY-MM-DD') HIGH_VALUE /
  FROM ALL_TAB_PARTITIONS DD WHERE (DD.TABLE_OWNER = 'test' AND DD.TABLE_NAME = 'T1') OR (DD.TABLE_OWNER = 'TEST' AND DD.TABLE_NAME IN ('T2','T3','T4')) )  WHERE HIGH_VALUE < SYSDATE - 366 /
  ORDER BY 1 DESC;
exit;
EOF


if [ -f $scr_file ];then
    cat $scr_file |  grep -v "^[[:space:]]*#" | sed 's/[[:space:]][[:space:]]*/    /g' | grep -v "^[[:space:]]*$" | while read line
    do
      table_name=`echo $line | grep -v "^[[:space:]]*#" | sed 's/[[:space:]][[:space:]]*/    /g' | grep -v "^[[:space:]]*$" | awk -F ' ' '{print $3}'`
      table_part=`echo $line | grep -v "^[[:space:]]*#" | sed 's/[[:space:]][[:space:]]*/    /g' | grep -v "^[[:space:]]*$" | awk -F ' ' '{print $6}'`
      echo "expdp system/"wwwwww"@IP:1521/ORCL dumpfile=`hostname`_${table_name}_${table_part}_${DATE_N}.dmp logfile=`hostname`_${table_name}_${table_part}_${DATE_N}.log directory=ORADATA  tables=${table_name}:${table_part}" >> ${EXP_FILE}
    done

fi
chmod +x ${EXP_FILE}
echo "start time is $DATE_N" >> $EXP_LOG
sh ${EXP_FILE} >> $EXP_LOG 2>&1
echo "stop time is $DATE_N" >> $EXP_LOG

cat $EXP_LOG | grep "ORA-" >> /dev/null
if [[ $? -eq 0 ]];then
    echo "expdp failed,shell will exit"
    exit 1
fi

df -h | grep -i /mnt/backup >> /dev/null
if [[ $? -ne 0 ]];then
    echo "/mnt/backup is not exist,shell will exit."
    exit 1
else
    mv /oradata/backup/`hostname`_*_${DATE_N}.dmp /mnt/backup/DB/ORACLE/MIDNPPDB
    mv /oradata/backup/`hostname`_*_${DATE_N}.log /mnt/backup/DB/ORACLE/MIDNPPDB
fi


sqlplus -s system/"wwwwww"@IP:1521/ORCL >>$out_log << EOF
set timing on
@$scr_file
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') cur_date from dual;
exit;
EOF

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle