Urgent:Problem with Partitioned Tables

[sumanroyc]

Hi All,

We have a table partitioned on the basis of create_date column such that each day's data residing in a different partition. This table has no index other than the index associated with the primary key column. Every day through a script ( which runs at midnight) we are dropping the previous day's partition and creating a partition for the next day.

Do we need to drop and recreate or rebuild the primary key index every time we drop or create a new partition?

Recently we got the following error while trying to insert into the table :-

ORA-01502: index 'ORADBA_DEV.OLSS_SESSION_WORK_PK' or partition of such index is in unusable state

Is this due to the fact that we are droppping and creating new partions every day ? If so what needs to be done to resolve this ?

Thanks and Regards,
Suman

[pragatiswain]

Please create partitions for may be around 1 year in advance.
Is the creationdate field updated? If so, row movement should be enabled across partion. This should be done at table creation time. Primary key may not be re-built but table must be analyzed.
Regarding "ORA-01502: index 'ORADBA_DEV.OLSS_SESSION_WORK_PK' or partition of such index is in unusable state", probably index is corrupted. Try to drop and recreate index/Primary key.
If the problem persists, take the data backup from the table and recreate the table with all the partitions, enable row movement across partition(if required), and Primary key.

Hope this helps