Find Biggest Segments
A segment in Oracle database is a set of extents that contains all the data for a specific logical storage structure within a tablespace. Which means segments contain not only metadata but real data. Normally, segments include tables, indexes and large objects, etc.
Let's see what biggest segments are in our database.
SQL> column owner format a10;
SQL> column segment_type format a20;
SQL> column segment_name format a30;
SQL> column "SIZE (GB)" format 99999.99;
SQL> select * from (select owner, segment_type, segment_name, bytes/1024/1024/1024 "SIZE (GB)" from dba_segments where segment_name not like 'BIN%' order by 4 desc) where rownum <= 10;
OWNER SEGMENT_TYPE SEGMENT_NAME SIZE (GB)
---------- -------------------- ------------------------------ ---------
ERP35 TABLE ERP35_GWRLTIRIVA 133.03
ERP35 TABLE ERP35_SDLFGHJOEJ5DX 80.35
ERP35 INDEX ERP35_LSKDGJRWEI_PK 61.25
ERP35 LOBSEGMENT SYS_LOB0000220085C00094$$ 51.88
ERP35 INDEX ERP35_SDGKLJRGLM4KL_PK 47.50
ERP35 TABLE ERP35_XLBERUZLR 45.36
ERP35 TABLE ERP35_SDGKEJWLZC 44.63
ERP35 LOBSEGMENT SYS_LOB0000220091C00045$$ 41.71
ERP35 LOBSEGMENT SYS_LOB0000220069C00012$$ 41.68
ERP35 LOBSEGMENT SYS_LOB0000220036C00039$$ 35.00
10 rows selected.
Please note that, the largest segments in the top 10 list are not always tables or indexes, some are large objects (LOB). LOB could be original application documents, scanned PDF or images, etc. Sometimes, you can even see table partition or index partition in the list.
LOB segments like SYS_LOB0000220085C00094$$, you can never guess what table or column uses it from its name. We need a way to know which table and column owns this LOB.
Tables and indexes are all segments, you can also use this way to list the biggest tables in your database.