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.