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.