How to find the size of a table in Oracle

To find the size of a table in Oracle, you can use several methods depending on the level of detail you need. Here's a summary of common approaches:

1. Using DBA_SEGMENTS (Shows table size)

The DBA_SEGMENTS view provides detailed information about the storage used by database objects, including tables. You can use this to find the size of a specific table.

Query for a Table's Size:

SELECT segment_name,
       segment_type,
       tablespace_name,
       SUM(bytes) / 1024 / 1024 AS size_mb
FROM dba_segments
WHERE segment_type = 'TABLE'
  AND owner = '<SCHEMA_NAME>'
  AND segment_name = '<TABLE_NAME>'
GROUP BY segment_name, segment_type, tablespace_name;

• Replace <SCHEMA_NAME> with the schema name (owner of the table).

• Replace <TABLE_NAME> with the name of the table.

• The result will show the size in MB.

---

2. Using USER_SEGMENTS (If you don't have DBA privileges)

If you don't have access to DBA_SEGMENTS (which is the case for regular users), you can use USER_SEGMENTS, which works similarly but only shows data for objects owned by the current user.

SELECT segment_name,
       segment_type,
       tablespace_name,
       SUM(bytes) / 1024 / 1024 AS size_mb
FROM user_segments
WHERE segment_type = 'TABLE'
  AND segment_name = '<TABLE_NAME>'
GROUP BY segment_name, segment_type, tablespace_name;

• Replace <TABLE_NAME> with the table name you're interested in.

• The result will also show the table size in MB.

---

3. Using ALL_TAB_COLUMNS (Approximate size using column stats)

If you need an approximate size based on the number of rows and average column length, you can query ALL_TAB_COLUMNS and ALL_TABLES.

SELECT t.table_name,
       SUM(c.data_length) / 1024 / 1024 AS estimated_size_mb
FROM all_tables t
JOIN all_tab_columns c
    ON t.table_name = c.table_name
WHERE t.table_name = '<TABLE_NAME>'
  AND t.owner = '<SCHEMA_NAME>'
GROUP BY t.table_name;

• Replace <SCHEMA_NAME> with the schema and <TABLE_NAME> with the name of the table.

• This method provides an estimated size based on column data lengths but doesn't account for actual storage blocks used (like DBA_SEGMENTS).

---

Key Points:

• DBA_SEGMENTS is the most reliable method for getting the actual size.
• If you only have access to the tables in your schema, use USER_SEGMENTS.
• DBMS_SPACE provides detailed space usage but requires more complex setup.