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!

split range partition/list subpartition

444915Sep 23 2005
I’ve a question about split partition in a table defined in partitions as range partition
and in subpartition defined as list partition.
That’s my ddl template:
PARTITION BY RANGE ( CODPRD )
SUBPARTITION BY LIST ( CODBNC )
SUBPARTITION TEMPLATE (
SUBPARTITION AV values ( 1 ),
SUBPARTITION CR values ( 2 ),
SUBPARTITION CM values ( 3 ),
SUBPARTITION BH values ( 6 ),
SUBPARTITION PB values ( 9 ) )
(PARTITION P200412 VALUES LESS THAN (200413)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE RPSD05
( SUBPARTITION P200412_AV VALUES (1)
TABLESPACE RPSD05 ,
SUBPARTITION P200412_CR VALUES (2)
TABLESPACE RPSD05 ,
SUBPARTITION P200412_CM VALUES (3)
TABLESPACE RPSD05 ,
SUBPARTITION P200412_PB VALUES (9)
TABLESPACE RPSD05 ,
SUBPARTITION P200412_BH VALUES (6)
TABLESPACE RPSD05 )

My final scope is change 200413 value assigned as high-value in P200412 partition and replace it in
200501 in P200412 partition ( using split and merge commands).
I don’t want recreate table changing high value and reinserting data (we have1.703.326.952 records).
When I try to use this command :
ALTER TABLE VBMANACLI_NEW SPLIT PARTITION P200412
at (200501, 200413)
INTO (PARTITION P200412_NEW tablespace RPSD05 , PARTITION P200412 tablespace RPSD05 );

I take this diagnostic:
ERROR at line 1:
ORA-14017: partition bound list contains too many elements.
P.S. : I’ve tried working with subpartitions , but i had no good result.
SQL> ALTER TABLE VBMANACLI_NEW SPLIT subPARTITION P200412_av
2 values (1, 1)
3 INTO (subPARTITION P200412_avNEW tablespace RPSD05, subPARTITION P200412_av tablespace RPSD0
5);
values (1, 1)
*
ERROR at line 2:
ORA-03113: end-of-file on communication channel


What’s possible to do ?


Thank you for your help
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 21 2005
Added on Sep 23 2005
0 comments
527 views