Friday , October 15 2021

How To Move a Partitioned Table To New Tablespace in Oracle

 

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.

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

The sample partition move command is as follows:

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

One comment

  1. Hello,
    Could you share a alter script that will generate the move for both the partitions and sub partitions with update index clause.
    Thank you,

Leave a Reply

Your email address will not be published. Required fields are marked *