11g之前还不支持分区自动interval day 功能,对于需要指定日期的分区需求例如每周2一个分区,可以采用下面脚本实现
select 'alter table END_USER_LOGIN_LOG_1 add partition P'|| TO_CHAR(a,'YYMMDD') ||
' values less than (TO_DATE(''' || TO_CHAR(a,'YYYY-MM-DD HH24:MI:SS')
|| ''', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) tablespace UDATA01 ;'
from (select next_day(to_date('2012-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+(level-1)*7,3) a
from dual
connect by next_day(to_date('2012-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+(level-1)*7,3) <= to_date('2013-12-31','yyyy-mm-dd'))
-------------------------------------
"alter table END_USER_LOGIN_LOG_1 add partition P120103 values less than (TO_DATE('2012-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01 ;"
"alter table END_USER_LOGIN_LOG_1 add partition P120110 values less than (TO_DATE('2012-01-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01 ;"
"alter table END_USER_LOGIN_LOG_1 add partition P120117 values less than (TO_DATE('2012-01-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01 ;"
"alter table END_USER_LOGIN_LOG_1 add partition P120124 values less than (TO_DATE('2012-01-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01 ;"
"alter table END_USER_LOGIN_LOG_1 add partition P120131 values less than (TO_DATE('2012-01-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01 ;"
"alter table END_USER_LOGIN_LOG_1 add partition P120207 values less than (TO_DATE('2012-02-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01 ;"
"alter table END_USER_LOGIN_LOG_1 add partition P120214 values less than (TO_DATE('2012-02-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01 ;"
"alter table END_USER_LOGIN_LOG_1 add partition P120221 values less than (TO_DATE('2012-02-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01 ;"
.....