Database Reference
In-Depth Information
In the previous example, you are specifying that data file 2 be moved.
If you're moving a data file and, for any reason, want to keep a copy of the original file, you can use the
KEEP
option:
SQL> alter database move datafile 4 to '/u02/dbfile/O12C/users01.dbf' keep;
You can specify the
REUSE
clause to overwrite an existing file:
SQL> alter database move datafile 4 to '/u01/dbfile/O12C/users01.dbf' reuse;
Oracle will not allow you to overwrite (reuse) a data file that is currently being used by the database.
That's a good thing.
Performing Offline Data File Operations
If you are using Oracle 11g or lower, before you rename or move a data file, you must take the data file offline. There
are two somewhat different approaches to moving and renaming offline data files:
•
Use a combination of SQL commands and OS commands.
•
Use a combination of re-creating the control file and OS commands.
These two techniques are discussed in the next two sections.
Using SQL and OS Commands
Here are the steps for renaming a data file using SQL commands and OS commands:
1.
Use the following query to determine the names of existing data files:
SQL> select name from v$datafile;
2.
Take the data file offline, using either the
ALTER TABLESPACE
or
ALTER DATABASE DATAFILE
statement (see the previous section, “Performing Offline Data File Operations,” for details
on how to do this). You can also shut down your database and then start it in mount mode;
the data files can be moved while in this mode because they aren't open for use.
3.
Physically move the data file to the new location, using either an OS command (like
mv
or
cp
) or the
COPY_FILE
procedure of the
DBMS_FILE_TRANSFER
built-in PL/SQL package.
4.
Use either the
ALTER TABLESPACE ... RENAME DATAFILE ... TO
statement or the
ALTER
DATABASE RENAME FILE ... TO
statement to update the control file with the new data file
name.
5.
Alter the data file online.
■
if you need to rename data files associated with the
SYSTEM
or
UNDO
tablespace, you must shut down your
database and start it in mount mode. When your database is in mount mode, you can rename these data files via the
ALTER DATABASE RENAME FILE
statement.
Note
