Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documentation
• Conventions

1 Introduction to Oracle Database

• About Relational Databases
  • Database Management System (DBMS)
  • Relational Model
  • Relational Database Management System (RDBMS)
  • Brief History of Oracle Database
• Schema Objects
  • Tables
  • Indexes
• Data Access
  • Structured Query Language (SQL)
  • PL/SQL and Java
• Transaction Management
  • Transactions
  • Data Concurrency
  • Data Consistency
• Oracle Database Architecture
  • Database and Instance
  • Database Storage Structures
  • Database Instance Structures
  • Application and Networking Architecture
• Oracle Database Documentation Roadmap
  • Basic Group
  • Intermediate Group
  • Advanced Group

Part I Oracle Relational Data Structures

2 Tables and Table Clusters

• Introduction to Schema Objects
  • Schema Object Types
  • Schema Object Storage
  • Schema Object Dependencies
  • SYS and SYSTEM Schemas
  • Sample Schemas
• Overview of Tables
  • Columns and Rows
  • Example: CREATE TABLE and ALTER TABLE Statements
  • Oracle Data Types
  • Integrity Constraints
  • Object Tables
  • Temporary Tables
  • External Tables
  • Table Storage
  • Table Compression
• Overview of Table Clusters
  • Overview of Indexed Clusters
  • Overview of Hash Clusters

3 Indexes and Index-Organized Tables

• Overview of Indexes
  • Index Characteristics
  • B-Tree Indexes
  • Bitmap Indexes
  • Function-Based Indexes
  • Application Domain Indexes
  • Index Storage
• Overview of Index-Organized Tables
  • Index-Organized Table Characteristics
  • Index-Organized Tables with Row Overflow Area
  • Secondary Indexes on Index-Organized Tables

4 Partitions, Views, and Other Schema Objects

• Overview of Partitions
  • Partition Characteristics
  • Partitioned Tables
  • Partitioned Indexes
  • Partitioned Index-Organized Tables
• Overview of Views
  • Characteristics of Views
  • Updatable Join Views
  • Object Views
• Overview of Materialized Views
  • Characteristics of Materialized Views
  • Refresh Methods for Materialized Views
  • Query Rewrite
• Overview of Sequences
  • Sequence Characteristics
  • Concurrent Access to Sequences
• Overview of Dimensions
  • Hierarchical Structure of a Dimension
  • Creation of Dimensions
• Overview of Synonyms

5 Data Integrity

• Introduction to Data Integrity
  • Techniques for Guaranteeing Data Integrity
  • Advantages of Integrity Constraints
• Types of Integrity Constraints
  • NOT NULL Integrity Constraints
  • Unique Constraints
  • Primary Key Constraints
  • Foreign Key Constraints
  • Check Constraints
• States of Integrity Constraints
  • Checks for Modified and Existing Data
  • Deferrable Constraints
  • Examples of Constraint Checking

6 Data Dictionary and Dynamic Performance Views

• Overview of the Data Dictionary
  • Contents of the Data Dictionary
  • Storage of the Data Dictionary
  • How Oracle Database Uses the Data Dictionary
• Overview of the Dynamic Performance Views
  • Contents of the Dynamic Performance Views
  • Storage of the Dynamic Performance Views
• Database Object Metadata

Part II Oracle Data Access

7 SQL

• Introduction to SQL
  • SQL Data Access
  • SQL Standards
• Overview of SQL Statements
  • Data Definition Language (DDL) Statements
  • Data Manipulation Language (DML) Statements
  • Transaction Control Statements
  • Session Control Statements
  • System Control Statement
  • Embedded SQL Statements
• Overview of the Optimizer
  • Use of the Optimizer
  • Optimizer Components
  • Access Paths
  • Optimizer Statistics
  • Optimizer Hints
• Overview of SQL Processing
  • Stages of SQL Processing
  • How Oracle Database Processes DML
  • How Oracle Database Processes DDL

8 Server-Side Programming: PL/SQL and Java

• Introduction to Server-Side Programming
• Overview of PL/SQL
  • PL/SQL Subprograms
  • PL/SQL Packages
  • PL/SQL Anonymous Blocks
  • PL/SQL Language Constructs
  • PL/SQL Collections and Records
  • How PL/SQL Runs
• Overview of Java in Oracle Database
  • Overview of the Java Virtual Machine (JVM)
  • Java Programming Environment
• Overview of Triggers
  • Advantages of Triggers
  • Types of Triggers
  • Timing for Triggers
  • Creation of Triggers
  • Execution of Triggers
  • Storage of Triggers

Part III Oracle Transaction Management

9 Data Concurrency and Consistency

• Introduction to Data Concurrency and Consistency
  • Multiversion Read Consistency
  • Locking Mechanisms
  • ANSI/ISO Transaction Isolation Levels
• Overview of Oracle Database Transaction Isolation Levels
  • Read Committed Isolation Level
  • Serializable Isolation Level
  • Read-Only Isolation Level
• Overview of the Oracle Database Locking Mechanism
  • Summary of Locking Behavior
  • Use of Locks
  • Lock Modes
  • Lock Conversion and Escalation
  • Lock Duration
  • Locks and Deadlocks
• Overview of Automatic Locks
  • DML Locks
  • DDL Locks
  • System Locks
• Overview of Manual Data Locks
• Overview of User-Defined Locks

10 Transactions

• Introduction to Transactions
  • Sample Transaction: Account Debit and Credit
  • Structure of a Transaction
  • Statement-Level Atomicity
  • System Change Numbers (SCNs)
