Oracle

In Oracle, the hierarchy of storage units (from smallest to largest) is Block, Extent, Segment, Tablespace, Database.

**Oracle Storage Hierarchy**

Block

**Oracle Database Block**

At the lowest level, data is held in blocks. All blocks in the database are of equal length, and this block size is specified when the database is created. It should be a multiple of the operating system page size.

Each block has a header that specifies the block address and the type of data contained in the block (the segment type). The block header consists of common and variable data. While some of the block overhead is fixed, the total overhead is variable. The block header information (including the table directory and row directory) grows ‘downward’ from the top of the block. The table directory identifies which tables have data in the block, and the row directory that contains information on the rows in the block such as addresses for each row piece in the row data area.

The actual data contained in the block is called row data, and this area grows ‘upward’ from the bottom of the block. When data is inserted into the database and it cannot fit into the free space in a block, a new block is created and the data is split so that each block contains a part of the data.

The area between the block header and the user data is free space and it is used to insert new rows and for updates to rows. Because the block size is fixed, large rows can span multiple blocks (row pieces).

Row data can contain table or index data, and each table row is stored in one or more ‘row pieces’. If a row will fit in a block, it is stored as a single row piece it is stored as multiple row pieces that are chained together across data blocks.

Extent

An extent is a contiguous number of blocks. The number of blocks in an extent, and hence the size of an extent, is configurable.

Segment

A segment is one or more extents. Segments are space allocated in a tablespace for specific logical storage structures. Structures stored in segments are tables, indices, temporary files and undo segments. Most objects in Oracle are stored in segments. A tablespace (and hence datafile) can contain both table and index segments. The main segment types are:

• One segment per defined index

• One segment per defined table or snapshot

• One or more rollback segments

• One or more temporary segments used to hold intermediate data of database operations.

Tablespace

A tablespace is one or more files, and the tablespace contains all the logical structures in the database.

**Oracle Tablespace**

Database

The tablespace is a fundamental construct in Oracle databases, the actual database itself is made up of one or more tablespaces and when the database looks at storage, it sees tablespaces. Since it is not visible to the underlying operating system, the tablespace is a logical structure. The tablespace is made up of one or more datafiles. Datafiles are operating system structures and only one tablespace can be associated with a datafile. Data files are associated with tablespaces depending on the logical structure of the database. Tablespaces may be created to contain different categories of data.

Tablespaces are used to allocate disk space for data in the database, set space quotas for database users and allocate storage across multiple devices to improve performance. A tablespace is created with the CREATE TABLESPACE command and this is also used to allocate additional space to the database, by creating additional tablespaces for use in the database.

When a database is created, Oracle creates a system tablespace that contains data dictionary information and PL/SQL data. DBA’s can manage tablespaces for maintenance by taking them offline (i.e. for backup or recovery), however the system tablespace cannot be taken offline since it contains control information for the database.

Disk File Management

We have been using an architectural diagram to describe the components of the database, and the files that live on disk look like this:

**Database Disk Structure**

The unit of management in Oracle for database files is the tablespace. Tablespaces contain the user data (tables), and these are represented on disk as blocks and in memory as blocks and extents. The DBMS and Operating System handle blocks and extents, and the level at which the DBA needs to determine where user data goes and how large to make the file allocations is done with the logical structures called tablespaces.

The user data – tables, rows, columns and indexes exist in .dbf files, and the allocation and management of these is the responsibility of the DBA. All the information about the disk files that make up the database is available in the System Catalog. This information keeps track of which directory systems contain the database files, their names, what tablespaces are associated with which data files and their sizes. Prior to Oracle 9i, the DBA had to know that this was where the information was kept, and also know how to interrogate the System Catalog to find the information needed to allocate tablespaces. Knowing where the files resided on disk was all the DBA needed to create tablespaces and while common sense dictates regular naming standards and file sizes, it was possible to create tablespaces with any name and size.

In 9i, Oracle introduced Oracle Managed Files (OMF) to make this process simpler. With OMF, all the DBA needs to know is that they want a new tablespace and OMF handles all the administrative details – name, location and size. With OMF the default tablespace file size is 100Mb. OMF additionally handles the Control Files, Redo Log Files and Temporary Files.

Oracle 10g extended the functionality of OMF with Automatic Storage Management (ASM). ASM allows the DBA to reference disk groups rather than individual disks and files. ASM includes striping and mirroring for balancing and security of data. The new ASM functionality can be used in combination with existing file systems including OMF and manually managed files. ASM is controlled by a lightweight ASM instance that implements only the memory structures necessary to manage the ASM files.

The main components of ASM are disk groups, which are groups of physical disks controlled as a single unit (ASM disks). The files in these disk groups are ASM files. ASM controls the physical locations and names for ASM files. ASM is built into the Oracle kernel and gives the DBA the ability to handle large disk for single and clustered (RAC) instances of Oracle. All the files and directories to be used for the Oracle databases are contained in a disk group, and ASM can automatically load balance in parallel across all available disk drives in a disk group.

ASM will also control the level of redundancy and the granularity of the striping across disk groups by allowing you to create failure groups within the disk groups. For example, two-way mirroring would implement two failure groups in a disk group so that individual files will be written to two locations. With ASM you can specify different levels of redundancy. External redundancy tells ASM that you are using an external redundancy option (i.e. RAID). With Normal Redundancy, ASM does two way mirroring (as described above). With High Redundancy, ASM does three way mirroring.

In summary ASM provides the following functionality:

Manages groups of disks (Disk Groups).

Management of database objects (i.e. tablespaces) without specifying physical mount points and file names.

Manages redundancy within a Disk Group.

Automatic I/O balancing.