Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documentation
• Conventions

Part I What Is Oracle?

1 Introduction to the Oracle Database

• Oracle Database Architecture
• • Overview of Oracle Grid Architecture
  • • Grid Computing Defined
    • Grid Computing in Oracle Database 10g
  • Overview of Application Architecture
  • • Client/Server Architecture
    • Multitier Architecture: Application Servers
  • Overview of Physical Database Structures
  • • Datafiles
    • Control Files
    • Redo Log Files
    • Archive Log Files
    • Parameter Files
    • Alert and Trace Log Files
    • Backup Files
  • Overview of Logical Database Structures
  • • Tablespaces
    • Oracle Data Blocks
    • Extents
    • Segments
  • Overview of Schemas and Common Schema Objects
  • • Tables
    • Indexes
    • Views
    • Clusters
    • Synonyms
  • Overview of the Oracle Data Dictionary
  • Overview of the Oracle Instance
  • • Real Application Clusters: Multiple Instance Systems
    • Instance Memory Structures
    • System Global Area
    • Program Global Area
    • Oracle Background Processes
    • Process Architecture
  • Overview of Accessing the Database
  • • Network Connections
    • Starting Up the Database
    • How Oracle Works
  • Overview of Oracle Utilities
• Oracle Database Features
• • Overview of Scalability and Performance Features
  • • Concurrency
    • Read Consistency
    • Locking Mechanisms
    • Quiesce Database
    • Real Application Clusters
    • Portability
  • Overview of Manageability Features
  • • Self-Managing Database
    • Oracle Enterprise Manager
    • SQL*Plus
    • Automatic Storage Management
    • The Scheduler
    • Database Resource Manager
  • Overview of Database Backup and Recovery Features
  • • Types of Failures
    • Structures Used for Recovery
  • Overview of High Availability Features
  • Overview of Business Intelligence Features
  • • Data Warehousing
    • Extraction, Transformation, and Loading (ETL)
    • Materialized Views
    • Bitmap Indexes in Data Warehousing
    • Table Compression
    • Parallel Execution
    • Analytic SQL
    • OLAP Capabilities
    • Data Mining
    • Partitioning
  • Overview of Content Management Features
  • • XML in Oracle
    • LOBs
    • Oracle Text
    • Oracle Ultra Search
    • Oracle interMedia
    • Oracle Spatial
  • Overview of Security Features
  • • Security Mechanisms
  • Overview of Data Integrity and Triggers
  • • Integrity Constraints
    • Keys
    • Triggers
  • Overview of Information Integration Features
  • • Distributed SQL
    • Oracle Streams
    • Oracle Transparent Gateways and Generic Connectivity
• Oracle Database Application Development
• • Overview of Oracle SQL
  • • SQL Statements
  • Overview of PL/SQL
  • • PL/SQL Program Units
  • Overview of Java
  • Overview of Application Programming Languages (APIs)
  • Overview of Transactions
  • • Commit and Undo Transactions
    • Savepoints
  • Overview of Datatypes
  • Overview of Globalization

Part II Oracle Database Architecture

2 Data Blocks, Extents, and Segments

• Introduction to Data Blocks, Extents, and Segments
• Overview of Data Blocks
• • Data Block Format
  • • Header (Common and Variable)
    • Table Directory
    • Row Directory
    • Overhead
    • Row Data
    • Free Space
  • Free Space Management
  • • Availability and Optimization of Free Space in a Data Block
    • Row Chaining and Migrating
  • PCTFREE, PCTUSED, and Row Chaining
  • • The PCTFREE Parameter
    • The PCTUSED Parameter
• Overview of Extents
• • When Extents Are Allocated
  • Determine the Number and Size of Extents
  • How Extents Are Allocated
  • When Extents Are Deallocated
  • • Extents in Nonclustered Tables
    • Extents in Clustered Tables
    • Extents in Materialized Views and Their Logs
    • Extents in Indexes
    • Extents in Temporary Segments
    • Extents in Rollback Segments
