ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle10g--11gR2分区表汇总七

oracle10g--11gR2分区表汇总七

原创 Linux操作系统 作者:jack22220613 时间:2011-03-18 09:39:39 0 删除 编辑

3.3.2 range分区表和range分区表(不支持)

SQL> create table t_par_range_2 (time date,

  2      id number,

  3      city_id number,

  4      value1 number,

  5      value2 varchar2(10)

  6      ) partition by range(time)

  7       ( partition p_t_par_range_2_0 values less than (to_date('2011-3-10 10:00:00','yyyy-mm-dd hh24:mi:ss')) ,

  8         partition p_t_par_range_2_1 values less than (to_date('2011-4-10 10:00:00','yyyy-mm-dd hh24:mi:ss'))

  9         )

 10     ;

 

表已创建。

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_par_range_2 ;

alter table t_par_range exchange partition p_t_par_range_0 with table t_par_range_2

                                                                      *

1 行出现错误:

ORA-14095: ALTER TABLE EXCHANGE 要求非分区, 非聚簇的表

 

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_par_range_2 with validation;

alter table t_par_range exchange partition p_t_par_range_0 with table t_par_range_2 with validation

                                                                      *

1 行出现错误:

ORA-14095: ALTER TABLE EXCHANGE 要求非分区, 非聚簇的表

3.3.3 list分区表和range-list分区表(支持)

SQL>

SQL> select * from t_par_list;

 

未选定行

 

SQL> create table t_par_range_list(time date,

  2  id number,

  3  city_id number,

  4  value1 number,

  5  value2 varchar2(10))

  6  partition by range(time)

  7    subpartition by list(city_id)

  8    subpartition template

  9    (

 10     subpartition p_t_par_range_list_0 values(10) ,

 11     subpartition p_t_par_range_list_1 values(20)

 12      )

 13    (

 14      partition t_par_range_list0 values less than (to_date('2011-3-10 10:00:00','yyyy-mm-dd hh24:mi:ss')),

 15      partition t_par_range_list1 values less than (to_date('2011-4-10 10:00:00','yyyy-mm-dd hh24:mi:ss'))

 16     ) ;

