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';
ALTER DATABASE DATAFILE 'D:\ORADATA\file.DBF' RESIZE xxxMB MAXSIZE xxxMB;
ALTER INDEX <SCHEMA>.<INDEX> REBUILD;
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: