Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documentation
• Conventions

Part I What Is Oracle?

1 Introduction to Oracle Database

• Oracle Database Architecture
  • Overview of Grid Architecture
  • Overview of Application Architecture
    • Client/Server Architecture
      • The Client
      • The Server
    • Multitier Architecture: Application Servers
    • Multitier Architecture: Service-Oriented Architecture
  • Overview of Physical Database Structures
    • Datafiles
    • Control Files
    • Online Redo Log Files
    • Archived Redo Log Files
    • Parameter Files
    • Alert and Trace Log Files
    • Backup Files
  • Overview of Logical Database Structures
    • Oracle Database Data Blocks
    • Extents
    • Segments
    • Tablespaces
      • Online and Offline Tablespaces
      • Read-only Tablespaces
  • Overview of Schemas and Common Schema Objects
    • Tables
    • Indexes
    • Views
    • Clusters
    • Synonyms
  • Overview of the Oracle Database Data Dictionary
  • Overview of the Oracle Database Instance
    • Oracle Database Background Processes
    • Instance Memory Structures
  • Overview of Accessing the Database
    • Network Connections
    • Starting Up the Database
    • How Oracle Database Works
  • Overview of Oracle Database Utilities
• Oracle Database Features
  • Overview of Oracle Real Application Testing
    • Database Replay
    • SQL Performance Analyzer
  • Overview of Concurrency Features
    • Concurrency
    • Read Consistency
      • Read Consistency, Undo Records, and Transactions
      • Read-Only Transactions
    • Caching Mechanisms
    • Locking Mechanisms
      • Automatic Locking
      • Manual Locking
  • Overview of Manageability Features
    • Self-Managing Database
    • Automatic Maintenance Tasks
    • Oracle Enterprise Manager
    • SQL Developer and SQL*Plus
    • Automatic Memory Management
    • Automatic Storage Management
    • Automatic Database Diagnostic Monitor
    • SQL Tuning Advisor
    • SQL Access Advisor
    • Streams Tuning Advisor
    • The Scheduler
    • Database Resource Manager
  • Overview of Diagnosability Features
  • Overview of Database Backup and Recovery Features
  • Overview of High Availability Features
  • Overview of Business Intelligence Features
    • Data Warehousing
    • Materialized Views
    • Table Compression
    • Parallel Execution
    • Analytic SQL
    • OLAP Capabilities
    • Data Mining
    • Very Large Databases (VLDB)
      • Information Lifecycle Management (ILM)
  • Overview of Content Management Features
    • XML in Oracle Database
    • LOBs
    • SecureFiles
    • Oracle Text
    • Oracle Ultra Search
    • Oracle Multimedia
    • Oracle Spatial
  • Overview of Security Features
    • Security Mechanisms
  • Overview of Data Integrity and Triggers
    • Integrity Constraints
    • Triggers
  • Overview of Information Integration Features
    • Distributed SQL
      • Location Transparency
      • SQL and Transaction Transparency
      • Distributed Query Optimization
    • Oracle Streams
      • Capture
      • Staging
      • Consumption
      • Message Queuing
      • Data Replication
    • Oracle Database Gateways and Generic Connectivity
• Oracle Database Application Development
  • Overview of Oracle SQL
    • SQL Statements
  • Overview of PL/SQL
  • Overview of Java
  • Overview of Application Programming Languages (APIs)
  • Overview of Application Development Environments
  • 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
    • How PCTFREE and PCTUSED Work Together
• 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
      • Allocation of Temporary Segments for Queries
      • Allocation of Temporary Segments for Temporary Tables and Indexes
  • Introduction to Undo Segments and Automatic Undo Management
    • Manual Undo Management
    • Undo Quota
    • Automatic Undo Retention

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
  • 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
  • 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
  • Visible and Invisible 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
      • DETERMINISTIC Functions
      • Privileges on the Defining Function
      • Resolve Dependencies of Function-Based Indexes
  • How Indexes Are Stored
    • Format of Index Blocks
    • The Internal Structure of Indexes
    • Index Properties
      • Branch Blocks
      • Leaf Blocks
    • 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 Schema Object Dependencies

• Overview of Schema Object Dependencies
• Querying Object Dependencies
• Object Status
• Invalidation of Dependent Objects
  • Session State and Referenced Packages
  • Security Authorization