• Overview of Segments
• • Introduction to Data Segments
  • Introduction to Index Segments
  • Introduction to Temporary Segments
  • • Operations that Require Temporary Segments
    • Segments in Temporary Tables and Their Indexes
    • How Temporary Segments Are Allocated
  • Introduction to Automatic Undo Management
  • • Undo Mode
    • Undo Quota
    • Automatic Undo Retention
    • External Views

3 Tablespaces, Datafiles, and Control Files

• Introduction to Tablespaces, Datafiles, and Control Files
• • Oracle-Managed Files
  • Allocate More Space for a Database
• Overview of Tablespaces
• • Bigfile Tablespaces
  • • Benefits of Bigfile Tablespaces
    • Considerations with Bigfile Tablespaces
  • The SYSTEM Tablespace
  • • The Data Dictionary
    • PL/SQL Program Units Description
  • The SYSAUX Tablespace
  • Undo Tablespaces
  • • Creation of Undo Tablespaces
    • Assignment of Undo Tablespaces
  • Default Temporary Tablespace
  • • How to Specify a Default Temporary Tablespace
  • Using Multiple Tablespaces
  • Managing Space in Tablespaces
  • • Locally Managed Tablespaces
    • Segment Space Management in Locally Managed Tablespaces
    • Dictionary Managed Tablespaces
  • Multiple Block Sizes
  • Online and Offline Tablespaces
  • • Bringing Tablespaces Offline
    • Use of Tablespaces for Special Procedures
  • Read-Only Tablespaces
  • Temporary Tablespaces for Sort Operations
  • • Sort Segments
    • Creation of Temporary Tablespaces
  • Transport of Tablespaces Between Databases
  • • Tablespace Repository
    • How to Move or Copy a Tablespace to Another Database
• Overview of Datafiles
• • Datafile Contents
  • Size of Datafiles
  • Offline Datafiles
  • Temporary Datafiles
• Overview of Control Files
• • Control File Contents
  • Multiplexed Control Files

4 Transaction Management

• Introduction to Transactions
• • Statement Execution and Transaction Control
  • Statement-Level Rollback
  • Resumable Space Allocation
• Overview of Transaction Management
• • Commit Transactions
  • Rollback of Transactions
  • Savepoints In Transactions
  • Transaction Naming
  • • How Transactions Are Named
    • Commit Comment
  • The Two-Phase Commit Mechanism
• Overview of Autonomous Transactions
• • Autonomous PL/SQL Blocks
  • • Transaction Control Statements in Autonomous Blocks

5 Schema Objects

• Introduction to Schema Objects
• Overview of Tables
• • How Table Data Is Stored
  • • Row Format and Size
    • Rowids of Row Pieces
    • Column Order
  • Table Compression
  • • Using Table Compression
  • Nulls Indicate Absence of Value
  • Default Values for Columns
  • • Default Value Insertion and Integrity Constraint Checking
  • Partitioned Tables
  • Nested Tables
  • Temporary Tables
  • • Segment Allocation
    • Parent and Child Transactions
  • External Tables
  • • The Access Driver
    • Data Loading with External Tables
    • Parallel Access to External Tables
• Overview of Views
• • How Views are Stored
  • How Views Are Used
  • Mechanics of Views
  • • Globalization Support Parameters in Views
    • Use of Indexes Against Views
  • Dependencies and Views
  • Updatable Join Views
  • Object Views
  • Inline Views
• Overview of Materialized Views
• • Define Constraints on Views
  • Refresh Materialized Views
  • Materialized View Logs
