Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187.1K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 443 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Splitting List-Partition

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!
Best Answer
-
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
-
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!
-
Hello David
Yep! It works! Thanks!