4

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.

migrated from stackoverflow.com Jan 6 '12 at 15:28

This question came from our site for professional and enthusiast programmers.

6

A rough estimate of your table size is:

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

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 = '<tablename>';

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.