• Overview of Dimensions
• Overview of the Sequence Generator
• Overview of Synonyms
• Overview of Indexes
• • Unique and Nonunique Indexes
  • Composite Indexes
  • Indexes and Keys
  • Indexes and Nulls
  • Function-Based Indexes
  • • Uses of Function-Based Indexes
    • Optimization with Function-Based Indexes
    • Dependencies of Function-Based Indexes
  • How Indexes Are Stored
  • • Format of Index Blocks
    • The Internal Structure of Indexes
    • Index Properties
    • Advantages of B-tree Structure
  • Index Unique Scan
  • Index Range Scan
  • Key Compression
  • • Prefix and Suffix Entries
    • Performance and Storage Considerations
    • Uses of Key Compression
  • Reverse Key Indexes
  • Bitmap Indexes
  • • Benefits for Data Warehousing Applications
    • Cardinality
    • Bitmap Index Example
    • Bitmap Indexes and Nulls
    • Bitmap Indexes on Partitioned Tables
  • Bitmap Join Indexes
• Overview of 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
  • • Mapping Table
  • Partitioned Index-Organized Tables
  • B-tree Indexes on UROWID Columns for Heap- and Index-Organized Tables
  • Index-Organized Table Applications
• Overview of Application Domain Indexes
• Overview of Clusters
• Overview of Hash Clusters

6 Dependencies Among Schema Objects

• Introduction to Dependency Issues
• Resolution of Schema Object Dependencies
• • Compilation of Views and PL/SQL Program Units
  • • Views and Base Tables
    • Program Units and Referenced Objects
    • Data Warehousing Considerations
    • Session State and Referenced Packages
    • Security Authorizations
• Object Name Resolution
• Shared SQL Dependency Management
• Local and Remote Dependency Management
• • Management of Local Dependencies
  • Management of Remote Dependencies
  • • Dependencies Among Local and Remote Database Procedures
    • Dependencies Among Other Remote Schema Objects
    • Dependencies of Applications

7 The Data Dictionary

• Introduction to the Data Dictionary
• • Structure of the Data Dictionary
  • • Base Tables
    • User-Accessible Views
  • SYS, Owner of the Data Dictionary
• How the Data Dictionary Is Used
• • How Oracle Uses the Data Dictionary
  • • Public Synonyms for Data Dictionary Views
    • Cache the Data Dictionary for Fast Access
    • Other Programs and the Data Dictionary
  • How to Use the Data Dictionary
  • • Views with the Prefix USER
    • Views with the Prefix ALL
    • Views with the Prefix DBA
    • The DUAL Table
• Dynamic Performance Tables
• Database Object Metadata

8 Memory Architecture

• Introduction to Oracle Memory Structures
• Overview of the System Global Area
• • The SGA_MAX_SIZE Initialization Parameter
  • Automatic Shared Memory Management
  • • The SGA_TARGET Initialization Parameter
    • Automatically Managed SGA Components
    • Manually Managed SGA Components
    • Persistence of Automatically Tuned Values
  • Adding Granules and Tracking Component Size
  • Database Buffer Cache
  • • Organization of the Database Buffer Cache
    • The LRU Algorithm and Full Table Scans
    • Size of the Database Buffer Cache
    • Multiple Buffer Pools
  • Redo Log Buffer
  • Shared Pool
  • • Library Cache
    • Shared SQL Areas and Private SQL Areas
    • PL/SQL Program Units and the Shared Pool
    • Dictionary Cache
    • Allocation and Reuse of Memory in the Shared Pool
  • Large Pool
  • Java Pool
  • Streams Pool
  • Control of the SGA's Use of Memory
  • Other SGA Initialization Parameters
  • • Physical Memory
    • SGA Starting Address
    • Extended Buffer Cache Mechanism
• Overview of the Program Global Areas
• • Content of the PGA
  • • Private SQL Area
    • Session Memory
  • SQL Work Areas
  • PGA Memory Management for Dedicated Mode
• Dedicated and Shared Servers
• Software Code Areas

9 Process Architecture

• Introduction to Processes
• • Multiple-Process Oracle Systems
  • Types of Processes
