HOW TO MOVE TABLES FROM ONE TABLESPACE TO ANOTHER

Suppose there are some users tables in USERS tablespace wants to move to USERS_DATA_SPACE AND USERS_INDEX_SPACE.

steps are
1- Make index rebuilt script
SPOOL INDEX-REBUILT.SQL
SELECT 'ALTER INDEX '|| index_name ||' REBUILD TABLESPACE USERS_INDEX_SPACE ;' FROM user_indexes WHERE tablespace_name='USERS';
SPOOL OFF;

2- Make table move script

SPOOL MOVE-TABLES.SQL
SELECT 'ALTER TABLE '|| table_name ||' MOVE TABLESPACE USERS_DATA_SPACE;' FROM user_tables WHERE tablespace_name='USERS';
SPOOL OFF;

3- run the table move scirpt

SQL> @MOVE-TABLES.SQL

4- rebuilt the indexes

SQL> @INDEX-REBUILT.SQL

 

Example:

SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME      TABLESPACE_NAME
------------------------------ 
EMP             USERS
BONUS           USERS
SALGRADE        USERS
DEPT            user_DATA_SPACE
SQL> alter table dept move tablespace USERS;
Table altered.
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME     TABLESPACE_NAME
------------------------------ 
EMP           USERS
BONUS         USERS
SALGRADE      USERS
DEPT          USERS
SQL>
Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Database administration. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s