In some case you could need to move or rename your datafiles which is belong to your tablespaces in your database.

I try to explain step by step how you can move your datafiles on your system.

Let us say, i need to move EXAMPLE tablespace’s datafiles from one other mount point because of space problem.

My database SID is TEST and my datafile name is example01.dbf, here is the steps

If your db status  is running:

– SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME=’EXAMPLE’;
– ALTER TABLESPACE EXAMPLE READ ONLY;

using the operating system copy command  cp to copy the datafiles to the new location, make sure that the sizes match.
ALTER TABLESPACE EXAMPLE  OFFLINE;
– ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/TEST/example01.dbf’ TO ‘/u01/gunes/example01.dbf’;

You will need to do this for all datafiles associated with this tablespace.

– ALTER TABLESPACE EXAMPLE ONLINE;
– ALTER TABLESPACE EXAMPLE READ WRITE;

Query v$dbfile and v$datafile to confirm that the changes made were correct.

SELECT FILE#,STATUS,ENABLED,NAME from V$DATAFILE;

SELECT * FROM V$DBFILE;

If your db status is shutdown:
– STARTUP MOUNT
– ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/TEST/example01.dbf’ TO ‘/u01/gunes/example01.dbf’;

You will need to do this for all datafiles associated with this tablespace.

– ALTER DATABASE OPEN;

Query v$dbfile and v$datafile to confirm that the changes made were correct.

SELECT FILE#,STATUS,ENABLED,NAME from V$DATAFILE;

SELECT * FROM V$DBFILE;

Reference:
How to Rename or Move Datafiles and Logfiles [ID 115424.1]

Advertisements