Skip navigation.

Moving multiple tables and indexes between tablespaces at once

David Lozano Lucas's picture

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')

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