Oracle9i Database Concepts
Release 1 (9.0.1)

Part Number A88856-02
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

Introduction to the Oracle Server

This chapter provides an overview of the Oracle server. The topics include:

Introduction to Databases and Information Management

A database server is the key to solving the problems of information management. In general, a server must reliably manage a large amount of data in a multiuser environment so that many users can concurrently access the same data. All this must be accomplished while delivering high performance. A database server must also prevent unauthorized access and provide efficient solutions for failure recovery.

The Oracle server provides solutions with the following features:

Client/server environments (distributed processing)

To take full advantage of a given computer system or network, Oracle enables processing to be split between the database server and the client application programs. The computer running the database management system handles all of the database server responsibilities while the workstations running the database applications concentrate on the interpretation and display of data.

Large databases and space management

Oracle supports the largest databases, which can contain terabytes of data. To make efficient use of expensive hardware devices, Oracle enables full control of space usage.

Many concurrent database users

Oracle supports large numbers of concurrent users executing a variety of database applications operating on the same data. It minimizes data contention and guarantees data concurrency.


Oracle software enables different types of computers and operating systems to share information across networks.

High transaction processing performance

Oracle maintains the preceding features with a high degree of overall system performance. Database users do not experience slow processing performance.

High availability

At some sites, Oracle works 24 hours a day with no down time to limit database throughput. Normal system operations such as database backup and partial computer system failures do not interrupt database use.

Controlled availability 

Oracle can selectively control the availability of data, at the database level and below. For example, an administrator can disallow use of a specific application so the application's data can be reloaded without affecting other applications.

Openness, industry standards 

Oracle adheres to industry accepted standards for the data access language, operating systems, user interfaces, and network communication protocols. It is an open system that protects a customer's investment.

Oracle also supports the Simple Network Management Protocol (SNMP) standard for system management. This protocol enables administrators to manage heterogeneous systems with a single administration interface.

Manageable security

To protect against unauthorized database access and use, Oracle provides security features to limit and monitor data access. These features make it easy to manage even the most complex design for data access.

Database enforced integrity 

Oracle enforces data integrity, business rules that dictate the standards for acceptable data. This reduces the costs of coding and managing checks in many database applications.


Oracle software works under different operating systems. Applications developed for Oracle can be ported to any operating system with little or no modification.


Oracle software is compatible with industry standards, including most industry standard operating systems. Applications developed for Oracle can be used on virtually any system with little or no modification.

Distributed systems

For networked, distributed environments, Oracle combines the data physically located on different computers into one logical database that can be accessed by all network users. Distributed systems have the same degree of user transparency and data consistency as nondistributed systems, yet they receive the advantages of local database management.

Oracle also offers the heterogeneous option that enables users to access data on some non-Oracle databases transparently.

Replicated environments

Oracle software lets you replicate groups of tables and their supporting objects to multiple sites. Oracle supports replication of both data- and schema-level changes to these sites. Oracle's flexible replication technology supports basic primary site replication as well as advanced dynamic and shared-ownership models.

The following sections provide a comprehensive overview of the Oracle architecture. Each section describes a different part of the overall architecture.

The Oracle Server

The Oracle server is an object-relational database management system that provides an integrated approach to information management. An Oracle server consists of an Oracle database and an Oracle server instance.

An Oracle Instance

Every time a database is started, a system global area (SGA) is allocated and Oracle background processes are started. The system global area is an area of memory used for database information shared by the database users. The combination of the background processes and memory buffers is called an Oracle instance.

An Oracle instance has two types of processes: user processes and Oracle processes.

Oracle9i Real Application Clusters: Multiple Instance Systems


Real Application Clusters are available only if you have purchased the Oracle9i Enterprise Edition. See Oracle9i Database New Features for details about the features and options available with Oracle9i Enterprise Edition.  

Some hardware architectures (for example, shared disk systems) enable multiple computers to share access to data, software, or peripheral devices. Oracle9i with Real Application Clusters can take advantage of such architecture by running multiple instances that share a single physical database. In most applications, Real Application Clusters enable access to a single database by the users on multiple machines with increased performance.

Real Application Clusters are inherently high availability systems. The clusters that are typical of Real Application Clusters environments can provide continuous service for both planned and unplanned outages.

See Also:

Oracle9i Real Application Clusters Concepts for more information about Real Application Clusters and high availability 

Database Structure and Space Management

An Oracle database is a collection of data that is treated as a unit. The purpose of a database is to store and retrieve related information. The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.

Logical Database Structures

The logical structures of an Oracle database include tablespaces, schema objects, data blocks, extents, and segments.


A database is divided into logical storage units called tablespaces, which group related logical structures together. For example, tablespaces commonly group all of an application's objects to simplify some administrative operations.

