Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Closed TopicStart new topic
> Index is in unusable state
ecravi
post Sep 1 2010, 10:26 AM
Post #1


Advanced Member
***

Group: Members
Posts: 34
Joined: 29-July 10
Member No.: 43,583



Hi,

I can see the error in alert log like

2437312:ORA-12801: error signaled in parallel query server P009
2437313:ORA-01502: index 'POS.XIETBK_POS_FACT_TRAN_DATE' or partition of such index is in unusable state

and tried to rebuild the index and i got following error.

ORA-14086: a partitioned index may not be rebuilt as a whole.

The table size for the index is large. we need to rebuild the hole index.

Thanks in advance.
Ravi
Go to the top of the page
 
+Quote Post
Steve
post Sep 1 2010, 10:55 AM
Post #2


Advanced Member
***

Group: Members
Posts: 618
Joined: 22-January 06
From: Virginia Beach, VA
Member No.: 3,560



This error means the index is a locally partitioned index, so you must rebuild each partition. You can read all about these features here: http://www.dba-oracle.com/art_partit.htm

You will use: alter index index_name rebuild partition partition_name;


--------------------
Steve Karam
Sr. Consultant - Burleson Consulting
Oracle 10g Certified Master / Oracle ACE
http://www.OracleAlchemist.com
steve@orcldba.com
Go to the top of the page
 
+Quote Post
burleson
post Sep 1 2010, 10:58 AM
Post #3


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



Hi,

> ORA-01502

Sorry, Steve did not send you to the correct page:

http://www.dba-oracle.com/t_ora_01502_inde...sable_state.htm



--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
ecravi
post Sep 2 2010, 03:32 AM
Post #4


Advanced Member
***

Group: Members
Posts: 34
Joined: 29-July 10
Member No.: 43,583



Hi ,

Thanks for you are reply.

In my table i have lot of partition. here how can i find the paratition name to rebuild the index.

alter index index_name rebuild partition partition_name;

in table dba_tab_partitions we have any option or we need to search other table.

Thanks,

Ravi
Go to the top of the page
 
+Quote Post
SteveC
post Sep 2 2010, 03:55 AM
Post #5


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



Take a look at the data dictionary views listed here:
http://www.praetoriate.com/data_dictionary/dd_index.htm

You know you are looking for something related to indexes and partitions, so be inventive in your search, not that hard to find.
http://www.praetoriate.com/data_dictionary..._partitions.htm

And then, you should probably try figuring out why the index became unusable in the first place:
http://www.dba-oracle.com/t_indexes_invalid_unusable.htm

A handy script to have on hand is one that queries dict (synonym for dictionary):

CODE
SQL> @dict
Enter value for tab_name: index
old   3: where upper(table_name) like upper('%&tab_name%')
new   3: where upper(table_name) like upper('%index%')

TABLE_NAME                     COMMENTS
------------------------------ ------------------------------------------------------------
USER_INDEXES                   Description of the user's own indexes
USER_INDEXTYPES                All user indextypes
USER_INDEXTYPE_ARRAYTYPES      All array types specified by the indextype
USER_INDEXTYPE_COMMENTS        Comments for user-defined indextypes
USER_INDEXTYPE_OPERATORS       All user indextype operators
USER_PART_INDEXES
USER_XML_INDEXES               Description of the user's own XMLType indexes
ALL_INDEXES                    Descriptions of indexes on tables accessible to the user
ALL_INDEXTYPES                 All indextypes available to the user
ALL_INDEXTYPE_ARRAYTYPES       All array types specified by the indextype
ALL_INDEXTYPE_COMMENTS         Comments for user-defined indextypes
ALL_INDEXTYPE_OPERATORS        All operators available to the user
ALL_PART_INDEXES
ALL_XML_INDEXES                Description of the all XMLType indexes that the user has pri
INDEX_HISTOGRAM                statistics on keys with repeat count
INDEX_STATS                    statistics on the b-tree

16 rows selected.

SQL> l
  1  select table_name, substr(comments,1,60) "COMMENTS"
  2  from dict
  3* where upper(table_name) like upper('%&tab_name%')
Go to the top of the page
 
+Quote Post
dineshb4u
post Sep 2 2010, 04:20 AM
Post #6


Advanced Member
***

Group: Members
Posts: 32
Joined: 27-June 09
From: Chennai, INDIA
Member No.: 41,081



You can use dba_ind_partitions
Go to the top of the page
 
+Quote Post
burleson
post Sep 2 2010, 08:06 AM
Post #7


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



Hi Ravi,

>> how can i find the paratition name to rebuild the index.

Start with dba_indexes, joined into dba_ind_partitions, but it;s not trivial . . . .

If you are not comfortable writing dictionary scripts, you can download my collection of over 600 scripts here:

http://www.dba-oracle.com/oracle_scripts.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
ecravi
post Sep 13 2010, 05:46 AM
Post #8


Advanced Member
***

Group: Members
Posts: 34
Joined: 29-July 10
Member No.: 43,583



Hi all,

I am getting error everyweek from server. how the indexes changing to unusable state.

thanks,
RAvi

Go to the top of the page
 
+Quote Post
dineshb4u
post Sep 13 2010, 06:25 AM
Post #9


Advanced Member
***

Group: Members
Posts: 32
Joined: 27-June 09
From: Chennai, INDIA
Member No.: 41,081



Some operations (like adding a new partition to a existing hash partitioned table) leave the indexes unusable. Go through the below link

http://download.oracle.com/docs/cd/B10500_...521/partiti.htm


Probably some one or your application is modifying or adding or dropping the partitions
Go to the top of the page
 
+Quote Post
SteveC
post Sep 13 2010, 08:38 AM
Post #10


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



Or moving tables. Which indexes? Only those related to partitioned tables or indexes in general?
Go to the top of the page
 
+Quote Post

Closed TopicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 24th October 2014 - 01:43 PM