Data Files: moving renaming deleting

Let’s have a look at what do we have after default database installation:

SQL> SELECT name FROM v$datafile;
NAME
---------------------------------------------------------
/u01/app/oracle/product/10.2.0/oradata/DBSID/SYSTEM01.DBF
/u01/app/oracle/product/10.2.0/oradata/DBSID/UNDOTBS01.DBF
/u01/app/oracle/product/10.2.0/oradata/DBSID/SYSAUX01.DBF
/u01/app/oracle/product/10.2.0/oradata/DBSID/USERS01.DBF
/u01/app/oracle/product/10.2.0/oradata/DBSID/EXAMPLE01.DBF

 

Shutdown the database first:

SQL> SHUTDOWN IMMEDIATE;

 

Rename a datafile:

SQL> host mv -v /u01/app/oracle/product/10.2.0/oradata/DBSID/EXAMPLE01.DBF 
/u01/app/oracle/product/10.2.0/oradata/DBSID/EXAMPLE02.DBF

 

Move a datafile to the new location:

SQL> HOST mv -v /u01/app/oracle/product/10.2.0/oradata/DBSID/EXAMPLE01.DBF 
/u01/app/oracle/oradata/DBSID/EXAMPLE01.DBF

Move and rename a datafile to the new location:

SQL> HOST mv -v /u01/app/oracle/product/10.2.0/oradata/DBSID/EXAMPLE01.DBF 
/u01/app/oracle/oradata/DBSID/EXAMPLE02.DBF

SQL> STARTUP MOUNT

 

Move and rename a datafile variant:

SQL> ALTER DATABASE RENAME FILE

'/u01/app/oracle/product/10.2.0/oradata/DBSID/EXAMPLE01.DBF' TO

'/u01/app/oracle/oradata/DBSID/EXAMPLE02.DBF';

SQL> ALTER DATABASE OPEN;

SQL> SELECT name FROM v$datafile;
NAME
----------------------------------------------------------------
/u01/app/oracle/product/10.2.0/oradata/DBSID/SYSTEM01.DBF
/u01/app/oracle/product/10.2.0/oradata/DBSID/UNDOTBS01.DBF
/u01/app/oracle/product/10.2.0/oradata/DBSID/SYSAUX01.DBF
/u01/app/oracle/product/10.2.0/oradata/DBSID/USERS01.DBF
/u01/app/oracle/oradata/DBSID/EXAMPLE02.DBF

Database altered

Deleting dropping a datafile:

If a datafile you wish to drop is only datafile, present in the tablespace in which it resides, you can simply drop a tablespace:

SQL> SELECT file_name, tablespace_name FROM dba_data_files;
FILE_NAME                                   TABLESPACE_NAME
------------------------------------------- ---------------
/u01/app/oracle/oradata/DBSID/EXAMPLE02.DBF EXAMPLE02 

SQL>ALTER DATABASE DATAFILE /u01/app/oracle/oradata/DBSID/EXAMPLE02.DBF OFFLINE DROP

SQL>DROP TABLESPACE EXAMPLE02 INCLUDING CONTENTS;

SQL> host rm -f /u01/app/oracle/oradata/DBSID/EXAMPLE02.DBF

OR

SQL>DROP TABLESPACE EXAMPLE02 INCLUDING CONTENTS AND DATAFILES;

 

That’s all Folks!