Oracle8 Concepts
Release 8.0
A58227-01

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Part I What Is Oracle?

1 Introduction to the Oracle Server

Databases and Information Management

The Oracle Server

Oracle Databases

Database Structure and Space Management

Logical Database Structures

Physical Database Structures

Memory Structure and Processes

Memory Structures

Process Architecture

The Program Interface

An Example of How Oracle Works

Data Concurrency and Consistency

Read Consistency

Locking Mechanisms

Distributed Processing and Distributed Databases

Client/Server Architecture: Distributed Processing

Distributed Databases

Table Replication

Oracle and Net8

Startup and Shutdown Operations

Database Security

Security Mechanisms

Database Backup and Recovery

Why Is Recovery Important?

Types of Failures

Structures Used for Recovery

Basic Recovery Steps

The Recovery Manager

The Object-Relational Model for Database Management

The Relational Model

The Object-Relational Model

Schemas and Schema Objects

The Data Dictionary

SQL - The Structured Query Language

Part II Database Structures

2 Data Blocks, Extents, and Segments

The Relationships Among Data Blocks, Extents, and Segments

Data Block Format

An Introduction to PCTFREE, PCTUSED, and Row Chaining

When Extents Are Allocated

Determining the Number and Size of Extents

How Extents Are Allocated

When Extents Are Deallocated

Temporary Segments

Rollback Segments

3 Tablespaces and Datafiles

An Introduction to Tablespaces and Datafiles

The SYSTEM Tablespace

Allocating More Space for a Database

Bringing Tablespaces Online and Offline

Read-Only Tablespaces

Temporary Tablespaces

Datafile Contents

Size of Datafiles

Offline Datafiles

4 The Data Dictionary

An Introduction to the Data Dictionary

The Structure of the Data Dictionary

SYS, the Owner of the Data Dictionary

How the Data Dictionary Is Used

How Oracle Uses the Data Dictionary

How Oracle Users Can Use the Data Dictionary

The Dynamic Performance Tables

Part III The Oracle Instance

5 Database and Instance Startup and Shutdown

Overview of an Oracle Instance

The Instance and the Database

Connecting with Administrator Privileges

Parameter Files

Instance and Database Startup

Starting an Instance

Mounting a Database

Opening a Database

Database and Instance Shutdown

Closing a Database

Dismounting a Database

Shutting Down an Instance

6 Memory Structures

Introduction to Oracle Memory Structures

System Global Area (SGA)

The Database Buffer Cache

The Redo Log Buffer

The Shared Pool

Size of the SGA

Controlling the SGA's Use of Memory

Program Global Areas (PGA)

Contents of a PGA

Sort Direct Writes

Software Code Areas

7 Process Structure

Introduction to Processes

Single-Process Oracle

Multiple-Process Oracle

Oracle Processes

Trace Files and the ALERT File

Variations in Oracle Configuration

Single-Task Configuration

Dedicated Server (Two-Task) Configuration

The Multithreaded Server

Examples of How Oracle Works

An Example of Oracle Using Dedicated Server Processes

An Example of Oracle Using the Multithreaded Server

The Program Interface

Program Interface Structure

The Program Interface Drivers

Operating System Communications Software

Part IV The Object-Relational DBMS

8 Schema Objects

Overview of Schema Objects

How Table Data Is Stored

Default Values for Columns

Storage for Views

How Views Are Used

The Mechanics of Views

Dependencies and Views

Updatable Join Views

The Sequence Generator

Unique and Non-Unique Indexes

Composite Indexes

Indexes and Keys

How Indexes Are Stored

Reverse Key Indexes

Index-Organized Tables

Benefits of Index-Organized Tables

Index-Organized Tables with Row Overflow Area

Applications of Interest for Index-Organized Tables

Performance Considerations

Format of Clustered Data Blocks

The Cluster Key

The Cluster Index

How Data Is Stored in a Hash Cluster

Hash Key Values

Allocation of Space for a Hash Cluster

9 Partitioned Tables and Indexes

Introduction to Partitioning

What Is Partitioning?

Advantages of Partitioning

Very Large Databases (VLDBs)

Reducing Downtime for Scheduled Maintenance

Reducing Downtime Due to Data Failures

DSS Performance

I/O Performance

Disk Striping: Performance versus Availability

Partition Transparency

Manual Partitioning with Partition Views

Basic Partitioning Model

Range Partitioning

Partition Names

Partition Bounds and Partitioning Keys

Equipartitioning

Rules for Partitioning Tables and Indexes

Table Partitioning

Index Partitioning

DML Partition Locks

Performance Considerations for Oracle Parallel Server

Maintenance Operations

Partition Maintenance Operations

Managing Indexes

Privileges for Partitioned Tables and Indexes

Auditing for Partitioned Tables and Indexes

SQL Extension: Partition-Extended Table Name

Examples of Partition-Extended Table Names

10 Built-In Datatypes

Oracle Datatypes

