Good day to all! Help solve this issue. I create a partitioned table in which there are partitions and subpartitions (by dates, the table is divided into years, and years are divided into quarters) I create it this way:

  CREATE TABLE test_composite
 (str_kv VARCHAR2 (100 BYTE) NOT NULL,
 d_1 date not null,
 d_2 date not null) 

 PARTITION BY RANGE (d_1)
 SUBPARTITION BY RANGE (d_2)
 (
     PARTITION t_2016 VALUES LESS THAN (TO_DATE ('01 .01.2017 ',' DD.MM.YYYY '))
       (
         SUBPARTITION t_20161 VALUES LESS THAN (TO_DATE ('01 .04.2016 ',' DD / MM / YYYY ')),
         SUBPARTITION t_20162 VALUES LESS THAN (TO_DATE ('01 .07.2016 ',' DD / MM / YYYY ')),
         SUBPARTITION t_20163 VALUES LESS THAN (TO_DATE ('01. 10.2016 ',' DD / MM / YYYY ')),
         SUBPARTITION t_20164 VALUES LESS THAN (MAXVALUE)
       ),
     PARTITION t_2017 VALUES LESS THAN (TO_DATE ('01 .01.2018 ',' DD.MM.YYYY '))
       (
         SUBPARTITION t_20171 VALUES LESS THAN (TO_DATE ('01 .04.2017 ',' DD / MM / YYYY ')),
         SUBPARTITION t_20172 VALUES LESS THAN (TO_DATE ('01 .07.2017 ',' DD / MM / YYYY ')),
         SUBPARTITION t_20173 VALUES LESS THAN (TO_DATE ('01 .10.2017 ',' DD / MM / YYYY ')),
         SUBPARTITION t_20174 VALUES LESS THAN (MAXVALUE)
       ),
     PARTITION t_max VALUES LESS THAN (maxvalue)
 );

Everything works, then the question arises, how can you optimize the process of creating new partitions (because the table will expand)? I only know how to break the existing partition (t_max) into two, but how can I do a single operation so that the entire structure is reproduced, along with all the subpartitions? At the moment, when the main partition is divided, an automatic subpartition with a system name is created, which is neither to the village nor to the city. I would be very grateful for the help, and the more detailed the help, the better =)

  • Read this, for example, docs.oracle.com/database/121/VLDBG/… - hinotf
  • I already looked at it, it’s about a template change, my partitions are not based on a template. - VNprk
  • Then we need to clarify what it means to "optimize the process of creating new partitions (since the table will expand)". If a new top-level section should be created automatically, the INTERVAL keyword may be suitable for this. - hinotf
  • Not only the top level section (year) should be created, but also 4 lower level sections (quarter), to be honest, I don’t even know how to do it manually. - VNprk

1 answer 1

alter table TEST_COMPOSITE split partition t_max at (TO_DATE('01.01.2019', 'DD.MM.YYYY')) INTO (PARTITION t_2018, PARTITION t_max); SELECT subpartition_name FROM user_tab_subpartitions WHERE table_name = 'TEST_COMPOSITE' and partition_name='T_2018' ORDER BY partition_name, subpartition_position; -- SYS_SUBP654 ALTER TABLE TEST_COMPOSITE SPLIT SUBPARTITION SYS_SUBP654 AT (TO_DATE('01.10.2018', 'DD.MM.YYYY')) INTO (SUBPARTITION T_20183, SUBPARTITION T_20184); ALTER TABLE TEST_COMPOSITE SPLIT SUBPARTITION T_20183 AT (TO_DATE('01.07.2018', 'DD.MM.YYYY')) INTO (SUBPARTITION T_20182, SUBPARTITION T_20183); ALTER TABLE TEST_COMPOSITE SPLIT SUBPARTITION T_20182 AT (TO_DATE('01.04.2018', 'DD.MM.YYYY')) INTO (SUBPARTITION T_20181, SUBPARTITION T_20182);