Oracle range 分区表

-- Create table
create table TLCB_MON_LINUX
(
  sdate        DATE,
  ip           CHAR(20),
  processcpu   CLOB,
  processmem   CLOB,
  port         CLOB,
  countprocess VARCHAR2(100),
  countport    VARCHAR2(100)
)
partition by range (SDATE)
(
  partition P20170524 values less than (TO_DATE(' 2017-05-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255,
  partition P20170525 values less than (TO_DATE(' 2017-05-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    )
);


select to_char(min(sdate)),to_char(max(sdate)) from tlcb_mon_linux partition (P20170526);
   	MIN(SDATE)	        MAX(SDATE)
1	2017-5-25 8:35:03	2017-5-25 8:40:17


SQL> select to_char(min(sdate)),to_char(max(sdate)) from tlcb_mon_linux partition (P20170525);

TO_CHAR(MIN(SD TO_CHAR(MAX(SD
-------------- --------------



添加分区脚本:

SQL> set linesize 200
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET'; 

PARAMETER		       VALUE
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_CHARACTERSET	       AL32UTF8

[oracle@yyjk ~]$ cat get_date 
export NLS_LANG="american_america.ZHS16GBK"
echo " set colsep |;
    set echo off;
    set feedback off;
    set heading off;
    set pagesize 0;
    set termout off;
    set trimout on;
    set trimspool on;
    set linesize 3000;
    spool ./date.txt1;
    select   to_char((to_date('$1','YYYY-MM-DD')$2),'YYYY-MM-DD')  from  dual  where  rownum=1;
    "  | sqlplus tlcbuser/tlcbuser >/dev/null
    if [ -f ./date.txt1 ]
    then
        cat ./date.txt1 | grep -v "^SQL>" | tr -d ' ' >./date.txt
        rm -f ./date.txt1
    else
        exit
    fi
    date=`cat ./date.txt`
    rm -f ./date.txt
echo $date



[oracle@yyjk ~]$ cat a1.sh
date=`echo $1 | tr -d '-'`
date1=`echo $2 | tr -d '-'`
date2=`echo $1`
date_end=`./get_date $2 +1 | tr -d '-'`
while :
    do
     xdate=`./get_date $date -1 | tr -d '-'`
     echo "alter table tlcb_mon_linux add partition p$xdate values less than (to_date('$date2','yyyy-mm-dd'));"
     date2=`./get_date $date +1`
     date=`echo $date2 | tr -d '-'`
     if [ "$date" = "$date_end" ]
        then 
        exit
fi
done

[oracle@yyjk ~]$ cat a1.sql  | head -10
alter table tlcb_mon_linux add partition p20170526 values less than (to_date('2017-05-27','yyyy-mm-dd'));
alter table tlcb_mon_linux add partition p20170527 values less than (to_date('2017-05-28','yyyy-mm-dd'));
alter table tlcb_mon_linux add partition p20170528 values less than (to_date('2017-05-29','yyyy-mm-dd'));
alter table tlcb_mon_linux add partition p20170529 values less than (to_date('2017-05-30','yyyy-mm-dd'));
alter table tlcb_mon_linux add partition p20170530 values less than (to_date('2017-05-31','yyyy-mm-dd'));
alter table tlcb_mon_linux add partition p20170531 values less than (to_date('2017-06-01','yyyy-mm-dd'));
alter table tlcb_mon_linux add partition p20170601 values less than (to_date('2017-06-02','yyyy-mm-dd'));
alter table tlcb_mon_linux add partition p20170602 values less than (to_date('2017-06-03','yyyy-mm-dd'));
alter table tlcb_mon_linux add partition p20170603 values less than (to_date('2017-06-04','yyyy-mm-dd'));
alter table tlcb_mon_linux add partition p20170604 values less than (to_date('2017-06-05','yyyy-mm-dd'));