Oracle move table from one tablespace to another

There could be a scenario when you will be required to move the table from one tablespace to another. Note that when you will be moving the table then you would link to move the indexes associated with this table as well.

Make sure that no one is using the table.

So below are the steps by which you can move the table and its index in one got to another tablespace:

SQL>  desc hdhillon.t;
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 OWNER                         NOT NULL VARCHAR2(30)
 OBJECT_NAME                   NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                         VARCHAR2(30)
 OBJECT_ID                     NOT NULL NUMBER
 DATA_OBJECT_ID                         NUMBER
 OBJECT_TYPE                            VARCHAR2(19)
 CREATED                       NOT NULL DATE
 LAST_DDL_TIME                 NOT NULL DATE
 TIMESTAMP                              VARCHAR2(19)
 STATUS                                 VARCHAR2(7)
 TEMPORARY                              VARCHAR2(1)
 GENERATED                              VARCHAR2(1)
 SECONDARY                              VARCHAR2(1)
 NAMESPACE                     NOT NULL NUMBER
 EDITION_NAME                           VARCHAR2(30)
SQL> select count (*) from hdhillon.t;
  COUNT(*)
----------
   1390900
SQL> select owner, index_name, INDEX_TYPE, table_name from dba_indexes where table_name ='T';
OWNER                          INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ ------------------------------ --------------------------- ------------------------------
HDHILLON                       T_OWNER_INDEX                  NORMAL                      T
SQL> select table_name, owner, tablespace_name from dba_tables where table_name ='T';
TABLE_NAME                     OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T                              HDHILLON                       MICHAEL
SQL> select index_name, owner, tablespace_name from dba_indexes where index_name ='T_OWNER_INDEX';
INDEX_NAME                     OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_OWNER_INDEX                  HDHILLON                       MICHAEL
SQL>
SQL> set lines 270 pages 1000
SQL> col sql format a90
SQL> select decode( segment_type, 'TABLE', 1, 2 ) order_col2,
  2        'alter ' || segment_type || ' ' || segment_name ||
  3        decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
  4        chr(10) ||
  5        ' tablespace &new_tablespace_name ' || chr(10) ||
  6        ' storage ( initial ' || initial_extent || ' next ' ||
  7          next_extent || chr(10) ||
  8        ' minextents ' || min_extents || ' maxextents ' ||
  9          max_extents || chr(10) ||');' sql
 10  from user_segments,
 11            (select table_name, index_name from user_indexes where table_name =upper('&&table_name'))
 12  where segment_type in ( 'TABLE', 'INDEX' )
 13  and segment_name in ( select table_name from user_indexes where table_name =upper('&&table_name'))
 14  or segment_name in ( select index_name from user_indexes where table_name =upper('&&table_name'))
 15  order by order_col2;
Enter value for new_tablespace_name: users
old   5:       ' tablespace &new_tablespace_name ' || chr(10) ||
new   5:       ' tablespace users ' || chr(10) ||
old  11:               (select table_name, index_name from user_indexes where table_name =upper('&&table_name'))
new  11:               (select table_name, index_name from user_indexes where table_name =upper('t'))
old  13: and segment_name in ( select table_name from user_indexes where table_name =upper('&&table_name'))
new  13: and segment_name in ( select table_name from user_indexes where table_name =upper('t'))
old  14: or segment_name in ( select index_name from user_indexes where table_name =upper('&&table_name'))
new  14: or segment_name in ( select index_name from user_indexes where table_name =upper('t'))
ORDER_COL2 SQL
---------- ------------------------------------------------------------------------------------------
         1 alter TABLE T move
            tablespace users
            storage ( initial 65536 next 1048576
            minextents 1 maxextents 2147483645
           );
         2 alter INDEX T_OWNER_INDEX rebuild
            tablespace users
            storage ( initial 65536 next 1048576
            minextents 1 maxextents 2147483645
           );
SQL>
SQL> alter TABLE T move
  2              tablespace users
  3              storage ( initial 65536 next 1048576
  4              minextents 1 maxextents 2147483645);
Table altered.
SQL> alter INDEX T_OWNER_INDEX rebuild
  2              tablespace users
  3              storage ( initial 65536 next 1048576
  4              minextents 1 maxextents 2147483645);
Index altered.
SQL> select table_name, owner, tablespace_name from dba_tables where table_name ='T';
TABLE_NAME                     OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T                              HDHILLON                       USERS
SQL> select index_name, owner, tablespace_name from dba_indexes where index_name ='T_OWNER_INDEX';
INDEX_NAME                     OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_OWNER_INDEX                  HDHILLON                       USERS