• Overview of User Processes
• • Connections and Sessions
• Overview of Oracle Processes
• • Server Processes
  • Background Processes
  • • Database Writer Process (DBWn)
    • Log Writer Process (LGWR)
    • Checkpoint Process (CKPT)
    • System Monitor Process (SMON)
    • Process Monitor Process (PMON)
    • Recoverer Process (RECO)
    • Job Queue Processes
    • Archiver Processes (ARCn)
    • Queue Monitor Processes (QMNn)
    • Other Background Processes
  • Trace Files and the Alert Log
• Shared Server Architecture
• • Dispatcher Request and Response Queues
  • • Dispatcher Processes (Dnnn)
    • 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

10 Application Architecture

• Introduction to Client/Server Architecture
• Overview of Multitier Architecture
• • Clients
  • Application Servers
  • Database Servers
• Overview of Oracle Net Services
• • How Oracle Net Services Works
  • The Listener
  • • Service Information Registration

11 Oracle Utilities

• Introduction to Oracle Utilities
• Overview of Data Pump Export and Import
• • Data Pump Export
  • Data Pump Import
• Overview of the Data Pump API
• Overview of the Metadata API
• Overview of SQL*Loader
• Overview of External Tables
• Overview of LogMiner
• Overview of DBVERIFY Utility
• Overview of DBNEWID Utility

12 Database and Instance Startup and Shutdown

• Introduction to an Oracle Instance
• • The Instance and the Database
  • Connection with Administrator Privileges
  • Initialization Parameter Files and Server Parameter Files
  • • How Parameter Values Are Changed
• Overview of Instance and Database Startup
• • How an Instance Is Started
  • • Restricted Mode of Instance Startup
    • Forced Startup in Abnormal Situations
  • How a Database Is Mounted
  • • How a Database Is Mounted with Real Application Clusters
    • How a Standby Database Is Mounted
    • How a Clone Database Is Mounted
  • What Happens When You Open a Database
  • • Instance Recovery
    • Undo Space Acquisition and Management
    • Resolution of In-Doubt Distributed Transaction
    • Open a Database in Read-Only Mode
• Overview of Database and Instance Shutdown
• • Close a Database
  • • Close the Database by Terminating the Instance
  • Unmount a Database
  • Shut Down an Instance
  • • Abnormal Instance Shutdown

Part III Oracle Database Features

13 Data Concurrency and Consistency

• 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 Real Application Clusters
  • Oracle Isolation Levels
  • • Set the Isolation Level
    • Read Committed Isolation
    • Serializable Isolation
  • Comparison of Read Committed and Serializable Isolation
  • • Transaction Set Consistency
    • Row-Level Locking
    • Referential Integrity
    • Distributed Transactions
  • Choice of Isolation Level
  • • Read Committed Isolation
    • Serializable Isolation
    • Quiesce Database
• How Oracle Locks Data
• • Transactions and Data Concurrency
  • • Modes of Locking
    • Lock Duration
    • Data Lock Conversion Versus Lock Escalation
  • Deadlocks
  • • Deadlock Detection
    • Avoid Deadlocks
  • Types of Locks
  • DML Locks
  • • Row Locks (TX)
    • Table Locks (TM)
    • DML Locks Automatically Acquired for DML Statements
  • DDL Locks
  • • Exclusive DDL Locks
    • Share DDL Locks
    • Breakable Parse Locks
    • Duration of DDL Locks
    • DDL Locks and Clusters
  • Latches and Internal Locks
  • • Latches
    • Internal Locks
  • Explicit (Manual) Data Locking
  • Oracle Lock Management Services
• Overview of Oracle Flashback Query
• • Flashback Query Benefits
  • Some Uses of Flashback Query

14 Manageability

• Installing Oracle and Getting Started
• • Simplified Database Creation
  • Instant Client
  • Automated Upgrades
  • Basic Initialization Parameters
  • Data Loading, Transfer, and Archiving
