Oracle 自动创建range分区表DDL的脚本

#!/bin/bash
#Print head
Head(){
echo $"create table $1 ( ... )"
echo $"partition by range ($2)"
echo $"("
}
#Print body
Body(){
Interval=$(echo $((30/$Pnumber)))
for y in $(seq $(echo $1|awk 'BEGIN{FS="-"}{if ($2!="") print $1,$2;else print $1,$1}'))
do
  for m in $(seq -w 12)
  do
    for p in $(seq $2)
    do
      if [ "$(echo $(($Interval*($p-1))))" -lt  9 ]
      then
        echo $"P_${y}_${m}_${p}">>ddl.swap1
        echo $"${y}-${m}-0$(echo $((1+$Interval*($p-1))))">>ddl.swap2
      else
        echo $"P_${y}_${m}_${p}">>ddl.swap1
        echo $"${y}-${m}-$(echo $((01+$Interval*($p-1))))">>ddl.swap2
      fi
    done
  done
done
sed -i '$d' ddl.swap1
sed -i '1d' ddl.swap2
paste ddl.swap1 ddl.swap2 >ddl.swap3
while read line
do
  Name=$(echo $line|awk '{print $1}')
  Time=$(echo $line|awk '{print $2}')
  echo $"  partition ${Name} values less than (TO_DATE(' ${Time} 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace $3,"
done<ddl.swap3
rm -f ddl.swap1 ddl.swap2 ddl.swap3
}
#Print max_partition
Max(){
MAXP="P_$(echo $1|awk 'BEGIN{FS="-"}{if ($2!="") print $2;else print $1}')_MAX"
echo $"  partition $MAXP  values less than (MAXVALUE) tablespace $3"
echo $");"
}

#main
if [ "$#" -ne 10 ]
then
    echo $"Usage: bash $(basename $0) -t Table_Nmae -c Column_Name -r Year_Range -n Month_partition_number -s Tablespace_Name"
    echo $"Example: bash $(basename $0) -t scott.emp -c hiredate -r 2016-2018 -n 2 -s users"
    echo $"Example: bash $(basename $0) -t scott.emp -c hiredate -r 2016      -n 2 -s users"
    exit 1
fi
while [ "$#" -gt 0 ]
do
    case "$1" in
            -t)
                shift
                typeset -u Tname="$1"
                shift
                ;;
            -c)
                shift
                typeset -u Cname="$1"
                shift
                ;;
            -r)
                shift
                Yrange="$1"
                shift
                ;;
            -n)
                shift
                Pnumber="$1"
                shift
                ;;
            -s)
                shift
                typeset -u Sname="$1"
                shift
                ;;
    esac
done

Head $Tname  $Cname
Body $Yrange $Pnumber $Sname
Max  $Yrange $Pnumber $Sname

将以上内容贴入脚本文件，执行结果如下：

需要传参 分区表表名、分区字段、分区年份、月分区数量和分区所在的表空间名：

[TOC]