The best place to ask programming/development questions, imo. UPDATE: stackoverflow is 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 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