Move indexes from one Tablespace to another Tablespace

Method 1:


Select ' ALTER INDEX ' || owner || '.'||INDEX_NAME ||' rebuild tablespace [
 new_tablespace_name];' from DBA_indexes
 where index_name in ( select segment_name from
 dba_segments where tablespace_name='USERS')

Method 2:

declare

type vt is table of varchar2(64);
l_in vt;
begin
-- fetching all names to avoid ORA-01555
select index_name 
bulk collect into l_in
from dba_indexes where tablespace_name='<current index tablespace>';

for i in 1..l_in.count loop
execute immediate 'alter index '||l_in(i)||' rebuild tablespace <new index tablespace>';
end loop;
end;
/