Move a table from one tablespace to another tablespace


Applies to: Database Management Software Oracle Server - Enterprise Edition - Version: 10.1.0.1 to 11.2.0.2 - Release: 10.1 to 11.2

Moving a table from one tablespace to another is mostly required in two cases.

a. When tablespace having table in it is full and doesn't have space to extend.
b. When there is fragmentation in table. Read more about fragmentation http://www.dbas-oracle.com/2011/04/identify-and-remove-table-fragmentation.html

Steps to move table from one tablespace to another.

Let's move table 'DB_OBJECTS' from USERS tablespace to TESTDB tablespace. This table also has an index 'PRIMARY_KEY'.

SQL> select segment_name,segment_type,tablespace_name from user_segments where segment_name like 'DB_OBJECTS';

SEGMENT_NAME   SEGMENT_TYPE       TABLESPACE_NAME
-------------- ------------------ ------------------------------
DB_OBJECTS        TABLE                                TESTDB

SQL> select index_name,status from user_indexes where table_name like 'DB_OBJECTS';

INDEX_NAME                     STATUS
------------------------------ --------
PRIMARY_KEY                    VALID

One Important thing, make sure destination tablespace have enough fee space to accommodate moved table. You can find this by looking at current size of table and free space in tablespaces. So, if free space in tablespace is more than current size of table, this movement will be success.

Current size of table:

SQL> select segment_name,bytes/(1024*1024) "size in mb" from user_segments where segment_name like 'DB_OBJECTS';

SEGMENT_NAME  size in mb
-------------                  ----------
DB_OBJECTS              9                                                                          

Free spaces in Tablespace:

SQL> select tablespace_name,bytes/(1024*1024) "Size in MB" from dba_free_space where tablespace_name like 'TESTDB';

TABLESPACE_NAME                Size in MB
------------------------------                 ----------
TESTDB                                         499

Here, TESTDB tablespace has 499 MB free space and DB_OBJECTS table is of 9 MB, So, we can proceed with movement.

SQL> alter table DB_OBJECTS move tablespace TESTDB;

Table altered.

Now, table is moved into TESTDB tablespace.

SQL> select segment_name,segment_type,tablespace_name from user_segments where segment_name like 'DB_OBJECTS';

SEGMENT_NAME   SEGMENT_TYPE       TABLESPACE_NAME
-------------- ------------------ ------------------------------
DB_OBJECTS           TABLE                            TESTDB

Here is a trick, Since movement of table will reset rowid of table, dependent indexes on table will be unusable, so need to validate them.

SQL> select index_name,status from user_indexes where table_name like 'DB_OBJECTS';

INDEX_NAME                     STATUS
------------------------------ --------
PRIMARY_KEY                    UNUSABLE

Rebuild all Indexs on table to use them:

SQL> ALTER INDEX PRIMARY_KEY REBUILD ONLINE;

Index altered.

SQL> select index_name,status from user_indexes where table_name like 'DB_OBJECTS';

INDEX_NAME                     STATUS
------------------------------ --------
PRIMARY_KEY                    VALID

No comments:

Post a Comment