Hi All,

I am facing a peculiar problem while doing a PARTITION exchange in oracle.

When i am doing a partition swap between this table (script given below) and a similarly structured table, the unique index which was created becomes un-partitioned (which it shouldn’t in the first place). After this as per the business requirement, we need to truncate this partition, and when we do that the unique index on the table gets into an invalid state.

Here are the scripts that we use for doing the above.

ALTER TABLE <table_name> EXCHANGE PARTITION <partition_name> INCLUDING INDEXES

and the script used for truncate is

ALTER TABLE <table_name> TRUNCATE PARTITION <partition_name>

Table script:

CREATE TABLE TEST1 (
Start_Col INTEGER NOT NULL,
Start_Date DATE NOT NULL,
Time_Period_Type_Num INTEGER NOT NULL,
Blob_Type_ID INTEGER NOT NULL,
Attribute_ID INTEGER NOT NULL,
Category_ID INTEGER NOT NULL,
Source_System_ID INTEGER NOT NULL,
End_Date DATE NULL,
Total_Column_Count INTEGER NULL,
Num_Cols INTEGER NULL,
Num_Rows INTEGER NULL,
Created_By VARCHAR2(20) NULL,
Modified_By VARCHAR2(20) NULL,
Create_Date DATE NULL,
Last_Modified_Date DATE NULL,
Sim_Data_Blob BLOB NULL
)
PARTITION BY RANGE (Source_System_ID)
(
PARTITION test1_part VALUES LESS THAN (2) TABLESPACE TEMP
);

CREATE UNIQUE INDEX XPKTEST1 ON TEST1
(
Start_Col ASC,
Start_Date ASC,
Time_Period_Type_Num ASC,
Blob_Type_ID ASC,
Attribute_ID ASC,
Category_ID ASC,
Source_System_ID ASC
)
TABLESPACE TEMP
LOCAL
(
PARTITION test1_part TABLESPACE TEMP
);

CREATE INDEX XIF1TEST1 ON TEST1
(
Category_ID ASC
)
TABLESPACE TEMP
LOCAL
(
PARTITION test1_part TABLESPACE TEMP
);

CREATE INDEX XIF2TEST1 ON TEST1
(
Attribute_ID ASC
)
TABLESPACE TEMP
LOCAL
(
PARTITION test1_part TABLESPACE TEMP
);

CREATE INDEX XIF3TEST1 ON TEST1
(
Blob_Type_ID ASC
)
TABLESPACE TEMP
LOCAL
(
PARTITION test1_part TABLESPACE TEMP
);

CREATE INDEX XIF4TEST1 ON TEST1
(
Time_Period_Type_Num ASC
)
TABLESPACE TEMP
LOCAL
(
PARTITION test1_part TABLESPACE TEMP
);

CREATE INDEX XIF5TEST1 ON TEST1
(
Source_System_ID ASC
)
TABLESPACE TEMP
LOCAL
(
PARTITION test1_part TABLESPACE TEMP
);

ALTER TABLE TEST1
ADD ( PRIMARY KEY (Start_Col, Start_Date,
Time_Period_Type_Num, Blob_Type_ID, Attribute_ID,
Category_ID, Source_System_ID)
USING INDEX
TABLESPACE TEMP
LOCAL
(
PARTITION test1_part TABLESPACE TEMP
) ) ;

Temporary Solution:

After the truncate partition step is executed, we have added a line to rebuild all indexes in the given table. I feel that this is not a clean way to solve the issue.

I need to figure out as to why this is happening? Any pointers would be helpful.

Thanks