oracle分区表脚本,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 hou@xxxx.com

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

最后加入crontab

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

阅读(4042) | 评论(0) | 转发(0) |