patternMinor

Estimate space left in Oracle

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

estimateoraclespaceleft

Problem

I have a large Oracle table that need to be re-partition. I was wonder there is a way to know that I have enough space to create a copy of this table with new partition and swap the new one with the old one.

Solution

A rough estimate of your table size is:

select sum(BYTES) / 1024 / 1024 as SIZE_MB
from   USER_SEGMENTS
where  SEGMENT_NAME = '';

However, see the question How do I calculate tables size in Oracle for better detail as the real size of a copied table on disk can differ from the original.
You can then check how much space you have in the tablespace using:

select TS.TABLESPACE_NAME
      ,nvl(nvl(DF.BYTES, 0) / 1024 / 1024, 0) + nvl(nvl(FS.BYTES, 0) / 1024 / 1024, 0) as AVAIL_SPC_MB
from   DBA_TABLESPACES TS
       left join (select   TABLESPACE_NAME, (sum(MAXBYTES) - sum(BYTES)) BYTES
                  from     DBA_DATA_FILES
                  group by TABLESPACE_NAME) DF
         on TS.TABLESPACE_NAME = DF.TABLESPACE_NAME
       left join (select   TABLESPACE_NAME, sum(BYTES) BYTES
                  from     DBA_FREE_SPACE
                  group by TABLESPACE_NAME) FS
         on TS.TABLESPACE_NAME = FS.TABLESPACE_NAME
       left join DBA_TABLES TAB
         on TS.TABLESPACE_NAME = TAB.TABLESPACE_NAME
where  TAB.TABLE_NAME = '';

If you don't have enough space then check your other tablespaces as you can temporarily create the table in there if you have permissions.

Code Snippets

select sum(BYTES) / 1024 / 1024 as SIZE_MB
from   USER_SEGMENTS
where  SEGMENT_NAME = '<tablename>';

select TS.TABLESPACE_NAME
      ,nvl(nvl(DF.BYTES, 0) / 1024 / 1024, 0) + nvl(nvl(FS.BYTES, 0) / 1024 / 1024, 0) as AVAIL_SPC_MB
from   DBA_TABLESPACES TS
       left join (select   TABLESPACE_NAME, (sum(MAXBYTES) - sum(BYTES)) BYTES
                  from     DBA_DATA_FILES
                  group by TABLESPACE_NAME) DF
         on TS.TABLESPACE_NAME = DF.TABLESPACE_NAME
       left join (select   TABLESPACE_NAME, sum(BYTES) BYTES
                  from     DBA_FREE_SPACE
                  group by TABLESPACE_NAME) FS
         on TS.TABLESPACE_NAME = FS.TABLESPACE_NAME
       left join DBA_TABLES TAB
         on TS.TABLESPACE_NAME = TAB.TABLESPACE_NAME
where  TAB.TABLE_NAME = '<tablename>';

Context

StackExchange Database Administrators Q#10267, answer score: 6

Revisions (0)

No revisions yet.