ITPub博客

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

oracle10g--11gR2分区表汇总三

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

3.2.6 normal索引(分区表有全局索引,非分区表有索引)

紧接上个试验过程:

 

SQL>

SQL> create index ind_t_par_range on t_par_range(id);

 

索引已创建。

 

SQL> create index ind_t_no_par on t_no_par(id);

 

索引已创建。

 

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

 

未选定行

 

SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR','IND_T_PAR_RANGE');

 

INDEX_NAME                     INDEX_TYPE                  STATUS              

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

IND_T_NO_PAR                   NORMAL                      VALID               

IND_T_PAR_RANGE                NORMAL                      VALID               

 

SQL>

SQL> select * from t_no_par;

 

未选定行

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

01-4 -11              2         20          2 b                              

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes;

alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes

                                                                      *

1 行出现错误:

ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配

--此时再指定including indexes就报错了

 

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

 

表已更改。

 

SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR','IND_T_PAR_RANGE');

 

INDEX_NAME                     INDEX_TYPE                  STATUS              

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

IND_T_NO_PAR                   NORMAL                      UNUSABLE            

IND_T_PAR_RANGE                NORMAL                      UNUSABLE            

 

SQL> select * from t_no_par;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                          

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

01-4 -11              2         20          2 b                              

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes;

alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes

                                                                      *

1 行出现错误:

ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配

 

 

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

 

表已更改。

 

SQL> select * from t_no_par;

 

未选定行

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

01-4 -11              2         20          2 b                              

 

SQL> drop index ind_t_no_par;

 

索引已删除。

 

SQL> drop index ind_t_par_range;

 

索引已删除。

 

3.2.7 有约束(分区表有Primary key约束,非分区表有Primary key约束)

接上个试验过程:

3.2.7.1 (分区表、非分区表enable)—---exchange—----exchange

解释:

首先:分区表和非分区表primary key 都是 enable的;

其次:交换分区且包括 including indexes;

然后:再次交换分区包括 including indexes;

 

 

SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR','IND_T_PAR_RANGE');

 

未选定行

 

SQL> alter table t_par_range add constraint ind_t_par_range using index local;

alter table t_par_range add constraint ind_t_par_range using index local

                                                             *

1 行出现错误:

ORA-00902: 无效数据类型

 

 

SQL> alter table t_par_range add constraint ind_t_par_range primary key(id) using index local;

alter table t_par_range add constraint ind_t_par_range primary key(id) using index local

*

1 行出现错误:

ORA-14039: 分区列必须构成 UNIQUE 索引的关键字列子集

 

 

SQL> alter table t_par_range add constraint ind_t_par_range primary key(time,id) using index local;

 

表已更改。

 

SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR','IND_T_PAR_RANGE');

 

INDEX_NAME                     INDEX_TYPE                  STATUS              

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

IND_T_PAR_RANGE                NORMAL                      N/A                 

 

SQL> alter table t_no_par add constraint ind_t_no_par primary key(id) using index;

 

表已更改。

 

SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR','IND_T_PAR_RANGE');

 

INDEX_NAME                     INDEX_TYPE                  STATUS              

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

IND_T_NO_PAR                   NORMAL                      VALID               

IND_T_PAR_RANGE                NORMAL                      N/A                 

 

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

 

INDEX_NAME                     PARTITION_NAME                 STATUS           

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

IND_T_PAR_RANGE                P_T_PAR_RANGE_0                USABLE           

IND_T_PAR_RANGE                P_T_PAR_RANGE_1                USABLE           

 

SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR');

 

INDEX_NAME                     INDEX_TYPE                  STATUS              

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

IND_T_NO_PAR                   NORMAL                      VALID               

 

SQL> select constraint_name,constraint_type,status,validated from User_Constraints where INDEX_name in ('IND_T_NO_PAR','IND_T_PAR_RANGE');

 

CONSTRAINT_NAME                C STATUS   VALIDATED                            

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

IND_T_PAR_RANGE                P ENABLED  VALIDATED                            

IND_T_NO_PAR                   P ENABLED  VALIDATED                            

 

SQL>

SQL> select * from t_no_par;

 

未选定行

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

01-4 -11              2         20          2 b                              

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes;

alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes

*

