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.