• Guidelines for Reducing Invalidation
  • Add New Items to End of Package
  • Reference Each Table Through a View
• Object Revalidation
• Name Resolution in Schema Scope
• Local Dependency Management
• Remote Dependency Management
  • Dependencies Among Local and Remote Database Procedures
  • Dependencies Among Other Remote Objects
  • Dependencies of Applications
• Remote Procedure Call (RPC) Dependency Management
  • Time-Stamp Checking
  • Signature Checking
    • Switching Datatype Classes
      • Modes
      • Default Parameter Values
    • Examples of Changing Procedure Signatures
  • Controlling Remote Dependencies
    • Dependency Resolution
    • Suggestions for Managing Dependencies
• Shared SQL Dependency Management

7 The Data Dictionary

• Introduction to the Data Dictionary
  • Structure of the Data Dictionary
  • SYS, Owner of the Data Dictionary
• How the Data Dictionary Is Used
  • How Oracle Database 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 Database Memory Structures
  • Basic Memory Structures
• Overview of the System Global Area
  • Database Buffer Cache
    • Organization of the Database Buffer Cache
    • The LRU Algorithm and Full Table Scans
  • Redo Log Buffer
  • Shared Pool
    • Library Cache
      • Shared SQL Areas and Private SQL Areas
      • PL/SQL Program Units and the Shared Pool
      • Allocation and Reuse of Memory in the Shared Pool
    • Dictionary Cache
    • Result Cache
      • SQL Query Result Cache
      • PL/SQL Function Result Cache
  • Large Pool
  • Java Pool
  • Streams Pool
• Overview of the Program Global Area
  • Content of the PGA
    • Session Memory
    • Private SQL Area
      • Cursors and SQL Areas
      • Private SQL Area Components
      • SQL Work Areas
  • PGA Memory Use in Dedicated and Shared Server Modes
• Overview of Memory Management Methods
• About 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 Database Processes
  • Oracle Database Server Processes
  • Oracle Database Background Processes
    • Archiver Processes (ARCn)
    • Checkpoint Process (CKPT)
    • Database Writer Process (DBWn)
    • Job Queue Processes
    • Log Writer Process (LGWR)
    • Process Monitor Process (PMON)
    • Queue Monitor Processes (QMNn)
    • Recoverer Process (RECO)
    • System Monitor Process (SMON)
    • Other Oracle Database Background Processes
  • Oracle Database 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
• Database Resident Connection Pooling
  • Using Database Resident Connection Pooling
    • Connection Classes
    • Session Purity
• 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
    • Oracle Database as a Web Service Provider
• Overview of Oracle Net Services
  • How Oracle Net Services Works
  • The Listener
    • Service Information Registration

11 Oracle Database Utilities

• Introduction to Oracle Database 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
• ADRCI: ADR Command Interpreter

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
    • Server Parameter Files and Hardware Assisted Resilient Data
    • 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 Oracle Real Application Clusters
    • How a Clone Database Is Mounted
  • What Happens When You Open a Database
    • Crash and Instance Recovery
      • Cache Recovery
      • Transaction 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 Database Manages Data Concurrency and Consistency
  • Multiversion Concurrency Control
  • Statement-Level Read Consistency
  • Transaction-Level Read Consistency
  • Read Consistency with Oracle Real Application Clusters
  • Oracle Database 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 a Database Is Quiesced
• How Oracle Database 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)
      • Row Share Table Locks (RS)
      • Row Exclusive Table Locks (RX)
      • Share Table Locks (S)
      • Share Row Exclusive Table Locks (SRX)
      • Exclusive Table Locks (X)
    • DML Locks Automatically Acquired for DML Statements
      • Default Locking for Queries
      • Default Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE
  • 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
      • Dictionary Cache Locks
      • File and Log Management Locks
      • Tablespace and Rollback Segment Locks
  • Explicit (Manual) Data Locking
  • Oracle Database Lock Management Services
• Overview of Oracle Flashback Query
  • Flashback Query Benefits
  • Some Uses of Flashback Query

14 Manageability

• Installing Oracle Database 11g 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
  • Fault Diagnosability Infrastructure
    • Automatic Diagnostic Repository
    • Incident Packaging Service
  • Server-Generated Alerts
  • Advisor Framework
  • Hang Manager
