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?


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


Rebuilding indexes


An alternative (and simpler) solution, if you don't mind moving all your data to the new partition:

  • Drop the new datafile.
  • Move the old datafile to the new partition.
  • Optional: Create a second datafile on the old partition.

Here's an excellent manual on moving datafiles around:

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.