Batch Modify Table and index tablespaces

Since developers put most of the objects in the ESS project into the user tablespace, after imp/exp is imported into the official database, the ESS user objects are still in the users tablespace. to put the ESS objects in the default table space ess, follow these steps:

 

1. Find the objects to modify the tablespace:

 

Select * From dba_tables where owner = 'ess 'and tablespace_name = 'users ';

 

Select * From dba_indexes where table_owner = 'ess 'and tablespace_name = 'users ';

 

2. Generate SQL statements for batch Modification

 

 

Select 'alter table ess. '| table_name | 'move tablespace ESS ;'
From dba_tables where owner = 'ess' and tablespace_name = 'users ';

 

 

Select 'alter index ess. '| index_name | 'rebuild online nologging tablespace ESS ;'
From dba_indexes
Where table_owner = 'ess 'and tablespace_name = 'users'

 

 

 

 

Export the generated SQL statement to the ess_table.txtand ess_index.txt files.

 

3. Execute the sqll statements in the ess_table.txt and ess_index.txt files, and then click OK.

 

4. Check the users tablespace to see if the ESS user has fewer objects.

This article is from the "todayboy" blog, please be sure to keep this source http://comtv.blog.51cto.com/17037/1548989

Batch Modify Table and index tablespaces