Contents
What causes unusable index in Oracle?
Oracle invalid or unusable indexes. Indexes can become invalid or unusable whenever a DBA tasks shifts the ROWID values, thereby requiring an index rebuild. Table partition maintenance – Alter commands (move, split or truncate partition) will shift ROWID’s, making the index invalid and unusable.
What does index Status n/a mean in Oracle?
partitioned index
N/A indicates that index is a partitioned index.This is explicitly enforced by catalog.sql.To get the status of the partitioned index query DBA_IND_PARTITIONS: select table_name,INDEX_TYPE,PARTITIONED,status from dba_indexes where status !=’USABLE’;
How to create an unusable Index in SQL?
A *unique* index cannot as “totally unusable” as a normal index. For example SQL> create table t as select object_id, owner, object_name, created 2 from dba_objects 3 where object_Id is not null; Table created. SQL> SQL> create index ix on t ( object_id ); — normal index Index created. SQL> SQL> alter index ix unusable; Index altered.
How to make primary key index usable from unusable?
Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition. Now, you have to rebuild index (es) according to their type, Global or Local. Rebuild each partition by issuing the ALTER INDEX REBUILD PARTITION statement (you can run the rebuilds concurrently).
Are there any indexes that are unusable in SYSAUX?
On PDB database after purging the old stats from sysaux tablespace. Moved the relevant tables and rebuild its indexes to reclaim space in sysaux tablespace, following are the unusable indexes: Rebuild of above indexes failed with below error: Index altered. Index altered. Executing utlrp also fails with error:
What causes an index partition to be unusable?
Cause: 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. Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition.