An Oracle database stores data inside Tablespaces which are logical storage units. Physically this data is stored as files in the Operating systems file system and are called datafiles. A Oracle database consists of one or more tablespaces and each tablespace contains one or more datafiles. Sometimes due to disk space constraints or other system related factors you might want to move the datafiles to a different location or rename them. This can be done by following the steps below.
Note: Before you start make sure you have a current backup of database.
1. Login to the database as a user with system privileges, such as the sys user.
2. List available tablespaces and their datafiles by querying the DBA_DATA_FILES tables.
SELECT TABLESPACE_NAME, FILE_NAME, BYTES, ONLINE_STATUS FROM DBA_DATA_FILES;
The output from this query helps to identify the tablespace and the datafiles correctly.
3. Take the tablespace offline using the ALTER TABLESPACE statement with OFFLINE clause
ALTER TABLESPACE tblspace_name OFFLINE NORMAL;
4. Rename, copy or move your datafiles using normal Operating System commands.
5. Rename the datafile usin ALTER TABLESPACE statement with RENAME DATAFILE clause
ALTER TABLESPACE tblspace_name RENAME DATAFILE
'old_file_name_with_full_path'
TO
'old_file_name_with_full_path';
You may rename multiple datafiles at same time. In that case seperated the datafile names by comma(,).
ALTER TABLESPACE tblspace_name RENAME DATAFILE
'old_datafile-1',
'old_datafile-2'
TO
'new_datafile-1',
'new_datafile-2';
6. Bring the tablespace back online using ALTER TABLESPACE statement with ONLINE clause
ALTER TABLESPACE tblspace_name ONLINE;
Renaming or relocating datafiles in multiple tablespaces
The procedure described above works for datafiles in a single Tablespace. If you need to rename or relocate datafiles from multiple tablespaces in a database you may do so with the ALTER DATABASE statement and RENAME FILE clause.
ALTER DATABASE RENAME FILE
'old_datafile-tablespace-1',
'old_datafile-tablespace-2'
TO
'new_datafile-tablespace-1',
'new_datafile-tablespace-2'
