Oracle8
i
Concepts
Release 2 (8.1.6)
Part Number A76965-01
Library
Product
Index
Contents
Title and Copyright Information
Send Us Your Comments
Preface
Part I What Is Oracle?
1 Introduction to the Oracle Server
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
An Example of How Oracle Works
The Object-Relational Model for Database Management
The Relational Model
The Object-Relational Model
Schemas and Schema Objects
The Data Dictionary
Data Concurrency and Consistency
Concurrency
Read Consistency
Locking Mechanisms
Distributed Processing and Distributed Databases
Client-Server Architecture: Distributed Processing
Multi-Tier Architecture: Application Servers
Distributed Databases
Startup and Shutdown Operations
Database Security
Security Mechanisms
Privileges
Database Backup and Recovery
Why Is Recovery Important?
Types of Failures
Structures Used for Recovery
Basic Recovery Steps
Recovery Manager
Data Access
SQL--The Structured Query Language
Transactions
PL/SQL
Data Integrity
Part II Database Structures
2 The Data Dictionary
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
3 Tablespaces and Datafiles
Introduction to Databases, Tablespaces, and Datafiles
Allocating More Space for a Database
Tablespaces
The SYSTEM Tablespace
Using Multiple Tablespaces
Space Management in Tablespaces
Online and Offline Tablespaces
Read-Only Tablespaces
Temporary Tablespaces
Transporting Tablespaces between Databases
Datafiles
Datafile Contents
Size of Datafiles
Offline Datafiles
Temporary Datafiles
4 Data Blocks, Extents, and Segments
Introduction to Data Blocks, Extents, and Segments
Data Blocks
Data Block Format
An Introduction to PCTFREE, PCTUSED, and Row Chaining
Extents
When Extents Are Allocated
Determining the Number and Size of Extents
How Extents Are Allocated
When Extents Are Deallocated
Segments
Data Segments
Index Segments
Temporary Segments
Rollback Segments
Part III The Oracle Instance
5 Database and Instance
Startup and Shutdown
Introduction to 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 Distributed Processing
Introduction to Oracle Client/Server Architecture
Distributed Processing
Net8
How Net8 Works
The Network Listener
Multi-Tier Architecture
Clients
Application Servers
Database Servers
7 Memory Architecture
Introduction to Oracle Memory Structures
System Global Area (SGA)
The Database Buffer Cache
The Redo Log Buffer
The Shared Pool
The Large 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
Virtual Memory
Software Code Areas
8 Process Architecture
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
Pre-Spawned Dedicated Processes
The Program Interface
Program Interface Structure
The Program Interface Drivers
Operating System Communications Software
9 Database Resource Management
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 Allocation for Resource Plans: Emphasis Method
Parallel Degree Limit for Resource Plans: Absolute Method
Resource Plan Directives
Examples
Using Resource Consumer Groups and Resource Plans
Using Subplans
Using Multi-Level Resource Plans
Using the Parallel Degree Limit Resource Plan Directive
Summary
Using the Database Resource Manager
Part IV The Object-Relational DBMS
10 Schema Objects
Introduction to Schema Objects
Tables
How Table Data Is Stored
Nulls
Default Values for Columns
Nested Tables
Temporary Tables
Views
Storage for Views
How Views Are Used
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
11 Partitioned Tables and Indexes
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
12 Built-In Datatypes
Introduction to 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
13 User-Defined Datatypes
Introduction to User-Defined Datatypes
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
14 Object Views
Introduction to Object Views
Advantages of Object Views
Defining Object Views
Using Object Views
Updating Object Views
Updating Nested Table Columns in Views
Part V Data Access
15 SQL and PL/SQL
Introduction to Structured Query Language
SQL Statements
Identifying Nonstandard SQL
Recursive SQL
Cursors
Shared SQL
Parsing
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
PL/SQL Server Pages
16 Transaction Management
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
17 Procedures and Packages
Introduction to Stored Procedures and Packages
Stored Procedures and Functions
Packages
Procedures and Functions
Definer Rights and Invoker Rights
Benefits of Procedures
Procedure Guidelines
Anonymous PL/SQL Blocks versus Stored Procedures
Standalone Procedures
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
18 Advanced Queuing
Introduction to Message Queuing
Oracle Advanced Queuing
Queuing Models
Queuing Entities
Features of Advanced Queuing
19 Triggers
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
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
20 Oracle Dependency Management
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
Part VI Optimization of SQL Statements
21 The Optimizer
Introduction to 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-Based Optimization
Part VII Parallel SQL and Direct-Load INSERT
22 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
23 Parallel Execution of SQL Statements
Introduction to Parallel Execution of SQL Statements
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
Part VIII Data Protection
24 Data Concurrency and Consistency
Introduction to 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
Comparing Read Committed and Serializable Isolation
Choosing an Isolation Level
How Oracle Locks Data
Transactions and Data Concurrency
Deadlocks
Types of Locks
DML Locks
DDL Locks
Latches and Internal Locks
Explicit (Manual) Data Locking
Oracle Lock Management Services
25 Data Integrity
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
Modifying Constraint States
26 Controlling Database Access
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
Multi-Tier Authentication and Authorization
Authentication by the Secure Socket Layer Protocol
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
27 Privileges, Roles, and Security Policies
Introduction to 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
28 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
29 Database Recovery
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
Improving Recovery Performance
Performing Recovery in Parallel
Fast-Start Fault 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
Managed Standby Database
Part IX Distributed Databases and Replication
30 Distributed Database Concepts
Introduction to Distributed Database Architecture
Homogenous Distributed Database Systems
Heterogeneous Distributed Database Systems
Client-Server Database Architecture
Database Links
What Are Database Links?
Why Use Database Links?
Global Database Names in Database Links
Names for Database Links
Types of Database Links
Users of Database Links
Creation of Database Links: Examples
Schema Objects and Database Links
Database Link Restrictions
Distributed Database Administration
Site Autonomy
Distributed Database Security
Auditing Database Links
Administration Tools
Transaction Processing in a Distributed System
Remote SQL Statements
Distributed SQL Statements
Shared SQL for Remote and Distributed Statements
Remote Transactions
Distributed Transactions
Two-Phase Commit Mechanism
Database Link Resolution
Schema Object Name Resolution
Global Name Resolution in Views, Synonyms, and Procedures
Distributed Database Application Development
Transparency in a Distributed Database System
Remote Procedure Calls
Distributed Query Optimization
National Language Support
Client-Server Environment
Homogeneous Distributed Environment
Heterogeneous Distributed Environment
31 Replication
Introduction to Replication
Applications That Use Replication
Replication Objects, Groups, and Sites
Types of Replication Environments
Multimaster Replication
Snapshot Replication
Multimaster and Snapshot Hybrid Configurations
Administration Tools for a Replication Environment
Oracle Replication Manager
Replication Management API
Replication Catalog
Distributed Schema Management
Replication Conflicts
Other Options for Multimaster Replication
Synchronous Replication
Procedural Replication
Part X Appendix
A Operating System-Specific Information
Index
Copyright © 1996-2000, Oracle Corporation.
All Rights Reserved.
Library
Product
Index