Browse > Home / Howto / Rename / Move Oracle datafile

Rename / Move Oracle datafile

March 10, 2008

I needed to move a datafiles from one directory to another. The datafile was created in the wrong location. Here is a procedure I used:


SQL> select name from v$datafile;

NAME
——————————————————————————–
/oracle/ACM/oradata/ACM/system01.dbf
/oracle/ACM/oradata/ACM/undotbs01.dbf
/oracle/ACM/oradata/ACM/sysaux01.dbf
/oracle/NRXD/oradata/ACM/users01.dbf <—- Datafile to be moved / renamed..

Now, the files to be moved are known, the database can be shut down:

SQL> shutdown

The file(s) can be copied to their destination:

$ cp /oracle/NRXD/oradata/ACM/users01.dbf \
/oracle/ACM/oradata/ACM/users01.dbf
$ sqlplus “/ as sysdba”

SQL> startup exclusive mount
SQL> alter database rename file ‘/oracle/NRXD/oradata/ACM/users01.dbf’ to
‘/oracle/ACM/oradata/ACM/users01.dbf’;
SQL> shutdown
SQL> startup
SQL> select name from v$datafile;

NAME
——————————————————————————–
/oracle/ACM/oradata/ACM/system01.dbf
/oracle/ACM/oradata/ACM/undotbs01.dbf
/oracle/ACM/oradata/ACM/sysaux01.dbf
/oracle/ACM/oradata/ACM/users01.dbf

Written by admin · Filed Under Howto 

Comments

Got something to say?