Moving multiple tables and indexes between tablespaces at once
Submitted by David Lozano Lucas on Fri, 2009-10-23 05:30

Here I present a simple query to use when we want to move the tables and indexes of several users at once.
To make it one by one:
ALTER TABLE xxxxxx MOVE TABLESPACE TEST; ALTER INDEX xxxxxx REBUILD TABLESPACE TEST;
To move data from multiple owners. In this example OWNER1 and OWNER2:
select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE ' || OWNER || ';' from DBA_INDEXES WHERE OWNER IN ('OWNER1','OWNER2')
select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE ' || OWNER || ';' from DBA_TABLES WHERE OWNER IN ('OWNER1','OWNER2')
»
- David Lozano Lucas's blog
- Login to post comments

Typo error
Hi David,
Above query has a typo error.
Please use the below query:
select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE ' || TABLESPACE_NAME || ';' from DBA_INDEXES WHERE OWNER IN ('OWNER1','OWNER2');
select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE ' || TABLESPACE_NAME || ';' from DBA_TABLES WHERE OWNER IN ('OWNER1','OWNER2');
Regards
Rajabaskar Thangaraj