oracle 10g分区表,oracle10g--11gR2分区表汇总五

3.2.7.3 (分区表diable keep index)—(非分区表disable keep index)—exchange--exchange

解释：

首先：分区表primary key是disable的，非分区表primary key是disable的；

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

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

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

INDEX_NAMEPARTITION_NAMESTATUS

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

IND_T_PAR_RANGEP_T_PAR_RANGE_0USABLE

IND_T_PAR_RANGEP_T_PAR_RANGE_1USABLE

SQL> drop index ind_t_par_range;

索引已删除。

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

表已更改。

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

表已更改。

SQL> select * from t_no_par;

未选定行

SQL> select * from t_par_range;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a

01-4月-112202 b

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

CONSTRAINT_NAMEC STATUSVALIDATED

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

IND_T_PAR_RANGEP ENABLEDVALIDATED

IND_T_NO_PARP ENABLEDVALIDATED

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

INDEX_NAMEINDEX_TYPESTATUS

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

IND_T_NO_PARNORMALVALID

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

INDEX_NAMEPARTITION_NAMESTATUS

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

IND_T_PAR_RANGEP_T_PAR_RANGE_0USABLE

IND_T_PAR_RANGEP_T_PAR_RANGE_1USABLE

SQL>

SQL> alter table t_no_par disable constraint ind_t_no_par keep index;

表已更改。

SQL> alter table t_par_range disable constraint ind_t_par_range keep index;

表已更改。

SQL>

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

INDEX_NAMEPARTITION_NAMESTATUS

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

IND_T_PAR_RANGEP_T_PAR_RANGE_0USABLE

IND_T_PAR_RANGEP_T_PAR_RANGE_1USABLE

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

CONSTRAINT_NAMEC STATUSVALIDATED

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

IND_T_PAR_RANGEP DISABLEDNOT VALIDATED

IND_T_NO_PARP DISABLEDNOT VALIDATED

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

INDEX_NAMEINDEX_TYPESTATUS

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

IND_T_NO_PARNORMALVALID

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

表已更改。

SQL>

SQL> select * from t_no_par;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a

SQL> select * from t_par_range;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-4月-112202 b

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

CONSTRAINT_NAMEC STATUSVALIDATED

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

IND_T_PAR_RANGEP DISABLEDNOT VALIDATED

IND_T_NO_PARP DISABLEDNOT VALIDATED

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

INDEX_NAMEINDEX_TYPESTATUS

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

IND_T_NO_PARNORMALVALID

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

INDEX_NAMEPARTITION_NAMESTATUS

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

IND_T_PAR_RANGEP_T_PAR_RANGE_0USABLE

IND_T_PAR_RANGEP_T_PAR_RANGE_1USABLE

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;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a

01-4月-112202 b

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

CONSTRAINT_NAMEC STATUSVALIDATED

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

IND_T_PAR_RANGEP DISABLEDNOT VALIDATED

IND_T_NO_PARP DISABLEDNOT VALIDATED

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

INDEX_NAMEINDEX_TYPESTATUS

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

IND_T_NO_PARNORMALVALID

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

INDEX_NAMEPARTITION_NAMESTATUS

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

IND_T_PAR_RANGEP_T_PAR_RANGE_0USABLEIND_T_PAR_RANGEP_T_PAR_RANGE_1USABLE