Oracle8 Concepts
Release 8.0







Title and Copyright Information

Send Us Your Comments


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
Trusted Oracle
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
Data Access
SQL - The Structured Query Language
Data Integrity

Part II Database Structures

2 Data Blocks, Extents, and Segments

The Relationships Among Data Blocks, Extents, and Segments
Data Blocks
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
Data Segments
Index Segments
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
Size of a PGA
Sort Areas
Sort Direct Writes
Virtual Memory
Software Code Areas

7 Process Structure

Introduction to Processes
Single-Process Oracle
Multiple-Process Oracle
User Processes
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
Nested Tables
Storage for Views
How Views Are Used
The Mechanics of Views
Dependencies and Views
Updatable Join Views
Object Views
The Sequence Generator
Unique and Non-Unique Indexes
Composite Indexes
Indexes and Keys
How Indexes Are Stored
Reverse Key Indexes
Bitmap 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
Hash Clusters
How Data Is Stored in a Hash Cluster
Hash Key Values
Hash Functions
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
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
DATE Datatype
LOB Datatypes
RAW and LONG RAW Datatypes
ROWID 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
Object Types
Collection Types
Application Interfaces

12 Using User-Defined Datatypes

References and Name Resolution
Table Aliases
Method Calls without Arguments
Storage of User-Defined Types
Leaf-Level Attributes
Row Objects
Column Objects
Nested Tables
Properties of Object Attributes
Privileges on User-Defined Types and Their Methods
System Privileges
Schema Object Privileges
Using Types in New Types or Tables
Privileges on Type Access and Object Access
Dependencies and Incomplete Types
Completing Incomplete Types
Type Dependencies of Tables
Import/Export of User-Defined Types

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)
SQL Statements
Identifying Nonstandard SQL
Recursive SQL
Shared SQL
SQL Processing
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

An Introduction to Stored Procedures and Packages
Stored Procedures and Functions
Procedures and Functions
Procedure Guidelines
Benefits of Procedures
Anonymous PL/SQL Blocks vs. Stored Procedures
Standalone Procedures
Dependency Tracking for Stored Procedures
External Procedures
Benefits of Packages
Dependency Tracking for Packages
How Oracle Stores Procedures and Packages
Compiling Procedures and Packages
Storing the Compiled Code in Memory
Storing Procedures or Packages in Database
How Oracle Executes Procedures and Packages
Verifying User Access
Verifying Procedure Validity
Executing a Procedure

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
Trigger Action
Types of Triggers
Row Triggers and Statement Triggers
BEFORE and AFTER Triggers
Trigger Combinations
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
LIKE Operator
IN Operator
ANY or SOME Operator
ALL Operator
BETWEEN Operator
NOT 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
PL/SQL and the Optimizer Goal
Choosing Access Paths
Access Methods
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
Logging Mode
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
Parallel DDL
DDL Statements That Can Be Parallelized
Recoverability and Parallel DDL
Space Management for Parallel DDL
Parallel DML
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
Types of Locks
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
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?
Naming 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
The Redo Log
Rollback Segments
Control Files
Rolling Forward and Rolling Back
The Redo Log and Rolling Forward
Rollback Segments and Rolling Back
Recovery Manager
Recovery Catalog
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 Files
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
How Net8 Works

30 Distributed Databases

Oracle's Distributed Database Architecture
Clients and Servers
The Network
Databases and Database Links
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
Site Autonomy
Distributed Database Security
Tools for Administering Oracle Distributed Databases
Oracle Enterprise Manager
Third-Party Administration Tools
SNMP Support
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.