• Performance Diagnostics 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
      • Types of Disk Groups
    • Automatic Storage Management Files
    • Automatic Storage Management Templates
    • Automatic Storage Management Disks
    • Failure Groups
    • Automatic Storage Management Instances
      • Automatic Storage Management Instance Background Processes
      • Database Instance Background Processes
    • SYSASM Privilege
  • 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 and Recovery
  • Flash Recovery Area
• Database Backups
  • What Are Database Backups?
  • Whole Database and Partial Database Backups
  • Consistent and Inconsistent Backups
    • Overview of Consistent Backups
    • Overview of Inconsistent Backups
  • RMAN and User-Managed Backups
    • Online Backups
    • Control File Backups
    • Archived Redo Log Backups
• Problems Requiring Data Repair
  • Media Failures
  • User Errors
• Data Repair
  • Data Recovery Advisor
  • Oracle Flashback Technology
    • Oracle Flashback Database
    • Oracle Flashback Table
    • Oracle Flashback Drop
  • Media Recovery
    • Datafile Media Recovery
    • Block Media Recovery
    • Complete Recovery
    • Database Point-in-Time Recovery
      • Tablespace Point-in-Time Recovery
    • RMAN and User-Managed Recovery
      • RMAN Restore and Recovery
      • User-Managed Restore and Recovery

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
  • Full Integration of Multidimensional Technology
  • Ease of Application Development
  • Ease of Administration
  • Security
  • Unmatched Performance and Scalability
  • Reduced Costs
• Overview of Data Mining

17 High Availability

• Introduction to High Availability
• Causes Of Downtime
• Protection Against Computer Failures
  • Overview of Enterprise Grids with Oracle Real Application Clusters and Oracle Clusterware
  • Fast Start Fault Recovery
  • Oracle Data Guard
  • Oracle Streams
• Protection Against Data Failures
  • Protecting Against Storage Failures
  • Protecting Against Human Errors
    • Guarding Against Human Errors
    • Oracle Flashback Technology
    • LogMiner SQL-Based Log Analyzer
    • Protecting Against Data Corruptions
      • Oracle Hardware Assisted Resilient Data (HARD)
      • Lost Writes
      • Data block corruption prevention and detection
      • Data Recovery Advisor
      • Flash Backup And Recovery
    • Protecting Against Site Failures
      • Oracle Data Guard and standby databases
      • Zero Data Loss Redo Transport
      • Real-Time Apply and Flashback Database
      • Data Guard Broker
      • Fast-Start Failover
• Avoiding Downtime During Planned Maintenance
  • Avoiding Downtime for Data Changes
    • Online Schema and Data Reorganization
    • Partitioned Tables and Indexes
    • Dynamic Resource Provisioning
  • Avoiding Downtime for System Changes
    • Rolling Patch Updates
    • Rolling Release Upgrade
• Maximum Availability Architecture (MAA) Best Practices

18 Very Large Databases (VLDB)

• Introduction to Partitioning
  • Partition Key
  • Partitioned Tables
  • Partitioned Index-Organized Tables
  • Partitioning Methods
• 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

19 Content Management

• Introduction to Content Management
• Overview of XML in Oracle Database
• 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 Multimedia
• 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
  • Tablespace 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 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
      • Session Level
      • Call Level
      • CPU Time
      • Logical Reads
      • Other Resources
    • Profiles
      • When to Use Profiles
      • Determine Values for Resource Limits of a Profile
  • Introduction to Privileges
    • System Privileges
    • Schema Object Privileges
  • Introduction to Roles
    • Common Uses for Roles
      • Application Roles
      • User 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
      • Protection for Connections as SYS and SYSTEM
      • Protection for Administrator Connections
    • Application Developer Security
      • Application Developers and Their Privileges
    • Application Administrator Security
  • Password Management Policy
  • Auditing Policy
• Overview of Database Auditing
  • Types and Records of Auditing
    • Audit Records and the Audit Trails
      • Database Audit Trail
      • Auditing in a Distributed Database
      • Operating System Audit Trail
      • Operating System Audit Records
      • Records Always in the Operating System Audit Trail
      • When Are Audit Records Created?

21 Data Integrity

• Introduction to Data Integrity
  • Data Integrity Rules
  • How Oracle Database Enforces Data Integrity
  • Constraint States
