The Physical Oracle Database

This suggested directory structure includes datafiles, control files and redo log files; all potentially on separate disks.


Oracle is divided up into physical and logical storage areas. Tablespaces use logical storage. The datafiles within these tablespaces are stored physically in the logical tablespace areas. Tablespaces should be created with as few datafiles as possible. Add small numbers of large datafiles when it is required to increase tablespace size or resize current datafiles larger. Use the autoextend option when creating tablespaces such that datfiles are increased in size automatically. Datafiles are subdivided into segments which are subdivided into extents. Segments can be of a number of different types.

Note that it is more efficient to maintain a small number of large datafiles. This is because each datafile is reserved in memory as a file handle by Oracle on instance startup and thus the more files present for a database then the more file handles are opened. Taking a datafile OFFLINE will remove it's file handle from memory until that datafile is placed back ONLINE.


Note that a single segment can not span multiple datafiles, it must be contiguous. Segments are made up of subsets called extents. An extent is a contiguous set of blocks. When existing extents are no longer able to hold new information a segment will obtain a new extent. The diagram below shows the relationship between the main physical and logical architectural structures of a database. Note that segments can be distributed across datafiles within a single tablespace, extents will always be contained within a single datafile.

Oracle Database Physical Structure

Table, Cluster and Partition Segments

These segments contain data. Data segments have a header block serving as a directory to the segment. A properly sized table should have a single extent. An extent cannot span datafiles. A segment that is larger than the largest datafile available will have multiple extents. Row deletion from a table has no affect on space allocated to the table. Tune space usage using the PCTFREE parameter. Segment extents are generally not returned to a tablespace for future reuse until the schema containing the object in which data is stored is dropped. With respect to clusters, without dropping or truncating the cluster, extents are retained by the cluster, even when a table which is part of the cluster is dropped or truncated.

Index Segments

MInimise contention by placing index datafiles onto a disk other than the table segments. Fragmentation of indexes is important. Indexes can be defragmented by periodically rebuilding indexes.

Rollback Segments

A rollback extent should be large enough to handle all the data in a single transaction. The OPTIMAL storage parameter shrinks rollback segments back to an optimal size after transaction completion. Rollback segments larger than optimal size are checked for. Extents are deallocated at this point by Oracle when checking for unused rollback segment extents.

Temporary Segments

Temporary data segments are generally used for sorting operations during large queries. Space used by a temporary segment is deallocated on query completion and extents are returned to the temporary segment. Also Oracle will drop temporary segments at transaction or session completion and thus returns extents for the temporary segment to the temporary tablespace for future reuse on query completion.

Coalescence of Free Extents

Use the alter table command with the COALESCE clause as show below. Coalescence of tablespaces forces neighboring free extents into larger free extents. Note that free extents separated by data extents will not be coalesced.


Shrinking Datafiles

Datafiles can be shrunk down to remove unused space. Previously allocated space cannot be reclaimed.


For tables, clusters and indexes adding large amounts of data will increase the high-water mark for a datafile. The high-water mark is the largest the datafile has ever been even if all of the datafile is not currently used. The only way to reset a segment high-water mark is by using the TRUNCATE command or dropping and recreating the segment. Space can be deallocated from a table as show below. The command below will reclaim empty space from a table. The KEEP option specifies the amount of free space to keep. The same can be done for clusters with DEALLOCATE CLAUSE in the ALTER CLUSTER command. Index space can be reclaimed by rebuilding indexes.


Redo Logs

The redo logs contain records or logs of all transactions against the database. The redo logs are copied to the archive logs as the redo logs are filled. The result is that redo logs and archive logs can be used to reconstruct the database where all previously executed database commands can be re-executed from the entries on the redo logs and archive logs. Therefore the database can in theory be recovered to any point in time throughout its history. However, note that the archive logs can use up an enormous amount of space if they are not continually removed or copied to offline backups.

The redo logs and the archive logs can also be mirrored.

