Renaming and Moving Data Files

This module describes how to rename and move files.

This module discusses the following:

Renaming and Moving Data Files While the Database is Open

Renaming and Moving Data Files While the Database is Closed

Move your mouse over this icon to show all screenshots. You can also move your mouse over each individual icon to see only the screenshot associated with it.

Back to List of Topics

You can rename and move data files while the database is open. You must determine what tablespace the data files belong to and put the tablespace in READ ONLY mode.

Complete the following steps to rename or move your data files:

1.

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

 

2.

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>';

Move your mouse over this icon to see the image

 

3.

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>';

Move your mouse over this icon to see the image

 

4.

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

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

Move your mouse over this icon to see the image

 

5.

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;

Move your mouse over this icon to see the image

 

6.

Verify your change:

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

Move your mouse over this icon to see the image

 

7.

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

 

8.

Take the tablespace offline using the following command:

ALTER TABLESPACE <your tablespace name> OFFLINE;

Move your mouse over this icon to see the image

 

9.

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>';

Move your mouse over this icon to see the image

 

10.

Bring the tablespace back online:

ALTER TABLESPACE <your tablespace name> ONLINE;

Move your mouse over this icon to see the image

 

11.

Make the tablespace READ WRITE again:

ALTER TABLESPACE <your tablespace name> READ WRITE;

Move your mouse over this icon to see the image

 

12.

Verify your change to READ WRITE:

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

Move your mouse over this icon to see the image

 

13.

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>';

Move your mouse over this icon to see the image

 

14.

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

 

Back to List of Topics

Complete the following steps to rename or move a data file while the database is closed:

1.

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

 

2.

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

SHUTDOWN IMMEDIATE

Move your mouse over this icon to see the image

 

3.

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

 
4.

Start the instance and mount the database:

STARTUP MOUNT

Move your mouse over this icon to see the image

 

5.

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>';

Move your mouse over this icon to see the image

 

6.

Open your database:

ALTER DATABASE OPEN;

Move your mouse over this icon to see the image

 

7.

Query V$DATAFILE to verify your changes:

SELECT name FROM v$datafile;

Move your mouse over this icon to see the image

 

Move your mouse over this icon to hide all screenshots