Databases, Tablespaces, and Datafiles

The relationship among databases, tablespaces, and datafiles (datafiles are described in the next section) is illustrated in Figure 1-1.

Figure 1-1 Databases, Tablespaces, and Datafiles

Text description of scn81022.gif follows
Text description of the illustration scn81022.gif

This figure illustrates the following:

Online and Offline Tablespaces Description

A tablespace can be online (accessible) or offline (not accessible). A tablespace is normally online so that users can access the information within the tablespace. However, sometimes a tablespace is taken offline to make a portion of the database unavailable while allowing normal access to the remainder of the database. This makes many administrative tasks easier to perform.

Schemas and Schema Objects

A schema is a collection of database objects. Schema objects are the logical structures that directly refer to the database's data. Schema objects include such structures as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.


There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas. 

See Also:

"Description of Schemas and Schema Objects" for more information about schema objects 

Description of Data Blocks, Extents, and Segments

Oracle enables fine-grained control of disk space use through the logical storage structures, including data blocks, extents, and segments.

See Also:

Chapter 3, "Data Blocks, Extents, and Segments" 

Oracle Data Blocks

At the finest level of granularity, Oracle database data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. The standard block size is specified by the initialization parameter DB_BLOCK_SIZE. In addition, Oracle9i, Release 1 (9.0.1), permits specification of up to five nonstandard block sizes. A database uses and allocates free database space in Oracle data blocks.

See Also:

"Nonstandard Block Sizes" for information about using nonstandard block sizes 


The next level of logical database space is called an extent. An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information.


The level of logical database storage above an extent is called a segment. A segment is a set of extents allocated for a certain logical structure. For example, the different types of segments include:

Data segment

Each nonclustered table has a data segment. All of the table's data is stored in the extents of its data segment. For a partitioned table, each partition has a data segment.

Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.

Index segment

Each index has an index segment that stores all of its data. For a partitioned index, each partition has an index segment.

Rollback segment

If you are operating in manual undo management mode, one or more rollback segments for a database are created by the database administrator to temporarily store undo information.

The information in a rollback segment is used:

  • To generate read-consistent database information

  • During database recovery

  • To roll back uncommitted transactions for users

If you are operating in automatic undo management mode, the database server manages undo space using tablespaces. See "Automatic Undo Management" for more information about using automatic undo management mode.

Temporary segment

Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment's extents are returned to the system for future use.

Oracle dynamically allocates space when the existing extents of a segment become full. Therefore, when the existing extents of a segment are full, Oracle allocates another extent for that segment as needed. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.

See Also:


Physical Database Structures

The following sections explain the physical database structures of an Oracle database, including datafiles, redo log files, and control files.


Every Oracle database has one or more physical datafiles. The datafiles contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.

The characteristics of datafiles are:

Data in a datafile is read, as needed, during normal database operation and stored in the memory cache of Oracle. For example, assume that a user wants to access some data in a table of a database. If the requested information is not already in the memory cache for the database, it is read from the appropriate datafiles and stored in memory.

Modified or new data is not necessarily written to a datafile immediately. To reduce the amount of disk access and increase performance, data is pooled in memory and written to the appropriate datafiles all at once, as determined by the database writer (DBWn) background process of Oracle.

See Also:

"Memory Structure and Processes" for more information about Oracle's memory and process structures and the algorithm for writing database data to the datafiles 

Redo Log Files

Every Oracle database has a set of two or more redo log files. The set of redo log files for a database is collectively known as the database's redo log. A redo log is made up of redo entries (also called redo records).

The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, the changes can be obtained from the redo log so work is never lost.

Redo log files are critical in protecting a database against failures. To protect against a failure involving the redo log itself, Oracle allows a multiplexed redo log so that two or more copies of the redo log can be maintained on different disks.

The information in a redo log file is used only to recover the database from a system or media failure that prevents database data from being written to a database's datafiles.

For example, if an unexpected power outage terminates database operation, data in memory cannot be written to the datafiles and the data is lost. However, any lost data can be recovered when the database is opened, after power is restored. By applying the information in the most recent redo log files to the database's datafiles, Oracle restores the database to the time at which the power failure occurred.

The process of applying the redo log during a recovery operation is called rolling forward.

See Also:

"Database Backup and Recovery Overview" for more information about redo log files 

Control Files

Every Oracle database has a control file. A control file contains entries that specify the physical structure of the database. For example, it contains the following types of information:

Like the redo log, Oracle lets the control file be multiplexed for protection of the control file.

The Use of Control Files

Every time an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. If the physical makeup of the database is altered (for example, a new datafile or redo log file is created), the control file is automatically modified by Oracle to reflect the change.

A control file is also used if database recovery is necessary.

See Also:

"Database Backup and Recovery Overview" for more information about the use of control files in database recovery 

Structured Query Language (SQL)

SQL (pronounced SEQUEL) is the programming language that defines and manipulates the database. SQL databases are relational databases, which means data is stored in a set of simple relations. A database can have one or more tables. Each table has columns and rows. Oracle stores each row of a database table containing data for less than 256 columns as one or more row pieces. A table that has an employee database, for example, can have a column called employee number and each row in that column is an employee's employee number.

You can define and manipulate data in a table with SQL statements. You use SQL's data definition language (DDL) statements to set up the data. DDL statements include statements for creating and altering databases and tables.

You can update, delete, or retrieve data in a table with SQL's data manipulation language (DML). DML statements include statements to alter and fetch data. The most common SQL statement is the SELECT statement, which retrieves data from the database.

In addition to SQL statements, the Oracle server has a procedural language called PL/SQL. PL/SQL enables programmers to program SQL statements. It lets you control the flow of a SQL program, use variables, and write error-handling procedures.

Data Utilities

There are three utilities for moving a subset of an Oracle database from one database to another: Export, Import, and SQL*Loader.

Export Utility

The Export utility transfers data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations. Export extracts the object definitions and table data from an Oracle database and stores them in an Oracle binary-format Export dump file located typically on disk or tape.

Such files can then be copied using FTP or physically transported (in the case of tape) to a different site and used, with the Import utility, to transfer data between databases that are on machines not connected through a network or as backups in addition to normal backup procedures.

When you run Export against an Oracle database, it extracts objects such as tables, followed by their related objects, and then writes them to the Export dump file.

Import Utility

The Import utility inserts the data objects extracted from one Oracle database by the Export utility into another Oracle database. Export dump files can be read only by Import.

Import reads the object definitions and table data that the Export utility extracted from an Oracle database and stored in an Oracle binary-format Export dump file located typically on disk or tape.

The Export and Import utilities can also facilitate certain aspects of Oracle Advanced Replication functionality, such as offline instantiation.

See Also:

Oracle9i Replication for more information about Oracle Advanced Replication 

SQL*Loader Utility

Export dump files can be read only by the Oracle Import utility. If you need to read load data from ASCII fixed-format or delimited files, you can use the SQL*Loader utility. SQL*Loader loads data from external files into tables in an Oracle database. SQL*Loader accepts input data in a variety of formats, can perform filtering (selectively loading records based upon their data values), and can load data into multiple Oracle database tables during the same load session.

See Also:

Oracle9i Database Utilities for detailed information about Export, Import, and SQL*Loader 

Memory Structure and Processes

This section discusses the memory and process structures used by an Oracle server to manage a database. The architectural features discussed in this section enable the Oracle server to support:

An Oracle server uses memory structures and processes to manage and access the database. All memory structures exist in the main memory of the computers that constitute the database system.

Processes are jobs or tasks that work in the memory of these computers. (Processes are known as "threads" in the NT environment. See "Processes and Threads on Windows NT".)

Figure 1-2 shows a typical variation of the Oracle server memory and process structures.

Memory Structures

Oracle creates and uses memory structures to complete several jobs. For example, memory stores program code being executed and data that is shared among users. Two basic memory structures are associated with Oracle: the system global area (which includes the database buffers, redo log buffers, and the shared pool) and the program global areas. The following subsections explain each in detail.

Figure 1-2 Memory Structures and Processes of Oracle

Text description of scn81024.gif follows
Text description of the illustration scn81024.gif

System Global Area

The System Global Area (SGA) is a shared memory region that contains data and control information for one Oracle instance. An SGA and the Oracle background processes constitute an Oracle instance.

Oracle allocates the system global area when an instance starts and deallocates it when the instance shuts down. Each instance has its own system global area.

Users currently connected to an Oracle server share the data in the system global area. For optimal performance, the entire system global area should be as large as possible (while still fitting in real memory) to store as much data in memory as possible and minimize disk I/O.

The information stored within the system global area is divided into several types of memory structures, including the database buffers, redo log buffer, and the shared pool. These areas have fixed sizes and are created during instance startup.

See Also:

Database Buffer Cache of the SGA

Database buffers of the system global area store the most recently used blocks of data. The set of database buffers in an instance is the database buffer cache. The buffer cache contains modified as well as unmodified blocks. Because the most recently (and often the most frequently) used data is kept in memory, less disk I/O is necessary and performance is improved.

Redo Log Buffer of the SGA

The redo log buffer of the system global area stores redo entries--a log of changes made to the database. The redo entries stored in the redo log buffers are written to an online redo log file, which is used if database recovery is necessary. The size of the redo log is static.

