How to Check Table Size in Oracle

Galaxy Glossary

How do I check the size of a table in Oracle?

Check the current and allocated disk space a specific Oracle table consumes.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why would I need to check an Oracle table’s size?

Knowing table size prevents storage overruns, guides partitioning decisions, and lets you forecast growth for capacity planning.

What catalog views reveal table size fast?

Use USER_SEGMENTS for objects in your schema, DBA_SEGMENTS for all objects, or ALL_SEGMENTS for objects you can access. These views return allocated bytes, not free space.

How do I get the size of a single table?

Query USER_SEGMENTS filtering by SEGMENT_NAME. Convert BYTES to MB or GB with simple math.

Code example: one table

SELECT ROUND(bytes / 1024 / 1024) AS size_mb FROM user_segments WHERE segment_name = 'ORDERS';

How can I list the largest tables in my schema?

Sort USER_SEGMENTS by BYTES to see heavy hitters.

Code example: top 5 tables

SELECT segment_name, ROUND(bytes / 1024 / 1024) AS size_mb FROM user_segments WHERE segment_type = 'TABLE' ORDER BY bytes DESC FETCH FIRST 5 ROWS ONLY;

How do I include indexes, LOBs, and partitions?

Sum BYTES across SEGMENT_TYPEs that belong to the same table. Use DBA_SEGMENTS when tables span schemas.

Code example: total footprint of Products table

SELECT segment_name, ROUND(SUM(bytes) / 1024 / 1024) AS total_mb FROM dba_segments WHERE owner = 'SHOP' AND segment_name = 'PRODUCTS' GROUP BY segment_name;

How do I monitor growth over time?

Schedule the above query in DBMS_SCHEDULER, store results in a metrics table, and graph them in a dashboard of your choice.

Best practices for accurate readings

Analyze after purges to reclaim space; use SHRINK SPACE for ASSM tablespaces. Always account for indexes and LOBs when sizing.

Why How to Check Table Size in Oracle is important

How to Check Table Size in Oracle Example Usage


-- How large is the OrderItems table, including indexes?
SELECT ROUND(SUM(bytes) / 1024 / 1024, 2) AS orderitems_mb
FROM   dba_segments
WHERE  owner = 'SHOP'
  AND  segment_name = 'ORDERITEMS';

How to Check Table Size in Oracle Syntax


-- Size of one table (MB)
SELECT ROUND(bytes / 1024 / 1024) AS size_mb
FROM   user_segments
WHERE  segment_name = 'CUSTOMERS';

-- Size including indexes & LOBs
SELECT segment_name,
       ROUND(SUM(bytes) / 1024 / 1024) AS total_mb
FROM   dba_segments
WHERE  owner = 'SHOP'
  AND  segment_name = 'ORDERS'
GROUP  BY segment_name;

-- Largest 5 tables in entire database
SELECT owner, segment_name,
       ROUND(SUM(bytes) / 1024 / 1024) AS total_mb
FROM   dba_segments
WHERE  segment_type = 'TABLE'
GROUP  BY owner, segment_name
ORDER  BY total_mb DESC
FETCH  FIRST 5 ROWS ONLY;

Common Mistakes

Relying on DBA_TABLES.BLOCKS instead of DBA_SEGMENTS.BYTES. BLOCKS shows current rows, not allocated space, so you underestimate size. Query BYTES instead.
Ignoring indexes and LOB segments. A table may appear small, yet its indexes consume more space. Always SUM(BYTES) across segment types for true footprint.