Move datafiles to another folder
Article by author Kamran Agayev Agamehdi
Kamran
Agayev Agamehdi is an Oracle Certified Professional DBA (9i,
10g) with over 5 years experience with UNIX Systems and with
Oracle Databases and author of
Oracle Backup & Recovery by Rampant Tech Press.
I have seen
this question asked repeatedly on several forums, so I thought I
would show my technique for moving datafiles to another folder.
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>
|
