Hello
I need to split a list-partition in my table into two partitiones.
I want to split the partition "PRT_TEST_02_03" as follwoing:
Value 2 should go into PRT_TEST_02
and
Value 3 should go into PRT_TEST_03
But I get always an error message.. I do not know why!
DROP TABLE TEST_TO_SPLIT
/
CREATE TABLE TEST_TO_SPLIT
( id NUMBER
, grp NUMBER(3)
, text VARCHAR2(100)
)
TABLESPACE ALL_DATA
PARTITION BY LIST (grp)
(PARTITION PRT_TEST_01 VALUES (1),
PARTITION PRT_TEST_02_03 VALUES (2, 3),
PARTITION PRT_TEST_04_07 VALUES (4, 5, 6, 7)
)
/
INSERT INTO TEST_TO_SPLIT (id, grp, text) VALUES (1, 1, 'a');
INSERT INTO TEST_TO_SPLIT (id, grp, text) VALUES (2, 1, 'b');
INSERT INTO TEST_TO_SPLIT (id, grp, text) VALUES (3, 2, 'c');
INSERT INTO TEST_TO_SPLIT (id, grp, text) VALUES (4, 3, 'd');
INSERT INTO TEST_TO_SPLIT (id, grp, text) VALUES (5, 3, 'e');
INSERT INTO TEST_TO_SPLIT (id, grp, text) VALUES (6, 4, 'f');
INSERT INTO TEST_TO_SPLIT (id, grp, text) VALUES (7, 5, 'g');
INSERT INTO TEST_TO_SPLIT (id, grp, text) VALUES (8, 6, 'h');
INSERT INTO TEST_TO_SPLIT (id, grp, text) VALUES (9, 7, 'i');
COMMIT;
ALTER TABLE TEST_TO_SPLIT SPLIT PARTITION PRT_TEST_02_03 VALUES (2, 3) INTO (PARTITION PRT_TEST_02, PARTITION PRT_TEST_03)
/
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ALTER TABLE TEST_TO_SPLIT SPLIT PARTITION PRT_TEST_02_03 VALUES (2, 3) INTO (PARTITION PRT_TEST_02, PARTITION PRT_TEST_03)
*
ERROR at line 1:
ORA-14314: resulting List partition(s) must contain atleast 1 value
SQL>
Could you please tell me what I am doing wrong?
Thanks in advance!