对分区表添加子分区

一、需求

开发人员提出需求，有一个财务表，需要插入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