Shared Pool of the SGA

The shared pool is a portion of the system global area that contains shared memory constructs such as shared SQL areas. A shared SQL area is required to process every unique SQL statement submitted to a database. A shared SQL area contains information such as the parse tree and execution plan for the corresponding statement. A single shared SQL area is used by multiple applications that issue the same statement, leaving more shared memory for other uses.

See Also:

"SQL Statements" for more information about shared SQL areas 

Large Pool in the SGA

The large pool is an optional area in the SGA that provides large memory allocations for Oracle backup and restore operations, I/O server processes, and session memory for the shared server and Oracle XA (used where transactions interact with more than one database).

Statement Handles or Cursors

A cursor is a handle (a name or pointer) for the memory associated with a specific statement. (The Oracle Call Interface, OCI, refers to these as statement handles.) Although most Oracle users rely on automatic cursor handling of Oracle utilities, the programmatic interfaces offer application designers more control over cursors.

For example, in precompiler application development, a cursor is a named resource available to a program and can be used specifically to parse SQL statements embedded within the application. The application developer can code an application so it controls the phases of SQL statement execution and thus improves application performance.

Program Global Area (PGA)

The Program Global Area (PGA) is a memory buffer that contains data and control information for a server process. A PGA is created by Oracle when a server process is started. The information in a PGA depends on the Oracle configuration.

Process Architecture

A process is a "thread of control" or a mechanism in an operating system that can execute a series of steps. Some operating systems use the terms job or task. A process normally has its own private memory area in which it runs.

An Oracle server has two general types of processes: user processes and Oracle processes.

User (Client) Processes

A user process is created and maintained to execute the software code of an application program (such as a Pro*C/C++ program) or an Oracle tool (such as Oracle Enterprise Manager). The user process also manages the communication with the server processes.

User processes communicate with the server processes through the program interface, which is described in a later section.

Oracle Process Architecture

Oracle processes are called (invoked) by other processes to perform functions on behalf of the invoking process. The different types of Oracle processes and their specific functions are discussed in the following sections.

Server Processes

Oracle creates server processes to handle requests from connected user processes. A server process is in charge of communicating with the user process and interacting with Oracle to carry out requests of the associated user process. For example, if a user queries some data that is not already in the database buffers of the system global area, the associated server process reads the proper data blocks from the datafiles into the system global area.

Oracle can be configured to vary the number of user processes for each server process. In a dedicated server configuration, a server process handles requests for a single user process. A shared server configuration lets many user processes share a small number of server processes, minimizing the number of server processes and maximizing the use of available system resources.

On some systems, the user and server processes are separate, while on others they are combined into a single process. If a system uses the shared server or if the user and server processes run on different machines, the user and server processes must be separate. Client/server systems separate the user and server processes and execute them on different machines.

Background Processes

Oracle creates a set of background processes for each instance. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user process. The background processes asynchronously perform I/O and monitor other Oracle processes to provide increased parallelism for better performance and reliability.

An SGA and the set of Oracle background processes constitute an Oracle instance. Each Oracle instance can use several background processes. The names of these processes are DBWn, LGWR, CKPT, SMON, PMON, ARCn, RECO, Dnnn, LMS, Jnnn, and QMNn.

See Also:

Database Writer (DBWn)

The database writer writes modified blocks from the database buffer cache to the datafiles. Although one database writer process (DBW0) is sufficient for most systems, you can configure additional processes (DBW1 through DBW9) to improve write performance for a system that modifies data heavily. The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes.

Because Oracle uses write-ahead logging, DBWn does not need to write blocks when a transaction commits. Instead, DBWn is designed to perform batched writes with high efficiency. In the most common case, DBWn writes only when more data needs to be read into the system global area and too few database buffers are free. The least recently used data is written to the datafiles first. DBWn also performs writes for other functions such as checkpointing.

See Also:

"Transactions" for more information about commits 

Log Writer (LGWR)

The log writer writes redo log entries to disk. Redo log entries are generated in the redo log buffer of the SGA, and LGWR writes the redo log entries sequentially into an online redo log file. If the database has a multiplexed redo log, LGWR writes the redo log entries to a group of online redo log files.

Checkpoint (CKPT)

At specific times, all modified database buffers in the system global area are written to the datafiles by DBWn. This event is called a checkpoint. The checkpoint process is responsible for signaling DBWn at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint.

System Monitor (SMON)

The system monitor performs crash recovery when a failed instance starts up again. In a multiple instance system (one that uses Oracle9i Real Application Clusters), the SMON process of one instance can perform instance recovery for other instances that have failed. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online. SMON also coalesces free extents within the database's dictionary-managed tablespaces to make free space contiguous and easier to allocate.

Process Monitor (PMON)

