Home » Developer & Programmer » Forms » truncate partition-index unusable state
truncate partition-index unusable state [message #186707] Wed, 09 August 2006 03:18 Go to next message
sridharreddy_d
Messages: 3
Registered: July 2006
Location: kualalumpur
Junior Member
hello all,
I have a partitined table (list partitioned) with a primary key defined on the table.
the table structure is following way..

CREATE TABLE xyz
(
SS_TX_ID CHAR(14 BYTE) NOT NULL,
CREATED_BY CHAR(3 BYTE),
CREATED_BY_MSISDN VARCHAR2(15 BYTE),
PARTITION_ID NUMBER(2),
UPDATED_BY_MSISDN VARCHAR2(15 BYTE)
)
TABLESPACE SSDATA
MAXTRANS 255
PARTITION BY LIST (PARTITION_ID)
(PARTITION abc01 VALUES (1)
TABLESPACE abc01
,
PARTITION abc02 VALUES (2)
TABLESPACE abc01
),
.
.
.
PARTITION abc23 VALUES (23)
TABLESPACE abc23
BUFFER_POOL DEFAULT
),
PARTITION abc24 VALUES (24)
TABLESPACE abc24
;
CREATE UNIQUE INDEX PK_TX_DISTRIBUTOR_DEALER ON TX_DISTRIBUTOR_DEALER
(ERS_TX_ID);

ALTER TABLE TX_DISTRIBUTOR_DEALER ADD (
CONSTRAINT PK_TX_DISTRIBUTOR_DEALER PRIMARY KEY (ERS_TX_ID)
USING INDEX
TABLESPACE ERSINDX;

After all the partitions 1..24 are filled I reuse the same partitions to store data ..such that..

export the partition data..,
truncate the partition.

but once I truncate the index goes to "unusable" and the DML cannot be performed on that table.

If I "rebuild the index" everything works fine.
But rebuilding indexes taking lots of time and lots of TEMP tablespace space.

Is there any alternative left for this issue.

Thanks & Regards,
sridhar
Re: truncate partition-index unusable state [message #186716 is a reply to message #186707] Wed, 09 August 2006 03:48 Go to previous message
ramakris
Messages: 21
Registered: July 2006
Location: Delhi
Junior Member
Hi Sridhar,

When you have a global index on the partitoned table you need to use UPDATE GLOBAL INDEXES clause in order to avoid moving index into UNUSABLE state, with the DDLs performed on partitions.

E.g.
ALTER TABLE <abc> TRUCATE PARTITION <P1> UPDATE GLOBAL INDEXES;

This is not the case when you maintain Local indexes.

Thanks,
Rama.
Previous Topic: Template Paths and FRM-18108
Next Topic: Change the font size of Alert Text message
Goto Forum:
  


Current Time: Wed Dec 07 03:18:23 CST 2016

Total time taken to generate the page: 0.09293 seconds