• Intelligent Infrastructure
• • Automatic Workload Repository
  • Automatic Maintenance Tasks
  • Server-Generated Alerts
  • Advisor Framework
• Performance Diagnostic and Troubleshooting
• Application and SQL Tuning
• Memory Management
• Space Management
• • Automatic Undo Management
  • Oracle-Managed Files
  • Free Space Management
  • Proactive Space Management
  • Intelligent Capacity Planning
  • Space Reclamation
• Storage Management
• Backup and Recovery
• • Recovery Manager
  • Mean Time to Recovery
  • Self Service Error Correction
• Configuration Management
• Workload Management
• • Overview of the Database Resource Manager
  • • Database Resource Manager Concepts
  • Overview of Services
  • • Workload Management with Services
    • High Availability with Services
• Automatic Storage Management
• • Basic Automatic Storage Management Concepts
  • • Disk Groups
    • Automatic Storage Management Files
    • Automatic Storage Management Templates
    • Automatic Storage Management Disks
    • Failure Groups
    • Automatic Storage Management Instances
  • Benefits of Using Automatic Storage Management
• Oracle Scheduler
• • What Can the Scheduler Do?
  • • Schedule Job Execution
    • Time-based scheduling
    • Event-Based Scheduling
    • Define Multi-Step Jobs
    • Schedule Job Processes that Model Business Requirements
    • Manage and Monitor Jobs
    • Execute and Manage Jobs in a Clustered Environment

15 Backup and Recovery

• Introduction to Backup
• • Consistent and Inconsistent Backups
  • • Overview of Consistent Backups
    • Overview of Inconsistent Backups
  • Whole Database and Partial Database Backups
  • • Whole Database Backups
    • Tablespace Backups
    • Datafile Backups
  • RMAN and User-Managed Backups
  • • RMAN with Online Backups
    • Control File Backups
    • Archived Redo Log Backups
• Introduction to Recovery
• • Overview of Media Recovery
  • • Complete Recovery
    • Incomplete Recovery
    • Datafile Media Recovery
    • Block Media Recovery
  • Overview of RMAN and User-Managed Restore and Recovery
  • • RMAN Restore and Recovery
    • User-Managed Restore and Recovery
  • Recovery Using Oracle Flashback Technology
  • • Overview of Oracle Flashback Database
    • Overview of Oracle Flashback Table
  • Other Types of Oracle Recovery
  • • Overview of Redo Application
    • Overview of Instance and Crash Recovery
• Deciding Which Recovery Technique to Use
• • When to Use Media Recovery
  • When to Use Oracle Flashback
  • When to Use CREATE TABLE AS SELECT Recovery
  • When to Use Import/Export Utilities Recovery
  • When to Use Tablespace Point-in-Time Recovery
• Flash Recovery Area
• • Flash Recovery Area Disk Limit

16 Business Intelligence

• Introduction to Data Warehousing and Business Intelligence
• • Characteristics of Data Warehousing
  • • Subject Oriented
    • Integrated
    • Nonvolatile
    • Time Variant
  • Differences Between Data Warehouse and OLTP Systems
  • • Workload
    • Data Modifications
    • Schema Design
    • Typical Operations
    • Historical Data
  • Data Warehouse Architecture
  • • Data Warehouse Architecture (Basic)
    • Data Warehouse Architecture (with a Staging Area)
    • Data Warehouse Architecture (with a Staging Area and Data Marts)
• Overview of Extraction, Transformation, and Loading (ETL)
• • Transportable Tablespaces
  • Table Functions
  • External Tables
  • Table Compression
  • Change Data Capture
• Overview of Materialized Views for Data Warehouses
• Overview of Bitmap Indexes in Data Warehousing
• Overview of Parallel Execution
• • How Parallel Execution Works
• Overview of Analytic SQL
• • SQL for Aggregation
  • SQL for Analysis
  • SQL for Modeling