• Overview of Transaction Control
  • Transaction Names
  • Active Transactions
  • Savepoints
  • Rollback of Transactions
  • Committing Transactions
• Overview of Autonomous Transactions
• Overview of Distributed Transactions
  • Two-Phase Commit
  • In-Doubt Transactions

Part IV Oracle Database Storage Structures

11 Physical Storage Structures

• Introduction to Physical Storage Structures
  • Mechanisms for Storing Database Files
  • Oracle Automatic Storage Management (Oracle ASM)
  • Oracle Managed Files and User-Managed Files
• Overview of Data Files
  • Use of Data Files
  • Permanent and Temporary Data Files
  • Online and Offline Data Files
  • Data File Structure
• Overview of Control Files
  • Use of Control Files
  • Multiple Control Files
  • Control File Structure
• Overview of the Online Redo Log
  • Use of the Online Redo Log
  • How Oracle Database Writes to the Online Redo Log
  • Structure of the Online Redo Log

12 Logical Storage Structures

• Introduction to Logical Storage Structures
  • Logical Storage Hierarchy
  • Logical Space Management
• Overview of Data Blocks
  • Data Blocks and Operating System Blocks
  • Data Block Format
  • Data Block Compression
  • Space Management in Data Blocks
• Overview of Extents
  • Allocation of Extents
  • Deallocation of Extents
  • Storage Parameters for Extents
• Overview of Segments
  • User Segments
  • Temporary Segments
  • Undo Segments
  • Segment Space and the High Water Mark
• Overview of Tablespaces
  • Permanent Tablespaces
  • Temporary Tablespaces
  • Tablespace Modes
  • Tablespace File Size

Part V Oracle Instance Architecture

13 Oracle Database Instance

• Introduction to the Oracle Database Instance
  • Database Instance Structure
  • Database Instance Configurations
• Overview of Instance Startup and Shutdown
  • Overview of Instance and Database Startup
  • Overview of Database and Instance Shutdown
• Overview of Checkpoints
  • Purpose of Checkpoints
  • When Oracle Database Initiates Checkpoints
• Overview of Instance Recovery
  • Purpose of Instance Recovery
  • When Oracle Database Performs Instance Recovery
  • Importance of Checkpoints for Instance Recovery
  • Instance Recovery Phases
• Overview of Parameter Files
  • Initialization Parameters
  • Server Parameter Files
  • Text Initialization Parameter Files
  • Modification of Initialization Parameter Values
• Overview of Diagnostic Files
  • Automatic Diagnostic Repository
  • Alert Log
  • Trace Files

14 Memory Architecture

• Introduction to Oracle Database Memory Structures
  • Basic Memory Structures
  • Oracle Database Memory Management
• Overview of the User Global Area
• Overview of the Program Global Area
  • Contents of the PGA
  • PGA Usage in Dedicated and Shared Server Modes
• Overview of the System Global Area
  • Database Buffer Cache
  • Redo Log Buffer
  • Shared Pool
  • Large Pool
  • Java Pool
  • Streams Pool
  • Fixed SGA
• Overview of Software Code Areas

15 Process Architecture

• Introduction to Processes
  • Multiple-Process Oracle Database Systems
  • Types of Processes
• Overview of Client Processes
  • Client and Server Processes
  • Connections and Sessions
• Overview of Server Processes
  • Dedicated Server Processes
  • Shared Server Processes
• Overview of Background Processes
  • Mandatory Background Processes
  • Optional Background Processes
  • Slave Processes

16 Application and Networking Architecture

• Overview of Oracle Application Architecture
  • Overview of Client/Server Architecture
  • Overview of Multitier Architecture
  • Overview of Grid Architecture
• Overview of Oracle Networking Architecture
  • How Oracle Net Services Works
  • The Oracle Net Listener
  • Dedicated Server Architecture
  • Shared Server Architecture
  • Database Resident Connection Pooling
• Overview of the Program Interface
  • Program Interface Structure
  • Program Interface Drivers
  • Communications Software for the Operating System

Part VI Oracle Database Administration and Development

17 Topics for Database Administrators and Developers

• Overview of Database Security
  • User Accounts
  • Authentication
  • Encryption
  • Access Control
  • Monitoring
• Overview of High Availability
  • High Availability and Unplanned Downtime
  • High Availability and Planned Downtime
• Overview of Grid Computing
  • Database Server Grid
  • Database Storage Grid
• Overview of Data Warehousing and Business Intelligence
  • Data Warehousing and OLTP
  • Data Warehouse Architecture
  • Overview of Extraction, Transformation, and Loading (ETL)
  • Business Intelligence
• Overview of Oracle Information Integration
  • Federated Access
  • Information Sharing

18 Concepts for Database Administrators

• Duties of Database Administrators
• Tools for Database Administrators
  • Oracle Enterprise Manager
  • SQL*Plus
  • Tools for Database Installation and Configuration
  • Tools for Oracle Net Configuration and Administration
  • Tools for Data Movement and Analysis
• Topics for Database Administrators
  • Backup and Recovery
  • Memory Management
  • Resource Management and Task Scheduling
  • Performance Diagnostics and Tuning

19 Concepts for Database Developers

• Duties of Database Developers
• Tools for Database Developers
  • SQL Developer
  • Oracle Application Express
  • Oracle JDeveloper
  • Oracle JPublisher
  • Oracle Developer Tools for Visual Studio .NET
• Topics for Database Developers
  • Principles of Application Design and Tuning
  • Client-Side Database Programming
  • Globalization Support
  • Unstructured Data

Glossary

Index