Archive logs should be periodically removed. It is not neccessary to keep all the archive logs, only those archive logs that would be required for restoration. Obviously if there is database crash and the archives are required for recovery many years of archives would be required. Archiving of redo logs can be forced from the Server Manager utility as shown below.

archive log all;

The archive log list; command can be used to view the status of database archiving and the V$LOG view can be used to inspect the current status of the online redo logs.

Redo Log File Mirroring

Mirroring of redo log files implies separated simultaneously duplicated redo log files maintained automatically by the database. This is achieved by setting logfile parameter values in alter database commands to a list of redo log files as shown below.

alter database add logfile group 1 ('d:\oracle\oradata\DB1\redo01.log', 'e:\oracle\oradata\DB1\redo01.log);
alter database add logfile group 2 ('d:\oracle\oradata\DB1\redo01.log', 'e:\oracle\oradata\DB1\redo01.log);
alter database add logfile group 3 ('d:\oracle\oradata\DB1\redo01.log', 'e:\oracle\oradata\DB1\redo01.log);
alter database add logfile group 4 ('d:\oracle\oradata\DB1\redo01.log', 'e:\oracle\oradata\DB1\redo01.log);

Control Files

The overall physical architecture is maintained by it's control files. The control files maintain maintain internal consistency and assist with recovery. The database is required to have atleast one control file. Oracle recommends atleast two control files on separate physical disks. Below is a trace of the control file using the command shown.


Control File Mirroring

Mirroring of control files implies separated simultaneously duplicated control files maintained automatically by the database. This is achieved by setting the control_files parameter in the init.ora file to a list of control files as shown below.

control_files = 

Parameter File

Parameter File (init<SID>.ora)

Oracle 7.3 contains 168 parameters. Oracle 8 contains 193 parameters. These parameters control many ascpects of an Oracle database instance. The Oracle control or parameter file is called init.ora. This control file is not part of the Oracle instance. This parameter or control file defines the instance. Oracle reads the parameter file at the start of the Oracle instance. This parameter file is used to build the SGA, start optional background processes and set processing limits. The parameter file contains values which determine the size of the SGA, identify the location of archived log files and the control file, set limits on the use of the instance, and may identify the name of the database to be mounted by the instance.

The parameters settings for the database can be viewed using the V$PARAMETER view. This view includes the defaulty paramter settings. The init.ora file will only contain non-default parameter settings.

There are some database initialisation parameters which do not require that the database be bounced in order for the change to take effect. Use the ALTER SYSTEM or the ALTER SESSION parameter to instantiate the parameter change. The ISSYS_MODIFIABLE column in the V$PARAMETER view will dictate the changeablility of the parameter. The settings are IMMEDIATE, FALSE and DEFERRED.

The Alert Log

Alert Log (alert_<SID>.log)

The Alert log is a chronological record and contains the following.

If the Alert log file disk location fills up then the Oracle instance will slow or stop until space is made available. The Alert log should be periodically archived or even removed. In general, the alert log tracks overall instance status.

Trace Files

Trace Files (*.trc)

Every process running in the Oracle Instance has an associated trace file. Note that the alter log contains records of the most significant database events. The alert log is generally used to track down reasons for database crashes and will usually contain events that the other logs may not have been capable of. The alert log is both the first and last resort.

Trace files are created either at process startup, when a process exits abnormally or certain errors occur in a process. Trace processes are forced to be generated by initialisation parameters or by turning on tracing at the process level by using the ALTER SESSION command. A special type of trace called a core dump is generated for severe errors. A core dump is caused by a database crash or can also be caused by an application running against an Oracle database instance crashing, ie. at the application level. Trace files are placed in locations specified in the initialisation file using the following parameters.

If dump files fill up the Oracle database instance could stall when attempting to write to that dump file. Tracing can be set at the session level by using the command shown below.


Setting SQL_TRACE to TRUE using the ALTER SYSTEM command will allow writing to trace files for individual user sessions. Be aware that tracing of all database actions can impact performance significantly. Only trace when absolutely neccessary.