Moving Tables and Indexes
Whenever you move a table to a different tablespace (or perform any one of several table maintenance procedures), any indexes that depend on the table are rendered unusable. Here is an example:
SQL> alter table test move tablespace dev_oim;
Table altered.
SQL> select index_name, status from dba_indexes where table_name='TEST';
INDEX_NAME STATUS
------------------------------ --------
TEST_IDX1 UNUSABLE
SQL>
Once you rebuild the index, it becomes usable again.
SQL> alter index test_idx1 rebuild
2 parallel 12
3 nologging;
Index altered.
SQL> select index_name, status from dba_indexes where table_name='TEST';
INDEX_NAME STATUS ------------------------------ ...Get Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