• Overview of Integrity Constraints
  • Advantages of Integrity Constraints
    • Declarative Ease
    • Centralized Rules
    • Maximum Application Development Productivity
    • Immediate User Feedback
    • 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
    • Combining 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
      • DELETE NO ACTION
      • DELETE CASCADE
      • DELETE SET NULL
      • DML Restrictions with Respect to Referential Actions
    • Concurrency Control, Indexes, and Foreign Keys
      • No Index on the Foreign Key
      • Index on the Foreign Key
  • 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

22 Triggers

• Introduction to Triggers
  • How Triggers Are Used
    • Some Cautionary Notes about Triggers
    • Triggers Compared with Declarative Integrity Constraints
• Components 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
  • Compound Triggers
  • 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
      • Triggers on LOGON and LOGOFF Events
      • Triggers on DDL Statements
      • Triggers on DML Statements
• 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

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
      • Capture
      • Staging
      • Consumption
    • Replication with Oracle Streams
      • Capturing DML and DDL Changes
      • Propagating Changes Over a Directed Network
      • Resolving Conflicts and Applying Changes
    • Oracle Streams Advanced Queuing
      • Asynchronous Application Integration
      • Extensible Integration Architecture
      • Heterogeneous Application Integration
      • Legacy Application Integration
      • Standard-Based API Support
    • Database Change Notification
    • Change Data Capture
    • Heterogeneous Environments
    • Oracle Streams Use Cases
  • Materialized Views
• Data Comparison and Convergence at Oracle Databases
• Integrating Non-Oracle Systems
  • Generic Connectivity
  • Oracle Database Gateways

Part IV Oracle Database Application Development

24 SQL

• Introduction to SQL
• SQL Statements
  • Data Manipulation Language Statements
    • DML Error Logging
  • Data Definition Language Statements
  • Transaction Control Statements
  • Session Control Statements
  • System Control Statements
  • Embedded SQL Statements
• Cursors
  • Scrollable Cursors
• Shared SQL Areas
• Parsing
• Query Processing
• SQL Processing
  • Flow Chart of SQL Statement Execution
  • Description of SQL Statement Processing
    • Stage 1: Open or Create a Cursor
    • Stage 2: Parse the Statement
    • Stage 3: Determine if there is a Query
    • Stage 4: Describe Results of a Query (Queries Only)
    • Stage 5: Define Output of a Query (Queries Only)
    • Stage 6: Bind Any Variables
    • Stage 7: Parallelize the Statement (Optional)
    • Stage 8: Run the Statement
    • Stage 9: Fetch Rows of a Query (Queries Only)
    • Stage 10: Close the Cursor
  • Processing Other Types of SQL Statements
    • DDL Statement Processing
    • Transaction Control Processing
    • Other Processing Types
• Overview of the Optimizer
  • SQL Plan Management (SPM)
  • Execution Plans
    • Stored Outlines
    • Editing Stored Outlines

25 Supported 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 the Oracle Type Translator
  • Overview of Pro*C/C++ Precompiler
    • Dynamic Creation and Access of Type Descriptions
• Overview of PL/SQL
  • How PL/SQL Runs
    • Interpreted Execution
    • Native Execution
  • Language Constructs for PL/SQL
    • Variables and Constants
    • Cursors
    • Exceptions
    • Dynamic SQL in PL/SQL
  • PL/SQL Program Units
  • Stored Procedures and Functions
    • Benefits of Procedures
    • Procedure Guidelines
    • Anonymous PL/SQL Blocks Compared with Stored Procedures
    • Standalone Procedures
    • Dependency Tracking for Stored Procedures
    • External Procedures
    • Table Functions
  • PL/SQL Packages
    • Benefits of Packages
  • PL/SQL Collections and Records
    • Collections
    • Records
  • PL/SQL Server Pages
• Overview of Java
  • Java and Object-Oriented Programming Terminology
    • Classes
    • Attributes
    • Methods
  • Class Hierarchy
  • Interfaces
  • Polymorphism
  • Overview of the Java Virtual Machine (JVM)
  • Why Use Java in Oracle Database?
    • Multithreading
    • Automated Storage Management
    • Footprint
    • Performance
    • Dynamic Class Loading
  • Oracle's Java Application Strategy
    • Java Stored Procedures
    • PL/SQL Integration and Oracle Database Functionality
    • JDBC
    • SQLJ
    • JPublisher
    • Java Messaging Service
• 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 Oracle Data Types

• 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 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 Object Datatypes and Object Views
• Data Conversion

Glossary

Index