http://www.orafaq.com/wiki/Move_datafile_to_different_location
Database is in ARCHIVELOG mode
Take the datafile offline with the "ALTER DATABASE DATAFILE '/old/location' OFFLINE;" command.
Copy or move the datafile to its new location.
SQL> ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
SQL> RECOVER DATAFILE '/new/location';
SQL> ALTER DATABASE DATAFILE '/new/location' ONLINE;
Database is in NOARCHIVELOG mode
Shutdown the database
Copy or move the datafile to its new location.
Start SQL*Plus, do a "STARTUP MOUNT" and rename the file:
SQL> ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
SQL> ALTER DATABASE OPEN;
Check current location of datafiles
If the database is offline you can still check the current location of the datafiles and archive log mode.
SQL> select log_mode from v$database;
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$tempfile;
SQL> select name from v$controlfile;
Note that the location of data files (and other fixed tables/views) is stored in the control files, and the location of the control files is stored in the init file (pfile or spfile).
No Comments