Data Dictionary ViewTable

How to Find Largest Tables in Oracle

Find Largest Tables

When the disk space is running out, you might like to list the 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 table and then sort them in descending order, which is from the largest to the smallest. 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 largest segments in your database.

Leave a Reply

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