#!/bin/ksh
 
OPATH=$PATH
type getconf >/dev/null 2>&1 && PATH=$(PATH=/bin:/usr/bin \getconf PATH)
PATH=/bin:/usr/bin
[[ -n "${BASH_VERSION}" ]] && shopt -s extglob

function my_getopts {
  version="3.141592p, 2002-02-02, Michael Wang <xw73@columbia.edu>."
  typeset SEP= i _I _J
  for i; do
    _I=$(echo $i | tr "[a-z]" "[A-Z]")
    _J=${_I%%=*} _J=${_J%:}
    eval ${_J}= unset ${_J}
    case $_I in
      *[!:]=*) eval $(IFS=$SEP; echo ${_I%%=*}=\"${_I#*=}\")   ;;
      *:=*)    eval $(IFS=$SEP; echo ${_I%%:=*}=\"${i#*:=}\")  ;;
    esac
  done
}

function shdoc {
  typeset version="3.0, 2006-09-06, Michael Wang <xw73@columbia.edu>."

  PATH=$OPATH

  typeset help doc file

  typeset    tmpdir=/tmp/shdoc_$$_$RANDOM
  typeset    base tmpf i flag=N

  eval help=y doc=pod "$@"

  set -- $(echo $help $doc | tr '[a-z]' '[A-Z]')
  help=$1 doc=$2

  base=${file##*/}
  base=${base%.*sh}

  case ${doc} in
    (POD) mkdir -p ${tmpdir}          && tmpf=${tmpdir}/${base}.pod        ;;  
    (MAN) mkdir -p ${tmpdir}/man/man1 && tmpf=${tmpdir}/man/man1/${base}.1 ;;
  esac

  while IFS= read -r i; do
    [[ $i = "## ${doc}_START"* ]] && { flag=Y; continue; }
    [[ $i = "## ${doc}_STOP"*  ]] && { flag=N; continue; }

    if [[ $flag = "Y" ]]; then
      i=${i#"##"}
      i=${i#" "}
      printf "%s\n" "${i}"
    fi
  done < ${file} > ${tmpf}

  case $help in
    (Y)      case ${doc} in
               (POD) perldoc ${tmpf}                   ;;
               (MAN) MANPATH=${tmpdir}/man man ${base} ;;
             esac
             ;;
    (PS|PDF) case ${doc} in
               (POD) pod2man ${tmpf} ;;
               (MAN) cat     ${tmpf} ;;
             esac |
               groff -man |
                 case ${help} in
                   (PS)  cat     >${base}.ps  ;;
                   (PDF) ps2pdf - ${base}.pdf ;;
                 esac
             ;;
    (POD)    cp ${tmpf} ${base}.pod
             ;;
    (HTML)   pod2html ${tmpf} > ${base}.html
             rm pod2htmd.tmp pod2htmi.tmp
             ;;
  esac

  rm -rf ${tmpdir}
  return 0
}

function end_month {
  typeset ym=$1 y m ld
  ((  y = ym  / 100 ))
  ((  m = ym  % 100 ))
  for ld in $(cal $m $y); do :; done
  printf "%s\n" $(( ym*100 + ld ))
}

function pn_month {
  typeset ym=$1 pn=$2 x n
  (( x = ym % 100 + pn ))

  if (( x > 0 ))
  then (( n = (x-1) / 12 ))
  else (( n = - (12-x) / 12 ))
  fi
  
  printf "%s\n" $(( ym + pn + 88*n ))
}

function pn_day_nr {
  typeset ymd=$1 pn=${2:-0}

  function pn_day1 {
    typeset ymd=$1 pn=$2 d ym x
    ((  d = ymd % 100 ))
    (( ym = ymd / 100 ))
    if (( pn == -1 )); then
      if (( d > 1 )); then
        (( x = ymd - 1 ))
        (( x > 17520902 && x < 17520914 )) && (( x = 17520902 ))
      else
        x=$(end_month $(pn_month $ym -1))
      fi
    elif (( pn == +1 )); then
      if (( d < 28 )) || (( ymd < $(end_month $ym) )); then
        (( x = ymd + 1 ))
        (( x > 17520902 && x < 17520914 )) && (( x = 17520914 ))
      else
        x=$(( 100*$(pn_month $ym +1) + 1 ))
      fi
    fi

    printf "%s\n" $x
    return 0
  }

  while (( pn != 0 )); do
    if (( pn < 0 ))
    then ymd=$(pn_day1 $ymd -1); (( pn = pn + 1 ))
    else ymd=$(pn_day1 $ymd +1); (( pn = pn - 1 ))
    fi
  done

  printf "%s\n" $ymd
  return 0
}

function next_year {
  printf "%s\n" $(( $1 + 1 ))
}

function prev_year {
  printf "%s\n" $(( $1 -1 ))
}

function next_month {
  printf "%s\n" $(pn_month $1 +1)
}

function prev_month {
  printf "%s\n" $(pn_month $1 -1)
}

function next_day {
  printf "%s\n" $(pn_day_nr $1 +1)
}

function next_x {
  typeset ymd=$1 n=$2 i=1
  
  while (( i <= n )); do
    ymd=$(next_day $ymd)
    (( i = i + 1 ))
  done

  printf "%s\n" $ymd
}

function next_week {
  next_x $1 7
}

function set_ora_env {
  export ORACLE_SID=$1

  typeset i line

  for i in /var/opt/oracle/oratab /etc/oratab; do
    [[ -r $i ]] && break
  done

  while IFS= read -r line; do
    [[ $line = $ORACLE_SID:* ]] && {
      ORACLE_HOME=${line#$ORACLE_SID:}
      export ORACLE_HOME=${ORACLE_HOME%:*}
    }
  done < $i

  [[ -z $ORACLE_HOME ]] && {
    echo "Can not find ORACLE_HOME."
    return 1
  }

  [[ :$PATH: = *:$ORACLE_HOME/bin/sqlplus:* ]] || PATH=$ORACLE_HOME/bin:$PATH

  typeset uname_s=$(uname -s)

  if [[ $uname_s = CYGWIN* ]]; then
    ORACLE_HOME=$(cygpath -w $ORACLE_HOME)
    TNS_ADMIN=$(cygpath -w $TNS_ADMIN)
  fi

  echo "The environment variables are now set to"
  echo "           ==>> $1 <<==             "
}

function init_param {
  typeset _a="$1$2$3" _p _q
  _p="${_a%%=*}"
  eval "$_p=; unset $_p"
  [[ "$_a" = "$_p" ]] || eval $_p=\"${_a#*=}\"
}

function padding {
  typeset text n c pos
  eval "$@"
  while (( ${#text} < n )); do
    if [[ $pos = "append" ]]
    then text="$text$c"
    else text="$c$text"
    fi
  done
  printf "%s\n" $text
}

init_param SID
init_param OWNER
init_param TABLE
init_param NEXT
init_param KEEP
init_param ARCHIVE
init_param MERGE
init_param HELP
init_param SHOW
init_param DEBUG
init_param my_file
init_param my_fileno
init_param my_part   # partition array
init_param my_patt   # partition pattern
init_param my_vcur   # partition value current
init_param my_vmax   # partition value maximum
init_param my_icur   # partition index current
init_param my_imax   # partition index maximum
init_param my_rows
init_param my_self
init_param my_tmpdir
init_param my_method
init_param my_version

my_getopts keep=999999 next=-999999 "$@"
: ${USERID:=/ as sysdba}

[[ $HELP = @(Y|PDF|POD|HTML) ]] && { shdoc file=$0 help=$HELP; exit 0; }

init_param i

i=${KEEP%?}
i=${KEEP#$i}
my_method=D

[[ $i = [0-9] ]] || {
  KEEP=${KEEP%?}
  my_method=$(echo $i | tr "a-z" "A-Z")
}

my_self=${0##*/}
my_tmpdir=/tmp/$my_self.$$
mkdir $my_tmpdir

if [[ $DEBUG = Y ]]; then
  set -x
else
  trap "rm -rf $my_tmpdir; exit 1" 0 1
fi

[[ $TABLE = *.* ]] && {
  OWNER=${TABLE%.*} TABLE=${TABLE#*.}
}

[[ -z $OWNER || -z $TABLE ]] && {
  echo "ERR: You must specify owner and table."
  exit 1
}

[[ -z "$SID" ]] && {
  echo "ERR: you must specify ORACLE_SID."
  exit 1
}

set_ora_env $SID || exit 1

my_fileno=0

(( my_fileno = my_fileno + 1 ))
my_file=$(padding text=$my_fileno n=2 c=0)_part_list

{ echo "set echo on"
  echo "whenever sqlerror exit 1"

  echo "SELECT 'partition='||partition_name"
  echo "FROM dba_tab_partitions"
  echo "WHERE table_owner='$OWNER'"
  echo "AND table_name='$TABLE'"
  echo "ORDER BY 1;"

  echo "SELECT 'version=' || VERSION from v\$instance;"

  echo "exit"
} > $my_tmpdir/$my_file.sql

(
cd $my_tmpdir &&
sqlplus /nolog << EOF | tee $my_file.log
connect $USERID
@$my_file.sql
EOF
)

init_param line
init_param p
init_param v_pat
init_param v_now
init_param Y
init_param M
init_param D
init_param ix = 0
init_param iy = 0

if [[ $TODAY = [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] ]]; then
  Y=${TODAY%????}
  M=${TODAY#$Y}
  M=${M%??}
  D=${TODAY#$Y$M}
else
  set -- $(date +"%Y %m %d")
  Y=$1 M=$2 D=$3
fi

while read line; do
  [[ $line = version=* ]] && {
    my_version=${line#version=}
    my_version=${my_version%%.*}
    continue
  }

  [[ $line = partition=*  ]] || continue

  p=${line#partition=}

  [[ $line = partition=A* ]] && {
    (( iy = iy + 1 ))
    my_apart[iy]=$p
    continue
  }

  [[ -z "$my_patt" ]] && {
    case $p in
      (P[0-9][0-9][0-9][0-9])                     my_patt=P4     v_now=$Y       ;;
      (P[0-9][0-9][0-9][0-9]00)                   my_patt=P400   v_now=${Y}00   ;;
      (P[0-9][0-9][0-9][0-9]0000)                 my_patt=P40000 v_now=${Y}0000 ;;
      (P[0-9][0-9][0-9][0-9][0-9][0-9])           my_patt=P6     v_now=$Y$M     ;;
      (P[0-9][0-9][0-9][0-9][0-9][0-9]00)         my_patt=P600   v_now=$Y${M}00 ;;
      (P[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]) my_patt=P8     v_now=$Y$M$D   ;;
      (PW*)                                       my_patt=PW     v_now=$Y$M$D   ;;
      (Y*)                                        my_patt=Y      v_now=$Y       ;;
      (M*)                                        my_patt=M      v_now=$Y$M     ;;
      (D*)                                        my_patt=D      v_now=$Y$M$D   ;;
      (W*)                                        my_patt=W      v_now=$Y$M$D   ;;
    esac
  }

  [[ -z "$my_patt" ]] || {
    (( ix = ix + 1 ))
    my_part[ix]=$p
    v_pat=${p##+([A-Z])}
    (( my_vmax = v_pat ))
    (( my_imax = ix    ))

    [[ -z "$my_icur" ]] || (( v_pat <= v_now )) && {
      (( my_vcur = v_pat ))
      (( my_icur = ix    ))
    }
  }
done < $my_tmpdir/$my_file.log

: ${FUNCTIONAL_BITMAP_INDEX:=N}

[[ $FUNCTIONAL_BITMAP_INDEX = Y ]] && { # see caveats section of man page.
  (( my_fileno = my_fileno + 1 ))
  my_file=$(padding text=$my_fileno n=2 c=0)_rows

  { echo "set echo on"
  
    echo "whenever sqlerror exit 1"
    echo "select 'my_rows='||TO_CHAR(COUNT(1))"
    echo "FROM $OWNER.$TABLE PARTITION (${my_part[my_imax]})"
    echo "/"
  
    echo "exit"
  } > $my_tmpdir/$my_file.sql

  (
  cd $my_tmpdir &&
  sqlplus /nolog << EOF | tee $my_file.log
  connect $USERID
  @$my_file.sql
EOF
  )
  
  while read i; do
    [[ $i = my_rows=* ]] || continue
    eval $i
  done < $my_tmpdir/$my_file.log
  
  (( my_rows == 0 )) || {
    echo "ERROR: Last partition is not empty ($my_rows rows)."
    exit 1
  }
}

init_param icur
init_param inxt
init_param vcur
init_param vnxt

(( my_fileno = my_fileno + 1 ))
my_file=$(padding text=$my_fileno n=2 c=0)_split

{ echo "set echo on"
  echo "whenever sqlerror exit 1"
  echo

  (( icur = 1 ))
  while (( icur <= my_icur - KEEP - 1 )); do
    echo "ALTER TABLE $OWNER.$TABLE"
    if [[ $my_method = T ]]; then
      echo "TRUNCATE PARTITION ${my_part[icur]}"
      (( my_version >= 9 )) && echo "UPDATE GLOBAL INDEXES"
      printf ";\n\n"

      echo "BEGIN"
      echo "  DBMS_STATS.GATHER_TABLE_STATS ("
      echo "    OWNNAME          => '$OWNER',"
      echo "    TABNAME          => '$TABLE',"
      echo "    PARTNAME         => '${my_part[icur]}',"
      echo "    GRANULARITY      => 'PARTITION',"
      echo "    CASCADE          => TRUE,"
      echo "    ESTIMATE_PERCENT => 20"
      echo "  );"
      echo "END;"
      printf "/\n\n"
    else
      echo "DROP     PARTITION ${my_part[icur]}"
      (( my_version >= 9 )) && echo "UPDATE GLOBAL INDEXES"
      printf ";\n\n"
    fi
    (( icur = icur + 1 ))
  done

  vcur=${my_vmax}
  [[ $my_patt = @(P40000|P400|P600) ]] && vcur=${vcur%%+(00)}

  (( icur = my_imax ))
  (( inxt = 0 ))
  while (( icur <= my_icur + NEXT - 1 )); do
    (( inxt = icur + 1 ))

    case $my_patt in
      (P4|P400|P40000) vnxt=$(next_year  $vcur); my_part[inxt]=P${vnxt}${my_patt#P4} ;;
      (P6|P600)        vnxt=$(next_month $vcur); my_part[inxt]=P${vnxt}${my_patt#P6} ;;
      (P8)             vnxt=$(next_day   $vcur); my_part[inxt]=P${vnxt}${my_patt#P8} ;;
      (Y)              vnxt=$(next_year  $vcur); my_part[inxt]=${my_patt}$vnxt       ;;
      (M)              vnxt=$(next_month $vcur); my_part[inxt]=${my_patt}$vnxt       ;;
      (D)              vnxt=$(next_day   $vcur); my_part[inxt]=${my_patt}$vnxt       ;;
      (PW|W)           vnxt=$(next_week  $vcur); my_part[inxt]=${my_patt}$vnxt       ;;
    esac

    echo "ALTER TABLE $OWNER.$TABLE"
    echo "SPLIT PARTITION ${my_part[icur]}"

    case $my_patt in
      (P4|P6|P8|PW) echo "AT (${vnxt})"                          ;;
      (P40000)      echo "AT (${vnxt}0101)"                      ;;
      (P400|P600)   echo "AT (${vnxt}01)"                        ;;
      (Y)           echo "AT (to_date(${vnxt}0101, 'YYYYMMDD'))" ;;
      (M)           echo "AT (to_date(${vnxt}01,   'YYYYMMDD'))" ;;
      (D|W)         echo "AT (to_date(${vnxt},     'YYYYMMDD'))" ;;
    esac

    echo "INTO (PARTITION ${my_part[icur]}, PARTITION ${my_part[inxt]})"
    (( my_version >= 9 )) && echo "UPDATE GLOBAL INDEXES"
    printf ";\n\n"

    echo "ALTER TABLE $OWNER.$TABLE MODIFY PARTITION ${my_part[icur]}"
    echo "REBUILD UNUSABLE LOCAL INDEXES;"
    printf "\n"

    echo "BEGIN"
    echo "  DBMS_STATS.GATHER_TABLE_STATS ("
    echo "    OWNNAME          => '$OWNER',"
    echo "    TABNAME          => '$TABLE',"
    echo "    PARTNAME         => '${my_part[icur]}',"
    echo "    GRANULARITY      => 'PARTITION',"
    echo "    CASCADE          => TRUE,"
    echo "    ESTIMATE_PERCENT => 20"
    echo "  );"
    echo "END;"
    printf "/\n\n"

    (( vcur = vnxt ))
    (( icur = icur + 1 ))
  done

  (( inxt > 0 )) && {
    echo "ALTER TABLE $OWNER.$TABLE MODIFY PARTITION ${my_part[inxt]}"
    echo "REBUILD UNUSABLE LOCAL INDEXES;"
    printf "\n"

    echo "BEGIN"
    echo "  DBMS_STATS.GATHER_TABLE_STATS ("
    echo "    OWNNAME          => '$OWNER',"
    echo "    TABNAME          => '$TABLE',"
    echo "    PARTNAME         => '${my_part[inxt]}',"
    echo "    GRANULARITY      => 'PARTITION',"
    echo "    CASCADE          => TRUE,"
    echo "    ESTIMATE_PERCENT => 20"
    echo "  );"
    echo "END;"
    printf "/\n\n"
  }

  [[ $ARCHIVE = +([0-9]) ]] && {
    (( icur = 1 ))
    while (( icur <= ${#my_apart[@]} - ARCHIVE )); do
      echo "ALTER TABLE $OWNER.$TABLE"
      echo "DROP PARTITION ${my_apart[icur]}"
      (( my_version >= 9 )) && echo "UPDATE GLOBAL INDEXES"
      printf ";\n"
      (( icur = icur + 1 ))
    done
  }

  [[ $MERGE = Y ]] && {
    init_param jp
    init_param jd
    init_param prev_part
    init_param i
    init_param prev

    (( jd = my_vcur / 100 ))

    case $my_patt in
      (P6|P600) jp=$(prev_year  $jd); prev_part=P$jp ;;
      (P8)      jp=$(prev_month $jd); prev_part=P$jp ;;
      (M)       jp=$(prev_year  $jd); prev_part=M$jp ;;
      (D)       jp=$(prev_month $jd); prev_part=D$jp ;;
      (*)       printf "%s\n" "-- Merge is not supported for $my_patt." ;;
    esac
 
    for i in "${my_part[@]}"; do
      [[ $i = ${prev_part}+([0-9]) ]] && {
        if [[ -z "$prev" ]]; then
          prev="$i"
        else
          echo "ALTER TABLE $OWNER.$TABLE MERGE PARTITIONS"
          echo "$prev, $i"
          echo "INTO PARTITION"
          echo "${i}a"
          (( my_version >= 9 )) && echo "UPDATE GLOBAL INDEXES"
          printf ";\n\n"
          prev="${i}a"
        fi
      }
    done

    [[ -n $prev ]] && {
      arch_part=$(echo ${prev_part} | sed "s:^[MDP]:A:")
  
      echo "ALTER TABLE $OWNER.$TABLE RENAME PARTITION ${prev}"
      echo "to ${arch_part};"
      printf "\n"
  
      echo "ALTER TABLE $OWNER.$TABLE MODIFY PARTITION ${arch_part}"
      echo "REBUILD UNUSABLE LOCAL INDEXES;"
      printf "\n"
  
      echo "BEGIN"
      echo "  DBMS_STATS.GATHER_TABLE_STATS ("
      echo "    OWNNAME          => '$OWNER',"
      echo "    TABNAME          => '$TABLE',"
      echo "    PARTNAME         => '${arch_part}',"
      echo "    GRANULARITY      => 'PARTITION',"
      echo "    CASCADE          => TRUE,"
      echo "    ESTIMATE_PERCENT => 20"
      echo "  );"
      echo "END;"
      printf "/\n\n"
    }
  }

  (( my_version >= 9 )) || {
    echo "connect $USERID"

    echo "DECLARE"
    echo "  cursor c is SELECT index_name"
    echo "  FROM dba_indexes"
    echo "  WHERE table_name = '$TABLE'"
    echo "  AND owner = '$OWNER'"
    echo "  AND partitioned = 'NO'"
    echo "  AND status != 'VALID'"
    echo "  ;"
    echo "BEGIN"
    echo "  FOR cr in c LOOP"
    echo "    execute immediate 'ALTER INDEX $OWNER.' ||"
    echo "    cr.index_name                           ||"
    echo "    ' REBUILD COMPUTE STATISTICS';"
    echo "  END LOOP;"
    echo "END;"
    echo "/"
  }

  echo "exit"

} > $my_tmpdir/$my_file.sql

[[ $SHOW = Y ]] && { cat $my_tmpdir/$my_file.sql; exit 0; }

(
cd $my_tmpdir &&
sqlplus /nolog << EOF
connect $USERID
@$my_file.sql
EOF
)

( cd / && rm -rf $my_tmpdir )
trap - 0
exit 0

## POD_START
##
## =head1 NAME
##
## B<PMAN> - partition manager for Oracle range partitions on date column.
##
## =head1 SYNOPSIS
##
## B<pman>
##   sid:=I<sid_name>
##   table=I<owner_name>.I<table_name>
##   [next=<number>]
##   [keep=<number>[T]]
##   [merge=y|n]
##   [archive=<number>]
##   [functional_bitmap_index=y|n>
##   [show=y]
##   [help=y]
##   [debug=y]
##   [today=YYYYMMDD]
##
## =head1 DESCRIPTION
## 
## Oracle table partitioning divides a usually a large table into smaller
## more manageable units, the partitions.  It is a simple
## "divide and conquer" concept, but offers tremendous benefits. 
##
## Partitioning greatly increased the database management capability.  Each
## partition can be individually managed, it can be dropped, truncated,
## moved, split, merged, and exchanged with a table.
## 
## While each partition can be individual queried, you do not have to
## change your SQL to take advantage of the partitioning.  The "partition
## pruning" is done transparently via Oracle optimizer. The performance
## increase is similar to that you can find your December bill easily in the
## folder of that month, instead of going through a pile of paper of the
## past 5 years' bills mixed together.
## 
## Partitioning, together with locally managed, uniform sized extents,
## permanently solved the problem of data fragmentation due to
## the data constantly move in and out of the table. The partitions dropped
## or truncated release the entire space occupied to the residing
## tablespace.  Those who have spent endless hours during weekends
## performing object reorganization would appreciate the beauty of the this
## feature. (However the consultants who bill those hours may not).
## 
## Simply speaking, there are 4 types of partitions:
## 
##   - range partition for continuous values like date and number.
##   - list partition for discrete values like US fifty states.
##   - hash partition for random values like license plates.
##   - composite partition is partition within partition.
## 
## This article presents PMAN utility, which handles the range partition
## based on date column, which is the most common type of partitioning.
## 
## The date can be either presented by Oracle internal date type or
## timestamp type, or by number type, for example, 20070130 for January 30,
## 2007, or character type such as '20070130'.
## 
## =head2 WHAT PMAN DOES 
## 
## PMAN manages the partitions through out the life cycle.
## 
## PMAN helps you to create partitions when you want to convert
## a non-partitioned table with many years data to partitioned table.
## 
## PMAN helps you to manage the partitions once you have a partitioned
## table. It creates new partitions, drops old partitions per your
## specification. It updates statistics, and validate indexes. 
## 
## PMAN can also help you to merge daily partitions to monthly partitions,
## and monthly partitions to yearly partitions if you like, and drop
## the merged (archived) partitions when you do not need them.
## 
## =head2 HOW PMAN DOES IT
## 
## PMAN needs to know how the table it operates on is partitioned.
## It could query the dictionary and gets an idea. But it is easier
## and more reliable just to look at the label on the partition.
## 
## Each partition needs to have a name to identify the partition. 
## Oracle does not care how you name it as long as you name it.
## The users either don't know what a table partition is,
## or is agreeable with whatever the name you use as long as her
## queries continue to run.
## 
## So here is PMAN's naming standard:
## 
##   partition_name  partitioned by
##   ==============  =============================================================
##   Y2004           YEAR  with DATE/TIMESTAMP type
##   M200407         MONTH with DATE/TIMESTAMP type
##   D20040704       DAY   with DATE/TIMESTAMP type
##   W20040705       WEEK  with DATE/TIMESTAMP type
##   P2004           YEAR  with NUMBER/CHAR type with date represented as YYYY
##   P200400         YEAR  with NUMBER/CHAR type with date represented as YYYYMM
##   P20040000       YEAR  with NUMBER/CHAR type with date represented as YYYYMMDD
##   P200407         MONTH with NUMBER/CHAR type with date represented as YYYYMM
##   P20040700       MONTH with NUMBER/CHAR type with date represented as YYYYMMDD
##   P20040704       DAY   with NUMBER/CHAR type with date represented as YYYYMMDD
##   PW20040705      WEEK  with NUMBER/CHAR type with date represented as YYYYMMDD
##   ==============  =============================================================
## 
## Standard is good as long as it is easy to follow, and it does not limit
## the application in any way.  With this naming standard, it is quite easy
## to find how the table is partitioned, and how to add new partitions,
## drop or merge old partitions, etc.
## 
## =head2 PMAN FEATURES
## 
## We would like to use an example go through the life cycle of
## partition management, and demonstrate the PMAN usage, features, and
## design decisions along the way.
## 
## =head3 01. CREATE INITIAL PARTITIONS
## 
## To start a new partitioned table, just create the table with the
## earliest partition manually, like this:
## 
##   SQL> CREATE TABLE trade.settlement (
##     2    settle_id   NUMBER,
##     3    settle_date DATE
##     4  )
##     5  PARTITION BY RANGE (settle_date)
##     6  (PARTITION M200509 VALUES LESS THAN (MAXVALUE));
##   
##   Table created.
##   
##   SQL> ALTER TABLE TRADE.SETTLEMENT
##     2> ADD CONSTRAINT pk_settlement PRIMARY KEY (settle_id);
##   
##   Table altered.
## 
## And let the PMAN takes care of the rest.
## 
## Please note:
## 
## - We create the last partition to be able to hold any future rows without
## upper bound. This is conscious decision for added reliability. In case
## that you forget to create future partitions, all rows will go to the last
## partition. This is not a ideal situation, but may be better than failure
## in most cases.
## 
## Then we can use PMAN to create the rest of the partitions.
## For example, the following command will create two more partitions.
## 
##   $ pman sid:=$ORACLE_SID table=trade.settlement next=2 show=n
## 
## The result is:
## 
##   SQL> ALTER TABLE TRADE.SETTLEMENT
##     2  SPLIT PARTITION M200509
##     3  AT (to_date(20051001,      'YYYYMMDD'))
##     4  INTO (PARTITION M200509, PARTITION M200510)
##     5  UPDATE GLOBAL INDEXES
##     6  ;
## 
##   Table altered.
## 
##   SQL> ALTER TABLE TRADE.SETTLEMENT
##     2  SPLIT PARTITION M200510
##     3  AT (to_date(20051101,      'YYYYMMDD'))
##     4  INTO (PARTITION M200510, PARTITION M200511)
##     5  UPDATE GLOBAL INDEXES
##     6  ;
## 
##   Table altered.
## 
## Please note:
## 
## - RMAN uses "SPLIT PARTITION" statement to split two partitions
## as specified. 
## 
## - The partition column is only needed one time when you create the partitioned
## table. It is not needed for subsequent management. In other words, the
## partition column name is not programing parameter and need not
## to be maintained outside the database.
## 
## - show=y option will produce the SQL statements without any changes
## on the database. This is useful if you want to test PMAN, or use PMAN
## as a SQL generator. It is tedious and error prone to manually create the SQL
## code to create many partitions, say, monthly partitions for past 7 years.
## You can leave the job to PMAN.
## 
## - The next=<n> options create next <n> partitions 
## relative to CURRENT partitions,
## which is the partition a row with current date would go.
## 
## We start with one partition M200509 which is the current partition
## for any dates. "next=2" option creates 2 more partitions on top of M200509
## partition, ending up with 3 partitions M200509, M200510, M200511.
## 
## =head3 02. MAINTAIN FUTURE PARTITIONS
## 
## Now suppose today is 10/15/2005, the current partition is
## M200510. If you want to precreate 3 future partitions, you
## should use option "next=3". The PMAN command is:
## 
##   $ pman sid:=$ORACLE_SID table=trade.settlement next=3 show=n today=20051015
## 
## Since there is already one future partitions M200511, PMAN
## will create just two more partitions. In other words, PMAN
## will create none, or as many as partitions necessary to
## maintain the number of future partitions as required.
## However, PMAN will not drop future partitions if the future
## partitions are more than what you need.
## 
## Suppose today is indeed October 15, 2005, or any day in October 2005
## for this example, you do not need to use <today=YYYYMMDD> option. This
## option is needed only when you pretend today is a different date.
## 
## =head3 03. MAINTAIN PAST PARTITIONS
## 
## While next=<n> option is the number of partitions you would like
## to maintain after the current partition, the keep=<n> is the number
## of partitions you would like to maintain before the current partition.
## 
## The PMAN command to drop all old partitions except the most recent
## 3 partitions are:
## 
##   $ pman sid:=$ORACLE_SID table=trade.settlement keep=3 show=n today=20060115
## 
## The result is:
## 
##   SQL> SELECT 'partition='||partition_name
##     2  FROM dba_tab_partitions
##     3  WHERE table_owner='TRADE'
##     4  AND table_name='SETTLEMENT'
##     5  ORDER BY 1;
##   
##   'PARTITION='||PARTITION_NAME
##   ----------------------------------------
##   partition=M200509
##   partition=M200510
##   partition=M200511
##   partition=M200512
##   partition=M200601
##   
##   SQL> SELECT 'version=' || VERSION from v$instance;
##   
##   'VERSION='||VERSION
##   -------------------------
##   version=10.1.0.5.0
##   
##   SQL> ALTER TABLE TRADE.SETTLEMENT
##     2  DROP        PARTITION M200509
##     3  UPDATE GLOBAL INDEXES
##     4  ;
##   
##   Table altered.
## 
## - We see first PMAN lists all the partitions in the time order.
## Since today is January 15, 2006, M200601 is the current partition,
## M200509 through M200512 are 4 past partitions. Since we only to
## keep 3 month's data, the oldest partition M200509 is dropped.
## 
## - We see also that Oracle version is queried. This is because
## when you drop the partitions with data, most likely there are,
## the global indexes will become invalid. Oracle version 9i and 10g
## provide "UPDATE GLOBAL INDEXES" clause to update the indexes simultaneously.
## 
## If it is an Oracle database version 8i and below, additional
## SQL statements like this:
## 
##   ALTER INDEX <owner>.<index> REBUILD COMPUTE STATISTICS;
## 
## will be executed inside PL/SQL.
## 
## - If you specify the option keep=<n>T instead of keep=<n>, PMAN will
## truncate the unwanted partitions instead of dropping them. This could be
## useful if the table structure needs to be maintained, or future restore
## is needed. 
## 
## - The keep and next options can be used at the same time.
## This is how jobs are normally setup, to drop one old partition,
## and precreate a new partition each month for monthly
## partitioned tables. We separate these two options for illustration purpose.
## 
## =head3 04. MERGE PAST PARTITIONS
## 
## The merge option will merge prior monthly partitions to yearly partitions,
## and prior daily partitions to monthly partitions. In our example,
## the following 3 partitions in previous year
## (relative to the current partition)
## 
##   M200510
##   M200511
##   M200512
## 
## will merge to A2005, where A stands for Archive.
## 
## The PMAN command is:
## 
##   $ pman sid:=$ORACLE_SID table=trade.settlement merge=y show=n today=20060115
## 
## The result is:
## 
##   SQL> ALTER TABLE TRADE.SETTLEMENT MERGE PARTITIONS
##     2  M200510, M200511
##     3  INTO PARTITION
##     4  M200511a
##     5  UPDATE GLOBAL INDEXES
##     6  ;
##   
##   Table altered.
##   
##   SQL> ALTER TABLE TRADE.SETTLEMENT MERGE PARTITIONS
##     2  M200511a, M200512
##     3  INTO PARTITION
##     4  M200512a
##     5  UPDATE GLOBAL INDEXES
##     6  ;
##   
##   Table altered.
##   
##   SQL> ALTER TABLE TRADE.SETTLEMENT RENAME PARTITION M200512a
##     2  to A2005;
##   
##   Table altered.
## 
## =head3 05. DROP MERGED (ARCHIVED) PARTITIONS
## 
## The archive option is just like the keep option, but operate
## on the merged (archived) partitions.
## 
## To illustrate this feature, let us fast forward the clock
## one year: you created all 2006 partitions, and archived them.
## 
## The following PMAN commands to simulate this process. 
## 
##   $ pman sid:=$ORACLE_SID table=trade.settlement next=12 show=n today=20060115
##   $ pman sid:=$ORACLE_SID table=trade.settlement merge=y show=n today=20070115
## 
## The first command will create 12 partitions, bringing the last partition
## to M200701, and the second command will merge all 2006 partitions.
## 
## Now we have two archived partitions A2005 and A2006. Suppose your site
## policy is to keep the data for 1 year besides the current year, you can 
## use the archive option to drop all archived partitions except the 
## most recent one.
## 
## The PMAN command to do this is:
## 
##   $ pman sid:=$ORACLE_SID table=trade.settlement archive=1 show=n today=20070115
## 
## which simply does:
## 
##   SQL> ALTER TABLE TRADE.SETTLEMENT
##     2  DROP PARTITION A2005
##     3  UPDATE GLOBAL INDEXES
##     4  ;
## 
##   Table altered.
## 
## today option would be useful if you need to merge 2006 partitions
## in the year 2008. You do not need to use the option if you run the command
## in year 2007. 
## 
## =head2 ORACLE CONSIDERATIONS
##
## =head3 STATISTICS
## 
## It was not shown in the example, but PMAN does gather statistics
## for the partitions it touched, including split partitions,
## and merged partitions. Example:
## 
##   SQL> BEGIN
##     2    DBMS_STATS.GATHER_TABLE_STATS (
##     3      OWNNAME          => 'TRADE',
##     4      TABNAME          => 'SETTLEMENT',
##     5      PARTNAME         => 'M200511',
##     6      GRANULARITY      => 'PARTITION',
##     7      CASCADE          => TRUE,
##     8      ESTIMATE_PERCENT => 20
##     9    );
##    10  END;
##    11  /
## 
## In normal situation, the last partition is future partition and empty.
## The newly split partitions are therefore empty as well.
## But zero statistics is meaningful statistics, while
## no statistics is not.
## 
## =head3 INDEXES
## 
## PMAN generate statements to rebuild local indexes
## for the partitions it touched:
## 
##   SQL> ALTER TABLE TRADE.SETTLEMENT MODIFY PARTITION M200511
##     2  REBUILD UNUSABLE LOCAL INDEXES;
## 
## In normal situation, we split empty partitions which will
## not make local indexes unusable.
## 
## PMAN also generates statements to rebuild global indexes
## for Oracle 8i and below, and uses "UPDATE GLOBAL INDEXES"
## for Oracle 9i and above.
## 
## =head3 TABLESPACES
## 
## One of the advantages of partitioning is we can put different
## partitions on different disks for performance and capacity
## planning.
## 
## PMAN is designed for maintaining a constant of number of
## partitions occupying constant or slowly growing amount of disk space.
## It does not use TABLESPACE clause for partitions. 
## As a result, the split table partitions and index
## partitions remain in the same tablespaces where they
## split out, respectively. 
## 
## You can expand its usage by periodically moving the last
## partitions to new tablespaces; or add TABLESPACE clause
## to put January partitions on January tablespace,
## February partitions on January tablespace, etc.
## 
## =head3 FUNCTIONAL BITMAP INDEX
## 
## In normal situation, the last partition should be empty any way. 
## It is more important to keep that way if you are running Oracle prior to
## Oracle 9.0.1 and have a functional bitmap index.
## 
## This is because when we split a non-empty partition, the
## functional bitmap index (and all other indexes) will become invalid.
## Rebuilding the functional bitmap index for a partition will
## cause entire table scanned due to the Oracle bug 1987514.
## Depending on the size of the table, this could be a problem.
## So in this case, it is better to drop the index and recreate it.
## 
## The bug is about functional local index in general
## (bitmap index is always local), but we only had experience with
## functional bitmap index. The bug is supposedly fixed in Oracle 9.0.1.
## 
## =head2 PORTABILITY
## 
## PMAN is written is portable shell, which runs on on any platforms that
## has ksh or bash. It has been tested on various combinations of
## shell (ksh, bash), Oracle database versions (8i, 9i, and 10g), and
## operating environments (Solaris, HPUX, AIX, and Cygwin).
## 
## =head2 CAVEATS
## 
## Although PMAN naming standard does not pose any limitation of the use of
## partitions, as said earlier, there would a problem if your organization
## may use a different standard.
## 
## There are two solutions to solve the problem if you want to use pman:
## to change the partition name to the PMAN standard if the partition name
## is not an externally exposed interface; or to change the pman to fit
## your existing standard. If your naming standard does not include the
## information how the table is partitioned (for example, yearly, monthly,
## etc), then you have to modify the program to accept the information,
## creating one more piece of loose part.
## 
## It is certainly possible to have a generic program without using the
## naming convention, just like it is possible, but more difficult, to
## do premise wiring without color coding standard. However, unless you opt to
## randomly generate partition names, you have to have a naming convention
## anyway. It is just a different standard.
## 
## The standard difference is not a technical issue. Like culture
## difference, while it causes confusion, it creates opportunities
## and makes life more interesting.
## 
## =head2 SUMMARY
## 
## PMAN can help you to create partitioned tables, or convert regular tables
## to partitioned tables.  It can help you to manage the partitions. It
## can also be used as a SQL generator, if that is what you want to do.
## 
## PMAN is simple, generic, portable, and reliable.
## 
## =head1 ACKNOWLEDGMENT
## 
## The version 1.0 (2001-12-18) was inspired by our friend David Xiao's
## PL/SQL code. Throughout the years, enhancements are made and bug are fixed
## with the help of friends and users. The current version 4.5 (2007-03-17),
## and future update is available from
## <http://www.unixlabplus.com/unix-prog/pman>.
## 
## =head1 REFERENCE
## 
## Wang, Michael and Julie Wang. "Date-Related Shell Functions."
## September 2005. Unix Review.
## <http://www.unixreview.com/documents/s=9521/ur0509a/>, or
## <http://www.unixlabplus.com/unix-prog/ date_function/>
## 
## Oracle Corporation. "Partitioned Tables and Indexes." 
## Oracle Database Concepts. October, 2005.
## <http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220.pdf>.
##
## =head1 AUTHORS
##
## Michael Wang has been a sysadmin, Oracle database admin,
## and now a Unix programmer. He is an an Oracle 10g certified professional.
## He can be reached at xw73@columbia.edu.
## 
## Julie Wang has managed Unix systems, Lawson Enterprise Systems,
## and currently Oracle databases. She is an Oracle 10g certified professional.
## She can be reached at julie_wangye@yahoo.com.
##
## =head1 VERSION HISTORY
##
## =head2 version 4.6, 2007-03-19.
##
## =over
##
## =item *
##
## Fixed merge option for P600 case (P20040900).
##
## =back
##
## =head2  version 4.5, 2007-03-17.
##
## =over
##
## =item *
##
## Fixed rebuilding global index for 8i with PL/SQL code.
##
## =back
##
## =head2  version 4.4, 2007-02-15.
##
## =over
##
## =item *
##
## Added Cygwin support.
##
## =back
##
## =head2  version 4.3, 2007-01-30.
##
## =over
##
## =item *
##
## Updated generic functions; make the code more portable (ksh88, bash).
##
## =item *
##
## Added today option to pretend we are running on that day.
##
## =back
##
## =head2  version 4.2, 2005-08-11.
##
## =over
##
## =item *
##
## Added userid option for a major telecom company because the
##     DBE's can not connect as sysdba, and oratab is owned by root.
##
## =back
##
## =head2  version 4.1, 2005-01-22.
##
## =over
##
## =item *
##
## Added archive option.
##
## =back
##
## =head2  version 4.0, 2005-01-20.
##
## =over
##
## =item *
##
## Added merge option for MYYYYMM, DYYYYMMDD, PYYYYYMM, PYYYYYMM00,
##     and PYYYYYMMDD to merge the monthly partition belonging to previous
##     year to yearly partition, and daily partition belonging to previous
##     month to monthly partition. The merged partition is named as 
##     AE<lt>numberE<gt> indicating archived partition, which do not participate
##     keep and next operation.
##
## =back
##
## =head2  version 3.1, 2004-08-18.
##
## =over
##
## =item *
##
## Padding vnum for PYYYY00, PYYYY0000, PYYYYMM00.
##
## =back
##
## =head2  version 3.0, 2004-06-24.
##
## =over
##
## =item *
##
## Added support for global indexes with help from Sheck C:
##   (1) "UPDATE GLOBAL INDEXES" for 9i database;
##   (2) "ALTER INDEX ... REBUILD COMPUTE STATISTICS" for 8i database.
##
## =item *
##
## Added 3 more parition scenarios: PYYYY00, PYYYY0000, PYYYYMM00.
##
## =item *
##
## Changed "ANALYZE" statement to "DBMS_STATS" per recommendation
##     from Sheck C.
##
## =back
##
## =head2  version 2.1, 2004-04-01.
##
## =over
##
## =item *
##
## Added the option to truncate the old partion instead of drop.
##
## =back
##
## =head2  version 2.0, 2004-03-19.
##
## =over
##
## =item *
##
## rewritten without use of GNU date or Perl.
##
## =item *
##
## handle 8 cases.
##
## =item *
##
## renamed from "split_part" to "pman" for marketing purpose.
##
## =back
##
## =head2  version 1.3, 2002-02-07.
##
## =over
##
## =item *
##
## Added help=pdf and /usr/perl5/bin checking.
##
## =back
##
## =head2  version 1.2, 2002-01-28.
##
## =over
##
## =item *
##
## When KEEP=<null>, ignore MIN(partition_name).
##
## =back
##
## =head2  version 1.1, 2002-01-23.
##
## =over
##
## =item *
##
## Changed "i++" to use "i=$(next_day $i)".
##
## =back
##
## =head2  version 1.0, 2001-12-18.
##
## =over
##
## =item *
##
## Based on PL/SQL code from David Xiao.
##
## =back
##
## POD_STOP