Oracle11g下自动创建分区

Love The Way You Lie 2022-06-11 09:57 321阅读 0赞

11g之前，维护分区需要手工。11g之后使用interval来实现自动扩展分区，简化了维护。  
根据年: INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))  
根据月: INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))  
根据天: INTERVAL(NUMTODSINTERVAL(1,'DAY'))  
根据时分秒: NUMTODSINTERVAL( n, \{ 'DAY'|'HOUR'|'MINUTE'|'SECOND'\})  
下面用按月自动扩展来做个试验：  
SQL> create table t\_range (id number not null PRIMARY KEY, test\_date date)  
partition by range (test\_date) interval (numtoyMinterval (1,'MONTH'))  
(  
partition p\_2014\_01\_01 values less than (to\_date('2014-01-01', 'yyyy-mm-dd'))  
);  
\--看到只有一个分区  
SQL> select partition\_name from user\_tab\_partitions where table\_name='T\_RANGE';  
PARTITION\_NAME  
\------------------------------  
P\_2014\_01\_01  
  
  
  
  
SQL> insert /\*+append \*/ into t\_range select rownum,  
to\_date(to\_char(sysdate - 140, 'J') +  
trunc(dbms\_random.value(0, 80)),  
'J')  
from dual  
connect by rownum <= 100000;  
SQL> commit;  
  
  
\--可以看到SYS开头的分区是自动扩展的  
SQL> select partition\_name from user\_tab\_partitions where table\_name='T\_RANGE';  
PARTITION\_NAME  
\------------------------------  
P\_2014\_01\_01  
SYS\_P21  
SYS\_P22  
SYS\_P23  
\--再看看t\_range的表结构  
create table T\_RANGE  
(  
ID NUMBER not null,  
TEST\_DATE DATE  
)  
partition by range (TEST\_DATE)  
(  
partition P\_2014\_01\_01 values less than (TO\_DATE(' 2014-01-01 00:00:00',  
'SYYYY-MM-DD HH24:MI:SS', 'NLS\_CALENDAR=GREGORIAN')),  
partition SYS\_P21 values less than (TO\_DATE(' 2014-02-01 00:00:00',  
'SYYYY-MM-DD HH24:MI:SS', 'NLS\_CALENDAR=GREGORIAN')),  
partition SYS\_P22 values less than (TO\_DATE(' 2014-03-01 00:00:00',  
'SYYYY-MM-DD HH24:MI:SS', 'NLS\_CALENDAR=GREGORIAN')),  
partition SYS\_P23 values less than (TO\_DATE(' 2014-04-01 00:00:00',  
'SYYYY-MM-DD HH24:MI:SS', 'NLS\_CALENDAR=GREGORIAN'))  
);  
\-- Create/Recreate primary, unique and foreign key constraints  
alter table T\_RANGE  
add primary key (ID);  
\--如果对分区名不满意，则可以自己修改一下：  
alter table t\_range rename partition SYS\_P21 to p\_2014\_02\_01;  
alter table t\_range rename partition SYS\_P22 to p\_2014\_03\_01;  
alter table t\_range rename partition SYS\_P23 to p\_2014\_04\_01;