在oracle 11.2环境下测试
--drop table tab_a purge;
--创建分区表
createtable tab_a
(
number(19)primarykey,
varchar2(300),
integer
)
partitionbylist(r_pat)
(
partitionvalues(1),
partitionvalues(2),
partitionvalues(3),
partitionvalues(default)
)
;
--创建普遍表
createtableasselect*from;
--创建主键
altertableaddprimarykey(r_id);
---插入测试数据
insertinto tab_a
(r_id,r_name,r_pat)
select,a.OBJECT_NAME,1from;
insertintoselect99199999,'test',1from;
commit;
--测试分区交换
----1.分区表，创建了主键索引，普通表没有创建，则
altertableexchangepartitionwithtableincludingindexeswithoutvalidation
--ORA-14097: ALTER TABLE EXCHANGE PARTITION 中的列类型或大小不匹配
---2.分区表,创建了主键索引，普通表也创建全局索引则
altertableaddprimarykey(r_id);
altertableexchangepartitionwithtableincludingindexeswithoutvalidationupdateglobalindexes;
--ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
--3.分区表,创建了主键索引，普通表也创建主键索引则,且不包含索引交换
altertableexchangepartitionwithtable/*including indexes*/withoutvalidation/*update global indexes*/;
--成功---
--但是 insert into tab_a select 99199999,'test',1 from dual;
--ORA-01502: 索引 'SCOTT.SYS_C0012090' 或这类索引的分区处于不可用状态
---这时需要重建索引才能更新数据 ----
alterindexrebuild;
insertintoselect99399999,'test',1from;
---成功，，，
--4.分区表,创建了主键索引，普通表也创建主键索引则,且不包含索引交换，更新全局索引
truncatetable;(/*清理分区不行*/)
insertintoselect99199999,'test',1from;
commit;
altertableexchangepartitionwithtable/*including indexes*/withoutvalidationupdateglobalindexes;
insertintoselect99399999,'test',1from;
---成功，，，
---但是更新普通表数据时，insert into tab_b select 99399999,'test',1 from dual 时，
--ORA-01502: 索引 'SCOTT.SYS_C0012090' 或这类索引的分区处于不可用状态
--------------------------
---5.在分区表的非分区表键上建立全局索引，普通表也建立全局索引
altertabledropprimarykey;
altertabledropprimarykey;
createindexon(r_id);
createindexon(r_id);
altertableexchangepartitionwithtableincludingindexeswithoutvalidation;
-----ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
---6.在分区表的非分区表键上建立全局索引，普通表不建立全局索引
dropindex;
altertableexchangepartitionwithtableincludingindexeswithoutvalidation;
---成功
-----7.在分区表的非分区键上建立本地索引，普通表不创建索引
dropindex;
createindexon(r_id)local;
altertableexchangepartitionwithtableincludingindexeswithoutvalidation;
--ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
-----8.在分区表的非分区键上建立本地索引，普通表创建索引
createindexon(r_id);
altertableexchangepartitionwithtableincludingindexeswithoutvalidation;
---成功
---9.在分区表的分区键上创建全局索引，普通表创建索引
dropindex;
dropindex;
createindexon(r_pat);
createindexon(r_pat);
altertableexchangepartitionwithtableincludingindexeswithoutvalidation;
--ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
---10.在分区表的分区键上创建全局索引，普通表不创建索引
dropindex;
dropindex;
createindexon(r_pat);
altertableexchangepartitionwithtableincludingindexeswithoutvalidation;
---成功
-----11.在分区表的分区键上创建本地索引，普通表不创建索引
dropindex;
createindexon(r_pat)local;
altertableexchangepartitionwithtableincludingindexeswithoutvalidation;
--ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
----12.在分区表的分区键上创建本地索引，普通表创建索引
createindexon(r_pat);
altertableexchangepartitionwithtableincludingindexeswithoutvalidation;
---成功
---
---13.在分区表上的分区键和非分区键上创建全局索引，普通表上创建索引
drop indexidx_a_r_id;
dropindexidx_b_r_id;
createindexidx_a_r_idontab_ar_id,r_pat);
createindexidx_b_r_idontab_br_id,r_pat);
altertabletab_aexchangepartitionwithtabletab_bincludingindexeswithoutvalidation;
--报错
---14.在分区表上的分区键和非分区键上创建全局索引，普通表不创建索引
dropindexidx_b_r_id;
altertabletab_aexchangepartitionwithtabletab_bincludingindexeswithoutvalidation;
---成功
--15.在分区表上的分区键和非分区键上创建本地索引，普通表不创建索引
dropindexidx_a_r_id;
createindexidx_a_r_idontab_a(r_id,r_pat)local;
altertabletab_aexchangepartitionwithtabletab_bincludingindexeswithoutvalidation;
---报错
--16.在分区表上的分区键和非分区键上创建本地索引，普通表创建索引
createindexidx_b_r_idontab_b(r_id,r_pat);
altertabletab_aexchangepartitionwithtabletab_bincludingindexeswithoutvalidation;---成功
---17.在分区表上的分区键和非分区键上创建本地索引a，同时又在非分区键上创全局建索引b,普通表对应字段上都创建索引
dropindexidx_a_r_id;
dropindexidx_b_r_id;
createindexidx_a_r_idontab_a(r_id);
createindexidx_b_r_idontab_b(r_id);
createindexidx_a_r_id_loc ontab_a(r_id,r_pat)local;
createindexidx_b_r_id_loc ontab_b(r_id,r_pat);
altertabletab_aexchangepartitionwithtabletab_bincludingindexeswithoutvalidation;--报错
---18.在分区表上的分区键和非分区键上创建本地索引a，同时又在非分区键上创建全局索引b,
--在普通表对应的分区表上的本地索引a的字段上创建索引，同时分区表的全局索引对应的字段上不创建索引
dropindexidx_a_r_id;
dropindexidx_b_r_id;
dropindexidx_a_r_id;
dropindexidx_b_r_id;
createindexidx_a_r_idontab_a(r_id);
createindexa_r_id_loc ontab_ar_id,r_pat)local;
createindexidx_b_r_idontab_br_id,r_pat);
altertabletab_aexchangepartitionwithtabletab_bincludingindexeswithoutvalidation;--成功
----以上创建普通表的索引时，都是跟分区表的字段相对应
