Oracle9i Database Concepts -- Contents

Introduction to Databases and Information Management

The Oracle Server

Database Structure and Space Management

Logical Database Structures

Physical Database Structures

Memory Structure and Processes

Memory Structures

Process Architecture

The Program Interface Mechanism

An Example of How Oracle Works

Processes and Threads on Windows NT

The Object-Relational Model for Database Management

The Relational Model

The Object-Relational Model

Type Inheritance

Description of Schemas and Schema Objects

Data Dictionary

Data Concurrency and Consistency Overview

Startup and Shutdown Operations

Internet Computing and Distributed Databases

Client/Server Architecture: Distributed Processing

Multitier Architecture: Application Servers

Distributed Databases

Replication

Heterogeneous Services

Advanced Queuing Overview

Uses for Message Queuing

Advantages of Oracle Advanced Queuing

Data Warehousing Overview

Data Access Overview

SQL--The Structured Query Language

Transactions

PL/SQL--Procedural Language Extension to SQL

Data Integrity

Database Security Overview

Security Mechanisms

Privileges

Database Backup and Recovery Overview

Why Recovery Is Important

Types of Failures

Structures Used for Recovery

Basic Recovery Steps

Transparent Application Failover (TAF)

Recovery Manager

Instance Recovery Tuning

Introduction to Oracle9i Data Guard

Background of Oracle9i Data Guard

Overview of Oracle9i Data Guard

How the Data Guard Components Work Together

Disaster Recovery Server and DRMON

Limiting Data Loss

LogMiner SQL-Based Log Analyzer Overview

Introduction to Data Blocks, Extents, and Segments

Data Blocks Overview

Data Block Format

Free Space Management

PCTFREE, PCTUSED, and Row Chaining

Extents Overview

When Extents Are Allocated

Determine the Number and Size of Extents

How Extents Are Allocated

When Extents Are Deallocated

Segments Overview

Introduction to Data Segments

Introduction to Index Segments

Introduction to Temporary Segments

Automatic Undo Management

Introduction to Rollback Segments

Introduction to Tablespaces, Datafiles, and Control Files

Allocate More Space for a Database

Tablespaces Overview

The SYSTEM Tablespace

Undo Tablespaces

Default Temporary Tablespace

Multiple Tablespace Usage

Space Management in Tablespaces

Nonstandard Block Sizes

Online and Offline Tablespaces

Read-Only Tablespaces

Temporary Tablespaces for Sorts

Transport of Tablespaces between Databases

Datafiles Overview

Control Files Overview

Control File Contents

Multiplexed Control Files

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 Users and DBAs Can Use the Data Dictionary

The Dynamic Performance Tables

Database Object Metadata

Introduction to an Oracle Instance

The Instance and the Database

Connection with Administrator Privileges

Initialization Parameter Files

Instance and Database Startup

How an Instance Is Started

How a Database Is Mounted

What Happens When You Open a Database

Database and Instance Shutdown

Close a Database

Unmount a Database

Shut Down an Instance

Client/Server Architecture

Multitier Architecture

Clients

Application Servers

Database Servers

Distributed Processing Overview

Oracle Net Services

How Oracle Net Services Works

The Listener

Oracle Internet Directory

Introduction to Oracle Memory Structures

System Global Area (SGA) Overview

Dynamic SGA

The Database Buffer Cache

The Redo Log Buffer

The Shared Pool

The Large Pool

Control of the SGA's Use of Memory

Other SGA Initialization Parameters

Program Global Areas (PGA) Overview

Content of the PGA

SQL Work Areas

PGA Memory Management for Dedicated Mode

Dedicated and Shared Servers

Software Code Areas

Introduction to Processes

Multiple-Process Oracle Systems

Types of Processes

User Processes Overview

Connections and Sessions

Oracle Processes Overview

Server Processes

Background Processes

Trace Files and the Alert Log

Shared Server Architecture

Internet Scalability

Dispatcher Request and Response Queues

Shared Server Processes (Snnn)

Restricted Operations of the Shared Server

Dedicated Server Configuration

The Program Interface

Program Interface Structure

Program Interface Drivers

Communications Software for the Operating System

Introduction to the Database Resource Manager

Database Resource Manager Terms

Example of a Simple Resource Plan

How the Database Resource Manager Works

Resource Control

Database Integration

Performance Overhead

Resource Plans and Resource Consumer Groups

Activation of a Resource Plan

