Data Dictionary ViewSegment

How to Find Biggest Segments in Oracle

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, some of 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.

Tables and indexes are all segments, you can also use this way to list the biggest tables in your database.

Leave a Reply

Your email address will not be published. Required fields are marked *