The best place to *find* answers to programming/development questions, imo, however it's the *worst* place to *ask* questions (if your first question/comment doesn't get any up-rating/response, then u can't ask anymore questions--ridiculously unrealistic), but again, a great reference for *finding* answers.

My Music (Nickleus)

20120222

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

No comments:

Post a Comment