Oracle March 19, 2009 0

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!

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close