The process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using. PMON also checks on dispatcher and server processes and restarts them if they have failed.

Archiver (ARCn)

The archiver copies the online redo log files to archival storage after a log switch has occurred. Although a single ARCn process (ARC0) is sufficient for most systems, you can specify up to 10 ARCn processes by using the dynamic initialization parameter LOG_ARCHIVE_MAX_PROCESSES. If the workload becomes too great for the current number of ARCn processes, LGWR automatically starts another ARCn process up to the maximum of 10 processes. ARCn is active only when a database is in ARCHIVELOG mode and automatic archiving is enabled.

See Also:

"The Redo Log" for more information about the archiver 

Recoverer (RECO)

The recoverer is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

Job Queue Processes

Job queue processes are used for batch processing. Beginning with Oracle9i, Release 1 (9.0.1), job queue processes are managed dynamically. This enables job queue clients to use more job queue processes when required. The resources used by the new processes are released when they are idle.

See Also:

Dispatcher (Dnnn)

Dispatchers are optional background processes, present only when a multithreaded server configuration is used. At least one dispatcher process is created for every communication protocol in use (D000, . . ., Dnnn). Each dispatcher process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes.

Lock Manager Server (LMS)

The Lock Manager Server process (LMS) is used for inter-instance locking in Oracle9i Real Application Clusters.

See Also:

"Oracle9i Real Application Clusters: Multiple Instance Systems" for more information about the configuration of the lock process 

Queue Monitor (QMNn)

The queue monitor(s) are optional background processes that monitor the message queues for Oracle Advanced Queuing. You can configure up to 10 queue monitor processes.

Communications Software and Oracle Net Services

If the user and server processes are on different computers of a network or if user processes connect to shared server processes through dispatcher processes, the user process and server process communicate using Oracle Net Services. Dispatchers are optional background processes, present only in the multithreaded server configuration. Oracle Net Services is Oracle's interface to standard communications protocols that enables data transmission between computers.

See Also:

"Oracle and Oracle Net Services" 

The Program Interface Mechanism

The program interface is the mechanism by which a user process communicates with a server process. It serves as a method of standard communication between any client tool or application (such as Oracle Forms) and Oracle software. Its functions are to:

An Example of How Oracle Works

The following example illustrates an Oracle configuration where the user and associated server process are on separate machines (connected through a network):

  1. An instance is currently running on the computer that is executing Oracle (often called the host or database server).

  2. A computer running an application (a local machine or client workstation) runs the application in a user process. The client application attempts to establish a connection to the server using the proper Oracle Net Services driver.

  3. The server is running the proper Oracle Net Services driver. The server detects the connection request from the application and creates a (dedicated) server process on behalf of the user process.

  4. The user executes a SQL statement and commits the transaction. For example, the user changes a name in a row of a table.

  5. The server process receives the statement and checks the shared pool for any shared SQL area that contains a similar SQL statement. If a shared SQL area is found, the server process checks the user's access privileges to the requested data, and the previously existing shared SQL area is used to process the statement. If not, a new shared SQL area is allocated for the statement so it can be parsed and processed.

  6. The server process retrieves any necessary data values from the actual datafile (table) or those stored in the system global area.

  7. The server process modifies data in the system global area. The DBWn process writes modified blocks permanently to disk when doing so is efficient. Because the transaction is committed, the LGWR process immediately records the transaction in the online redo log file.

  8. If the transaction is successful, the server process sends a message across the network to the application. If it is not successful, an error message is transmitted.

  9. Throughout this entire procedure, the other background processes run, watching for conditions that require intervention. In addition, the database server manages other users' transactions and prevents contention between transactions that request the same data.

These steps describe the most basic level of operations that Oracle performs.

See Also:

Chapter 9, "Process Architecture" for more information about Oracle configuration 

Processes and Threads on Windows NT

In the UNIX environment, most Oracle processes are threads of one master Oracle process rather than being individual processes. On Windows NT, all processes consist of at least one thread. A thread is an individual execution within a process. Threads are objects within a process that execute program instructions. Threads enable concurrent operations within a process so that a process can execute different parts of its program simultaneously on different processors. A thread is the most fundamental component that can be scheduled on Windows NT.

In UNIX documentation such as this book, whenever the word "process" is mentioned, it is considered a "thread" on Windows NT.

See Also:

Oracle9i Database Administrator's Guide for Windows for more information about processes and threads on the Windows NT operating system 

The Object-Relational Model for Database Management

Database management systems have evolved from hierarchical to network to relational models. The most widely accepted database model is the relational model. Oracle extends the relational model to an object-relational model, which makes it possible to store complex business models in a relational database.

The Relational Model

The relational model has three major characteristics:


