首页 » ORACLE, ORACLE [C]系列 » Oracle partition part# 如何增长

Oracle partition part# 如何增长

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 —

打赏

目前这篇文章还没有评论(Rss)

我要评论