This tip lets you identify tables that need more than a specified amount of space. This example uses a size of 1GB.

SELECT segment_type, SUBSTR (segment_name, 1, 30),
SUBSTR (tablespace_name, 1, 30),
SUM (TRUNC (BYTES / (1024 * 1024 * 1024), 2)) "size in GB"
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
AND owner IN ('BO_USER', 'DWH_OWNER', 'OWBTARGET', 'SM_OWNER')
HAVING SUM (TRUNC (BYTES / (1024 * 1024 * 1024), 2)) > 1
GROUP BY segment_type,
SUBSTR (segment_name, 1, 30),
SUBSTR (tablespace_name, 1, 30)

Identify Tables That Need Space