前端之家收集整理的这篇文章主要介绍了
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)
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'));