how to change oracle table tablespace and rebuild unusable indexes

after installing oracle 11g on ubuntu 11.10, importing (using impdp) from an oracle 10g database, i noticed that some of the tables were in the SYSTEM tablespace instead of the USER tablespace so i wanted to move them all to the same tablespace. you do it in 2 parts, first move, then rebuild indexes. here's how i did it:


* log in as sys:

su - oracle 

cd /u01/app/oracle/product/11.1.0/db_1/bin 

./sqlplus sys as sysdba

* now, to find out which tables were in the SYSTEM tablespace:

select table_name from dba_tables where owner = 'SCOTT' and tablespace_name = 'SYSTEM';

let's say the query returns:

TABLEX 

TABLEY 

TABLEZ

* and to find their index names:

select index_name from dba_indexes where STATUS='UNUSABLE';

(this will technically give you all the unusable index names, or you can narrow your search to the 3 tables above if you like, by modifying the where clause, but why wouldn't you want to see if there were additional unusable indexes? =))


let's say the query returns:

PK_TABLEX 

PK_TABLEY 

PK_TABLEZ

(in my table i had all kinds of different looking index names, e.g.: SYS_C0014766, TABLEX_PK, IX_HTTPMSGFM_MEMBER_LDAP_ID, AGREEMENTNO_UNIQUE, etc -- who the hell named these so inconsistently?)

* move them to USER tablespace, then rebuild the unusable index:

alter table SCOTT.TABLEX move tablespace USERS;alter index SCOTT.TABLEX rebuild tablespace USERS;
alter table SCOTT.TABLEY move tablespace USERS;alter index SCOTT.TABLEY rebuild tablespace USERS;
alter table SCOTT.TABLEZ move tablespace USERS;alter index SCOTT.TABLEZ rebuild tablespace USERS;

sources:
http://www.dba-oracle.com/t_alter_table_move_index_constraint.htm
http://www.databasejournal.com/features/oracle/article.php/3735286/Oracle-Unusable-Indexes.htm