What happens to the indexes after "alter table tbl move"?

[marist89]

Hmm, lets see...

SQL> create table xyz (x char(1), y char(1), z char(1));

Table created.

SQL> create index xyz_indx on xyz(x);

Index created.

SQL> select index_name, status from dba_indexes
  2  where table_name = 'XYZ';

INDEX_NAME                     STATUS
------------------------------ --------
XYZ_INDX                       VALID

SQL> alter table xyz move tablespace users;

Table altered.

SQL> select index_name, status from dba_indexes
  2  where table_name = 'XYZ';

INDEX_NAME                     STATUS
------------------------------ --------
XYZ_INDX                       VALID

SQL>  alter table xyz move tablespace tools;

Table altered.

SQL> select index_name, status from dba_indexes
  2  where table_name = 'XYZ';

INDEX_NAME                     STATUS
------------------------------ --------
XYZ_INDX                       VALID

At first site, you would say no, oracle rebuilds the indexes. However, when you put data into the table:

SQL> insert into xyz values ('x','y','z');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table xyz move tablespace tools;

Table altered.

SQL> select index_name, status from dba_indexes
  2  where table_name = 'XYZ';

INDEX_NAME                     STATUS
------------------------------ --------
XYZ_INDX                       UNUSABLE

Oracle tells us a different story. Yes, this index must be rebuilt. The same lies true for regular indexes on partitioned tables, global indexes, and the affected partition on partitioned tables.