Groups of Resource Plans

Resource Allocation Methods and Resource Plan Directives

Resource Plan Directives

CPU Resource Allocation

Interaction with Operating-System Resource Control

Dynamic Reconfiguration

Introduction to Schema Objects

Tables

How Table Data Is Stored

Nulls Indicate Absence of Value

Default Values for Columns

Views

The Mechanics of Views

Dependencies and Views

Updatable Join Views

Object Views in an Oracle Database

Inline Views

Materialized Views

Define Constraints on Views

Refresh 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

How Indexes Are Searched

Index-Organized Tables

Benefits of Index-Organized Tables

Index-Organized Tables with Row Overflow Area

Secondary Indexes on Index-Organized Tables

Bitmap Indexes on Index-Organized Tables

Partitioned Index-Organized Tables

B-tree Indexes on UROWID Columns for Heap- and Index-Organized Tables

Index-Organized Table Applications

Application Domain Indexes

Clusters

Hash Clusters

Introduction to Partitioning

Partition Key

Partitioned Tables

Partitioned Index-Organized Tables

Partitioning Methods

Range Partitioning

List Partitioning

Hash Partitioning

Composite Partitioning

When to Partition a Table

Partitioned Indexes

Local Partitioned Indexes

Global Partitioned Indexes

Global Nonpartitioned Indexes

Index Examples

Miscellaneous Information about Creating Indexes on Partitioned Tables

Using Partitioned Indexes in OLTP Applications

Using Partitioned Indexes in DSS Applications

Partitioned Indexes on Composite Partitions

Partitioning to Improve Performance

Partition Pruning

Partition-wise Joins

Parallel DML

Introduction to Oracle Datatypes

Character Datatypes

CHAR Datatype

VARCHAR2 and VARCHAR Datatypes

Length Semantics for Character Datatypes

NCHAR and NVARCHAR2 Datatypes

Use of Unicode Data in an Oracle Database

LOB Character Datatypes

LONG Datatype

NUMBER Datatype

Internal Numeric Format

DATE Datatype

Use of Julian Dates

Date Arithmetic

Centuries and the Year 2000

Daylight Savings Support

Time Zones

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

XML Datatypes

XMLType

XMLIndex

Data Conversion

Introduction to User-Defined Datatypes

Complex Data Models

Multimedia Datatypes

User-Defined Datatype Categories

Object Types

Collection Types

Type Inheritance

FINAL and NOT FINAL Types

NOT INSTANTIABLE Types and Methods

User-Defined Aggregate Functions

Why Have User-Defined Aggregate Functions?

Creation and Use of UDAGs

How Do Aggregate Functions Work?

Application Interfaces

Datatype Evolution

Introduction to Object Views

Advantages of Object Views

How Object Views Are Defined

Use of Object Views

Updates of Object Views

Updates of Nested Table Columns in Views

View Hierarchies

Introduction to Structured Query Language

SQL Statements Overview

Identification of Nonstandard SQL

Overview of SQL Statement Execution

DML Statement Processing

DDL Statement Processing

Control of Transactions

The Optimizer

Execution Plans

PL/SQL Overview

How PL/SQL Executes

Language Constructs for PL/SQL

Stored Procedures

PL/SQL Server Pages

PL/SQL Program Units

Introduction to Stored Procedures and Packages

Stored Procedures and Functions

Packages

Procedures and Functions Overview

Benefits of Procedures

Procedure Guidelines

Anonymous PL/SQL Blocks Compared with Stored Procedures

Standalone Procedures

Dependency Tracking for Stored Procedures

External Procedures

Table Functions

Packages Overview

Java Overview

Java and Object-Oriented Programming Terminology

Class Hierarchy

Interfaces

The Java Virtual Machine (JVM)

Java Program Units

Package DBMS_JAVA

Introduction to Transactions

Statement Execution and Transaction Control

Statement-Level Rollback

Resumable Space Allocation

Oracle and Transaction Management

Commit Transactions

Rollback of Transactions

Savepoints In Transactions

Transaction Naming

The Two-Phase Commit Mechanism

Discrete Transaction Management

Autonomous Transactions

Autonomous PL/SQL Blocks

Transaction Control Statements in Autonomous Blocks

Introduction to Triggers

How Triggers Are Used

Parts of a Trigger

The Triggering Event or Statement

Trigger Restriction

Trigger Action

Types of Triggers

Row Triggers and Statement Triggers

