How to Find Biggest Objects in Oracle Database?

SELECT DS.OWNER,
DS.SEGMENT_TYPE,
DS.SEGMENT_NAME,
CASE DS.SEGMENT_TYPE
WHEN ‘TABLE’ THEN DS.SEGMENT_NAME
WHEN ‘INDEX’ THEN DI.TABLE_NAME
WHEN ‘LOBSEGMENT’ THEN DL.TABLE_NAME
WHEN ‘LOBINDEX’ THEN DI.TABLE_NAME
END
TABLE_NAME,
NVL (DL.COLUMN_NAME, ‘—–‘)
COLUMN_NAME,
NVL (DI.INDEX_NAME, ‘—–‘)
INDEX_NAME,
DS.BYTES / 1024 / 1024 / 1024
SIZE_IN_GB
FROM DBA_SEGMENTS DS
LEFT JOIN DBA_LOBS DL
ON DS.OWNER || DS.SEGMENT_NAME = DL.OWNER || DL.SEGMENT_NAME
LEFT JOIN DBA_INDEXES DI
ON DS.OWNER || DS.SEGMENT_NAME = DI.OWNER || DI.INDEX_NAME
WHERE DS.SEGMENT_TYPE IN (‘TABLE’,
‘INDEX’,
‘LOBSEGMENT’,
‘LOBINDEX’)
ORDER BY DS.BYTES DESC