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.
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.