Oracle8i Concepts

Net8

How Net8 Works

The Network Listener

Multi-Tier Architecture

Clients

Application Servers

Database Servers

Introduction to Oracle Memory Structures

System Global Area (SGA)

The Database Buffer Cache

Controlling the SGA's Use of Memory

Program Global Areas (PGA)

Contents of a PGA

Size of a PGA

Sort Areas

Virtual Memory

Software Code Areas

Introduction to Processes

Multiple-Process Oracle Systems

Types of Processes

User Processes

Connections and Sessions

Oracle Processes

Server Processes

Background Processes

Trace Files and the ALERT File

Multi-Threaded Server Configuration

Dispatcher Request and Response Queues

Shared Server Processes

Artificial Deadlocks

Restricted Operations of the Multi-Threaded Server

An Example of Oracle Using the Multi-Threaded Server

Dedicated Server Configuration

An Example of Oracle Using Dedicated Server Processes

The Program Interface

Program Interface Structure

The Program Interface Drivers

Operating System Communications Software

Introduction to the Database Resource Manager

Resource Consumer Groups and Resource Plans

What Are Resource Consumer Groups?

What Are Resource Plans?

Resource Allocation Methods

CPU Resource Allocation Method: Emphasis

Maximum Degree of Parallelism Resource Allocation Method: Absolute

Resource Plan Directives

Examples

Using Resource Consumer Groups and Resource Plans

Using Subplans

Using Multi-Level Resource Plans

Using the Parallel Degree Limit Resource Directive

Summary

Using the Database Resource Manager

Overview of Schema Objects

Tables

How Table Data Is Stored

Nulls

Default Values for Columns

Views

The Mechanics of Views

Dependencies and Views

Updatable Join Views

Object Views

Inline Views

Materialized Views

Refreshing Materialized Views

Materialized View Logs

Dimensions

The Sequence Generator

Synonyms

Indexes

Unique and Nonunique Indexes

Composite Indexes

Indexes and Keys

Indexes and Nulls

Function-Based Indexes

How Indexes Are Stored

Key Compression

Reverse Key Indexes

Bitmap Indexes

Index-Organized Tables

Benefits of Index-Organized Tables

Index-Organized Tables with Row Overflow Area

Secondary Indexes on Index-Organized Tables

Additional Features of Index-Organized Tables

Applications of Interest for Index-Organized Tables

Application Domain Indexes

Indextypes

Domain Indexes

User-Defined Operators

Clusters

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

Single Table Hash Clusters

Introduction to Partitioning

What Is Partitioning?

Advantages of Partitioning

Manual Partitioning with Partition Views

Basic Partitioning Model

Range Partitioning

Hash Partitioning

Composite Partitioning

Partition and Subpartition Names

Partitioning and Subpartitioning Columns and Keys

Partition Bounds for Range Partitioning

Equipartitioning

Rules for Partitioning Tables and Indexes

Table Partitioning

Index Partitioning

Partitioning of Tables with LOB Columns

Partitioning Index-Organized Tables and Their Secondary Indexes

DML Partition Locks and Subpartition Locks

DML Partition Locks

DML Subpartition 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

Partition-Extended and Subpartition-Extended Table Names

PARTITION and SUBPARTITION Specifications

Viewing Partitions or Subpartitions as Tables

Using Partition- and Subpartition-Extended Table Names

Overview of Oracle Datatypes

Character Datatypes

CHAR Datatype

VARCHAR2 and VARCHAR Datatypes

Column Lengths for Character Datatypes and NLS Character Sets

NCHAR and NVARCHAR2 Datatypes

LOB Character Datatypes

LONG Datatype

NUMBER Datatype

Internal Numeric Format

DATE Datatype

Using Julian Dates

Date Arithmetic

Centuries and the Year 2000

LOB Datatypes

BLOB Datatype

CLOB and NCLOB Datatypes

BFILE Datatype

RAW and LONG RAW Datatypes

ROWID and UROWID Datatypes

The ROWID Pseudocolumn

Physical Rowids

Logical Rowids

Rowids in Non-Oracle Databases

ANSI, DB2, and SQL/DS Datatypes

Data Conversion

Introduction

Complex Data Models

Multimedia Datatypes

User-Defined Datatypes

Object Types

Collection Types

Application Interfaces

SQL

PL/SQL

Pro*C/C++

OCI

OTT

JPublisher

JDBC

SQLJ

Introduction

Object Types and References

Properties of Object Attributes

Collections

Nested Table Locators

DML on Collections

Privileges on User-Defined Types and Their Methods

System Privileges

Schema Object Privileges

Using Types in New Types or Tables

Example

Privileges on Type Access and Object Access

Dependencies and Incomplete Types

Completing Incomplete Types

Type Dependencies of Tables

Storage of User-Defined Types

Leaf-Level Attributes

Utilities

Import/Export of User-Defined Types

Loading User-defined Types

Introduction

Advantages of Object Views

Defining Object Views

Using Object Views

Updating Object Views

Updating Nested Table Columns in Views

Structured Query Language (SQL)

SQL Statements

Identifying Nonstandard SQL

SQL Processing

Overview of SQL Statement Execution

DML Statement Processing

