REBUILD INDEXES

Moving Table & Rebuild Index

In this article i’m going to show you how to move a table in new tablespace. Then rebuild indexes which is based on that table, because after moving table all indexes goes invalid.

You also read how to Transport Database from Linux to Windows

SQL> CREATE TABLE ocp TABLESPACE users AS SELECT * FROM EMP;

That table stored in users tablespace.

Now create an index on that table.

SQL> create index ocp_idx on ocp(id);

After creating table move above table from one tablespace to another tablespace using following query.

SQL> alter table ocp move tablespace technology;

(ocp is a table name & technology is a new tablespace name)

Now check all unusable index using following command.

SQL> SELECT INDEX_NAME, TABLE_NAME FROM DBA_INDEXES WHERE STATUS='UNUSABLE';

Above command will show you all unusable indexes list.

Start Rebuild Indexes

Now Moving index from old tablespace to another tablespace using below command.

SQL> alter index ocp_idx rebuild tablespace technology;

After run above query your index will rebuild with ocp table in tehchnology tablespace and it work fine now, you can check again your index status using following query.

SQL> SELECT INDEX_NAME, TABLE_NAME FROM DBA_INDEXES WHERE STATUS='UNUSABLE';

You Can subscribe my YouTube channel

Share this
Share

Leave a Comment

Share