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)
UNUSABLE INDEX on Partition table [message #669098] Tue, 03 April 2018 04:47 Go to next message
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.
Re: UNUSABLE INDEX on Partition table [message #669099 is a reply to message #669098] Tue, 03 April 2018 06:09 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
Re: UNUSABLE INDEX on Partition table [message #669117 is a reply to message #669108] Tue, 03 April 2018 08:52 Go to previous message
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.
Previous Topic: Resource allocation problem
Next Topic: Answer the query
Goto Forum:
  


Current Time: Thu Mar 28 14:12:22 CDT 2024