DDL Statement Processing

Controlling Transactions

PL/SQL

How PL/SQL Executes

Language Constructs for PL/SQL

Stored Procedures

External Procedures

Introduction to Transactions

Statement Execution and Transaction Control

Statement-Level Rollback

Oracle and Transaction Management

Committing Transactions

Rolling Back Transactions

Savepoints

The Two-Phase Commit Mechanism

Discrete Transaction Management

Autonomous Transactions

Autonomous PL/SQL Blocks

Transaction Control Statements in Autonomous Blocks

An Introduction to Stored Procedures and Packages

Stored Procedures and Functions

Packages

Procedures and Functions

Procedure Guidelines

Benefits of Procedures

Anonymous PL/SQL Blocks versus Stored Procedures

Standalone Procedures

Definer Rights and Invoker Rights

Dependency Tracking for Stored Procedures

External Procedures

Packages

Benefits of Packages

Dependency Tracking for Packages

Oracle Supplied 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

Introduction to Message Queuing

Oracle Advanced Queuing

Queuing Entities

Features of Advanced Queuing

An Introduction to Triggers

How Triggers Are Used

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 Type Combinations

INSTEAD-OF Triggers

Triggers on System Events and User Events

Trigger Execution

The Execution Model for Triggers and Integrity Constraint Checking

Data Access for Triggers

Storage of PL/SQL Triggers

Execution of Triggers

Dependency Maintenance for Triggers

An Introduction to Dependency Issues

Resolving Schema Object Dependencies

Compiling Views and PL/SQL Program Units

Function-Based Index Dependencies

Dependency Management and Nonexistent Schema Objects

Shared SQL Dependency Management

Local and Remote Dependency Management

Managing Local Dependencies

Managing Remote Dependencies

What Is Optimization?

Execution Plans

Execution Order

Optimizer Plan Stability

Cost-Based Optimization

Goal of the Cost-Based Approach

Statistics for Cost-Based Optimization

When to Use the Cost-Based Approach

Extensible Optimization

User-Defined Statistics

User-Defined Selectivity

User-Defined Costs

Rule-Base Optimization

Overview of Optimizer Operations

Optimizer Operations

Types of SQL Statements

Evaluation of Expressions and Conditions

DETERMINISTIC Functions

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

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

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

Overview of Parallel Execution

Operations That Can Be Parallelized

How Oracle Parallelizes Operations

Process Architecture for Parallel Execution

The Parallel Execution Server Pool

How Parallel Execution Servers Communicate

Parallelizing SQL Statements

Setting the Degree of Parallelism

How Oracle Determines the Degree of Parallelism for Operations

Balancing the Work Load

Parallelization Rules for SQL Statements

Parallel Query

Parallel Queries on Index-Organized Tables

Parallel Queries on Object Types

Parallel DDL

DDL Statements That Can Be Parallelized

CREATE TABLE ... AS SELECT in Parallel

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

Parallel Execution of Functions

Affinity

Affinity and Parallel Queries

Affinity and Parallel DML

Other Types of Parallelism

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

Read Consistency in the Oracle Parallel Server

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

Deadlocks

Types of Locks

DML (Data) Locks

DDL Locks (Dictionary Locks)

Latches and Internal Locks

Explicit (Manual) Data Locking

Oracle Lock Management Services

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

Constraint States

Modifying Constraint States

Database Security

Schemas, Database Users, and Security Domains

User Authentication

Authentication by the Operating System

Authentication by the Network

Authentication by the Oracle Database

Multi-Tier Authentication and Authorization

Authentication of Database Administrators

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

Profiles

Licensing

Concurrent Usage Licensing

Named User Licensing

Privileges

System Privileges

Schema Object Privileges

Table Security Topics

View Security Topics

Procedure Security Topics

Type Security Topics

Roles

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

PL/SQL Blocks and Roles

Data Definition Language Statements and Roles

Predefined Roles

The Operating System and Roles

Roles in a Distributed Environment

Fine-Grained Access Control

Dynamic Predicates

Security Policy Example

Application Context

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

An Introduction to Database Recovery

Errors and Failures

Structures Used for Database Recovery

Rolling Forward and Rolling Back

The Redo Log and Rolling Forward

Rollback Segments and Rolling Back

Improving Recovery Performance

Performing Recovery in Parallel

Fast-Start Recovery

Masking Failures with Transparent Application Failover

Recovery Manager

Recovery Catalog

Parallelization

Report Generation

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

Survivability

Planning for Disaster Recovery

Automated Standby Database

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

Heterogeneous Services

Heterogeneous Services Agents

Features

Developing Distributed Database Applications

Distributed Query Optimization

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

Enterprise Manager

Third-Party Administration Tools

SNMP Support

National Language Support

What Is Replication?

Replication Objects, Groups, and Sites

Multimaster Replication

Uses for Multimaster Replication

Snapshot Replication

Read-Only Snapshots

Updateable Snapshots

Uses of Snapshot Replication

Multimaster and Snapshot Hybrid Configurations

Administering a Replicated Environment

Replication Catalog

Replication Management API and Administration Requests

Oracle Replication Manager

Replication Conflicts

Specialized Replication Options