How to move datafile to another location?

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: