Move datafiles to another folder
Posted by Kamran Agayev A. on August 14th, 2009
This question is one of the most famous questions in OTN forum 
So I decided to show the way to do it
SQL> select name from v$datafile;
NAME
——————————————————————————–
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\USERS01.DBF
SQL> alter tablespace users offline;
Tablespace altered.
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>move c:\oracle\product\10.2.0\oradata\test_tbs\USERS01.DBF c:\oradata
C:\Documents and Settings\Administrator>exit
SQL> alter database rename file ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\USERS
01.DBF’ to ‘c:\oradata\users01.dbf’;
Database altered.
SQL> alter tablespace users online;
Tablespace altered.
SQL> select name from v$datafile;
NAME
——————————————————————————–
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\SYSAUX01.DBF
C:\ORADATA\USERS01.DBF
SQL>
September 7th, 2009 at 1:47 am
Excellent site, keep up the good work