Character Datatypes

NUMBER Datatype

RAW and LONG RAW Datatypes

MLSLABEL Datatype

Summary of Oracle Datatype Information

ANSI, DB2, and SQL/DS Datatypes

Data Conversion

11 User-Defined Datatypes (Objects Option)

Complex Data Models

Multimedia Datatypes

User-Defined Datatypes

Collection Types

Application Interfaces

12 Using User-Defined Datatypes

13 Object Views

Advantages of Object Views

Defining Object Views

Using Object Views

Updating Object Views

Part V Data Access

14 SQL and PL/SQL

Structured Query Language (SQL)

Identifying Nonstandard SQL

Overview of SQL Statement Execution

DML Statement Processing

DDL Statement Processing

Controlling Transactions

How PL/SQL Executes

Language Constructs for PL/SQL

Stored Procedures

External Procedures

15 Transaction Management

Introduction to Transactions

Statement Execution and Transaction Control

Statement-Level Rollback

Oracle and Transaction Management

Committing Transactions

Rolling Back Transactions

The Two-Phase Commit Mechanism

Discrete Transaction Management

16 Advanced Queuing

Introduction to Message Queuing

Synchronous Communication

Asynchronous Communication

Oracle Advanced Queuing

Queuing Entities

Features of Advanced Queuing

17 Procedures and Packages

18 Database Triggers

An Introduction to Triggers

How Triggers Are Used

Some Cautionary Notes about Triggers

Triggers vs. Declarative Integrity Constraints

Parts of a Trigger

Triggering Event or Statement

Trigger Restriction

Types of Triggers

Row Triggers and Statement Triggers

BEFORE and AFTER Triggers

Trigger Combinations

INSTEAD OF Triggers

Trigger Execution

The Execution Model for Triggers and Integrity Constraint Checking

Data Access for Triggers

Storage of Triggers

Execution of Triggers

Dependency Maintenance for Triggers

19 Oracle Dependency Management

An Introduction to Dependency Issues

Resolving Schema Object Dependencies

Compiling Views and PL/SQL Program Units

Dependency Management and Nonexistent Schema Objects

Shared SQL Dependency Management

Local and Remote Dependency Management

Managing Local Dependencies

Managing Remote Dependencies

20 The Optimizer

What Is Optimization?

Execution Plans

Execution Order

Cost-Based and Rule-Based Optimization

The Cost-Based Approach

Overview of Optimizer Operations

Optimizer Operations

Types of SQL Statements

Evaluation of Expressions and Conditions

ANY or SOME Operator

BETWEEN Operator

Transforming and Optimizing Statements

Transforming ORs into Compound Queries

Transforming Complex Statements into Join Statements

Optimizing Statements That Access Views

Optimizing Compound Queries

Optimizing Distributed Statements

Choosing an Optimization Approach and Goal

The OPTIMIZER_MODE Initialization Parameter

Statistics in the Data Dictionary

The OPTIMIZER_GOAL Parameter of the ALTER SESSION Command

The FIRST_ROWS, ALL_ROWS, CHOOSE, and RULE Hints

PL/SQL and the Optimizer Goal

Choosing Access Paths

Choosing Among Access Paths

Optimizing Join Statements

Join Operations

Choosing Execution Plans for Join Statements

Views in Outer Joins

Optimizing Anti-Joins and Semi-Joins

Optimizing "Star" Queries

Star Query Example

Tuning Star Queries

Star Transformation

Part VI Parallel SQL and Direct-Load INSERT

21 Direct-Load INSERT

Introduction to Direct-Load INSERT

Advantages of Direct-Load INSERT

INSERT ... SELECT Statements

Varieties of Direct-Load INSERT Statements

Serial and Parallel INSERT

Additional Considerations for Direct-Load INSERT

Index Maintenance

Space Considerations

Locking Considerations

Restrictions on Direct-Load INSERT

22 Parallel Execution

Overview of Parallel Execution

Operations That Can Be Parallelized

How Oracle Parallelizes Operations

Process Architecture for Parallel Execution

The Parallel Server Pool

Parallelizing SQL Statements

Setting the Degree of Parallelism

Determining the Degree of Parallelism for Operations

Balancing the Work Load

Parallelization Rules for SQL Statements

DDL Statements That Can Be Parallelized

CREATE TABLE ... AS SELECT in Parallel

Recoverability and Parallel DDL

Space Management for Parallel DDL

Advantages of Parallel DML over Manual Parallelism

When to Use Parallel DML

Enabling Parallel DML

Transaction Model for Parallel DML

Recovery for Parallel DML

Space Considerations for Parallel DML

Lock and Enqueue Resources for Parallel DML

Restrictions on Parallel DML

Other Types of Parallelism

Part VII Data Protection

23 Data Concurrency and Consistency

Data Concurrency and Consistency in a Multiuser Environment

Preventable Phenomena and Transaction Isolation Levels

Locking Mechanisms

