How to Find Biggest Tables in Oracle

  • by

Find Biggest Oracle Table Size

Beside to calculate the size of a single table by some approaches, you can also list the most noticeable tables on space usage. Specifically, when the disk space is running out, you might like to calculate Oracle table size and list the top 10 or top N biggest tables in your database in order to release some space by dropping unused tables. Here's how we find the top 10 largest tables in Oracle.

SQL> column owner format a10;
SQL> column table_name format a30;
SQL> column "SIZE (GB)" format 99999.99;
SQL> select * from (select owner, segment_name table_name, bytes/1024/1024/1024 "SIZE (GB)" from dba_segments where segment_type = 'TABLE' and segment_name not like 'BIN%' order by 3 desc) where rownum <= 10;

OWNER      TABLE_NAME                     SIZE (GB)
---------- ------------------------------ ---------
ERP35      ERP35_GWRLTIRIVA                  133.03
ERP35      ERP35_SDLFGHJOEJ5DX                80.35
ERP35      ERP35_XLBERUZLR                    45.36
ERP35      ERP35_SDGKEJWLZC                   44.63
ERP35      ERP35_RTBVXGRLX                    18.35
ERP35      ERP35_ASBWEIR                      13.75
ERP35      ERP35_XBGRTUPQFL                   11.25
ERP35      ERP35_GFONVDEKGK                   10.63
ERP35      ERP35_CRKMGTHVZ                     9.38
ERP35      ERP35_UGVMKOFDEH                    8.42

10 rows selected.

We use dba_segments dictionary view to calculate the size of Oracle table and then sort them in descending order, which is from the largest to the smallest of Oracle table size. Please note that, I excluded tables which have been dropped and renamed with "BIN" prefixed, which means, they are now in recycle bin.

Tables, indexes and table partitions are all segments, you can even see various kinds of LOB (Large Object) in the top 10 list of the biggest Oracle segment size in your database.

Leave a Reply

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