對分區表添加子分區

一、需求

開發人員提出需求，有一個财務表，需要插入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

posted on 2019-02-27 00:05 綠茶有點甜閱讀(...) 評論(...) 編輯收藏

對分區表添加子分區

對分區表添加子分區

繼續閱讀

關閉或開啟memory_target

線上排錯之curl指令詳解

DG_資料檔案轉換參數測試

建立新表，自動授權trigger

25個Linux日常運維指令答題測試

利用wrk工具壓測騰訊CLB

centos7 做ftp伺服器

postgresql 10.10部署timesacledb1.3.2

10.9-檢視網絡狀态

使用Spring Cloud Stream的事件驅動架構（利用kafka和redis，學習筆記）

Linux 系統 9 大技能，搞定 90% 日常運維

為ThinkCentre M910t-NO76重裝Windows7（64位）系統

快速識别桌上型電腦的記憶體條

一些超實用的 Kubernetes 日常運維常用指令，建議收藏

eNSP三層交換機配置-01