shell脚本实现为分区表自动添加分区

ANONYMOUSUSER表是季度分区表，该脚本的作用是：提前一个月自动为ANONYMOUSUSER添加新的季度分区。

-------------------------------------------------
#!/bin/bash
#3月、6月、9月、12月的1号执行该脚本,为hou.ANONYMOUSUSER添加新分区
#变量quarter:分区名、表空间、数据文件的名字一样
#变量range：生成的下一个分区的范围值，也就是下个分区小于的最大日期。


#获得当前月份
month=`date +%m`

#12月份，得出下一年第1季度的变量
if [ $month = 12 ];then
   quarter=ANONY_ALL_`date +%Y --date="+1 year"`Q1
   range=`date +%Y --date="+1 year"`-04-01

#3月份，得出当年第2季度的变量
elif [ $month = 03 ];then
   quarter=ANONY_ALL_`date +%Y`Q2
   range=`date +%Y`-07-01

#6月份，得出当年第3季度的变量
elif [ $month = 06 ];then
   quarter=ANONY_ALL_`date +%Y`Q3
   range=`date +%Y`-10-01

#9月份，得出当年第4季度的变量（quarter的时间依然当年，但range是下一个年份的）
elif [ $month = 09  ];then
   quarter=ANONY_ALL_`date +%Y`Q4
   range=`date +%Y --date="+1 year"`-01-01
fi


#日志记录在/home/oracle/shell/add_partition/add_partition_log.txt中
echo  "      ">>/home/oracle/shell/add_partition/add_partition_log.txt
echo  "      ">>/home/oracle/shell/add_partition/add_partition_log.txt
echo  "      ">>/home/oracle/shell/add_partition/add_partition_log.txt
echo  "      ">>/home/oracle/shell/add_partition/add_partition_log.txt
echo  "      ">>/home/oracle/shell/add_partition/add_partition_log.txt
echo ========================`date`================================ >>/home/oracle/shell/add_partition/add_partition_log.txt
echo  "      ">>/home/oracle/shell/add_partition/add_partition_log.txt
echo  "      ">>/home/oracle/shell/add_partition/add_partition_log.txt
echo  新增分区名称:$quarter >>/home/oracle/shell/add_partition/add_partition_log.txt
echo  分区范围（最大值）:小于$range >>/home/oracle/shell/add_partition/add_partition_log.txt


#创建tablespace,添加分区
source /home/oracle/.bash_profile
sqlplus / as sysdba <>/home/oracle/shell/add_partition/add_partition_log.txt
create tablespace $quarter datafile '/u01/db_file/hou/$quarter.dbf' size 20M autoextend on next 10M maxsize 32767M;
alter user hou quota unlimited on $quarter;
alter table hou.ANONYMOUSUSER add partition $quarter values less than(to_date('$range','YYYY-MM-DD')) tablespace $quarter update indexes;
exit
EOF

#发送邮件,同时附带检测分区是否成功的SQL语句。
echo "hi DBA,
       hou.ANONYMOUSUSER has add partition! Please read the log. Path is "/home/oracle/shell/add_partition"     
      select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,TABLESPACE_NAME from user_tab_partitions where table_name='ANONYMOUSUSER' order by table_name,partition_name ; "
|mutt -s "  hou.ANONYMOUSUSER has add partition." -a /home/oracle/shell/add_partition/add_partition_log.txt [e-mail]

-------------------------------------


最后加入crontab

00 09 1 3,6,9,12 * /home/oracle/shell/add_partition/add_partition.sh