The Oracle (tm) Users' Co-Operative FAQ

How do I move tablespaces from one file system to another?


Author's name: Norman Dunbar;

Author's Email: Oracle@bountifulsolutions.co.uk

Date written: 28/06/2001

Oracle version(s): 7.3.4 onwards

From time to time, a tablespace may require to be moved - with or without its contents - from one file system to another. How does the DBA carry out this task. In a related matter, how would the DBA move redo logfile and the control files to different file systems?

Back to index of questions


Moving datafiles (and redo logs)

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

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 :

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 :

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 :

Renaming a control file.

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

Note

Although the question asked about moving files between file systems, the above procedures can be used to rename a file, or move it between different locations on the same file system.


Further reading:

Oracle 8 DBA Handbook by Kevin Loney on Oracle Press. Chapter 4: The Physical Database Layout.

Oracle 8 Administrator's Guide. Chapter 6: Managing Control Files

Oracle 8 Administrator's Guide. Chapter 9: Managing Data Files


Back to top

Back to index of questions