• Overview of OLAP Capabilities
• • Benefits of OLAP and RDBMS Integration
  • • Scalability
    • Availability
    • Manageability
    • Backup and Recovery
    • Security
• Overview of Data Mining

17 High Availability

• Introduction to High Availability
• Overview of Unplanned Downtime
• • Oracle Solutions to System Failures
  • • Overview of Fast-Start Fault Recovery
    • Overview of Real Application Clusters
  • Oracle Solutions to Data Failures
  • • Overview of Backup and Recovery Features for High Availability
    • Overview of Partitioning
    • Overview of Transparent Application Failover
  • Oracle Solutions to Disasters
  • • Overview of Oracle Data Guard
  • Oracle Solutions to Human Errors
  • • Overview of Oracle Flashback Features
    • Overview of LogMiner
    • Overview of Security Features for High Availability
• Overview of Planned Downtime
• • System Maintenance
  • Data Maintenance
  • Database Maintenance

18 Partitioned Tables and Indexes

• Introduction to Partitioning
• • Partition Key
  • Partitioned Tables
  • Partitioned Index-Organized Tables
• Overview of Partitioning Methods
• • Range Partitioning
  • • Range Partitioning Example
  • List Partitioning
  • • List Partitioning Example
  • Hash Partitioning
  • • Hash Partitioning Example
  • Composite Partitioning
  • • Composite Partitioning Range-Hash Example
    • Composite Partitioning Range-List Example
  • When to Partition a Table
• Overview of Partitioned Indexes
• • Local Partitioned Indexes
  • Global Partitioned Indexes
  • • Global Range Partitioned Indexes
    • Global Hash Partitioned Indexes
    • Maintenance of Global Partitioned Indexes
  • Global Nonpartitioned Indexes
  • Miscellaneous Information about Creating Indexes on Partitioned Tables
  • Using Partitioned Indexes in OLTP Applications
  • Using Partitioned Indexes in Data Warehousing and DSS Applications
  • Partitioned Indexes on Composite Partitions
• Partitioning to Improve Performance
• • Partition Pruning
  • • Partition Pruning Example
  • Partition-wise Joins
  • Parallel DML

19 Content Management

• Introduction to Content Management
• Overview of XML in Oracle
• Overview of LOBs
• Overview of Oracle Text
• • Oracle Text Index Types
  • Oracle Text Document Services
  • Oracle Text Query Package
  • Oracle Text Advanced Features
• Overview of Oracle Ultra Search
• Overview of Oracle interMedia
• Overview of Oracle Spatial

20 Database Security

• Introduction to Database Security
• • Database Users and Schemas
  • • Security Domain
  • Privileges
  • Roles
  • Storage Settings and Quotas
  • • Default Tablespace
    • Temporary Tablespace
    • Tablespace Quotas
    • Profiles and Resource Limits
• Overview of Transparent Data Encryption
• Overview of Authentication Methods
• • Authentication by the Operating System
  • Authentication by the Network
  • • Third Party-Based Authentication Technologies
    • Public-Key-Infrastructure-Based Authentication
    • Remote Authentication
  • Authentication by the Oracle Database
  • • Password Encryption
    • Account Locking
    • Password Lifetime and Expiration
    • Password Complexity Verification
  • Multitier Authentication and Authorization
  • Authentication by the Secure Socket Layer Protocol
  • Authentication of Database Administrators
• Overview of Authorization
• • User Resource Limits and Profiles
  • • Types of System Resources and Limits
    • Profiles
  • Introduction to Privileges
  • • System Privileges
    • Schema Object Privileges
  • Introduction to Roles
  • • Common Uses for Roles
    • Role Mechanisms
    • The Operating System and Roles
  • Secure Application Roles
• Overview of Access Restrictions on Tables, Views, Synonyms, or Rows
• • Fine-Grained Access Control
  • • Dynamic Predicates
  • Application Context
  • • Dynamic Contexts
  • Fine-Grained Auditing
