在 oracle 19c 資料庫中，在鍵值表上，我在加載時間戳上定義了一個磁區，并在自定義列集上定義了子磁區。
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)
);
這導致例如磁區 SYS_P242603 子磁區 SYS_P242603_G、SYS_P242603_M 和 SYS_P242603_OTH。
如何將存盤在默認子磁區 SYS_P242603_OTH 中的新屬性（例如 GD100）移動到子磁區 SYS_P242603_G？
我已經相應地更改了子磁區模板并嘗試將值 GD100 從 SYS_P242603_OTH 拆分（想稍后將其合并到 SYS_P242603_G）但沒有成功 - 它引發 ORA-14400 錯誤。
ALTER TABLE mytable SPLIT SUBPARTITION SYS_P242603_OTH VALUES ('GD100') INTO
( SUBPARTITION SYS_P242603_G_TEMP ,
SUBPARTITION SYS_P242603_OTH
)
ONLINE;
uj5u.com熱心網友回復：
按時間戳列出磁區？？？
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/457340.html