Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Splitting List-Partition

Adme12Jan 20 2014 — edited Jan 20 2014

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!

This post has been answered by David Berger on Jan 20 2014
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 17 2014
Added on Jan 20 2014
2 comments
231 views