Link to home
Create AccountLog in
Avatar of Bobby Sandhu
Bobby SandhuFlag for Canada

asked on

unused indexes

I saw this in the Oracle logs. any ideas what action should i take?

Some indexes or index [sub]partitions of table DMART_SFA.DMT_FORECAST_SUMMARIES_FT have been marked unusable
Tue Mar  4 09:48:08 2008
Some indexes or index [sub]partitions of table DMART_SFA.DMT_FORECAST_SUMMARIES_FT_P have been marked unusable
Avatar of johnsone
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
The error indicates an attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation.

The problem usually happens when using the Direct Path for the SQL*Loader, Direct Load or DDL operations. This requires enough temporary space to build all indexes of the table. If there is no enough space in TEMP tablespace, all rows will still be loaded and imported, but the indices are left with STATUS = INVALID.

Invalid indexes can be checked with a SELECT * from USER_INDEXES WHERE STATUS = INVALID; SQL statement.

Solution to this error is simple. You can:

Drop the specified index and/or recreate the index
Rebuild the specified index
Rebuild the unusable index partition
Generally, the following SQL manipulation language will be able to rebuild the unusable index:


Avatar of Bobby Sandhu


k when i run

SELECT * from user_INDEXES WHERE STATUS = 'UNUSABLE' i get some indexes which are unusable but when i run
SELECT * from USER_INDEXES WHERE STATUS = 'INVALID' i didnt get anything is the issue same if the status is unusable  when i say same issue i mean not  enough temp space to build indexes
did you recently add/split/merge some partitions on this table?  
if you have LOCAL indexes, you'll have to rebuild them

for x in (
select distinct 'ALTER table DMT_FORECAST_SUMMARIES_FT modify subpartition ' || a.subpartition_name || ' REBUILD UNUSABLE LOCAL INDEXES' rebuild_index
from user_ind_subpartitions a,
     user_tab_partitions b
where a.status != 'USABLE'
  and a.partition_name = b.partition_name
  and b.table_name = 'DMT_FORECAST_SUMMARIES_FT'
order by 1

    execute immediate x.rebuild_index;

end loop;
Rebuilding indexes is always a good thing.
Do not hesitate to do this.