OraFAQ Forum: SQL & PL/SQL » UNUSABLE INDEX on Partition table

Home » SQL & PL/SQL » SQL & PL/SQL » UNUSABLE INDEX on Partition table (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

UNUSABLE INDEX on Partition table [message #669098]

Tue, 03 April 2018 04:47

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi All,

I have interval partition table on timestamp related column. From the application there some insertions into this table.Some times some how composite primary key corresponding INDEX is becoming UNUSABLE state.

This Composite Primary key corresponding INDEX is non-partitioned.
Partition key column(TIMESTAMP) is part of this composite primary key.

Here I am unable to find out why it is becoming UNUSABLE ?
What are the conditions which leads to UNUSABLE state.

Thanks
SaiPradyumn.

Report message to a moderator

Re: UNUSABLE INDEX on Partition table [message #669099 is a reply to message #669098]

Tue, 03 April 2018 06:09

msol25
Messages: 396
Registered: June 2011

Senior Member

Hi,

Please check with below url:

https://docs.oracle.com/cd/A91202_01/901_doc/server.901/a90117/partiti.htm

[Updated on: Tue, 03 April 2018 06:09]

Report message to a moderator

Re: UNUSABLE INDEX on Partition table [message #669108 is a reply to message #669099]

Tue, 03 April 2018 07:45

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

Referring someone on 11.2.0.4.0 to outdated Oracle 9.1 documentation can't be really helpful.

To OP: Based on "This Composite Primary key corresponding INDEX is non-partitioned", I assume it is global non-partitioned index. Do you truncate partitions? Do you drop partitions? Do you split partitions? Do you do merge partitions? Do you do partition exchange? Any of such actions will make global indexes unusable unless UPDATE GLOBAL INDEXES is used.

SY.

Report message to a moderator

Re: UNUSABLE INDEX on Partition table [message #669117 is a reply to message #669108]

Tue, 03 April 2018 08:52

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Yes, Solomon Yakobson /msol25

We are doing all such kind of things.We have some EOD jobs where we are truncating the partitions.
After execution of those procedures its becoming UNUSABLE. Now I am taking care to enable the indexes.

Thanks you very much for valuable information .

Thanks
SaiPradyumn.

Report message to a moderator

Previous Topic:	Resource allocation problem
Next Topic:	Answer the query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 07 16:53:24 CDT 2026