The size of an Oracle table can be calculated by different ways. In this post, I will introduce 3 approaches, theoretical table sizing, logical table sizing and allocated table sizing.
Although we have listed the top 10 biggest tables in our Oracle database, we also can check single Oracle table size by similar ways.
Theoretical Table Size
We used NUM_ROWS and AVG_ROW_LEN (in byte) in DBA_TABLES to calculate how many bytes that active rows of the table are used.
The result can not be very accurate from time to time because the statistics were derived from dynamically sampling.
Theoretically, the table size can be reduced as small as the size if we compacted the table by means of reorganization or shrinking.
SQL> column owner format a10;
SQL> column table_name format a20;
SQL> select owner, table_name, num_rows * avg_row_len / 1024 / 1024 / 1024 "Theoretical Size (GB)" from dba_tables where owner = 'FINAP' and table_name = 'IMPORT_EXPORT';
OWNER TABLE_NAME Theoretical Size (GB)
---------- -------------------- ---------------------
FINAP IMPORT_EXPORT 1.35152919
Further reading: How Big a Table Can Be?
Logical Table Size
We used BLOCKS in DBA_TABLES to calculate how many bytes that the table are used, assuming that the block size is 8192 bytes (8 KB).
SQL> select owner, table_name, blocks * 8192 / 1024 / 1024 / 1024 "Logical Size (GB)" from dba_tables where owner = 'FINAP' and table_name = 'IMPORT_EXPORT';
OWNER TABLE_NAME Logical Size (GB)
---------- -------------------- -----------------
FINAP IMPORT_EXPORT 1.51263428
Allocated Table Size
We directly used BYTES in DBA_SEGMENTS to calculate how many bytes that the table are allocated.
SQL> select owner, segment_name table_name, bytes / 1024 / 1024 / 1024 "Physical Size (GB)" from dba_segments where owner = 'FINAP' and segment_name = 'IMPORT_EXPORT';
OWNER TABLE_NAME Physical Size (GB)
---------- -------------------- ------------------
FINAP IMPORT_EXPORT 1.5625
Please note that, the above statements can only be used for non-partitioned tables. As for partitioned tables, you should use the aggregation function SUM() to calculate the table physical size. For example:
SQL> select owner, segment_name table_name, sum(bytes) / 1024 / 1024 / 1024 "Physical Size (GB)" from dba_segments where owner = 'FINAP' and segment_name = 'PAY_BUY_HIST' group by owner, segment_name;
Next, you may be interested in how to check the whole database size by some approaches.