Oracle database: Move tables and indexes to another tablespace

This way use to move the data objects to another tablespace. Applicable on 9i onward.

-- Tables
select distinct 'alter table '||owner||'.'||segment_name||' move tablespace STG_TS;'
from dba_extents
where tablespace_name ='STG'
and segment_type in ('TABLE');

-- Tables partitions
select distinct 'alter table '||owner||'.'||segment_name||' move PARTITION '||PARTITION_NAME||' tablespace STG_TS;'
from dba_extents
where tablespace_name ='STG'
and segment_type in ('TABLE PARTITION');

-- Indexes
select distinct 'alter index '||owner||'.'||segment_name||' rebuild tablespace STG_TS;'
from dba_extents
where tablespace_name ='STG'
and segment_type in ('INDEX');

-- LOB's Index
select distinct 'alter table '||a.owner||'.'||b.table_name||'  move LOB('||b.column_name||') store as (tablespace STG_TS);'
from dba_extents a , dba_lobs b
where a.tablespace_name ='STG'
and  a.segment_name = b.index_name
and  segment_type in ('LOBINDEX');

-- Index
select distinct 'alter table '||a.owner||'.'||b.table_name||'  move LOB('||b.column_name||') store as (tablespace STG_TS);'
from dba_extents a , dba_lobs b
where a.tablespace_name ='STG'
and  a.segment_name = b.index_name
and  segment_type in ('LOBSEGMENT');