JAVA J2EE 4 U

 SET LINES 200

COLUMN owner FORMAT A20

COLUMN table_name FORMAT A30

COLUMN tablespace_name FORMAT A20

COLUMN size_gb FORMAT 999,999.99

-- Report largest tables in TABLESPACE_A (size in GB)

SELECT owner,

       table_name,

       tablespace_name,

       ROUND(SUM(bytes) / (1024 * 1024 * 1024), 2) AS size_gb

FROM dba_segments

WHERE segment_type = 'TABLE'

  AND tablespace_name = 'TABLESPACE_A'

GROUP BY owner, table_name, tablespace_name

ORDER BY size_gb DESC;

-------------------------------------------

SET PAGES 0

SET HEADING OFF

SET FEEDBACK OFF

SET LINES 200

-- Generate ALTER TABLE MOVE commands for large tables in TABLESPACE_A

SELECT 'ALTER TABLE ' || owner || '.' || table_name ||

       ' MOVE TABLESPACE TABLESPACE_B;' AS move_cmd

FROM dba_segments

WHERE segment_type = 'TABLE'

  AND tablespace_name = 'TABLESPACE_A'

ORDER BY bytes DESC;