Renaming and Moving Data Files While the Database is Closed

Invoke SQL*Plus. Connect as a user with SYSDBA privileges.

Query DBA_DATA_FILES to determine the name of the tablespace that your data files belong to:

SELECT tablespace_name
FROM dba_data_files
WHERE file_name = '<your data file name>';

Query DBA_DATA_FILES again to verify that all of the data files that belong to the tablespace have AVAILABLE in the STATUS column:

SELECT file_name, status
FROM dba_data_files
WHERE tablespace_name = '<your tablespace name>';

Query DBA_TABLESPACES to determine whether the tablespace is in READ ONLY mode:

SELECT status
FROM dba_tablespaces
WHERE tablespace_name = '<your tablespace name>';

If the tablespace is not in READ ONLY mode, issue the following command to change it to READ ONLY:

ALTER TABLESPACE <your tablespace name> READ ONLY;

Verify your change:

SELECT status
FROM dba_tablespaces
WHERE tablespace_name = '<your tablespace name>';

At the operating system prompt, use the appropriate command to copy your data files.

Take the tablespace offline using the following command:

ALTER TABLESPACE <your tablespace name> OFFLINE;

Use the ALTER DATABASE command to update the control file with the new file location(s) as follows. Issue this command for each file that you renamed or moved.

ALTER DATABASE RENAME FILE '<old location/old file name>'
TO '<new location/new file name>';

Bring the tablespace back online:

ALTER TABLESPACE <your tablespace name> ONLINE;

Make the tablespace READ WRITE again:

ALTER TABLESPACE <your tablespace name> READ WRITE;

Verify your change to READ WRITE:

SELECT status
FROM dba_tablespaces
WHERE tablespace_name = '<your tablespace name>';

Query DBA_DATA_FILES again to verify that all of the data files that belong to the tablespace have AVAILABLE in the STATUS column:

SELECT file_name, status
FROM dba_data_files
WHERE tablespace_name = '<your tablespace name>';

At the operating system prompt, use the appropriate command to remove your old data file.

Invoke SQL*Plus. Connect as a user with SYSDBA privileges.

If your database is open, shut down the instance using the NORMAL, IMMEDIATE, or TRANSACTIONAL option:

SHUTDOWN IMMEDIATE

At the operating system prompt, use the appropriate command to copy your data files.

Start the instance and mount the database:

STARTUP MOUNT

Use the ALTER DATABASE command to update the control file with the new file location(s) as follows. Issue this command for each file that you renamed or moved.

ALTER DATABASE RENAME FILE '<old location/old file name>'
TO '<new location/new file name>';

Open your database:

ALTER DATABASE OPEN;

Query V$DATAFILE to verify your changes:

SELECT name FROM v$datafile;