Splitting List-Partition

Adme12

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!

David Berger

Hello Adme12

Yes.. your syntax is wrong.

Try this:

ALTER TABLE TEST_TO_SPLIT SPLIT PARTITION PRT_TEST_02_03 VALUES (2) INTO (PARTITION PRT_TEST_02, PARTITION PRT_TEST_03)
;

select grp, COUNT(*) from test_to_split PARTITION (PRT_TEST_02) GROUP BY grp
UNION ALL
select grp, COUNT(*) from test_to_split PARTITION (PRT_TEST_03) GROUP BY grp
;

In this case the value 2 will be maped to the partition PRT_TEST_02 and the remaining values in the original partition PRT_TEST_02_03 - in this case the 3 - will be mapped to PRT_TEST_03!

I hope it helps you!

David Berger

Hello Adme12

Yes.. your syntax is wrong.

Try this:

ALTER TABLE TEST_TO_SPLIT SPLIT PARTITION PRT_TEST_02_03 VALUES (2) INTO (PARTITION PRT_TEST_02, PARTITION PRT_TEST_03)
;

select grp, COUNT(*) from test_to_split PARTITION (PRT_TEST_02) GROUP BY grp
UNION ALL
select grp, COUNT(*) from test_to_split PARTITION (PRT_TEST_03) GROUP BY grp
;

In this case the value 2 will be maped to the partition PRT_TEST_02 and the remaining values in the original partition PRT_TEST_02_03 - in this case the 3 - will be mapped to PRT_TEST_03!

I hope it helps you!

Adme12

Hello David

Yep! It works! Thanks!