$SQLplus / as sysdba
sqlplus>Shutdown immediate;

Copy large data files using DD command.
dd if=/u09/MJBDB/oradata/ts_voipdm_data01.dbf of=/u10/MJBDB/oradata/ts_voipdm_data01.dbf bs=2048K
dd if=/u09/MJBDB/oradata/ts_voipdm_data02.dbf of=/u10/MJBDB/oradata/ts_voipdm_data02.dbf bs=2048K
dd if=/u09/MJBDB/oradata/ts_voipdm_data03.dbf of=/u10/MJBDB/oradata/ts_voipdm_data03.dbf bs=2048K
dd if=/u09/MJBDB/oradata/ts_voipdm_index01.dbf of=/u10/MJBDB/oradata/ts_voipdm_index01.dbf bs=2048K
dd if=/u09/MJBDB/oradata/ts_voipdm_index02.dbf of=/u10/MJBDB/oradata/ts_voipdm_index02.dbf bs=2048K

$SQLplus / as sysdba
sqlplus>startup mount;

sqlplus>ALTER DATABASE RENAME FILE '/u09/MJBDB/oradata/ts_voipdm_data01.dbf' TO '/u10/MJBDB/oradata/ts_voipdm_data01.dbf';
sqlplus>ALTER DATABASE RENAME FILE '/u09/MJBDB/oradata/ts_voipdm_data02.dbf' TO '/u10/MJBDB/oradata/ts_voipdm_data02.dbf';
sqlplus>ALTER DATABASE RENAME FILE '/u09/MJBDB/oradata/ts_voipdm_data03.dbf' TO '/u10/MJBDB/oradata/ts_voipdm_data03.dbf';
sqlplus>ALTER DATABASE RENAME FILE '/u09/MJBDB/oradata/ts_voipdm_index01.dbf' TO '/u10/MJBDB/oradata/ts_voipdm_index01.dbf';
sqlplus>ALTER DATABASE RENAME FILE '/u09/MJBDB/oradata/ts_voipdm_index02.dbf' TO '/u10/MJBDB/oradata/ts_voipdm_index02.dbf';

sqlplus>alter database open;

If the contents of the tablespace(s) to be moved are not required, then :

    * Drop tablespace xxx including contents; to get rid of the tablespace and anything in it.
    * Once this has completed, delete the datafiles associated with the tablespace from the old file system - Oracle doesn't delete operating system files.
    * Create tablespace xxx datafile <filespec> <default storage clause> ; to recreate it anew (and empty) on the new file system.

In most cases the contents are required, so a bit more work is required. The following method can also be used to move a redo logfile :

    * Shutdown the database.
    * Advisable, but optional, take a full cold backup - it could save your job.
    * Copy the affected datafiles from the old file system to the new, renaming as required. Copy is better than move, because if it does go wrong, you could be left with a totally broken data file. You did take a backup didn't you?
    * Startup mount the instance - we need the control file for the next step.
    * For each file involved, alter database rename file <old filename> to <new filename>
    * Alter database open; to open the database.
    * Alter database backup controlfile to trace; because we have changed the structure of the database and must preserve it.
    * Delete the old datafiles from the old file system.

As you can see from the above, some downtime of the entire database is required. This affects all users of the database, while the tablespaces being moved may only contain data for a few, or even just one user. How best to avoid upsetting all your users when there is no need to? Oracle allows datafiles to be moved while the database is still running, but only if they :

    * Are not part of the SYSTEM tablespace.
    * Do not contain temporary segments.
    * Do not contain active rollback segments.

To move one or more datafiles belonging to a tablespace between file systems, without shutting down the entire database, is similar - but you won't be able to take a cold backup either. This task is best performed first thing after a (nightly) hot/cold backup so that you have something to fall back on. The steps involved are :

    * Alter tablespace xxx offline; to take the tablespace offline.
    * Copy the affected datafiles to the new location, again, copy rather than move.
    * For each data file affected, alter tablespace xxx rename datafile <old filename> to <new filename>;
    * Alter tablespace xxx online; to bring the tablespace back online again using the files in the new file system.
    * Alter database backup controlfile to trace; because we have changed the structure of the database and must preserve it.
    * Delete the old datafiles from the old file system.

Renaming a control file.

Control files can be moved easily as well. To move these, however, the database must be closed. The procedure is :

    * Shutdown the database.
    * Copy the affected control files from the old file system to the new, renaming as required.
    * Edit the initSID.ora file and specify the new control file names & paths.
    * Startup the instance & open the database.
    * Delete the old control files from the old file system.

Accidentally you just added a datafile to the wrong directory or tablespace—a common error from even experienced DBA's. All is not lost; the datafile doesn't contain any data yet, so you can easily drop it, right?

Unfortunately, you can't. Prior to Oracle Database 10g Release 2, your only clean option for removing a datafile is to drop the entire tablespace and then rebuild it without that particular file. If the tablespace contains data, you have to go through the time-consuming and laborious process of storing the data on a separate location and reinstating it. In addition to its inconvenience, this process makes the tablespace unavailable.


Oracle Database 10g Release 2 the process has been simplified: You can just drop the datafile. For example, the following command will remove the indicated datafile from the tablespace as well as from the server.

alter tablespace users drop datafile  '/tmp/users02.dbf'

There are a couple restrictions, however: The datafile must be empty to be dropped. You can't drop the last datafile in a tablespace; the tablespace itself must be dropped. And the tablespace must be online and in read-write status.