Inside Oracle DBMS: Architecture, Memory, Processes, and Transaction Management

Basic Outline and Program Module Structure

Oracle Database Management System consists of several logical layers:

Database : a shared, organized repository of data.

Instance : the combination of System Global Area (SGA) and Oracle processes that manage the database.

Physical Structure : data files, redo log files, and control files stored on the operating system.

Logical Structure : tablespaces, segments, extents, data blocks, and schema objects that control disk space usage.

Schema Objects : tables, views, sequences, procedures, triggers, synonyms, indexes, clusters, database links, snapshots, etc.

Instance Basic Concepts

An Oracle instance is the memory and process environment (SGA + Oracle processes) that accesses a database.

Start instance – allocate SGA and launch Oracle processes.

Mount – the instance reads the control file to associate with a physical database.

Open – the database becomes available for authorized users.

Close – the database is unmounted and the instance is shut down.

DBAs have the privilege to start and open instances. Multiple instances can run on the same machine, and Oracle Parallel Server (OPS) allows several instances to share a single database.

Process Structure

Processes are independent programs that operate on a data set. In Oracle:

Oracle Processes : execute client and server code.

Oracle Instances : can be single‑process or multi‑process.

Relationship : a process is an execution instance of a program; a thread is a lightweight execution unit within a process.

Single‑process instances run client and server code in one process (rarely used). Multi‑process instances separate user processes from server processes, allowing many users to share server processes.

Memory Structure

Oracle memory is divided into four main areas:

Software Code Area (SCA) : stores executable code.

System Global Area (SGA) : shared memory for all processes, containing:

Database Buffer Cache (DBC)

Redo Log Buffer (RLB)

Shared Pool (SQL area, PL/SQL area, dictionary cache, control structures)

Dictionary Cache (DC)

Other information areas

Program Global Area (PGA) : private memory for a single process, holding stack, session info, private SQL area, etc.

Sort Area (SA) : temporary space for sorting, located in PGA for dedicated servers or in SGA for multi‑threaded servers.

Key initialization parameters control the size and placement of these areas (e.g., SGA_MAX_SIZE, PGA_AGGREGATE_TARGET, SORT_AREA_SIZE).

Process Configuration Schemes

Single‑process Oracle instance : client and server run in one process (rare).

Multi‑process Oracle instance : separate user processes and server processes; can be dedicated or shared.

User processes : created when an application or Oracle tool connects; each user process may have a dedicated or shared server.

Oracle server processes : handle client requests; include dedicated server processes, shared server processes, and background processes.

Program Interface

The interface provides a security barrier, communication mechanism, and data conversion between client processes and the SGA.

OCI (Oracle Call Interface) or SQLLIB libraries.

Client side (UPI) and server side (OPI) components.

Net8 drivers for protocol handling.

Operating‑system communication software (TCP/IP, DECnet, etc.).

Data Dictionary

The data dictionary stores metadata about the database:

User names and privileges.

Schema objects and their storage usage.

Column default values, integrity constraints, audit information, and general database statistics.

It consists of base tables (stored in the SYSTEM tablespace), views (decoded data for users), and dynamic performance tables (virtual tables that do not consume disk space).

Typical uses include retrieving performance data, persisting DDL changes, and referencing metadata in applications.

Transaction Management

A transaction is a logical unit of work composed of one or more SQL statements.

Start: Oracle allocates a rollback segment and assigns a System Change Number (SCN).

Commit: writes redo log entries to online redo logs, releases locks, and marks the transaction as complete.

Rollback: either full (no savepoint) or partial (to a savepoint), releasing appropriate locks.

Savepoints: intermediate markers that allow partial rollbacks within a long transaction.

Transaction termination can also occur implicitly with DDL execution, disconnect, or process failure.