11.2.0.3 Interval Partitioning Constraint Crea ...

A small problem that cropped up on a client site this week which might warrant a quick post to help any Google desperadoes that might find themselves in the same spot, not least because there's an easy workaround.

The existing application schema creation scripts used the following type of syntax to create Primary Key constraints and the underlying index in one shot against Interval Partitioned Tables. (Note, this is a simple test case I created for the Service Request that you should be able to try in other environments.)

CREATE TABLE TEST (PK_COLUMN NUMBER, NON_PK_COLUMN VARCHAR2(30))
PARTITION BY RANGE (PK_COLUMN)
INTERVAL( 1)
(
PARTITION RUN_0 VALUES LESS THAN (1)
);

ALTER TABLE TEST
ADD CONSTRAINT TEST_PK PRIMARY KEY (PK_COLUMN)
USING INDEX (CREATE INDEX TEST_PK ON TEST (PK_COLUMN)
LOCAL);

If you try to run this on an 11.2.0.2 database instance, it works fine. Run it on 11.2.0.3 and you'll get the following error.

ORA-00600: internal error code, arguments: [kkpoxPaxdInit0], [], [], [], [], [], [], [], [], [], [], []

A hunt on My Oracle Support didn't yield much apart from Bug 14230768 which looks like it's at a very early stage of discovery and being fixed. As far as I can tell from my own playing around, this only happens if you're using Interval Partitioning (which might explain why this hasn't been spotted at more sites) and the simple workaround is to split the constraint work into two steps.

CREATE TABLE TEST (PK_COLUMN NUMBER, NON_PK_COLUMN VARCHAR2(30))
PARTITION BY RANGE (PK_COLUMN)
INTERVAL( 1)
(
PARTITION RUN_0 VALUES LESS THAN (1)
);

CREATE INDEX TEST_PK ON TEST (PK_COLUMN)
LOCAL;

ALTER TABLE TEST
ADD CONSTRAINT TEST_PK PRIMARY KEY (PK_COLUMN)