Forum Stats

  • 3,827,794 Users
  • 2,260,821 Discussions
  • 7,897,376 Comments

Discussions

Splitting List-Partition

Adme12
Adme12 Member Posts: 14
edited Jan 20, 2014 10:38AM in SQL & PL/SQL

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!

Tagged:

Best Answer

  • David Berger
    David Berger Member Posts: 345

    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!

Answers

  • David Berger
    David Berger Member Posts: 345

    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
    Adme12 Member Posts: 14

    Hello David

    Yep! It works! Thanks!

This discussion has been closed.