Dec 20 2014

How to Move Datafile to Different Directory in Oracle

Category: Administration,Backup And RecoveryFatih Acar @ 15:23

You can move datafiles to different directory. You have to make offline tablespace or take mount mode database to change datafiles location. System, temporary and rollback tablespace can move only when database is mount mode.

Show Tablespaces And Datafiles With Locations

SQL> select tablespace_name, file_name from dba_data_files;


Change Location Of Normal Datafile

SQL> alter tablespace TS_TEST offline;
[oracle@testdb oradata] mv /oradata/ts_test01.dbf to /oradata2/ts_test02.dbf
SQL> alter tablespace TS_TEST rename datafile ‘/oradata/ts_test01.dbf’ to ‘/oradata2/ts_test02.dbf’;
SQL> alter tablespace TS_TEST online;

Task completed.

Change Location Of System, Temporary or Rollback Tablespace’s Datafiles

SQL> shutdows immediate;
SQL> startup mount;
[oracle@testdb oradata] mv /oradata/system01.dbf to /oradata2/system02.dbf
SQL> alter database rename file ‘/oradata/system01.dbf’ to ‘/oradata2/system02.dbf’;
SQL> alter database open;

Task completed.

You can move redo log files like system tablespace. Firstly, you have to take mount mode database then you can move with “alter database rename file” command.

Second method for redo logs, you can drop redo log when redo log is inactive, after you can create redo log on new location when database is online.

29,447 total views, 4 views today

Tags: Database Administration, Oracle Administration, Oracle Backup and Restore

facebook comments:

Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.