Move datafile to NEW Location

SQL> select * from v$version;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

PL/SQL Release 11.2.0.2.0 – Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 – Production

NLSRTL Version 11.2.0.2.0 – Production

SQL>

SQL> select name from v$datafile;

NAME

——————————————————————————–

/opt/oracle/data/DEV3/system01.dbf

/opt/oracle/data/DEV3/sysaux01.dbf

/opt/oracle/data/TEST01.dbf

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;

NAME

——————————————————————————–

/opt/oracle/data/DEV3/system01.dbf

/opt/oracle/data/DEV3/sysaux01.dbf

/opt/oracle/data/DEV3/TEST01.dbf

Now my TEST datafile moved to the new location.

Advertisements
Leave a comment

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 )

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: