Oracle定期清理10日分区数据

10 02 10 * * /bin/sh /home/oracle/truncate_table_partion.sh &> /dev/null

[oracle@test1 ~]$ cat /home/oracle/truncate_table_partion.sh
#!/bin/bash
source /home/oracle/.bash_profile
SDATE=$(date -d "-3 month" +%Y%m)
SPNAME="P"${SDATE}
SPNAME11=${SPNAME}"11"
SPNAME21=${SPNAME}"21"
TDATE=$(date -d "-2 month" +%Y%m)
TPNAME="P"${TDATE}
TPNAME01=${TPNAME}"01"
exec >> /home/oracle/truncate_table_partion.log
sqlplus -s / as sysdba << EOF
set feedback off heading off term off
spool on /home/oracle/truncate_table_partion.txt
ALTER TABLE pro.p_trade TRUNCATE PARTITION $SPNAME11;
ALTER TABLE pro.p_trade TRUNCATE PARTITION $SPNAME21;
ALTER TABLE pro.p_trade TRUNCATE PARTITION $TPNAME01;
spool off
exit;
EOF
date
[oracle@test1 ~]$

[oracle@test1 ~]$ SDATE=$(date -d "-3 month" +%Y%m)
[oracle@test1 ~]$ SPNAME="P"${SDATE}
[oracle@test1 ~]$ SPNAME11=${SPNAME}"11"
[oracle@test1 ~]$ SPNAME21=${SPNAME}"21"
[oracle@test1 ~]$
[oracle@test1 ~]$ echo $SPNAME11
P20200811
[oracle@test1 ~]$ echo $SPNAME21
P20200821
[oracle@test1 ~]$
[oracle@test1 ~]$ TDATE=$(date -d "-2 month" +%Y%m)
[oracle@test1 ~]$ TPNAME="P"${TDATE}
[oracle@test1 ~]$ TPNAME01=${TPNAME}"01"
[oracle@test1 ~]$
[oracle@test1 ~]$ echo $TPNAME01
P20200901
[oracle@test1 ~]$