Move datafile to NEW Location

SQL> select * from v$version;



Oracle Database 11g Enterprise Edition Release – 64bit Production

PL/SQL Release – Production

CORE      Production

TNS for Linux: Version – Production

NLSRTL Version – Production


SQL> select name from v$datafile;






Here my TEST tablespace datafile is not in DEV3 location, so I wanted to move into /DEV location.

Here I’m testing in my DEV database but make sure no one using this tablespace if this is doing in prod database J

1)      Make the corresponding Tablespace OFFLINE

        SQL> ALTER tablespace TEST offline;

Tablespace altered.

2)      Update the data dictionary pointing to new location using below command:

         SQL> ALTER DATABASE RENAME FILE  ‘/opt/oracle/data/TEST01.dbf’  to    ‘/opt/oracle/data/DEV3/TEST01.dbf’;

Database altered.

3)      Now, recover the datafile from new location:

         SQL> RECOVER DATAFILE ‘/opt/oracle/data/DEV3/TEST01.dbf’;

Media recovery complete.

4)      Bring back the TEST tablespace ONLINE

         SQL>  ALTER tablespace TEST online;

Tablespace altered.

SQL> select name from v$datafile;






Now my TEST datafile moved to the new location.

Leave a comment

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: