You can use the ALTER DATABASE MOVE DATAFILE SQL statement to rename or relocate online data files. This statement enables you to rename or relocate a data file while the database is open and users are accessing the data file.
When you rename or relocate online data files, the pointers to the data files, as recorded in the database control file, are changed. The files are also physically renamed or relocated at the operating system level.
You might rename or relocate online data files because you want to allow users to access the data files when you perform one of the following tasks:
-
Move the data files from one type of storage to another
-
Move data files that are accessed infrequently to lower cost storage
-
Make a tablespace read-only and move its data files to write-once storage
-
Move a database into Oracle Automatic Storage Management (Oracle ASM)
When you run the ALTER DATABASE MOVE DATAFILE statement and a file with the same name exists in the destination location, you can specify the REUSE option to overwrite the existing file. When REUSE is not specified, and a file with the same name exists in the destination location, the existing file is not overwritten, and the statement returns an error.
By default, when you run the ALTER DATABASE MOVE DATAFILE statement and specify a new location for a data file, the statement moves the data file. However, you can specify the KEEP option to retain the data file in the old location and copy it to the new location. In this case, the database only uses the data file in the new location when the statement completes successfully.
When you rename or relocate a data file with ALTER DATABASE MOVE DATAFILE statement, Oracle Database creates a copy of the data file when it is performing the operation. Ensure that there is adequate disk space for the original data file and the copy during the operation.
You can view the name, location, and online status of each data file by querying the DBA_DATA_FILES view.
Note:
-
The
ALTERDATABASEMOVEDATAFILEstatement raises an error if the specified data file is offline. -
If you are using a standby database, then you can perform an online move data file operation independently on the primary and on the standby (either physical or logical). The standby is not affected when a data file is moved on the primary, and vice versa. See Oracle Data Guard Concepts and Administration for more information.
-
A flashback operation does not relocate a moved data file to its previous location. If you move a data file online from one location to another and later flash back the database to a point in time before the move, then the data file remains in the new location, but the contents of the data file are changed to the contents at the time specified in the flashback. See Oracle Database Backup and Recovery User's Guide for more information about flashback database operations.
-
When you relocate a data file on the Windows platform, the original data file might be retained in the old location, even when the
KEEPoption is omitted. In this case, the database only uses the data file in the new location when the statement completes successfully. You can delete the old data file manually after the operation completes if necessary.
To rename or relocate online data files:
-
In SQL*Plus, connect to the database as a user with
ALTER DATABASEsystem privilege.See "Starting Up a Database Using SQL*Plus".
-
Issue the
ALTERDATABASEMOVEDATAFILEstatement and specify the data file.
Example 14-1 Renaming an Online Data File
This example renames the data file user1.dbf to user01.dbf while keeping the data file in the same location.
ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' TO '/u01/oracle/rbdb1/user01.dbf';
Example 14-2 Relocating an Online Data File
This example moves the data file user1.dbf from the /u01/oracle/rbdb1/ directory to the /u02/oracle/rbdb1/ directory. After the operation, the file is no longer in the /u01/oracle/rbdb1/ directory.
ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' TO '/u02/oracle/rbdb1/user1.dbf';
Example 14-3 Copying an Online Data File
This example copies the data file user1.dbf from the /u01/oracle/rbdb1/ directory to the /u02/oracle/rbdb1/ directory. After the operation, the old file is retained in the /u01/oracle/rbdb1/ directory.
ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' TO '/u02/oracle/rbdb1/user1.dbf' KEEP;
Example 14-4 Relocating an Online Data File and Overwriting an Existing File
This example moves the data file user1.dbf from the /u01/oracle/rbdb1/ directory to the /u02/oracle/rbdb1/ directory. If a file with the same name exists in the /u02/oracle/rbdb1/ directory, then the statement overwrites the file.
ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' TO '/u02/oracle/rbdb1/user1.dbf' REUSE;
Example 14-5 Relocating an Online Data File to Oracle ASM
This example moves the data file user1.dbf from the /u01/oracle/rbdb1/ directory to an Oracle ASM location.
ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' TO '+dgroup_01/data/orcl/datafile/user1.dbf';
Example 14-6 Moving a File from One ASM Location to Another ASM Location
This example moves the data file from one Oracle ASM location to another Oracle ASM location.
ALTER DATABASE MOVE DATAFILE '+dgroup_01/data/orcl/datafile/user1.dbf' TO '+dgroup_02/data/orcl/datafile/user1.dbf';
You also can move an online data file with Oracle ASM by mirroring the data file and then removing the original file location from the mirror. The online data file move operation might be faster when you use Oracle ASM to move the file instead of the ALTER DATABASE MOVE DATAFILE statement.
