Moving values from default subpartition

Question

in an oracle 19c database, on a key value table I've defined a partition on the load timestamp and subpartitions on custom sets of columns. create table mytable ( load_Dts timestamp(6) not null, segment VARCHAR2(4000), field_ident VARCHAR2(4000), value VARCHAR2(4000) ) partition by list (LOAD_DTS) AUTOMATIC SUBPARTITION by LIST (FIELD_IDENT) SUBPARTITION template ( SUBPARTITION G VALUES ('GD161','GD171'), SUBPARTITION M VALUES ('MD020','MD031'), SUBPARTITION OTH VALUES (DEFAULT) ) ( partition P0 values (NULL) );

That results in e.g. partition SYS_P242603 the subpartitions SYS_P242603_G, SYS_P242603_M and SYS_P242603_OTH.

How can I move a new attribute e.g. GD100 that is stored in the default subpartition SYS_P242603_OTH to subpartition SYS_P242603_G?

I've already altered the subpartition template accordingly and tried to split the value GD100 from SYS_P242603_OTH (wanted to merge it later to SYS_P242603_G) but with no success - it throws ORA-14400 error.

ALTER TABLE mytable SPLIT SUBPARTITION SYS_P242603_OTH VALUES ('GD100') INTO ( SUBPARTITION SYS_P242603_G_TEMP , SUBPARTITION SYS_P242603_OTH ) ONLINE;

Answer 1

List partitioning by timestamp???

SQL> create table mytable(
  2                       load_dts    timestamp(6) not null,
  3                       segment     VARCHAR2(4000),
  4                       field_ident VARCHAR2(4000),
  5                       value       VARCHAR2(4000)
  6                      )
  7    partition by list (load_dts) automatic
  8    subpartition by list (field_ident)
  9    subpartition template(
 10                          subpartition g values ('GD161','GD171'),
 11                          subpartition m values ('MD020','MD031'),
 12                          subpartition oth values (DEFAULT)
 13                        )
 14    (
 15     partition p0 values (null)
 16    )
 17  /


Table created.


SQL> alter table mytable
  2    set subpartition template(
  3                              subpartition g values ('GD161','GD171'),
  4                              subpartition m values ('MD020','MD031'),
  5                              subpartition g1 values ('GD100'),
  6                              subpartition oth values (DEFAULT)
  7                          )
  8  /


Table altered.


SQL> alter table mytable
  2   split subpartition p0_oth
  3     values('GD100')
  4       into(
  5            subpartition g1,
  6            subpartition oth
  7           )
  8  /


Table altered.


SQL> select  partition_name,
  2          subpartition_name,
  3          high_value
  4    from  user_tab_subpartitions
  5    where table_name = 'MYTABLE'
  6    order by subpartition_position
  7  /


PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
-------------- ----------------- --------------------
P0             P0_G              'GD161', 'GD171'
P0             P0_M              'MD020', 'MD031'
P0             G1                'GD100'
P0             OTH               DEFAULT