Move or rename datafiles
                                                 Last update (2012-01-09 08:46:38)
                                                                                                                   Date added (2008-02-11 12:59:07)

Summary
First of all you don't have to restart the database. Renaming datafiles is very common due to the following reasons.

Maybe you don't like the name of datafile and want to keep a naming method, or the most important, you want to move the datafile to a new, faster filesystem with more empty space. Moving datafiles is something that you gone need when you will start searching how I/O is spread on you filesystems!

How to
For our example. Lets say that the name of the tablespace is TBS1 and the datafile is '/filesystem01/tbs01.dbf' and you want to move it to '/filesystem02/tbs01.dbf'

1. First take the tablespace offline
ALTER TABLESPACE TBS1 OFFLINE;
If someone is using the tablespace, then you can't take it offline! You must kill all sessions using it. The time needed to take it offline, depends on the size of tablespace, a 10Mbytes tablespace may take 1 sec, a 1TByte tablespace may take some minutes… so be patient.

2. Move the datafile to the new filesystem with the os command.
$>mv /filesystem01/tbs01.dbf /filesystem02/tbs01.dbf
3. Do the renaming to update the dictionary and controlfile
ALTER TABLESPACE TBS1 RENAME DATAFILE '/filesystem01/tbs01.dbf' TO '/filesystem02/tbs01.dbf';
4. Take back tablespace online.
ALTER TABLESPACE TBS1 ONLINE;
Other links
Create new control file
Create new TEMPORARY tablespace
Create new UNDO tablespace
Move or rename System datafiles
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search