Oracle: Getting Current Table Size and Extent Information

In Oracle, you can use the user_extents and user_tables dictionary views to get the current table size and extent information. Below is an example:

Oracle SQL Query to Get the Current Table Size and Extent Information

The following query returns the table name, initial extent size, total extents used, current table size in MB, total rows, next extent size in MB, and the maximum extents:

Select
segment_name table_name,
size_in_mb
From
(
Select
ds.tablespace_name,
segment_name,
round(Sum(ds.bytes) /(1024 * 1024)) As size_in_mb
From
user_segments ds
Where
segment_name In (
Select
table_name
From
user_tables
)
Group By
ds.tablespace_name,
segment_name
)
Where
size_in_mb > 0
Order By
size_in_mb Desc

To get the table size for the tables having LOB columns, use the following query:

Select
l.table_name,
( s.bytes ) / ( 1024 * 1024 ) size_in_mb
From
user_segments s,
user_lobs l
Where
l.segment_name = s.segment_name