Creating and Dropping Datafiles

Datafiles are physical files of the operating system that store the data of all logical structures in the database. Database assigns each datafile two associated file numbers, an absolute file number and a relative file number that are used to uniquely identify it.

Note: Relative and absolute File Number usually having the same number however when the number of datafiles in database exceeds a threshold (1023) then relative file number differ from absolute file number.

Creating and Adding Datafiles to a Tablespace: (Using any of the statement)

CREATE TABLESPACE

ALTER TABLESPACE ... ADD DATAFILE

CREATE DATABASE

ALTER DATABASE ... CREATE DATAFILE

Note: If you add new datafiles to a tablespace and do not fully specify the filenames then the location of datafiles in the default database directory or the current directory. Oracle recommends you always specify a fully qualified name for a datafile.

Changing Datafile Size:

There are two way to Alter the size of datafiles.

·   Enabling and Disabling Automatic Extension for a Datafile

·   Manually Resizing a Datafile

Enabling/Disabling Auto-Extension for a Datafile:

ALTER TABLESPACE users ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M

AUTOEXTEND ON NEXT 512K MAXSIZE 250M;

Note: The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' AUTOEXTEND OFF;

Manually Resizing a Datafile:

For a bigfile tablespace you can use the ALTER TABLESPACE statement to resize a datafile. You are not allowed to add a datafile to a bigfile tablespace.

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;

Note: The size can be decreased of a file to specific value. In case of bigfile tablespace you are not allowed to add a datafile.

Taking Datafiles Online or Taking Offline in ARCHIVELOG Mode:

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;

Note: The datafiles of a read-only tablespace can be taken offline or brought online, but bringing a file online does not affect the read-only status of the tablespace. You cannot write to the datafile until the tablespace is returned to the read/write state. You can make all datafiles of a tablespace temporarily unavailable by taking the tablespace itself offline.

To take a datafile offline when the database is in NOARCHIVELOG mode, use the ALTER DATABASE statement with both the DATAFILE and OFFLINE FOR DROP clauses:

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE FOR DROP;

It does not actually drop the datafile. It remains it in the data dictionary, and you must drop it yourself using An ALTER TABLESPACE ... DROP DATAFILE statement.

Renaming and Relocating Datafiles (Single Tablespace)

1.      Take the tablespace that contains the datafiles offline (database must be open).

ALTER TABLESPACE users OFFLINE NORMAL;

2.      Rename the datafiles using the operating system command.

3.      Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.

ALTER TABLESPACE users RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf', '/u02/oracle/rbdb1/user02.dbf' TO '/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf';

4.      Back up the database. After making any structural changes to a database.

Relocating Datafiles in a Single Tablespace

1.      Check the file name or size using DBA_data_files view.

SQL> SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';

FILE_NAME                                  BYTES

------------------------------------------ ----------------

/u02/oracle/rbdb1/users01.dbf         102400000

/u02/oracle/rbdb1/users02.dbf         102400000

2.      Take the Tablespace containing the datafiles offline:

ALTER TABLESPACE users OFFLINE NORMAL;

3.      Copy or Move the datafiles to their new locations and rename them

ALTER TABLESPACE users RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf' TO '/u03/oracle/rbdb1/users01.dbf', '/u04/oracle/rbdb1/users02.dbf';

4.      Back up the database. After making any structural changes to a database.

Renaming and Relocating Datafiles in Multiple Tablespaces

For that you must have the ALTER DATABASE system privilege.

1.      Ensure that the database is mounted.

2.      Copy the datafiles to be renamed to their new locations and new names.

3.      Use ALTER DATABASE to rename the file pointers in the database control file.

ALTER DATABASE RENAME FILE '/u02/oracle/rbdb1/sort01.dbf', '/u02/oracle/rbdb1/user3.dbf' TO '/u02/oracle/rbdb1/temp01.dbf', '/u02/oracle/rbdb1/users03.dbf;

4.      Back up the database. After making any structural changes to a database.

Note: To rename or relocate datafiles of the SYSTEM tablespace, the default temporary tablespace, or the active undo tablespace you must use this ALTER DATABASE method because you cannot take these tablespaces offline.

Dropping Datafiles

Alter Database Datafile 'C:\Oracle1\Oradata\Shaan\Users01.Dbf' Offline Drop;

Alter Tablespace Users Drop Datafile 'C:\Oracle1\Oradata\Shaan\Users01.Dbf';

Restrictions for Dropping Datafiles

·         The Database must be open.

·         If a datafile is not empty, it cannot be dropped.

·         You cannot drop the first or only datafile in a tablespace.

·         This means that DROP DATAFILE cannot be used with a bigfile tablespace.

·         You cannot drop datafiles in a read-only tablespace.

·         You cannot drop datafiles in the SYSTEM tablespace.

·         If a datafile in a locally managed tablespace is offline, it cannot be dropped.