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.