Moving/Renaming datafile to another location

You can move a datafile to another disk, or change it’s name (such as for restoring a copy of it from your backup tape to that other location in the event of a disk failure or moving it to another disk that has more room for it) by making the move after shutting down the database, and using the “alter database rename file” command to tell Oracle where the file has been moved to, as in the following example, which moves one of the development datafiles from disk u03 to disk u04:

$ sqlplus “/ as sysdba”
SQL> shutdown immediate
SQL> !mv /u03/oradata/PROD/devl_PROD_01.dbf /u04/oradata/PROD
SQL> startup mount
SQL> alter database rename file ‘/u03/oradata/PROD/devl_PROD_01.dbf’ to ‘/u04/oradata/PROD/devl_PROD_01.dbf’;
SQL> alter database open;
SQL> select * from v$datafile;
Lists the current datafiles, showing your changes.

This can also be done without shutting down the database, but taking the associated tablespace offline first (which prevents others from accessing that tablespace’s tables, indexes, and other data):

$ sqlplus “/ as sysdba”
SQL> alter tablespace development offline;
SQL> !mv /u03/oradata/PROD/devl_PROD_01.dbf /u04/oradata/PROD
SQL> alter database rename file ‘/u03/oradata/PROD/devl_PROD_01.dbf’ to ‘/u04/oradata/PROD/devl_PROD_01.dbf’;
SQL> alter tablespace development online;
SQL> select * from v$datafile;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: