Move index or table to another tablespace

A) Tip :- How to move index  from one tablespace to another ?
B) Tip :- How to move Table from one tablespace to another ?


Answer A)

Move index from one tablespace to another :-

Test case : Assume you have a index FB1 in SYSTEM  tablespace and you want to move it in TEST tablespace

ora816 SamSQL :> select index_name,tablespace_name from dba_indexes where table_name='EMP' and owner='SCOTT';

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
FB1                            SYSTEM
FBI_TEST                       TEST

Test case : Solution

ora816 SamSQL :> connect scott/tiger
Connected.
ora816 SamSQL :> alter index FB1 rebuild tablespace TEST;

Index altered.
ora816 SamSQL :> connect sys
Enter password:
Connected.
ora816 SamSQL :> select index_name,tablespace_name from dba_indexes where table_name='EMP' and owner='SCOTT';

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
FB1                            TEST
FBI_TEST                       TEST

Conclusion : Index name  FB1 move from tablespace SYSTEM to TEST by

 ALTER INDEX <INDEX NAME> REBUILD TABLESPACE  <TABLESPACE_NAME>

Answer B)

Move Table from one tablespace to another ?


Test case : Assume you have a table  DEPT in SYSTEM  tablespace and you  want to move it in TEST tablespace

ora816 SamSQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           SYSTEM

Test case : Solution

ora816 SamSQL :> connect scott/tiger
Connected.
ora816 SamSQL :> alter table DEPT move tablespace TEST;

Table altered.
ora816 SamSQL :> connect
Enter user-name: sys
Enter password:
Connected.
ora816 SamSQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           TEST

Conclusion : Table name  DEPT move from tablespace SYSTEM to TEST by

          ALTER TABLE <TABLE NAME> MOVE TABLESPACE  <TABLESPACE_NAME>