How to Check Oracle Table Size

  • by

Although we have listed the top 10 biggest tables in our Oracle database, we also can check single 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.

Leave a Reply

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