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.