• Overview of Security Policies
• • System Security Policy
  • • Database User Management
    • User Authentication
    • Operating System Security
  • Data Security Policy
  • User Security Policy
  • • General User Security
    • End-User Security
    • Administrator Security
    • Application Developer Security
    • Application Administrator Security
  • Password Management Policy
  • Auditing Policy
• Overview of Database Auditing
• • Types and Records of Auditing
  • • Audit Records and the Audit Trails

21 Data Integrity

• Introduction to Data Integrity
• • Types of Data Integrity
  • • Null Rule
    • Unique Column Values
    • Primary Key Values
    • Referential Integrity Rules
    • Complex Integrity Checking
  • How Oracle Enforces Data Integrity
  • • Integrity Constraints Description
    • Database Triggers
• Overview of Integrity Constraints
• • Advantages of Integrity Constraints
  • • Declarative Ease
    • Centralized Rules
    • Maximum Application Development Productivity
    • Immediate User Feedback
    • Superior Performance
    • Flexibility for Data Loads and Identification of Integrity Violations
  • The Performance Cost of Integrity Constraints
• Types of Integrity Constraints
• • NOT NULL Integrity Constraints
  • UNIQUE Key Integrity Constraints
  • • Unique Keys
    • UNIQUE Key Constraints and Indexes
    • Combine UNIQUE Key and NOT NULL Integrity Constraints
  • PRIMARY KEY Integrity Constraints
  • • Primary Keys
    • PRIMARY KEY Constraints and Indexes
  • Referential Integrity Constraints
  • • Self-Referential Integrity Constraints
    • Nulls and Foreign Keys
    • Actions Defined by Referential Integrity Constraints
    • Concurrency Control, Indexes, and Foreign Keys
  • CHECK Integrity Constraints
  • • The Check Condition
    • Multiple CHECK 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

22 Triggers

• Introduction to Triggers
• • How Triggers Are Used
  • • Some Cautionary Notes about Triggers
    • Triggers Compared with Declarative Integrity Constraints
• Parts of a Trigger
• • The Triggering Event or Statement
  • Trigger Restriction
  • Trigger Action
• Types of Triggers
• • Row Triggers and Statement Triggers
  • • Row Triggers
    • Statement Triggers
  • BEFORE and AFTER Triggers
  • • BEFORE Triggers
    • AFTER Triggers
    • Trigger Type Combinations
  • INSTEAD OF Triggers
  • • Modify Views
    • Views That Are Not Modifiable
    • INSTEAD OF Triggers on Nested Tables
  • Triggers on System Events and User Events
  • • Event Publication
    • Event Attributes
    • System Events
    • 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

Part IV Oracle Database Application Development

23 Information Integration

• Introduction to Oracle Information Integration
• Federated Access
• • Distributed SQL
  • Location Transparency
  • SQL and COMMIT Transparency
  • Distributed Query Optimization
• Information Sharing
• • Oracle Streams
  • • Oracle Streams Architecture
    • Replication with Oracle Streams
    • Oracle Streams Advanced Queuing
    • Database Change Notification
    • Change Data Capture
    • Heterogeneous Environments
    • Oracle Streams Use Cases
  • Materialized Views
• Integrating Non-Oracle Systems
• • Generic Connectivity
  • Oracle Transparent Gateways

24 SQL, PL/SQL, and Java

• Overview of SQL
• • SQL Statements
  • • Data Manipulation Language Statements
    • Data Definition Language Statements
    • Transaction Control Statements
    • Session Control Statements
    • System Control Statements
    • Embedded SQL Statements
  • Cursors
  • • Scrollable Cursors
  • Shared SQL
  • Parsing
  • SQL Processing
  • • SQL Statement Execution
    • DML Statement Processing
    • DDL Statement Processing
    • Control of Transactions
  • Overview of the Optimizer
  • • Execution Plans
