How To Move All Objects of a Schema to a Different Tablespace

To move all objects in a schema to a different schema, the following steps must be followed.

The following queries prepare the queries required for move operation. DATA_STAGE in queries is the tablespace to be moved and ADURUOZ is the user to which we will move all of its objects.

To move the tables in the schema;

To move table partitions in the schema;

To move lobs in the schema;

To move indexes in the schema;

To move index partitions in the schema;

Rebuild Unusable Indexes

If there are unusable indexes after move operation, recompile commands can be generated as follows.

The above index move queries are run in parallel 8 for quick completion of operations. In this case, the index degree value will be 8. This can adversely affect performance. Therefore, the degree value of indexes should be 1. So the index should be noparallel.

Set Indexes as Noparallel

You can create the necessary queries to make the indexes noparallel with the following query.

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.

Leave a Reply

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