对分区表添加子分区
一、需求
开发人员提出需求，有一个财务表，需要插入2019年新的数据进行报销，但是分区表中没有容纳的分区，因此需要添加一个分区。 经过get_ddl语法，得到该表的创建语法。 现有情况：财务表A： 地市列表，报销时间字段。 第一个分区part_a,第一个列表1,报销时间2018年1月前的数据； 第二个分区Part_b,第一个列表2，报销时间2018年1月前的数据； 第二个分区part_b,第一个列表2，报销时间2018年9月份数据； ？？？此时，需要将2019年的报销数据，插入第二个分区中，无法插入，报错 ORA-14400: inserted partition key does not map to any partition
二、模拟测试环境
1）在11.2.0.4版本中，模拟创建列表-列表，复合分区
2）创建不同类型的索引（全局索引，全局分区索引，本地索引），为了模拟添加分区后，是否会对索引造成影响
2.1测试表
create table range_list_part_tab(id number,deal_date date,area_code varchar2(12),contents varchar2(4000))
partition by range(area_code)
subpartition by range(deal_date)
(
partition p1 values less than('952')
(subpartition "part1_20181205" values less than (to_date('2018-12-16 00:00:00','YYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')),
subpartition "part1_20190105" values less than (to_date('2019-01-05 00:00:00','YYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))),
partition p2 values less than('970')
(subpartition "part2_20181205" values less than (to_date('2018-12-16 00:00:00','YYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')))
)
;
select PARTITION_NAME,SUBPARTITION_COUNT,HIGH_VALUE,HIGH_VALUE_LENGTH,PARTITION_POSITION,
TABLESPACE_NAME from dba_tab_partitions where table_owner='SYS' and table_name=upper('range_list_part_tab')
PARTITION_ SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME
---------- ------------------ -------------------- ----------------- ------------------ ------------------------------------------------------------
P1 3 '952' 5 1 SYSTEM
P2 1 '970' 5 2 SYSTEM
SYS@a11204>select PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,SUBPARTITION_POSITION,TABLESPACE_NAME from dba_tab_subpartitions where table_owner='SYS'
and table_name=upper('range_list_part_tab');
PARTITION_ SUBPARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH SUBPARTITION_POSITION TABLESPACE_NAME
---------- ------------------------------------------------------------ -------------------- ----------------- -------------------
P1 part1_20181205 TO_DATE(' 2018-12-16 83 1 SYSTEM
P1 part1_20190105 TO_DATE(' 2019-01-05 83 2 SYSTEM
P1 PART1_MAX MAXVALUE 8 3 SYSTEM
P2 part2_20181205 TO_DATE(' 2018-12-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 83 1 SYSTEM
insert into range_list_part_tab values(20,sysdate,'952','qq');
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
insert into range_list_part_tab values(10,sysdate-365,'953','weixin');
2.2 创建索引
create index IND_GLOBAL_A on range_list_part_tab(id);
create index IND_PART_GLOBAL_B on range_list_part_tab(deal_date) global partition by range(deal_date)
(partition p1 values less than (to_date('2018-12-16 00:00:00','YYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) tablespace system,
partition p2 values less than (to_date('2019-01-05 00:00:00','YYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) tablespace users,
partition p3 values less than (maxvalue) tablespace sysaux);
create index IND_PART_LOCAL_C on range_list_part_tab(area_code) local;
全局索引
SQL> select index_name,STATUS,GLOBAL_STATS,TABLESPACE_NAME from dba_indexes where owner='SYS' and index_name='IND_GLOBAL_A';
INDEX_NAME STATUS GLOBAL TABLESPACE_NAME
------------------------------ ---------------- ------ ------------------------------------------------------------
IND_GLOBAL_A VALID YES SYSTEM
全局本地索引
SYS@a11204>select index_name,PARTITIONING_TYPE,SUBPARTITIONING_TYPE,PARTITION_COUNT,LOCALITY,DEF_TABLESPACE_NAME from DBA_PART_INDEXES where index_name='IND_PART_GLOBAL_B';
INDEX_NAME PARTITIONING_TYPE SUBPARTITIONING_TY PARTITION_COUNT LOCALITY DEF_TABLESPACE_NAME
-------------------- ------------------ ------------------ --------------- ------------ ------------------------------------------------------------
IND_PART_GLOBAL_B RANGE NONE 3 GLOBAL SYSTEM
select index_name,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_owner='SYS' and index_name='IND_PART_GLOBAL_B'
INDEX_NAME PARTITION_ HIGH_VALUE STATUS TABLESPACE_NAME
-------------------- ---------- -------------------------------------------------------------------------------- ---------------- ------------------------------------------------------------
IND_PART_GLOBAL_B P1 TO_DATE(' 2018-12-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEM
IND_PART_GLOBAL_B P2 TO_DATE(' 2019-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE USERS
IND_PART_GLOBAL_B P3 MAXVALUE
select index_name,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_owner='SYS' and index_name='IND_PART_LOCAL_C';
INDEX_NAME PARTITION_ HIGH_VALUE STATUS TABLESPACE_NAME
-------------------- ---------- -------------------- ---------------- ------------------------------------------------------------
IND_PART_LOCAL_C P1 '952' N/A
IND_PART_LOCAL_C P2 '970' N/A
本地索引
SELECT INDEX_NAME,PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,SUBPARTITION_POSITION,STATUS,TABLESPACE_NAME FROM DBA_IND_SUBPARTITIONS WHERE INDEX_OWNER='SYS' and INDEX_NAME='IND_PART_LOCAL_C'
INDEX_NAME PARTITION_ SUBPARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH SUBPARTITION_POSITION STATUS TABLESPACE_NAME
-------------------- ---------- -------------------- -------------------- ----------------- --------------------- ---------------- -----------------------------------------------------------
IND_PART_LOCAL_C P1 part1_20181205 TO_DATE(' 2018-12-16 83 1 USABLE SYSTEM
IND_PART_LOCAL_C P1 part1_20190105 TO_DATE(' 2019-01-05 83 2 USABLE SYSTEM
IND_PART_LOCAL_C P2 part2_20181205 TO_DATE(' 2018-12-16 83 1 USABLE SYSTEM
SYS@ceshi>select index_name,table_name,PARTITIONING_TYPE,SUBPARTITIONING_TYPE,PARTITION_COUNT,LOCALITY,DEF_TABLESPACE_NAME from DBA_PART_INDEXES where index_name='IND_PART_LOCAL_C';
INDEX_NAME TABLE_NAME PARTITIONING_TYPE SUBPARTITIONING_TY PARTITION_COUNT LOCALITY DEF_TABLESPACE_NAME
-------------------- ------------------------------ ------------------ ------------------ --------------- ------------ ------------------------------------------------------------
IND_PART_LOCAL_C RANGE_LIST_PART_TAB RANGE RANGE 2 LOCAL
三、添加子分区操作
ALTER TABLE range_list_part_tab
MODIFY PARTITION p1
ADD SUBPARTITION part1_max VALUES LESS THAN (MAXVALUE) ;
insert into range_list_part_tab values(20,sysdate,'950','qq');
09:28:42 SYS@a11204>COMMIT;
再次查询上述三个索引视图：均为有效，因此可忽略。添加子分区对索引无影响。
***其它**************
删除子分区
SYS@a11204>ALTER TABLE range_list_part_tab drop SUBPARTITION part1_max ;
Table altered.
删除子分区，子分区数据均自动删除
select * from range_list_part_tab
ID DEAL_DATE AREA_CODE CONTENTS
---------- ------------------- ------------------------ --------------------
10 2018-02-26 09:17:19 953 weixin
ALTER TABLE range_list_part_tab MODIFY PARTITION p1 ADD SUBPARTITION part1_max VALUES LESS THAN (MAXVALUE) update global index ;
***添加分区，申请的锁资源*******************
session_1
insert into range_list_part_tab values(30,sysdate-90,'940','en');
insert into range_list_part_tab values(80,'2018-12-01','960','ha');
session_2
SQL> alter session set ddl_lock_timeout=600;
ALTER TABLE range_list_part_tab MODIFY PARTITION p1 ADD SUBPARTITION part1_max VALUES LESS THAN (MAXVALUE) ;
不受影响，并没有对表申请很严重的锁。
具体分区表添加子分区，申请的锁资源，继续测试。
session_1
10:57:33 SYS@a11204>LOCK TABLE range_list_part_tab IN EXCLUSIVE MODE;
session_2，被阻塞
SQL> SQL> ALTER TABLE range_list_part_tab MODIFY PARTITION p1 ADD SUBPARTITION part1_max VALUES LESS THAN (MAXVALUE) ;
查询锁模式类型
SQL> select sid,type,id1,lmode,request,ctime,block from v$Lock where type in('TX','TM');
SID TYPE ID1 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ----------
135 TM 18 3 0 47 0
135 TM 98881 0 3 47 0
130 TM 98881 6 0 72 1
135 TX 262159 6 0 47 0
select owner,object_name,object_type,object_id from dba_objects where object_id in('18','98881')
OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID
-------------------- -------------------- -------------------- ----------
SYS RANGE_LIST_PART_TAB TABLE 98881
SYS OBJ$ TABLE 18
添加子分区的操作，需要申请RANGE_LIST_PART_TAB 表的TM3号锁，测试表提前添加TM6号锁，被阻塞。因此添加子分区的操作，等同于dml申请TM锁需求，影响很小。
***********对列表子分区操作，无法执行update global维护索引操作，还好之前测试索引不受影响，添加子分区对索引无影响。
ALTER TABLE range_list_part_tab MODIFY PARTITION p1 ADD SUBPARTITION part1_max VALUES LESS THAN (MAXVALUE) update global indexes ;
*
ERROR at line 1:
ORA-14633: Index maintainence clause not allowed for ADD list subpartition to a
Composite partitioned table
