Unusable index in Partitioned table

From: bocgco <bocgco_at_bocgco.com>
Date: Wed, 21 May 2003 12:07:59 +0800
Message-ID: <3ecafb4e$1@shknews01>

In Oracle 9.2.0, solaris platform, I have encountered the following problem.

As the data is loaded thru direct path loading and the index has been set unusable during the load. When I use select statement, the error occurred even in sqlplus.

ERROR at line 1:
ORA-01502: index 'MYDB.MYDATA_IDXL01' or partition of such index is in unusable state

I have exec "alter session set unusable index=true" already but it still can't work. Finally I found that if all partitons' index has been set unusable then it will be fine. Oracle will be able to do a table scan. The problem occurs when only some of the partitioned index of the same table has been set to unusable.

Do anyone have similar experience or have workaround for me ? Received on Tue May 20 2003 - 23:07:59 CDT