Moving data files from a nearly full mount point to other mount points which could have more space to accommodate growing data files seems a common job for DBA. Here I simplify the scenario to a basic procedure that can be easy to follow.
- Some data files will be moved to the new location.
- Their tablespaces will remain unchanged.
Determine what datafiles are going to move
For example, we would like to move two data files below:
See what tablespaces will be affected
SQL> column datafile_name format a40;
SQL> column tablespace_name format a20;
SQL> select d.name datafile_name, t.name tablespace_name from v$datafile d inner join v$tablespace t on d.ts# = t.ts# where d.name in ('/oradata1/ORCL/datafile_123.dbf', '/oradata1/ORCL/datafile_456.dbf');
Although the two data files are going to move to another place, their tablespace belonging to will remain unchanged in our case.
Take these tablespaces offline
SQL> alter tablespace tbs_1 offline;
SQL> alter tablespace tbs_4 offline;
We have to take their tablespaces offline first for further operations, but the result will affect online users who are using them. Therefore, you should notify online users before taking any actions.
Alternatively, you can take data files offline instead of tablespaces, if the database is in ARCHIVELOG mode. You don't have to know the tablespaces that the data files belong to.
SQL> alter database datafile '/oradata1/ORCL/datafile_123.dbf' offline;
SQL> alter database datafile '/oradata1/ORCL/datafile_456.dbf' offline;
Copy the data files to the new location on OS-level
[oracle@test ~]$ cp -ip /oradata1/ORCL/datafile_123.dbf /oradata2/ORCL/datafile_123.dbf
[oracle@test ~]$ cp -ip /oradata1/ORCL/datafile_456.dbf /oradata2/ORCL/datafile_456.dbf
For protecting the data files with same names in the new location from overwriting, we copy the data files with interactive mode "-i" for safety. Further more, we remain the meta data of source files.
Change the location of the data files to the new path on database-level
SQL> alter database rename file '/oradata1/ORCL/datafile_123.dbf' to '/oradata2/ORCL/datafile_123.dbf';
SQL> alter database rename file '/oradata1/ORCL/datafile_456.dbf' to '/oradata2/ORCL/datafile_456.dbf';
As you can see, the renaming process is actually an operation of changing the absolute path of data files.
Alternatively, you can do renaming on tablespace-level.
SQL> alter tablespace tbs_1 rename datafile '/oradata1/ORCL/datafile_123.dbf' to '/oradata2/ORCL/datafile_123.dbf';
SQL> alter tablespace tbs_4 rename datafile '/oradata1/ORCL/datafile_456.dbf' to '/oradata2/ORCL/datafile_456.dbf';
I still think you should always operate data files on database level, if there's nothing to do with the tablespace throughout your plan.
Bring the tablespaces online
SQL> alter tablespace tbs_1 online;
SQL> alter tablespace tbs_4 online;
Or do this:
SQL> recover datafile '/oradata2/ORCL/datafile_123.dbf';
SQL> recover datafile '/oradata2/ORCL/datafile_456.dbf';
SQL> alter database datafile '/oradata2/ORCL/datafile_123.dbf' online;
SQL> alter database datafile '/oradata2/ORCL/datafile_456.dbf' online;
As you can see, I recover the two data files before bringing them online. Don't forget that, this is only for ARCHIVELOG mode.
Now, data files are now ready for service.
Remove the unused data files
For reclaiming the disk space, you have to remove the source data files.
[oracle@test ~]$ rm -i /oradata1/ORCL/datafile_123.dbf
[oracle@test ~]$ rm -i /oradata1/ORCL/datafile_456.dbf
After that, you may check the disk space for sure.