How to move segments between tablespace

select * from dba_objects where status <> 'VALID' and owner='ALDO'

Generate script to move segments, when prompted supply destination tablespace:

SELECT 
DECODE (segment_type,
  'TABLE', 'alter table ' || owner || '.' || segment_name || ' move tablespace &tablespace_name;',
  'INDEX', 'ALTER INDEX ' || owner || '.' || segment_name || ' REBUILD NOPARALLEL NOLOGGING TABLESPACE &tablespace_name;',
  'LOBINDEX', 'ALTER TABLE ' || owner || '.' || table_name || ' move lob ('|| lob_column || ') store as (tablespace &tablespace_name);',
  'segment type ' || segment_type || ' not implemented') sql_move
         --, table_name, tablespace_name curr_tbs
FROM (SELECT DECODE(dba_segments.segment_type,
  'TABLE', dba_segments.segment_name,
  'INDEX', dba_indexes.table_name,
  'LOBINDEX', (select table_name from dba_lobs where index_name = dba_segments.segment_name),
  'Unable to get table name for segment type ' || dba_segments.segment_type || '. Not implemented') table_name,
  dba_segments.segment_name, segment_type, dba_segments.owner,
  dba_lobs.column_name lob_column,
  dba_segments.tablespace_name
  FROM dba_segments, dba_indexes, dba_lobs
  WHERE dba_indexes.owner(+) = dba_segments.owner
  AND dba_indexes.index_name(+) = dba_segments.segment_name
  AND dba_lobs.owner(+) = dba_segments.owner
  AND dba_lobs.index_name(+) = dba_segments.segment_name
) where tablespace_name = 'NEWTMP'
ORDER BY owner, table_name, segment_type DESC

How to move a Blob field to another tablespace (may be compressed for 10g)
    
CREATE TABLESPACE "TBS_COMPRESS" 
    LOGGING 
    DATAFILE 
    'C:\ORACLE\PRODUCT\10.1.0\ORADATA\AL11\TBS_COMPRESS.ora' SIZE
    5M AUTOEXTEND 
    ON NEXT  2M MAXSIZE UNLIMITED
	default compress;

select 
--dba_segments.bytes, dba_lobs.*,
'alter table ' || dba_lobs.owner||'.'||dba_lobs.table_name ||' move lob('||column_name||') store as '|| 
dba_lobs.segment_name ||' (tablespace tbs_compress);' move_script
, dba_tables.tablespace_name table_tablespace
, (select sum(bytes)/1024/1024 from dba_segments 
   where segment_name=dba_tables.table_name and owner=dba_tables.owner) table_size_MB
from dba_segments, dba_lobs, dba_indexes, dba_tables
where  dba_lobs.owner in ('WEBOWN')
and dba_indexes.tablespace_name = 'USERS'
and dba_lobs.segment_name = dba_segments.segment_name
--No system tbales
and instr(dba_lobs.table_name, '$') = 0
and dba_indexes.owner=dba_lobs.owner
and dba_indexes.index_name=dba_lobs.index_name
and dba_tables.owner(+)=dba_lobs.owner
and dba_tables.table_name(+)=dba_lobs.table_name
order by bytes desc;

select 'dbms_stats.gather_table_stats(ownname=> '''||owner||''', tabname=> '''||table_name||''', partname=> NULL , estimate_percent=> 49, cascade=>true );' script
from dba_tables
where tablespace_name = 'DEST'

select 'DBMS_STATS.GATHER_INDEX_STATS (ownname => '''||owner||''', indname => '''||index_name||''', partname => NULL);' script 
from dba_indexes
where tablespace_name='DEST'

select * from dba_objects where status <> 'VALID' and owner='ALDO'