Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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;
1 2 | select 'alter table '||owner||'.'||table_name||' move tablespace DATA_STAGE nologging parallel 8;' from dba_tables where owner='ADURUOZ' and tablespace_name <>'DATA_STAGE'; |
To move table partitions in the schema;
1 2 | select 'ALTER TABLE '||table_owner||'.'||table_name||' MOVE PARTITION '||partition_name||' TABLESPACE DATA_STAGE NOLOGGING parallel 8;' from dba_tab_partitions where table_owner='ADURUOZ'; |
To move lobs in the schema;
1 2 | select 'alter table '||owner||'.'||table_name||' move lob ('||column_name||') store as (tablespace DATA_STAGE);' from dba_lobs where tablespace_name<>'DATA_STAGE' and owner='ADURUOZ'; |
To move indexes in the schema;
1 2 | select 'alter index '||owner||'.'||index_name||' rebuild nologging parallel 8 tablespace DATA_STAGE;' from dba_indexes where tablespace_name<>'DATA_STAGE' and owner='ADURUOZ'; |
To move index partitions in the schema;
1 2 | select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' nologging parallel 8;' from dba_ind_partitions where tablespace_name<>'DATA_STAGE' and index_owner='ADURUOZ' |
Rebuild Unusable Indexes
If there are unusable indexes after move operation, recompile commands can be generated as follows.
1 2 | select 'alter index '||owner||'.'||index_name||' rebuild nologging parallel 8 tablespace DATA_STAGE;' from dba_indexes where status='UNUSABLE'; |
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.
1 2 | select 'alter index '||owner||'.'||index_name||' noparallel;' from dba_indexes where degree>1; |
