How To Move a Partitioned Table To New Tablespace in Oracle

dbtut

6 years ago

Partitioned table cannot be moved like a normal table. Therefore, partitions need to be moved separately. In order to prevent the table from creating a new partition in the old tablespace after the move, the tablespace of the table needs to be updated.

You can create commands for moving the table’s partitions with the following query.

select 'ALTER TABLE USER.TABLE_NAME
MOVE PARTITION '||partition_name||'
TABLESPACE NEW_TABLESPACE NOLOGGING;'
from dba_tab_partitions where TABLE_NAME='TABLE_NAME';

select 'ALTER TABLE USER.TABLE_NAME

MOVE PARTITION '||partition_name||'

TABLESPACE NEW_TABLESPACE NOLOGGING;'

from dba_tab_partitions where TABLE_NAME='TABLE_NAME';

You can run the records returned from the query at once with a script.

The sample partition move command is as follows:

ALTER TABLE USER.TABLE_NAME MOVE PARTITION PARTITION_NAME TABLESPACE NEW_TABLESPACE NOLOGGING;

1	ALTER TABLE USER.TABLE_NAME MOVE PARTITION PARTITION_NAME TABLESPACE NEW_TABLESPACE NOLOGGING;

Since there may be many partitions in a table, you can use the script that I shared first, instead of running the commands one by one.

After moving the partitions in the table to the new tablespace, the tablespace information needs to be changed so that the table does not create a partition in the old tablespace. You can change existing tablespace information with the following command.

ALTER TABLE USER.TABLE_NAME MODIFY DEFAULT ATTRIBUTES TABLESPACE NEW_TABLESPACE;

1	ALTER TABLE USER.TABLE_NAME MODIFY DEFAULT ATTRIBUTES TABLESPACE NEW_TABLESPACE;