• Overview of Procedural Languages
• • Overview of PL/SQL
  • • How PL/SQL Runs
    • Language Constructs for PL/SQL
    • PL/SQL Program Units
    • Stored Procedures and Functions
    • PL/SQL Packages
    • PL/SQL Collections and Records
    • PL/SQL Server Pages
  • Overview of Java
  • • Java and Object-Oriented Programming Terminology
    • Class Hierarchy
    • Interfaces
    • Polymorphism
    • Overview of the Java Virtual Machine (JVM)
    • Why Use Java in Oracle?
    • Oracle's Java Application Strategy

25 Overview of Application Development Languages

• Introduction to Oracle Application Development Languages
• Overview of C/C++ Programming Languages
• • Overview of Oracle Call Interface (OCI)
  • Overview of Oracle C++ Call Interface (OCCI)
  • • OCCI Associative Relational and Object Interfaces
    • OCCI Navigational Interface
  • Overview of Oracle Type Translator
  • Overview of Pro*C/C++ Precompiler
  • • Dynamic Creation and Access of Type Descriptions
• Overview of Microsoft Programming Languages
• • Open Database Connectivity
  • Overview of Oracle Objects for OLE
  • • OO4O Automation Server
    • Oracle Data Control
    • The Oracle Objects for OLE C++ Class Library
  • Oracle Data Provider for .NET
• Overview of Legacy Languages
• • Overview of Pro*Cobol Precompiler
  • Overview of Pro*FORTRAN Precompiler

26 Native Datatypes

• Introduction to Oracle Datatypes
• Overview of Character Datatypes
• • CHAR Datatype
  • VARCHAR2 and VARCHAR Datatypes
  • • VARCHAR Datatype
  • Length Semantics for Character Datatypes
  • NCHAR and NVARCHAR2 Datatypes
  • • NCHAR
    • NVARCHAR2
  • Use of Unicode Data in an Oracle Database
  • • Implicit Type Conversion
  • LOB Character Datatypes
  • LONG Datatype
• Overview of Numeric Datatypes
• • NUMBER Datatype
  • • Internal Numeric Format
  • Floating-Point Numbers
  • • BINARY_FLOAT Datatype
    • BINARY_DOUBLE Datatype
• Overview of DATE Datatype
• • Use of Julian Dates
  • Date Arithmetic
  • Centuries and the Year 2000
  • Daylight Savings Support
  • Time Zones
• Overview of LOB Datatypes
• • BLOB Datatype
  • CLOB and NCLOB Datatypes
  • BFILE Datatype
• Overview of RAW and LONG RAW Datatypes
• Overview of ROWID and UROWID Datatypes
• • The ROWID Pseudocolumn
  • Physical Rowids
  • • Extended Rowids
    • Restricted Rowids
    • Examples of Rowid Use
    • How Rowids Are Used
  • Logical Rowids
  • • Comparison of Logical Rowids with Physical Rowids
    • Guesses in Logical Rowids
  • Rowids in Non-Oracle Databases
• Overview of ANSI, DB2, and SQL/DS Datatypes
• Overview of XML Datatypes
• • XMLType Datatype
• Overview of URI Datatypes
• Overview of Data Conversion

27 Object Datatypes and Object Views

• Introduction to Object Datatypes
• • Complex Data Models
  • Multimedia Datatypes
• Overview of Object Datatype Categories
• • Object Types
  • • Types of Methods
    • Object Tables
    • Object Identifiers
    • Object Views Description
    • REFs
  • Collection Types
  • • VARRAYs
    • Nested Tables
• Overview of Type Inheritance
• • FINAL and NOT FINAL Types
  • NOT INSTANTIABLE Types and Methods
• Overview of User-Defined Aggregate Functions
• • Why Have User-Defined Aggregate Functions?
• Overview of Datatype Evolution
• Introduction to Object Views
• • Advantages of Object Views
  • Use of Object Views
  • Updates of Object Views
  • Updates of Nested Table Columns in Views
  • View Hierarchies

Glossary

Index