Structures are well-defined objects (such as tables, views, indexes, and so on) that store or access the data of a database. Structures and the data contained within them can be manipulated by operations.


Operations are clearly defined actions that enable users to manipulate the data and structures of a database. The operations on a database must adhere to a predefined set of integrity rules.

Integrity rules

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

Relational database management systems offer benefits such as:

The Object-Relational Model

The object-relational model enables users to define object types, specifying both the structure of the data and the methods of operating on the data, and to use these datatypes within the relational model.

An object type differs from native SQL datatypes in that it is user-defined and it specifies both the underlying persistent data (attributes) and the related behaviors (methods).

Object types are abstractions of the real-world entities--for example, purchase orders--that application programs deal with. Object types are used to extend the modeling capabilities provided by the native datatypes.

An object type has three kinds of components:

Type Inheritance

Oracle9i, Release 1 (9.0.1), supports inheritance of object types, including the ability to:

Type inheritance is a way to organize types the same way types can be used for organizing objects. Inheritance provides a higher level of abstraction for managing complexity of the application model.

Type inheritance enables sharing of similarities between types as well as extending their characteristics. Sharing provides efficiency in development time and improves manageability of applications. Extensibility provides flexibility and power of expression to solving application problems.

Most object applications organize their objects into types, and types into type hierarchies. Empirically, it is sufficient to organize type hierarchies into sets of trees; thus, single type inheritance is sufficient to support type organization for most applications. A subtype may extend (inherit from) at most one supertype; furthermore, a supertype must be an object type and so a subtype is always an object type. A subtype inherits all its supertype's attributes and methods. A subtype may also add new (called declared) attributes and methods. All attributes, inherited and declared, and methods of a type are accessible to methods declared in the type and its subtype(s) or to other methods that have access to the type.

Type inheritance and polymorphism enable applications to use a shrink-wrapped type library as given, or to extend the type library by adding new attributes or methods to the library's types, or by specializing (overriding) already defined methods of the library's types. A subtype may override any method in its supertype chain.

The benefits of polymorphism derive partially from substitutability. Substitutability enables a value of some subtype to be used by code originally written for the supertype, without any specific knowledge of the subtype being needed in advance. The subtype value behaves to the surrounding code just like a value of the supertype would, even if it perhaps uses different mechanisms within its specializations of methods. Instance substitutability refers to the ability to use an object value of a subtype in a context declared in terms of a supertype; REF substitutability refers to the ability to use a REF to a subtype in a context declared in terms of a REF to a supertype.


A REF is a reference to an object stored in a database table, instead of the object itself. REF types can occur in relational columns and also as datatypes of an object type. 

See Also:

Oracle9i SQL Reference 

Description of Schemas and Schema Objects

A schema is a collection of database objects that are available to a user. Schema objects are the logical structures that directly refer to the database's data. Schema objects include such structures as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links. (There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.)

Description of Tables

A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user-accessible data.

Table data is stored in rows and columns. Every table is defined with a table name and set of columns. Each column is given a column name, a datatype (such as CHAR, DATE, or NUMBER), and a width (which could be predetermined by the datatype, as in DATE) or scale and precision (for the NUMBER datatype only). Once a table is created, valid rows of data can be inserted into it. The table's rows can then be queried, deleted, or updated.

To enforce defined business rules on a table's data, integrity constraints and triggers can also be defined for a table.

See Also:


Description of Views

A view is a custom-tailored presentation of the data in one or more tables. A view can also be thought of as a "stored query."

Views do not actually contain or store data. Rather, they derive their data from the tables on which they are based, referred to as the base tables of the views. Base tables (also known as master tables) can in turn be tables or can themselves be views.

Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view.

Views are often used to do the following:

Views that involve a join (a SELECT statement that selects data from multiple tables) of two or more tables can only be updated under certain conditions.

See Also:

"Updatable Join Views" for more information about updating join views 

Materialized Views

A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views. A materialized view can be stored in the same database as its base table(s) or in a different database.

Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.

The optimizer can rewrite the query to access the precomputed results stored in a materialized view in the following cases:


A sequence generates a serial list of unique numbers for numeric columns of a database's tables. Sequences simplify application programming by automatically generating unique numerical values for the rows of a single table or multiple tables.

For example, assume two users are simultaneously inserting new employee rows into the EMP table. By using a sequence to generate unique employee numbers for the EMPNO column, neither user has to wait for the other to enter the next available employee number. The sequence automatically generates the correct values for each user.

Sequence numbers are independent of tables, so the same sequence can be used for one or more tables. After creation, a sequence can be accessed by various users to generate actual sequence numbers.

Program Units

Program unit is used in this manual to refer to stored procedures, functions, packages, triggers, and anonymous blocks.

