# 11g(11.2.0.1)

## 1.      建分区表

10g中的用法在11仍可用，不再赘述，下面只是列出11gR2中特有的用法：

### 1.1 interval (range分区的扩展)

--按小时建分区

create table tab_1 (time date,

id number,

city_id number,

value1 number,

value2 varchar2(10)

) partition by range(time)

interval(numtodsinterval(1,'hour')) store in (tbs_1)

( partition p_tab_1_0 values less than (to_date('2011-3-10 1:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace tbs_1)

;

--按天建分区

...

interval(numtodsinterval(1,'day'))

...

--按月建分区

interval(numtoyminterval(1,'month'))

--按年建分区

interval(numtoyminterval(1,'year'))

--number类型作为分区字段

create table tab_1 (time date,

id number,

city_id number,

value1 number,

value2 varchar2(10)

) partition by range(id)

interval(100) store in (tbs_1)

( partition p_tab_1_0 values less than (1000) tablespace tbs_1)

;

SQL> create table tab_1 (time date,

2  id number,

3  city_id number,

4  value1 number,

5  value2 varchar2(10)

6  ) partition by range(time)

7   interval(numtodsinterval(1,'hour')) store in (tbs_1)

8   ( partition p_tab_1_0 values less than (to_date('2011-3-10 1:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace tbs_1)

9  ;

SQL>

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAME                 PARTITION_POSITION

------------------------------ ------------------

P_TAB_1_0                                       1

SQL> INSERT INTO tab_1 values(to_date('2011-3-10 2:00:00','yyyy-mm-dd hh24:mi:ss'),1,10,1,'a');

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAME                 PARTITION_POSITION

------------------------------ ------------------

P_TAB_1_0                                       1

SYS_P161                                        2

SQL> select * from tab_1;

TIME                   ID    CITY_ID     VALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3 -11              1         10          1 a

SQL> INSERT INTO tab_1 values(to_date('2011-3-10 5:00:00','yyyy-mm-dd hh24:mi:ss'),1,10,1,'a');

---注意时间间隔

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAME                 PARTITION_POSITION

------------------------------ ------------------

P_TAB_1_0                                       1

SYS_P161                                        2

SYS_P162                                        3

---增长了一个，而不是多个

SQL> rollback;

SQL> select * from tab_1;

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAME                 PARTITION_POSITION

------------------------------ ------------------

P_TAB_1_0                                       1

SYS_P161                                        2

SYS_P162                                        3

SQL> alter table tab_1 drop partition p_tab_1_0;

alter table tab_1 drop partition p_tab_1_0

*

1 行出现错误:

ORA-14758: 不能删除范围段中的最后一个分区

SQL> alter table tab_1 drop partition sys_p161;

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAME                 PARTITION_POSITION

------------------------------ ------------------

P_TAB_1_0                                       1

SYS_P162                                        2

SQL> alter table tab_1 drop partition sys_p162;

SQL> truncate table tab_1;

SQL> INSERT INTO tab_1 values(to_date('2011-3-10 2:00:00','yyyy-mm-dd hh24:mi:ss'),1,10,1,'a');

SQL> select * from tab_1;

TIME                   ID    CITY_ID     VALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3 -11              1         10          1 a

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAME                 PARTITION_POSITION

------------------------------ ------------------

P_TAB_1_0                                       1

SYS_P164                                        2

SQL> alter table tab_1

2  add partition p000 values less than (to_date('2011-3-11 19:00:00','yyyy-mm-dd hh24:mi:ss'))

3  ;

alter table tab_1

*

1 行出现错误:

--已经隐含commit了，drop partition也会隐含commit了，有兴趣的可以试试

SQL> select * from tab_1;

TIME                   ID    CITY_ID     VALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3 -11              1         10          1 a

SQL> rollback;

SQL> select * from tab_1;

TIME                   ID    CITY_ID     VALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3 -11              1         10          1 a

SQL> alter table tab_1 drop partition sys_p169;

SQL> alter table tab_1 drop partition for(1004);

merge操作时，也可以用for()这种写法，如：

Alter table xxx merge partitions for(),for() into partition xxx;

SQL>

SQL>

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAME                 PARTITION_POSITION

------------------------------ ------------------

P_TAB_1_0                                       1

SYS_P172                                        2

SYS_P171                                        3

SQL> select * from tab_1 partition(p_tab_1_0);

TIME                   ID    CITY_ID     VALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3 -11            999         10          1 a

10-3 -11             10         10          1 a

SQL> select * from tab_1 partition(sys_p172);

TIME                   ID    CITY_ID     VALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3 -11           1000         10          1 a

SQL> select * from tab_1 partition(sys_p171);

TIME                   ID    CITY_ID     VALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3 -11           1100         10          1 a

SQL> alter table tab_1 drop partition sys_p172;

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAME                 PARTITION_POSITION

------------------------------ ------------------

P_TAB_1_0                                       1

SYS_P171                                        2

SQL> INSERT INTO tab_1 values(to_date('2011-3-10 0:00:00','yyyy-mm-dd hh24:mi:ss'),1000,10,1,'a');

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAME                 PARTITION_POSITION

------------------------------ ------------------

P_TAB_1_0                                       1

SYS_P173                                        2

SYS_P171                                        3

SQL> select * from tab_1 partition(p_tab_1_0);

TIME                   ID    CITY_ID     VALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3 -11            999         10          1 a

10-3 -11             10         10          1 a

SQL> select * from tab_1 partition(sys_p173);

TIME                   ID    CITY_ID     VALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3 -11           1000         10          1 a

• 博文量
94
• 访问量
362665