0

I would like to ask you for help, I've been looking for an example for days and I haven't found anything that works.

I have a partitioned table in Oracle 11g and these are its last two partitions. P2026 and P_OTHER , dataType NUMBER(4). This table does not have any column with a DATE type

PARTITION "P2026" VALUES LESS THAN (2027) ( SUBPARTITION "P2026_SP0" VALUES (0) , SUBPARTITION "P2026_SP1" VALUES (1) , SUBPARTITION "P2026_SP2" VALUES (2) , SUBPARTITION "P2026_SP3" VALUES (3) , SUBPARTITION "P2026_SP4" VALUES (4) , SUBPARTITION "P2026_SP5" VALUES (5) , SUBPARTITION "P2026_SP6" VALUES (6) , SUBPARTITION "P2026_SP7" VALUES (7) , SUBPARTITION "P2026_SP8" VALUES (8) , SUBPARTITION "P2026_SP9" VALUES (9) , SUBPARTITION "P2026_SP_OTHER" VALUES (DEFAULT) ) , PARTITION "P_OTHER" VALUES LESS THAN (MAXVALUE) ( SUBPARTITION "P_OTHER_SP0" VALUES (0) , SUBPARTITION "P_OTHER_SP1" VALUES (1) , SUBPARTITION "P_OTHER_SP2" VALUES (2) , SUBPARTITION "P_OTHER_SP3" VALUES (3) , SUBPARTITION "P_OTHER_SP4" VALUES (4) , SUBPARTITION "P_OTHER_SP5" VALUES (5) , SUBPARTITION "P_OTHER_SP6" VALUES (6) , SUBPARTITION "P_OTHER_SP7" VALUES (7) , SUBPARTITION "P_OTHER_SP8" VALUES (8) , SUBPARTITION "P_OTHER_SP9" VALUES (9) , SUBPARTITION "P_OTHER_SP_OTHER" VALUES (DEFAULT) ) ) ;

I want to split P_OTHER to add partitions corresponding to the number that represents the year 2027

I try this:

ALTER TABLE "OWNER"."PARTITIONEDTABLE" SPLIT PARTITION P_OTHER VALUES LESS THAN (2028) (SUBPARTITION "P2027_SP0" VALUES (0) , SUBPARTITION "P2027_SP1" VALUES (1) , SUBPARTITION "P2027_SP2" VALUES (2) , SUBPARTITION "P2027_SP3" VALUES (3) , SUBPARTITION "P2027_SP4" VALUES (4) , SUBPARTITION "P2027_SP5" VALUES (5) , SUBPARTITION "P2027_SP6" VALUES (6) , SUBPARTITION "P2027_SP7" VALUES (7) , SUBPARTITION "P2027_SP8" VALUES (8) , SUBPARTITION "P2027_SP9" VALUES (9) , SUBPARTITION "P2027_SP_OTHER" VALUES (DEFAULT) ) INTO (partition P2027, partition P_OTHER);

and I get;

ERROR at line 1: ORA-00906: missing left parenthesis

I tried making just the partition without the subpartitions to add them later and I also have the same error

ALTER TABLE "OWNER"."PARTITIONEDTABLE" SPLIT PARTITION P_OTHER VALUES LESS THAN (2028) INTO (partition P2027, partition P_OTHER); 
ERROR at line 1: ORA-00906: missing left parenthesis

Does anyone know what the syntax error would be?

Improve this question

1 Answer 1

Reset to default
0

Sometimes Oracle's errors in parsing SQL are not very helpful. In this case it isn't about missing parentheses.

You need to use the AT clause, not VALUES LESS THAN. Also, you don't list the subpartitions when splitting. The syntax for splitting partitions would need to be simply:

ALTER TABLE tmp1 SPLIT PARTITION "P_OTHER" AT (2028) INTO (PARTITION "P2027", PARTITION "P_OTHER")

You may want to consider interval partitioning, however. If you dropped the P_OTHER partition and then set an interval of (1) (as your year column is clearly a numeric which would increment by one each year; for real date columns it would be (NUMTOYMINTERVAL(1,'YEAR')) ), Oracle would automatically add new yearly partitions when they are needed, removing the need to expensive splits:

alter table tmp1 drop partition p_other; alter table tmp1 set interval(1);

No more splits! The only draw-back is you lose the nice partition names (they'll be system-generated). But since you won't need to do any more partition maintenance, it's not a big deal (and if you ever did need to script some partition work that was date-specific you can use the newish FOR syntax partition referencing).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.