See Also:

"Data Access Overview" for more information about SQL and PL/SQL procedures, functions, and packages 


A synonym is an alias for a table, view, sequence, or program unit. A synonym is not actually a schema object itself, but instead is a direct reference to a schema object. Synonyms are used to:

A synonym can be public or private. An individual user can create a private synonym, which is available only to that user. Database administrators most often create public synonyms that make the base schema object available for general, systemwide use by any database user.


Indexes are optional structures associated with tables. Indexes can be created to increase the performance of data retrieval. Just as the index in this manual helps you locate specific information faster than if there were no index, an Oracle index provides a faster access path to table data.

When processing a request, Oracle can use some or all of the available indexes to locate the requested rows efficiently. Indexes are useful when applications often query a table for a range of rows (for example, all employees with a salary greater than 1000 dollars) or a specific row.

Indexes are created on one or more columns of a table. Once created, an index is automatically maintained and used by Oracle. Changes to table data (such as adding new rows, updating rows, or deleting rows) are automatically incorporated into all relevant indexes with complete transparency to the users.

Indexes are logically and physically independent of the data. They can be dropped and created any time with no effect on the tables or other indexes. If an index is dropped, all applications continue to function. However, access to previously indexed data could be slower.

You can partition indexes.

See Also:

Chapter 12, "Partitioned Tables and Indexes" for more information about partitioning indexes 

Clusters and Hash Clusters

Clusters and hash clusters are optional structures for storing table data. They can be created to increase the performance of data retrieval.

Clustered Tables

Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, disk access time improves.

The related columns of the tables in a cluster are called the cluster key. The cluster key is indexed so that rows of the cluster can be retrieved with a minimum amount of I/O. Because the data in a cluster key of an index cluster (a nonhash cluster) is stored only once for multiple tables, clusters store a set of tables more efficiently than if the tables were stored individually (not clustered).

Clusters also can improve performance of data retrieval, depending on data distribution and what SQL operations are most often performed on the data. In particular, clustered tables that are queried in joins benefit from the use of clusters because the rows common to the joined tables are retrieved with the same I/O operation.

Like indexes, clusters do not affect application design. Whether or not a table is part of a cluster is transparent to users and to applications. Data stored in a clustered table is accessed by SQL in the same way as data stored in a nonclustered table.

Figure 1-3 illustrates how clustered and nonclustered data are physically stored.

Figure 1-3 Clustered and Nonclustered Tables

Text description of scn81048.gif follows
Text description of the illustration scn81048.gif
Hash Clusters

Hash clusters cluster table data in a manner similar to normal, index clusters (clusters keyed with an index rather than a hash function). However, a row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same key value are stored together on disk.

Hash clusters are a better choice than using an indexed table or index cluster when a table is often queried with equality queries (for example, return all rows for department 10). For such queries, the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the rows.


A dimension defines hierarchical (parent/child) relationships between pairs of columns or column sets. Each value at the child level is associated with one and only one value at the parent level.

A dimension schema object is a container of logical relationships between tables and does not have any data storage assigned to it. The CREATE DIMENSION statement specifies:

The columns in a dimension can come either from the same table (denormalized) or from multiple tables (fully or partially normalized). To define a dimension over columns from multiple tables, connect the tables by inner equijoins using the JOIN clause of the HIERARCHY clause.

See Also:

Oracle9i Data Warehousing Guide for information about using dimensions in materialized views and data warehousing 

Database Links

A database link is a named schema object that describes a path from one database to another. Database links are implicitly used when a reference is made to a global object name in a distributed database.

See Also:

"Distributed Databases" 

Data Dictionary

Each Oracle database has a data dictionary. An Oracle data dictionary is a set of tables and views that are used as a read-only reference about the database. For example, a data dictionary stores information about both the logical and physical structure of the database. In addition to this valuable information, a data dictionary also stores such information as:

A data dictionary is created when a database is created. To accurately reflect the status of the database at all times, the data dictionary is automatically updated by Oracle in response to specific actions (such as when the structure of the database is altered). The data dictionary is critical to the operation of the database, which relies on the data dictionary to record, verify, and conduct ongoing work. For example, during database operation, Oracle reads the data dictionary to verify that schema objects exist and that users have proper access to them.

See Also:

Chapter 5, "The Data Dictionary" 

Data Concurrency and Consistency Overview

This section explains the software mechanisms used by Oracle to fulfill the following important requirements of an information management system:


A primary concern of a multiuser database management system is how to control concurrency, which is the simultaneous access of the same data by many users. Without adequate concurrency controls, data could be updated or changed improperly, compromising data integrity.

