Skip to content
Home » Oracle Database » How to Find Biggest Tables in Oracle

How to Find Biggest Tables in Oracle

Top 10 Biggest Tables in Whole Database

When the disk space is running out, you might want 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.

Moreover, I excluded tables which have been dropped and renamed with "BIN" prefixed, which means, they are now in recycle bin.

Top 10 Biggest Tables in Specific Tablespace

To find biggest tables in a specific tablespace, you should add one more filter to the statement.

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%' and tablespace_name in ('ERP3TB1') order by 3 desc) where rownum <= 10;

Please note that, the table sizes we calculated are physical sizes. A table size can also be calculated as theoretical and logical size. They have different meanings for database administrators.

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 *