Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

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

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

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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