BEFORE and AFTER Triggers

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

Introduction to Dependency Issues

Resolution of Schema Object Dependencies

Compilation of 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

Management of Local Dependencies

Management of Remote Dependencies

Introduction to Parallel Execution

When to Implement Parallel Execution

When Not to Implement Parallel Execution

How Parallel Execution Works

Parallelized SQL Statements

Degree of Parallelism

Parallel Query Intra- and Inter-Operation Example

SQL Operations That Can Be Parallelized

How to Make a Statement Run in Parallel

Introduction to Direct-Path INSERT

Advantages of Direct-Path INSERT

Serial and Parallel Direct-Path INSERT

Direct-Path INSERT Into Partitioned and Nonpartitioned Tables

Serial Direct-Path Insert into Partitioned and Nonpartitioned Tables

Parallel Direct-Path INSERT into Partitioned Tables

Parallel Direct-Path INSERT into a Nonpartitioned Table

Direct-Path INSERT and Logging Mode

Direct-Path INSERT with Logging

Direct-Path INSERT without Logging

Additional Considerations for Direct-Path INSERT

Index Maintenance

Space Considerations

Locking Considerations

Introduction to Data Concurrency and Consistency in a Multiuser Environment

Preventable Phenomena and Transaction Isolation Levels

Overview of Locking Mechanisms

How Oracle Manages Data Concurrency and Consistency

Multiversion Concurrency Control

Statement-Level Read Consistency

Transaction-Level Read Consistency

Read Consistency with Oracle9i Real Application Clusters

Oracle Isolation Levels

Comparison of Read Committed and Serializable Isolation

Choice of Isolation Level

How Oracle Locks Data

Transactions and Data Concurrency

Latches and Internal Locks

Explicit (Manual) Data Locking

Oracle Lock Management Services

Flashback Query

Flashback Query Benefits

Some Uses of Flashback Query

Introduction to Data Integrity

Types of Data Integrity

How Oracle Enforces Data Integrity

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

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

Constraint State Modification

Introduction to Database Security

Schemas, Database Users, and Security Domains

User Authentication

Authentication by the Operating System

Authentication by the Network

Authentication by the Oracle Database

Multitier Authentication and Authorization

Authentication by the Secure Socket Layer Protocol

Authentication of Database Administrators

User Tablespace Settings and Quotas

Default Tablespace Option

Temporary Tablespace Option

Tablespace Access and Quotas

The User Group PUBLIC

User Resource Limits and Profiles

Types of System Resources and Limits

Profiles

Overview of Licensing

Concurrent Usage Licensing

Named User Licensing

Introduction to Privileges

System Privileges

Schema Object Privileges

Introduction to Roles

Common Uses for Roles

The Mechanisms of Roles

Grant and Revoke Roles

Who Can Grant or Revoke Roles?

Role Names

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

Secure Application Roles

Creation of Secure Application Roles

Introduction to Auditing

Features of Auditing

Mechanisms for Auditing

Statement Auditing

Privilege Auditing

Schema Object Auditing

Schema Object Audit Options for Views and Procedures

Fine-Grained Auditing

Focus Statement, Privilege, and Schema Object Auditing

Successful and Unsuccessful Statement Executions Auditing

BY SESSION compared with BY ACCESS Clauses of Audit Statement

Audit By User

Audit in a Multitier Environment

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Part I What Is Oracle?

1 Introduction to the Oracle Server

2 Oracle Server Features

Part II Database Structures

3 Data Blocks, Extents, and Segments

4 Tablespaces, Datafiles, and Control Files

5 The Data Dictionary

Part III The Oracle Instance

6 Database and Instance Startup and Shutdown

7 Distributed Processing

8 Memory Architecture

9 Process Architecture

10 Database Resource Management

Part IV The Object-Relational DBMS

11 Schema Objects

12 Partitioned Tables and Indexes

13 System-Provided Datatypes

14 User-Defined Datatypes

15 Object Views

Part V Data Access

16 SQL, PL/SQL, and Java

17 Transaction Management

18 Triggers

19 Dependencies Among Schema Objects

Part VI Parallel SQL and Direct-Load INSERT

20 Parallel Execution of SQL Statements

21 Direct-Path INSERT

Part VII Data Protection

22 Data Concurrency and Consistency

23 Data Integrity

24 Controlling Database Access

25 Privileges, Roles, and Security Policies

26 Auditing

A Operating System Specific Information

Glossary

Index