Oracle Datafiles

It's not at all easy to learn a technology completely, but we will try to learn oracle as we can. Let us see oracle datafiles, their structure, how to manage them and how they work.

Datafiles are physical structure of oracle database. Oracle stores your important data in these datafiles. These are located on hard disk in directory $ORACLE_HOME/oradata/SID along with others important files like redo log files, controlfiles etc.

One or more physical datafiles make a tablespace. It means a datafile can have only one tablespace, but a tablespace can have more than one datafiles. Oracle creates a datafile by allocating sufficient amount of disk space plus the overhead required for the file header.

When the datafile is created, the associated disk space is formatted, but does not contain any user data. Oracle reserve this space to use it in future.

When you create database with DBCA, oracle allocates first datafile to system tablespace as it is most important tablespace in database. We can explicitly allocate datafiles to tablespace as and hen we want. Have a look on tablespace and datafiles structure in oracle:


The data related to related schema in tablespace is physically stored in datafiles. Schema object does not corresponds to a specific datafile, but a datafile is repository for the schema objects. A datafile can be allocated to multiple schema, but it is not necessary that a schema object (say table) will reside only in that datafile as shown in above example.

Datafiles contain first block as it's header, which contains information about space allocated, space available, blocks present etc. in datafile. It also contain other important information such as block size, tablespace, file size, timestamps etc. When database opens, oracle checks whether information in datafile header matches with that of control file. When user request some data, oracle checks it first in buffer cache in SGA and if it is not present there then oracle reads it from appropriate datafile. CKPT process writes in datafile header the current SCN and log sequence number.

You can check information about datafiles by using v$datafile. You can take datafiles offline any time for you purpose. For this purpose execute following command:

sql> alter database datafile 'data/file/path.dbf' offline;

and online by executing following command

sql> alter database datafile 'data/file/path.dbf' online;

but remember, when taking datafile online it may ask for media recovery, then you can do

sql>alter database recover datafile 'data/file/path.dbf';

That's all what we can discuss about datafiles now. I am sure as we go on, we ill definitly get to know more about them.

enjoy...