How Oracle Manages Data Concurrency and Consistency

Multiversion Concurrency Control

Statement-Level Read Consistency

Transaction-Level Read Consistency

Oracle Isolation Levels

Setting the Isolation Level

Comparing Read Committed and Serializable Isolation

Choosing an Isolation Level

How Oracle Locks Data

Transactions and Data Concurrency

DML (Data) Locks

DDL Locks (Dictionary Locks)

Latches and Internal Locks

Explicit (Manual) Data Locking

Oracle Lock Management Services

24 Data Integrity

Definition of Data Integrity

Types of Data Integrity

How Oracle Enforces Data Integrity

An Introduction to Integrity Constraints

Advantages of Integrity Constraints

The Performance Cost of Integrity Constraints

Types of Integrity Constraints

NOT NULL Integrity Constraints

UNIQUE Key Integrity Constraints

PRIMARY KEY Integrity Constraints

FOREIGN KEY (Referential) Integrity Constraints

CHECK Integrity Constraints

The Mechanisms of Constraint Checking

Default Column Values and Integrity Constraint Checking

Deferred Constraint Checking

Constraint Attributes

SET CONSTRAINTS Mode

Unique Constraints and Indexes

Enabled, Disabled, and Enable Novalidate Constraints

25 Controlling Database Access

Database Security

Schemas, Database Users, and Security Domains

User Authentication

Authentication by the Operating System

Authentication by the Network

Authentication by the Oracle Database

Database Administrator Authentication

User Tablespace Settings and Quotas

Default Tablespace

Temporary Tablespace

Tablespace Access and Quotas

The User Group PUBLIC

User Resource Limits and Profiles

Types of System Resources and Limits

Concurrent Usage Licensing

Named User Licensing

26 Privileges and Roles

System Privileges

Schema Object Privileges

Common Uses for Roles

The Mechanisms of Roles

Granting and Revoking Roles

Who Can Grant or Revoke Roles?

Security Domains of Roles and Users

Named PL/SQL Blocks and Roles

Data Definition Language Statements and Roles

Predefined Roles

The Operating System and Roles

Roles in a Distributed Environment

27 Auditing

Introduction to Auditing

Auditing Features

Auditing Mechanisms

Statement Auditing

Privilege Auditing

Schema Object Auditing

Schema Object Audit Options for Views and Procedures

Focusing Statement, Privilege, and Schema Object Auditing

Auditing Successful and Unsuccessful Statement Executions

Auditing BY SESSION versus BY ACCESS

Auditing By User

28 Database Recovery

An Introduction to Database Recovery

Errors and Failures

Structures Used for Database Recovery

Database Backups

Rollback Segments

Rolling Forward and Rolling Back

The Redo Log and Rolling Forward

Rollback Segments and Rolling Back

Recovery Manager

Recovery Catalog

Parallelization

Report Generation

Performing Recovery in Parallel

Situations That Benefit from Parallel Recovery

Recovery Processes

Database Archiving Modes

NOARCHIVELOG Mode (Media Recovery Disabled)

ARCHIVELOG Mode (Media Recovery Enabled)

Control File Contents

Multiplexed Control Files

Database Backups

Whole Database Backups

Partial Database Backups

The Export and Import Utilities

Read-Only Tablespaces and Backup

Planning for Disaster Recovery

Standby Database

Part VIII Distributed Processing and Distributed Databases

29 Distributed Processing

Oracle Client/Server Architecture

Distributed Processing

30 Distributed Databases

Oracle's Distributed Database Architecture

Clients and Servers

Databases and Database Links

Schema Object Name Resolution

Connecting Between Oracle Server Versions

Distributed Databases and Distributed Processing

Distributed Databases and Database Replication

Heterogeneous Distributed Databases

Transparent SQL Access

Procedural Access

Gateway Features

Version 8 Gateways

Version 4 Gateways

Developing Distributed Database Applications

Remote and Distributed SQL Statements

Remote Procedure Calls (RPCs)

Remote and Distributed Transactions

Transparency in a Distributed Database System

Administering an Oracle Distributed Database System

Distributed Database Security

Tools for Administering Oracle Distributed Databases

Oracle Enterprise Manager

Third-Party Administration Tools

National Language Support

31 Database Replication

What Is Replication?

Basic Replication

Advanced (Symmetric) Replication

Basic Replication Concepts

Uses of Basic Replication

Read-Only Table Snapshots

Snapshot Refreshes

Other Basic Replication Options

Advanced Replication Concepts

Uses for Advanced Replication

Advanced Replication Configurations

Advanced Replication and the Oracle Replication Manager

Replication Objects, Groups, Sites, and Catalogs

Oracle's Advanced Replication Architecture

Replication Administrators, Propagators, and Receivers

Replication Conflicts

Unique Advanced Replication Options

Part IX Appendix

A Operating System-Specific Information

Copyright © 1997 Oracle Corporation.
All Rights Reserved.