Troubleshooting 19c ORA-1: unique constraint (sys.i_indpart_bopart$) during ALTER TABLE SPLIT PARTITION
上一篇blog中提到LOCAL 分区，分区索引part#和分区表part#是相等的，上一篇在还原那个问题时，让part#占用时发现还不是那么简单，如果用10046跟split partition,时而insert,时而存在update, 其实oracle在这方面也是做了优化， 在split分区位置和个数也有性能上的细微的差别。
SQL> @ddl anbob.t
PL/SQL procedure successfully completed.
DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
-------------------------------------------------------------------------------------------------------------
CREATE TABLE "ANBOB"."T"
( "ID" NUMBER(*,0),
"CTIME" DATE,
"NAME" VARCHAR2(100)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("CTIME")
(PARTITION "P1" VALUES LESS THAN (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P2" VALUES LESS THAN (TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P3" VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P4" VALUES LESS THAN (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P5" VALUES LESS THAN (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P6" VALUES LESS THAN (TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P7" VALUES LESS THAN (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P8" VALUES LESS THAN (TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P9" VALUES LESS THAN (TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P10" VALUES LESS THAN (TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P11" VALUES LESS THAN (TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P12" VALUES LESS THAN (TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P13" VALUES LESS THAN (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P14" VALUES LESS THAN (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P15" VALUES LESS THAN (TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P16" VALUES LESS THAN (TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P17" VALUES LESS THAN (TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P18" VALUES LESS THAN (TO_DATE(' 2016-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P19" VALUES LESS THAN (TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P20" VALUES LESS THAN (TO_DATE(' 2016-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
ROW STORE COMPRESS ADVANCED LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ) ;
1 row selected.
SQL> @ind anbob.t_i1
Display indexes where table or index name matches %anbob.t_i1%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB T T_I1 1 CTIME
2 NAME
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
ANBOB T T_I1 NORMAL NO N/A YES N 3 4314 1000000 1000000 195853 2021-12-18 22:00:50 1 VISIBLE
SQL> @o anbob.t_i1
owner object_name subname object_type status OID D_OID CREATED LAST_DDL_TIME
------------------------- ------------------------------ ------------------------------ -------------------- --------- ---------- ---------- ------------------- -------------------
ANBOB T_I1 INDEX VALID 78788 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P10 INDEX PARTITION VALID 78798 78798 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P11 INDEX PARTITION VALID 78799 78799 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P12 INDEX PARTITION VALID 78800 78800 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P13 INDEX PARTITION VALID 78801 78801 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P14 INDEX PARTITION VALID 78802 78802 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P15 INDEX PARTITION VALID 78803 78803 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P16 INDEX PARTITION VALID 78804 78804 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P17 INDEX PARTITION VALID 78805 78805 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P18 INDEX PARTITION VALID 78806 78806 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P19 INDEX PARTITION VALID 78807 78807 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P2 INDEX PARTITION VALID 78790 78790 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P20 INDEX PARTITION VALID 78947 78950 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P3 INDEX PARTITION VALID 78791 78791 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P4 INDEX PARTITION VALID 78792 78792 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P5 INDEX PARTITION VALID 78793 78793 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P6 INDEX PARTITION VALID 78794 78794 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P7 INDEX PARTITION VALID 78795 78795 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P8 INDEX PARTITION VALID 78796 78796 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P9 INDEX PARTITION VALID 78797 78797 2021-11-19 15:14:56 2021-11-19 15:14:56
ANBOB T_I1 P1 INDEX PARTITION VALID 78789 78789 2021-11-19 15:14:56 2021-11-19 15:14:56
21 rows selected.
SQL> r
1* select obj#,part# ,SUBOBJECT_NAME from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1'
OBJ# PART# subname
---------- ---------- ------------------------------
78790 20 P2
78791 30 P3
78792 40 P4
78793 50 P5
78794 60 P6
78795 70 P7
78796 80 P8
78797 90 P9
78789 10 P1
78798 100 P10
78799 110 P11
78800 120 P12
78801 130 P13
78802 140 P14
78803 150 P15
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
78947 200 P20
20 rows selected.
Note:
能看到创建后默认part# 间隔为10.
1, 增加分区
SQL> alter table anbob.t add partition p30 values less than (to_date('20200101','yyyymmdd'));
Table altered.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1';
OBJ# PART# subname
---------- ---------- ------------------------------
...
78802 140 P14
78803 150 P15
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
78947 200 P20
79108 210 P30
21 rows selected.
Note：
增加分区还是part#增加10
2， 从最大分区拆
SQL> alter table anbob.t split partition p30 into(partition p21 values less than (to_date('20190101','yyyymmdd')),partition p30);
Table altered.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' order by 2;
OBJ# PART# subname
---------- ---------- ------------------------------
...
...
78805 170 P17
78806 180 P18
78807 190 P19
78947 200 P20
79112 211 P21
79111 221 P30
22 rows selected.
SQL> alter table anbob.t split partition p30 into(partition p22 values less than (to_date('20190201','yyyymmdd')),partition p30);
Table altered.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' order by 2;
OBJ# PART# subname
---------- ---------- ------------------------------
...
78803 150 P15
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
78947 200 P20
79112 211 P21
79116 222 P22
79115 232 P30
23 rows selected.
SQL> alter table anbob.t split partition p30 into(partition p23 values less than (to_date('20190301','yyyymmdd')),partition p30);
Table altered.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' order by 2;
OBJ# PART# subname
---------- ---------- ------------------------------
78789 10 P1
78790 20 P2
78791 30 P3
78792 40 P4
78793 50 P5
78794 60 P6
78795 70 P7
78796 80 P8
78797 90 P9
78798 100 P10
78799 110 P11
78800 120 P12
78801 130 P13
78802 140 P14
78803 150 P15
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
78947 200 P20
79112 211 P21
79116 222 P22
79120 233 P23
79119 243 P30
24 rows selected.
SQL> alter table anbob.t split partition p30 into(partition p24 values less than (to_date('20190401','yyyymmdd')),partition p30);
Table altered.
OBJ# PART# subname
---------- ---------- ------------------------------
...
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
78947 200 P20
79112 211 P21
79116 222 P22
79120 233 P23
79124 244 P24
79123 254 P30
25 rows selected.
... 就以上规律继续拆
SQL> alter table anbob.t split partition p30 into(partition p28 values less than (to_date('20190801','yyyymmdd')),partition p30);
Table altered.
OBJ# PART# subname
---------- ---------- ------------------------------
...
78803 150 P15
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
78947 200 P20
79112 211 P21
79116 222 P22
79120 233 P23
79124 244 P24
79128 255 P25
79132 266 P26
79136 277 P27
79140 288 P28
79139 298 P30
29 rows selected.
SQL> alter table anbob.t split partition p30 into(partition p29 values less than (to_date('20190901','yyyymmdd')),partition p30);
Table altered.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' order by 1;
OBJ# PART# subname
---------- ---------- ------------------------------
...
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
78947 200 P20
79112 211 P21
79116 222 P22
79120 233 P23
79124 244 P24
79128 255 P25
79132 266 P26
79136 277 P27
79140 288 P28
79144 299 P29
79143 309 P30
30 rows selected.
SQL> alter table anbob.t split partition p30 into(partition p29_1 values less than (to_date('20191001','yyyymmdd')),partition p30);
Table altered.
OBJ# PART# subname
---------- ---------- ------------------------------
...
78805 170 P17
78806 180 P18
78807 190 P19
78947 200 P20
79112 211 P21
79116 222 P22
79120 233 P23
79124 244 P24
79128 255 P25
79132 266 P26
79136 277 P27
79140 288 P28
79144 299 P29
79148 310 P29_1
79147 320 P30
31 rows selected.
note:
如果10046 跟的话是delete 1条，insert 2条， 看到part# 规律是 split X into (PARTITION Y , PARTITION X), X的part#是原part#+10+1, Y的part#是新x的part# -10.
3, 从中间分区拆
SQL> alter table anbob.t split partition p20 into(partition p19_1 values less than (to_date('20160701 01','yyyymmdd hh24')),partition p20);
Table altered.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' order by 2;
OBJ# PART# subname
---------- ---------- ------------------------------
...
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
79152 199 P19_1
79151 201 P20
79112 211 P21
79116 222 P22
79120 233 P23
79124 244 P24
79128 255 P25
79132 266 P26
79136 277 P27
79140 288 P28
79144 299 P29
79147 320 P30
79148 310 P29_1
32 rows selected.
SQL> alter table anbob.t split partition p20 into(partition p19_2 values less than (to_date('20160701 02','yyyymmdd hh24')),partition p20);
Table altered.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' order by 2;
OBJ# PART# subname
---------- ---------- ------------------------------
...
78805 170 P17
78806 180 P18
78807 190 P19
79152 199 P19_1
79156 200 P19_2
79155 202 P20
79112 211 P21
79116 222 P22
79120 233 P23
79124 244 P24
79128 255 P25
79132 266 P26
79136 277 P27
79140 288 P28
79144 299 P29
79148 310 P29_1
79147 320 P30
33 rows selected.
SQL> alter table anbob.t split partition p20 into(partition p19_3 values less than (to_date('20160701 03','yyyymmdd hh24')),partition p20);
Table altered.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;
OBJ# PART# subname
---------- ---------- ------------------------------
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
79152 199 P19_1
79156 200 P19_2
79160 201 P19_3
79159 203 P20
79112 211 P21
79116 222 P22
79120 233 P23
79124 244 P24
79128 255 P25
79132 266 P26
79136 277 P27
79140 288 P28
79144 299 P29
79148 310 P29_1
79147 320 P30
19 rows selected.
SQL> alter table anbob.t split partition p20 into(partition p19_4 values less than (to_date('20160701 04','yyyymmdd hh24')),partition p20);
Table altered.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;
OBJ# PART# subname
---------- ---------- ------------------------------
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
79152 199 P19_1
79156 200 P19_2
79160 201 P19_3
79164 202 P19_4
79163 204 P20
79112 211 P21
79116 222 P22
79120 233 P23
79124 244 P24
79128 255 P25
79132 266 P26
79136 277 P27
79140 288 P28
79144 299 P29
79148 310 P29_1
79147 320 P30
20 rows selected.
SQL> alter table anbob.t split partition p20 into(partition p19_5 values less than (to_date('20160701 05','yyyymmdd hh24')),partition p20);
Table altered.
SQL> alter table anbob.t split partition p20 into(partition p19_6 values less than (to_date('20160701 06','yyyymmdd hh24')),partition p20);
Table altered.
SQL> alter table anbob.t split partition p20 into(partition p19_7 values less than (to_date('20160701 07','yyyymmdd hh24')),partition p20);
Table altered.
SQL> alter table anbob.t split partition p20 into(partition p19_8 values less than (to_date('20160701 08','yyyymmdd hh24')),partition p20);
Table altered.
SQL> alter table anbob.t split partition p20 into(partition p19_9 values less than (to_date('20160701 09','yyyymmdd hh24')),partition p20);
Table altered.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;
OBJ# PART# subname
---------- ---------- ------------------------------
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
79152 199 P19_1
79156 200 P19_2
79160 201 P19_3
79164 202 P19_4
79168 203 P19_5
79172 204 P19_6
79176 205 P19_7
79180 206 P19_8
79184 207 P19_9
79183 209 P20
79112 211 P21
79116 222 P22
79120 233 P23
79124 244 P24
79128 255 P25
79132 266 P26
79136 277 P27
79140 288 P28
79144 299 P29
79148 310 P29_1
79147 320 P30
25 rows selected.
SQL> alter table anbob.t split partition p20 into(partition p19_10 values less than (to_date('20160701 10','yyyymmdd hh24')),partition p20);
Table altered.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;
OBJ# PART# subname
---------- ---------- ------------------------------
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
79152 199 P19_1
79156 200 P19_2
79160 201 P19_3
79164 202 P19_4
79168 203 P19_5
79172 204 P19_6
79176 205 P19_7
79180 206 P19_8
79184 207 P19_9
79188 208 P19_10
79187 210 P20
79112 211 P21
79116 222 P22
79120 233 P23
79124 244 P24
79128 255 P25
79132 266 P26
79136 277 P27
79140 288 P28
79144 299 P29
79148 310 P29_1
79147 320 P30
26 rows selected.
Note:
注意到拆中间的分区时，看到part# 规律是 split X into (PARTITION Y , PARTITION X), X的part# 是原X的part#+1, Y的part#是原X的part#-1. 也就是相差2， 但这有一个问题，就是如果X的值，每次增加1，如果把开始预留的10个part#耗尽了呢？
4， 中间拆分区， X的next part# 已使用时
SQL> alter table anbob.t split partition p20 into(partition p19_11 values less than (to_date('20160701 11','yyyymmdd hh24')),partition p20);
Table altered.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;
OBJ# PART# subname
---------- ---------- ------------------------------
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
79152 199 P19_1
79156 200 P19_2
79160 201 P19_3
79164 202 P19_4
79168 203 P19_5
79172 204 P19_6
79176 205 P19_7
79180 206 P19_8
79184 207 P19_9
79188 208 P19_10
79192 209 P19_11
79191 210 P20
79112 211 P21
79116 222 P22
79120 233 P23
79124 244 P24
79128 255 P25
79132 266 P26
79136 277 P27
79140 288 P28
79144 299 P29
79148 310 P29_1
79147 320 P30
27 rows selected.
Note:
X如果part# next+1的方式在下一个号被使用时， 看到part# 规律是 split X into (PARTITION Y , PARTITION X), X的part#未改变，Y的part#是X的PART#-1， 这样x与y的part#就相差1， 如果再拆就面临X 如果不增加，就把Y next part#占用的情况，oracle会如何做呢？
5, 中间拆分区， Y的next part#补占用时
SQL> alter table anbob.t split partition p20 into(partition p19_12 values less than (to_date('20160701 12','yyyymmdd hh24')),partition p20);
Table altered.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;
OBJ# PART# subname
---------- ---------- ------------------------------
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
79152 199 P19_1
79156 200 P19_2
79160 201 P19_3
79164 202 P19_4
79168 203 P19_5
79172 204 P19_6
79176 205 P19_7
79180 206 P19_8
79184 207 P19_9
79188 208 P19_10
79192 209 P19_11
79196 219 P19_12
79195 229 P20
79112 239 P21
79116 249 P22
79120 259 P23
79124 269 P24
79128 279 P25
79132 289 P26
79136 300 P27
79140 311 P28
79144 321 P29
79148 331 P29_1
79147 341 P30
28 rows selected.
Note:
Y next+1, X在第10次未+1,y next part#被使用时， 看到part# 规律是 split X into (PARTITION Y , PARTITION X), Y的part# 是原来X的part#+10-1,而后面所有的分区part#基本都增加了10, 但是， 但是p27这个300 打破了规律，有点蒙。
10046中显示也是直接 update 成300,无征兆。
就到这里， 2021年最后一篇。
— enjoy —