Oracle Database Structures

The Relational Data Model has three major aspects

1. Structures

2. Operations

3. Integrity Rules

Structures: These are well defined objects (tables) that store data of a database. Structure and the data within them can be manipulated by operations.

Operations: Operations are clearly defined actions that allow users to manipulate the data and structures of a database. The operations must adhere to a pre-defined set of integrity rules.

Integrity Rules: Integrity rules are the laws that govern which operations are allowed on the data and structure of a database. Integrity rules protect data and the structure of a database.

An Oracle Database has both Logical and Physical Structure. (Fig#1 for reference)

Logical Database Structure: An Oracle database's logical structure is determined by one or more tablespaces, the database's schema objects (ex. tables, views, indexes, clusters, sequences, stored procedures, etc) The logical structures including tablespaces, segments, extents and blocks dictates how the physical space of a database is used. A tablespace can be associated with only one database.

Physical Database Structure: An Oracle database's physical structure is determinded by datafiles. One or more datafiles are grouped together to make a tablespace. A datafile can be associated with only one tablespace.

Although databases, tablespaces, datafiles and segments are closely related they have important difference. The differences are categorized below.

1. Databases & Tablespaces: An Oracle Database comprises of one or more logical storage untis called tablespaces. The database's data is collectively stored in the database's tablespaces.

2. Tablespaces & Datafiles: In Oracle Database each tablespace comprises of one or more physical operating system files called Datafiles. A tablespace's datafile physically stores the associated database data on disk.

3. Databases & Datafiles: A database's data is collectively stored in the datafiles that constitute each tablespace of the database.

The units of logical database allocations are data blocks, extents and segments.

(Fig#2 for reference)

1. Data Blocks: At the finest level of granularity, an Oracle Database's data is stored in data blocks, also known as logical blocks, oracle blocks, or pages.

2. Extents: An extent is a specific number of contigious data blocks that are allocated for storing a specific type of information.

3. Segments: A segment is a set of extents which have been allocated to for a specific type of data structure, and all are stored in the same tablespace. i.e. a segment can be associated with only one tablespace, though it can be spread over one of more datafiles.