Home » SQL & PL/SQL » SQL & PL/SQL » index is in unusable state - Please help
index is in unusable state - Please help [message #196786] Sat, 07 October 2006 07:21 Go to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member

I get this error.

< ORA-20000: index I_ELIGCNT or partition of such index is in unusable state


I tried to re-build the index after looking at the problem and reading details of the error. Solution to such errors is to either drop and create OR re-build the index.

Re-build is failed because of "ORA-01652: unable to extend temp segment by 128 in tablespace SWK01 (Temporary Sort tablespace)". I looked at the SWK01 tablespace and it looks very much okay. Tablespace is completely empty when I tried to re-build the index still I get this error.

Should I drop the index completely and recreate it as it was?

Can someone give me any advise as to where and what to look for and how to solve this.
Thanks in advance
R
Re: index is in unusable state - Please help [message #196788 is a reply to message #196786] Sat, 07 October 2006 07:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> ORA-20000: index I_ELIGCNT or partition of such index is in unusable state
Did you do direct load using SQL*LOADER recently?
This may be a by-product of it.

>>Re-build is failed because of "ORA-01652: unable to extend temp segment by 128 in tablespace SWK01 (Temporary Sort tablespace)"

With online rebuild ,upto somepoint the before image and after image of index would be maintained. So your index tablepace would need double the actual space.
This has also more to do with sorting.
When you create an index, the data is actually sorted first. Temporary segments are used for this (inside the tablespace used for index). After the sorting is done, the temporary segment is converted into an index segment.
So allocate more space to the tablespaces or use autoextend on for index tablespace ony for the duration of this operation).
Re: index is in unusable state - Please help [message #196790 is a reply to message #196786] Sat, 07 October 2006 07:58 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member

Thanks for the quick response Mahesh.
Liked your explanation.

1. I haven't used any SQL*LOADER for this. May be some developer must have done it which I can't know.
However I do get alert log messages from the system when errors occurs. I troubleshoot and try to resolve the same.

2. I checked the tablespace for the table (DATA01) and index for the table (tablespace DATA02). They all have plenty of space (more than 1 gig in each). Therefore could not figure out.
Any advise, please
Ravi

Re: index is in unusable state - Please help [message #196791 is a reply to message #196790] Sat, 07 October 2006 08:25 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Increase your tempspace
or
rebuild index offline.
Re: index is in unusable state - Please help [message #196792 is a reply to message #196786] Sat, 07 October 2006 08:43 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member

You mean take the tablespace (DATA02 where the index resides) offline and then do the rebuild index OR

is there an option to rebuild offline in "alter index command..."
like
alter index X rebuild offline;.... something like that.

Sorry for troubling you...
Truly I appreciate your help.
Thanks
Re: index is in unusable state - Please help [message #196793 is a reply to message #196792] Sat, 07 October 2006 08:52 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
No. That was my fault. Bad terminology.
Drop the index
and
recreate it.
Generate the ddl of index using dbms_metadata or any GUI tool.
For dbms_metadata, search the board/articles section.
But i doubt whether it is actually going to fix it. Still, there is a need for space.

[Updated on: Sat, 07 October 2006 08:54]

Report message to a moderator

Previous Topic: display date with time
Next Topic: some just table names after dropping demo tables
Goto Forum:
  


Current Time: Sat Dec 03 12:27:14 CST 2016

Total time taken to generate the page: 0.07640 seconds