create table t_par_range_list(time date,

*

1 行出现错误:

ORA-14613: 尝试从父级名称 T_PAR_RANGE_LIST0 和模板名称 P_T_PAR_RANGE_LIST_0

生成名称, 但由于合成的名称过长而失败

 

 

SQL> create table t_par_range_list(time date,

  2  id number,

  3  city_id number,

  4  value1 number,

  5  value2 varchar2(10))

  6  partition by range(time)

  7    subpartition by list(city_id)

  8    subpartition template

  9    (

 10     subpartition p0 values(10) ,

 11     subpartition p1 values(20)

 12      )

 13    (

 14      partition t_par_range_list0 values less than (to_date('2011-3-10 10:00:00','yyyy-mm-dd hh24:mi:ss')),

 15      partition t_par_range_list1 values less than (to_date('2011-4-10 10:00:00','yyyy-mm-dd hh24:mi:ss'))

 16     ) ;

 

表已创建。

 

SQL>

 

SQL> insert into t_par_range_list values(to_date('2011-3-1 10:00:00','yyyy-mm-dd hh24:mi:ss'),1,10,1,'a');

 

已创建 1 行。

 

SQL> insert into t_par_range_list values(to_date('2011-3-2 10:00:00','yyyy-mm-dd hh24:mi:ss'),2,20,2,'b');

 

已创建 1 行。

 

SQL> insert into t_par_range_list values(to_date('2011-4-1 10:00:00','yyyy-mm-dd hh24:mi:ss'),3,10,3,'c');

 

已创建 1 行。

 

SQL> insert into t_par_range_list values(to_date('2011-4-2 10:00:00','yyyy-mm-dd hh24:mi:ss'),4,20,4,'d');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_LIST';

 

INDEX_NAME           PARTITION_NAME       STATUS                               

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

IND_T_PAR_LIST       P_T_PAR_LIST_1       USABLE                               

IND_T_PAR_LIST       P_T_PAR_LIST_0       USABLE                                

 

SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_SUBPartitions where INDEX_name='IND_T_PAR_RANGE_LIST';

 

未选定行

 

SQL> select * from t_par_range_list;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

02-3 -11              2         20          2 b                              

01-4 -11              3         10          3 c                              

02-4 -11              4         20          4 d                              

 

SQL> alter table t_par_range_list exchange partition T_PAR_RANGE_LIST0 with table t_par_list;

 

表已更改。

 

SQL> select partition_name,subpartition_name from User_Tab_subPartitions where table_name='T_PAR_RANGE_LIST';

 

PARTITION_NAME                                                                  

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

SUBPARTITION_NAME                                                              

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

T_PAR_RANGE_LIST0                                                              

T_PAR_RANGE_LIST0_P1                                                           

                                                                               

T_PAR_RANGE_LIST0                                                              

T_PAR_RANGE_LIST0_P0                                                           

                                                                               

T_PAR_RANGE_LIST1                                                              

T_PAR_RANGE_LIST1_P1                                                           

                                                                               

T_PAR_RANGE_LIST1                                                              

T_PAR_RANGE_LIST1_P0                                                           

                                                                               

 

SQL> select * from t_par_list;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

02-3 -11              2         20          2 b                              

 

SQL> select * from t_par_range_list;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-4 -11              3         10          3 c                              

02-4 -11              4         20          4 d                              

 

SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_LIST';

 

INDEX_NAME           PARTITION_NAME       STATUS                               

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

IND_T_PAR_LIST       P_T_PAR_LIST_1       UNUSABLE                              

IND_T_PAR_LIST       P_T_PAR_LIST_0       UNUSABLE                             

 

SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_SUBPartitions where INDEX_name='IND_T_PAR_RANGE_LIST';

 

未选定行

 

SQL> alter table t_par_range_list exchange partition T_PAR_RANGE_LIST0 with table t_par_list;

 

表已更改。

 

SQL> select * from t_par_list;

 

未选定行

 

SQL> select * from t_par_range_list;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

02-3 -11              2         20          2 b                              

01-4 -11              3         10          3 c                              

02-4 -11              4         20          4 d                              

 

SQL> alter table t_par_range_list exchange partition T_PAR_RANGE_LIST0 with table t_par_list;

 

表已更改。

 

SQL> alter table t_par_range_list exchange partition T_PAR_RANGE_LIST1 with table t_par_list;

alter table t_par_range_list exchange partition T_PAR_RANGE_LIST1 with table t_par_list

                                                                             *

1 行出现错误:

ORA-14099: 未对指定分区限定表中的所有行

 

 

SQL> select * from t_par_list;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

02-3 -11              2         20          2 b                              

 

SQL> select * from t_par_range_list;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-4 -11              3         10          3 c                              

02-4 -11              4         20          4 d                              

 

SQL> select * from t_par_list partition(p_t_par_list_0);

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                               

 

SQL> select * from t_par_list partition(p_t_par_list_1);

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

02-3 -11              2         20          2 b                              

 

SQL> create table t_par_list_tmp as select * from t_par_list;

 

表已创建。

 

SQL> select * from t_par_list_tmp;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                          

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

01-3 -11              1         10          1 a                              

02-3 -11              2         20          2 b                              

 

SQL> truncate table t_par_list;

 

表被截断。

 

SQL> alter table t_par_range_list exchange partition T_PAR_RANGE_LIST1 with table t_par_list;

 

表已更改。

 

SQL> select * from t_par_list;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-4 -11              3         10          3 c                              

02-4 -11              4         20          4 d                              

 

SQL> select * from t_par_range_list;

 

未选定行

 

SQL> alter table t_par_range_list exchange partition T_PAR_RANGE_LIST0 with table t_par_list;

alter table t_par_range_list exchange partition T_PAR_RANGE_LIST0 with table t_par_list

                                                                             *

1 行出现错误:

ORA-14099: 未对指定分区限定表中的所有行

 

 

SQL> create table t_par_list_tmp_2 as select * from t_par_list;

 

表已创建。

 

SQL> delete from t_par_list;

 

已删除2行。

 

SQL> commit;

 

提交完成。

 

SQL> alter table t_par_range_list exchange partition T_PAR_RANGE_LIST0 with table t_par_list;

 

表已更改。

 

SQL> select * from t_par_list;

 

未选定行

 

SQL> select * from t_par_range_list;

 

未选定行

3.3.4 hash分区表和range-hash分区表(支持)

 

 

关于10g的分区表使用就写这么多了,接下来,总结下11gR2的分区表功能。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10037372/viewspace-689828/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-13

  • 博文量
    94
  • 访问量
    357908