oracle range用法,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','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)

12017-5-25 8:35:032017-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'));

总结

以上是编程之家为你收集整理的Oracle range 分区表全部内容，希望文章能够帮你解决Oracle range 分区表所遇到的程序开发问题。

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。