Joe's Oracle

Adding, renaming, relocating and resizing Datafile's

From Oracle 7.2
To increase or decrease the size of a datafile

alter database datafile 'xxxx' resize 999M;
This will not reduce the size to zero, it can be done online safely , it does not slow the machine down.

To Rename or Relocate Datafiles for a Single Tablespace

1. Take the non-SYSTEM tablespace that contains the datafiles offline.
2. Copy the datafiles to the new location or new names using the operating system.
3. Make sure that the new, fully specified filenames are different from the old filenames.
4. Use either the Rename Datafile dialog box of Server Manager/GUI or the SQL command ALTER TABLESPACE with the RENAME DATAFILE option to change the filenames within the database.
For example, the following statement renames the datafiles FILENAME1 and FILENAME2 to FILENAME3 and FILENAME4, respectively:

ALTER TABLESPACE users
RENAME DATAFILE 'filename1', 'filename2'
TO 'filename3', 'filename4';
The new file must already exist; this command does not create a file. Also, always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old filename exactly as it appears in the DBA_DATA_FILE view of the data dictionary.

Renaming and Relocating Datafiles for Multiple Tablespaces or the SYSTEM Tablespace

You can rename and relocate datafiles of one or more tablespaces with the SQL command ALTER DATABASE with the RENAME FILE option. This option is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation, or rename or relocate datafiles of the SYSTEM tablespace. If the database must remain open, consider instead the procedure outlined in the previous section. To rename datafiles of several tablespaces in one operation or to rename datafiles of the SYSTEM tablespace, you must have the ALTER DATABASE system privilege.

1. Ensure that the database is mounted but closed.
2. Copy the datafiles to be renamed to their new locations and new names, using the operating system.
3. Make sure the new copies of the datafiles have different fully specified filenames from the datafiles currently in use.
4. Use the SQL command ALTER DATABASE to rename the file pointers in the database's control file.
For example, the following statement renames the datafiles FILENAME 1 and FILENAME2 to FILENAME3 and FILENAME4, respectively:

ALTER DATABASE
RENAME FILE 'filename1', 'filename2'
TO 'filename3', 'filename4';
The new file must already exist; this command does not create a file. Also, always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old filename exactly as it appears in the DBA_DATA_FILE view of the data dictionary.

To resize a datafile
ALTER DATABASE DATAFILE 'file_name' RESIZE 100M;
or
ALTER DATABASE DATAFILE 4 RESIZE 2000M;