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;
CodePudding user response：
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