Oracle – Balancing the Size of Datafiles for a Tablespace

disk-spaceoracletablespaces

I have a tablespace where I had to add datafiles, because a partion run full. The new datafiles are not filled yet. Can I move data from one datafile to the other?

Best Answer

If a tablespace has multiple datafiles, Oracle manages what goes into what datafile. You have no control over this. If you have enough diskspace to do so, I suggest the following data-juggling-opperation:

  • Create a new tablespace with a datafile big enough to hold all the data of your source tablespace.
  • Move all the tables, indexes and other objects to this new tablespace.
  • Resize the datafiles of your old tablespace how you would like them.
  • Move all tables, indexes and other objects back to the resized tablespace.
  • Rebuild all your indexes
  • Drop the temporary tablespace you created.

Sidenote. If you had used MAXSIZE on your datafiles you would probably not have been in this situation right now... I tend to set MAXSIZE to a conservative size, since I rather run out of datafile size, than out of diskspace...

Some tools to get you going

Create DML statements to move your tables around

select distinct 'ALTER TABLE ' || owner || '.' || segment_name || ' MOVE TABLESPACE target_tablespace;'
from dba_segments 
where tablespace_name = 'old_tablespace'
and segment_type = 'TABLE';

Create DML statements to move your indexes around

select distinct 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD TABLESPACE target_tablespace;'
from dba_indexes
where tablespace_name = 'old_tablespace'
and index_type != 'LOB';

Resizing datafiles 

ALTER DATABASE DATAFILE 'D:\ORADATA\file.DBF' RESIZE xxxMB MAXSIZE xxxMB;

Rebuilding indexes

ALTER INDEX <SCHEMA>.<INDEX> REBUILD;
Related Question