| truncate partition-index unusable state [message #186707] |
Wed, 09 August 2006 03:18  |
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  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