If many people are accessing the same data, one way of managing data concurrency is to make each user wait for a turn. The goal of a database management system is to reduce that wait so it is either nonexistent or negligible to each user. All data manipulation language statements should proceed with as little interference as possible, and destructive interactions between concurrent transactions must be prevented. Destructive interaction is any interaction that incorrectly updates data or incorrectly alters underlying data structures. Neither performance nor data integrity can be sacrificed.

Oracle resolves such issues by using various types of locks and a multiversion consistency model. Both features are discussed later in this section. These features are based on the concept of a transaction. It is the application designer's responsibility to ensure that transactions fully exploit these concurrency and consistency features.

See Also:

"Data Consistency Using Transactions" for more information about concurrency and consistency features 

Read Consistency

Read consistency, as supported by Oracle, does the following:

The simplest way to think of Oracle's implementation of read consistency is to imagine each user operating a private copy of the database, hence the multiversion consistency model.

Read Consistency, Undo Records, and Transactions

To manage the multiversion consistency model, Oracle must create a read-consistent set of data when a table is being queried (read) and simultaneously updated (written). When an update occurs, the original data values changed by the update are recorded in the database's undo records. As long as this update remains part of an uncommitted transaction, any user that later queries the modified data views the original data values. Oracle uses current information in the system global area and information in the undo records to construct a read-consistent view of a table's data for a query.

Only when a transaction is committed are the changes of the transaction made permanent. Statements that start after the user's transaction is committed only see the changes made by the committed transaction.

Note that a transaction is key to Oracle's strategy for providing read consistency. This unit of committed (or uncommitted) SQL statements:

Read-Only Transactions

By default, Oracle guarantees statement-level read consistency. The set of data returned by a single query is consistent with respect to a single point in time. However, in some situations, you may also require transaction-level read consistency. This is the ability to run multiple queries within a single transaction, all of which are read-consistent with respect to the same point in time, so that queries in this transaction do not see the effects of intervening committed transactions.

If you want to run a number of queries against multiple tables and if you are not doing any updating, you may prefer a read-only transaction. After indicating that your transaction is read-only, you can execute as many queries as you like against any table, knowing that the results of each query are consistent with respect to the same point in time.

Locking Mechanisms

Oracle also uses locks to control concurrent access to data. Locks are mechanisms intended to prevent destructive interaction between users accessing Oracle data.

Locks are used to achieve two important database goals:


Ensures that the data a user is viewing or changing is not changed (by other users) until the user is finished with the data.


Ensures that the database's data and structures reflect all changes made to them in the correct sequence.

Locks guarantee data integrity while enabling maximum concurrent access to the data by unlimited users.

Automatic Locking

Oracle locking is performed automatically and requires no user action. Implicit locking occurs for SQL statements as necessary, depending on the action requested.

Oracle's lock manager automatically locks table data at the row level. By locking table data at the row level, contention for the same data is minimized.

Oracle's lock manager maintains several different types of row locks, depending on what type of operation established the lock. In general, there are two types of locks: exclusive locks and share locks. Only one exclusive lock can be placed on a resource (such as a row or a table); however, many share locks can be placed on a single resource. Both exclusive and share locks always allow queries on the locked resource but prohibit other activity on the resource (such as updates and deletes).

Manual Locking

Under some circumstances, a user may want to override default locking. Oracle allows manual override of automatic locking features at both the row level (by first querying for the rows that will be updated in a subsequent statement) and the table level.

See Also:

"Explicit (Manual) Data Locking" 

Quiesce Database

Database administrators often need isolation from concurrent nonDBA actions, that is, isolation from concurrent nonDBA transactions, queries, or PL/SQL statements. One way to provide such isolation is to shut down the database and reopen it in restricted mode. The Quiesce Database feature introduced in Oracle9i, Release 1 (9.0.1), provides another way of providing isolation: to put the system into quiesced state without disrupting users.

The database administrator uses SQL statements to quiesce the database. Once the system is in quiesced state, the DBA can safely perform certain actions whose executions require isolation from concurrent nonDBA users.

See Also:

"Quiesce Database" for more information about quiescing a database 

Startup and Shutdown Operations

An Oracle database is not available to users until the Oracle server has been started up and the database has been opened. These operations must be performed by the database administrator. Starting a database and making it available for systemwide use consists of three steps:

  1. Start an instance of the Oracle server.

  2. Mount the database.

  3. Open the database.

When the Oracle server starts up, it uses a parameter file that contains initialization parameters. These parameters specify the name of the database, the amount of memory to allocate, the names of control files, and various limits and other system parameters.

Shutting down an instance and the database to which it is connected takes three steps:

  1. Close the database.

  2. Unmount the database.

  3. Shut down the instance of the Oracle server.

Oracle automatically performs all three steps when an instance is shut down.

See Also:


Go to previous page Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index