ORA-01502: index is in unusable state

When try to execute query on any table with select SQL statement, Oracle returns the following error:

ORA-01502: index 'indexname' or partition of such index is in unusable state

The reason is that some indexes has been marked unusable by a direct load or by a DDL operation, or shrinking space etc. Here is simple solution.

1. Drop the specified index and/or recreate the index
2. Rebuild the specified index
3. Rebuild the unusable index partition

Here we rebuilding UNUSABLE indexes onling, quering to USER_INDEXES view.

$sqlplus /nolog

Sql> connect system/oracle
Sql> SELECT count(*),status FROM all_indexes GROUP BY status;
Sql> SELECT 'alter index 'table_owner'.'index_name' rebuild online ;' FROM all_indexes where status='UNUSABLE' ORDER BY table_owner

Just execute sql statement output, it will rebuild all unused indexes.

Moving Tables, Indexes to another Tablespace

We can move oracle database segments (tables, indexes) from one tablespace to another tablespace easily, following are some simple example. Logon to database and find create sql script for moving tables and index if multiple object, In SQL statement mention tablespace name, object owner and tables/indexes.

Moving Singe Table/Index:

TABLE employees MOVE TABLESPACE hr;
ALTER index.red_id_pk REBUILD TABLESPACE hrindex;

Moging Multipuls Tables:

spool c:\sqlscripts\movetbls.sql;

SELECT 'ALTER TABLE ' OBJECT_NAME ' MOVE TABLESPACE '' [TABLESPACE_NAME] ;'
FROM ALL_OBJECTS
WHERE OWNER = '[USER_NAME']
AND OBJECT_TYPE = 'TABLE';

spool off;

Moging Multipuls Indexes:

spool c:\sqlscripts\moveindx.sql;

SELECT 'ALTER INDEX 'INDEX_NAME' REBUILD TABLESPACE [TABLESPACE_NAME] ;'
FROM ALL_INDEXES
WHERE OWNER = 'USER_NAME'

spool off;

Now, just execute created scripts (movetbls.sql, moveindx.sql)

SQL> @c:\sqlscripts\movetbls.sql
SQL> @c:\sqlscripts\moveindx.sql

We also move multiple tables/indexes to another tablespace using PL/SQL block:

DECLARE
vquery varchar2(300);
cursor indx is SELECT ' ALTER INDEX 'INDEX_NAME' REBULD TABLESPACE hrindex ;' AS cindex
FROM ALL_INDEXES WHERE OWNER='SCOTT';
BEGIN
FOR a IN indx LOOP
EXIT WHEN indx%NOTFOUND IS NULL;
EXECUTE IMMEDIATE indx.cinds;
END LOOP;
END;