How to move datafile to another location?
June 3, 2011 Leave a comment
First of all, let’s determine in what tablespace this datafile is:
select file_name, tablespace_name from dba_data_files
—My output on Windows
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------- ------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF USERS
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF SYSAUX
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF UNDOTBS1
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF SYSTEM
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF EXAMPLE
D:\TESTTBS.DBF TESTTBS
—My output on Linux
FILE_NAME TABLESPACE_NAME ------------------------------------ ----------------- /u0/ORADATA/ORCL/USERS01.dbf USERS /u0/ORADATA/ORCL/SYSAUX01.dbf SYSAUX /u0/ORADATA/ORCL/UNDOTBS01.dbf UNDOTBS1 /u0/ORADATA/ORCL/SYSTEM01.dbf SYSTEM /u0/ORADATA/ORCL/EXAMPLE01.dbf EXAMPLE /u7/TESTTBS.dbf TESTTBS
1. TESTTBS tablespace should be taken to offline:
alter tablespace TESTTBS offline
2. Datafile should be moved to another location manually:
—On Windows
copy D:\TESTTBS.DBF C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL
—On Linux
cp /u7/TESTTBS.DBF /u0/ORADATA/ORCL/TESTTBS.DBF
3.
—On Windows
alter tablespace TESTTBS rename datafile 'D:\TESTTBS.DBF' to 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TESTTBS.DBF'
—On Linux
alter tablespace TESTTBS rename datafile '/u7/TESTTBS.DBF ' to '/u0/ORADATA/ORCL/TESTTBS.DBF'
4. Take TESTTBS tablespace to online
alter tablespace TESTTBS online
5. Check datafile location and tablespace status:
select file_name, tablespace_name from dba_data_files
—My output on Windows
FILE_NAME TABLESPACE_NAME -------------------------------------------------------- ------------------------------ C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF USERS C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF SYSAUX C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF UNDOTBS1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF SYSTEM C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF EXAMPLE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TESTTBS.DBF TESTTBS
—My output on Linux
FILE_NAME TABLESPACE_NAME ------------------------------------ ----------------- /u0/ORADATA/ORCL/USERS01.dbf USERS /u0/ORADATA/ORCL/SYSAUX01.dbf SYSAUX /u0/ORADATA/ORCL/UNDOTBS01.dbf UNDOTBS1 /u0/ORADATA/ORCL/SYSTEM01.dbf SYSTEM /u0/ORADATA/ORCL/EXAMPLE01.dbf EXAMPLE /u0/ORADATA/ORCL/TESTTBS.dbf TESTTBS
SQL> select tablespace_name, 2 status 3 from dba_tablespaces 4 where tablespace_name='TESTTBS' 5 / TABLESPACE_NAME STATUS ------------------------------ --------- TESTTBS ONLINE
That’s all.
About Mariami Kupatadze
DBA Knowledge Base 