This article will explain how to move a table from one tablespace to another in the Oracle database.

In some scenarios, you might want to drop a tablespace that has some tables inside. Before performing the drop tablespace command, you have to make sure that you moved those tables to another tablespace, and this is what we will demonstrate here in this article.

Oracle Move Table to Another Tablespace? We have two ways to move table to another tablespace using the alter table command in offline or online mode. Moving table to another tablespace in offline mode we have to take care of indexes to rebuild them while in online mode Oracle rebuild the indexes automatically for us.

Create Test Tablespaces

In this step, we will create two new tablespaces for testing how we will move the table between them.

-- Create First Test Tablespace (oraask_tb_01)
CREATE TABLESPACE oraask_ts_01 
   DATAFILE 'oraask_ts_data01.dbf' 
   SIZE 10m;

-- Create Second Test Tablespace (oraask_tb_02)   
CREATE TABLESPACE oraask_ts_02 
   DATAFILE 'oraask_ts_data02.dbf' 
   SIZE 10m;

Create and Populate Test Table on First Tablespace

After creating the tablespaces, we’ll create a table on the “oraask_ts_01” tablespace and populate that table with data.

CREATE TABLE oraask_test_t01
TABLESPACE oraask_ts_01
AS
  SELECT     level AS sn, 'This is the Serial Number: ' || level AS meaning, sysdate AS creation_date
  FROM       dual
  CONNECT BY level <= 50;

ALTER TABLE oraask_test_t01 ADD CONSTRAINT oraask_test_t01_pk PRIMARY KEY (sn);
CREATE INDEX creation_date_idx01 ON oraask_test_t01(creation_date);

In the above script, we created the table “oraask_test_t01” on a tablespace “oraask_ts_01“, then we populated it with random 50 records just for testing purposes. Then we added the primary key for that table on column “sn” and created another index on the “creation_date” column.

Check The Tablespace Name and Size of the Table

SELECT   owner
        ,segment_name
        ,segment_type
        ,sum (bytes / 1024 / 1024) table_size_MB
        ,tablespace_name
FROM     dba_segments
WHERE    segment_name = 'ORAASK_TEST_T01'
GROUP BY owner
        ,segment_name
        ,segment_type
        ,tablespace_name;

Output:

In the above script, we checked the current tablespace name of our table along with the owner, type, and table size in megabytes.

Oracle Move Table to Another Tablespace in Offline Mode

ALTER TABLE oraask_test_t01 MOVE TABLESPACE oraask_ts_02;

In the above script, we moved the table “oraask_test_t01” to the new tablespace “oraask_ts_02“.

Check Indexes Status

SELECT index_name, status
FROM   user_indexes
WHERE  table_name = 'ORAASK_TEST_T01';

Output:

As we can see from the above query result, the status of two indexes has been marked as “unusable” after moving the table to another tablespace in offline mode.

Rebuild Unusable Indexes

Now we will manually rebuild our indexes to make them valid.

ALTER INDEX ORAASK_TEST_T01_PK REBUILD ONLINE;

ALTER INDEX CREATION_DATE_IDX01 REBUILD ONLINE;

Note: Rerun the above query to check the status of the index after executing the rebuild command.

Check the New Tablespace name of the Table

SELECT   segment_name
        ,tablespace_name
FROM     dba_segments
WHERE    segment_name = 'ORAASK_TEST_T01';

Output:

As we can see in the above result that the table “ORAASK_TEST_T01” has been moved to the second tablespace, “ORAASK_TS_02“.

Don’t forget to check our detailed tutorial about checking tablespace usage

Restrictions on Moving Table in Offline Mode

There is some restriction on moving the table in offline mode that is mentioned here. some of them are:

• We cannot move a table containing a LONG or LONG RAW column.
• We cannot move a partitioned table. Only moving the partition or sub-partition individually.

Oracle Move Table to Another Tablespace in Online Mode (Oracle DB V 12.2 Onward)

ALTER TABLE oraask_test_t01 MOVE ONLINE TABLESPACE oraask_ts_02;

In the above script, we used the “ONLINE” clause to move the table from tablespace “oraask_ts_01” to the new tablespace “oraask_ts_02” with auto manage the indexes related to the table to make them “VALID“.

By using the “ONLINE” clause, we will allow any DML operations to happen against our table while the moving process is in progress.

Note: Rerun the above query to check the tablespace name of the table

Oracle Move Multiple Tables to New Tablespace

select 'ALTER TABLE '|| table_name||' MOVE ONLINE TABLESPACE '||'ORAASK_TS_02;' "Move Tables Commands" from user_tables where tablespace_name='USERS';

By executing the above generated SQL commands, we will move (DEPT_BKP14082021,OSK_TEST,EMPS,XX_DUMMY_DATA_CSV) to the new tablespace “ORAASK_TS_02“.

Restrictions on Moving Table in Online Mode

There is some restriction on moving the table in online mode that is mentioned here. some of them are:

• We cannot combine this clause with any other clause in the same statement.
• You cannot specify this clause for index-organized tables that contain any LOB, VARRAY, Oracle-supplied type, or user-defined object type columns.

Conclusion

In this article, we have learned how to move a table or multiple tables to another tablespace in Oracle in offline and online mode and what is the restrictions on both of them.

If you have any questions, please don’t hesitate to ask them in the comments section below.
If you have an addition to this guide that will add extra value for the readers, kindly let me know.

Hopefully, it was clear and concise. Please share it to spread the knowledge