1 行出现错误:

ORA-14130: UNIQUE 约束条件在 ALTER TABLE EXCHANGE PARTITION 中不匹配

--因为上边创建primary key时,分区表和非分区表创建的不一样

 

SQL> drop constraint ind_t_no_par;

drop constraint ind_t_no_par

     *

1 行出现错误:

ORA-00950: 无效 DROP 选项

 

 

SQL> alter table t_no_par drop constraint ind_t_no_par;

 

表已更改。

 

SQL> select constraint_name,constraint_type,status,validated from User_Constraints where INDEX_name in ('IND_T_NO_PAR','IND_T_PAR_RANGE');

 

CONSTRAINT_NAME                C STATUS   VALIDATED                            

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

IND_T_PAR_RANGE                P ENABLED  VALIDATED                            

 

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

 

INDEX_NAME                     PARTITION_NAME                 STATUS           

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

IND_T_PAR_RANGE                P_T_PAR_RANGE_0                USABLE           

IND_T_PAR_RANGE                P_T_PAR_RANGE_1                USABLE           

 

SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR');

 

未选定行

 

SQL> alter table t_no_par add constraint ind_t_no_par primary key(time,id) using index;

 

表已更改。

 

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

 

INDEX_NAME                     PARTITION_NAME                 STATUS           

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

IND_T_PAR_RANGE                P_T_PAR_RANGE_0                USABLE           

IND_T_PAR_RANGE                P_T_PAR_RANGE_1                USABLE           

 

SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR');

 

INDEX_NAME                     INDEX_TYPE                  STATUS              

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

IND_T_NO_PAR                   NORMAL                      VALID               

 

SQL> select constraint_name,constraint_type,status,validated from User_Constraints where INDEX_name in ('IND_T_NO_PAR','IND_T_PAR_RANGE');

 

CONSTRAINT_NAME                C STATUS   VALIDATED                            

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

IND_T_PAR_RANGE                P ENABLED  VALIDATED                            

IND_T_NO_PAR                   P ENABLED  VALIDATED                            

 

SQL> select * from t_no_par;

 

未选定行

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

01-4 -11              2         20          2 b                              

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes;

 

表已更改。

 

SQL> select * from t_no_par;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-4 -11              2         20          2 b                               

 

SQL> select constraint_name,constraint_type,status,validated from User_Constraints where INDEX_name in ('IND_T_NO_PAR','IND_T_PAR_RANGE');

 

CONSTRAINT_NAME                C STATUS   VALIDATED                            

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

IND_T_PAR_RANGE                P ENABLED  VALIDATED                            

IND_T_NO_PAR                   P ENABLED  VALIDATED                            

 

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

 

INDEX_NAME                     PARTITION_NAME                 STATUS           

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

IND_T_PAR_RANGE                P_T_PAR_RANGE_0                USABLE           

IND_T_PAR_RANGE                P_T_PAR_RANGE_1                USABLE           

 

SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR');

 

INDEX_NAME                     INDEX_TYPE                  STATUS              

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

IND_T_NO_PAR                   NORMAL                      VALID               

 

SQL> select * from t_no_par;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                               

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-4 -11              2         20          2 b                               

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes;

 

表已更改。

 

SQL> select * from t_no_par;

 

未选定行

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

01-4 -11              2         20          2 b                              

 

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

 

INDEX_NAME                     PARTITION_NAME                 STATUS           

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

IND_T_PAR_RANGE                P_T_PAR_RANGE_0                USABLE           

IND_T_PAR_RANGE                P_T_PAR_RANGE_1                USABLE           

 

SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR');

 

INDEX_NAME                     INDEX_TYPE                  STATUS              

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

IND_T_NO_PAR                   NORMAL                      VALID               

 

SQL> select constraint_name,constraint_type,status,validated from User_Constraints where INDEX_name in ('IND_T_NO_PAR','IND_T_PAR_RANGE');

 

CONSTRAINT_NAME                C STATUS   VALIDATED                            

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

IND_T_PAR_RANGE                P ENABLED  VALIDATED                            

IND_T_NO_PAR                   P ENABLED  VALIDATED                             

 

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

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

注册时间:2009-05-13

  • 博文量
    94
  • 访问量
    350385