1/35

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
  • Overview of Physical Database Structures
  • Overview of Logical Database Structures
  • Overview of Schemas and Common Schema Objects
  • Overview of the Oracle Database Data Dictionary
  • Overview of the Oracle Database Instance
  • Overview of Accessing the Database
  • Overview of Oracle Database Utilities
• Oracle Database Features
  • Overview of Oracle Real Application Testing
  • Overview of Concurrency Features
  • Overview of Manageability Features
  • Overview of Diagnosability Features
  • Overview of Database Backup and Recovery Features
  • Overview of High Availability Features
  • Overview of Business Intelligence Features
  • Overview of Content Management Features
  • Overview of Security Features
  • Overview of Data Integrity and Triggers
  • Overview of Information Integration Features
• Oracle Database Application Development
  • Overview of Oracle SQL
  • 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
  • Free Space Management
  • PCTFREE, PCTUSED, and Row Chaining
• Overview of Extents
  • When Extents Are Allocated
  • Determine the Number and Size of Extents
  • How Extents Are Allocated
  • When Extents Are Deallocated
• Overview of Segments
  • Introduction to Data Segments
  • Introduction to Index Segments
  • Introduction to Temporary Segments
  • Introduction to Undo Segments and Automatic Undo Management

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
  • The SYSTEM Tablespace
  • The SYSAUX Tablespace
  • Undo Tablespaces
  • Default Temporary Tablespace
  • Using Multiple Tablespaces
  • Managing Space in Tablespaces
  • Multiple Block Sizes
  • Online and Offline Tablespaces
  • Read-Only Tablespaces
  • Temporary Tablespaces
  • Transport of Tablespaces Between Databases
• 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
  • The Two-Phase Commit Mechanism
• Overview of Autonomous Transactions
  • Autonomous PL/SQL Blocks

5 Schema Objects

• Introduction to Schema Objects
• Overview of Tables
  • How Table Data Is Stored
  • Table Compression
  • Nulls Indicate Absence of Value
  • Default Values for Columns
  • Partitioned Tables
  • Nested Tables
  • Temporary Tables
  • External Tables
• Overview of Views
  • How Views are Stored
  • How Views Are Used
  • Mechanics of 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
  • How Indexes Are Stored
  • Index Unique Scan
  • Index Range Scan
  • Key Compression
  • Reverse Key Indexes
  • Bitmap Indexes
  • 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
  • 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
  • Controlling Remote 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
  • How to Use the Data Dictionary
• 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
  • Redo Log Buffer
  • Shared Pool
  • Large Pool
  • Java Pool
  • Streams Pool
• Overview of the Program Global Area
  • Content of the PGA
  • 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
  • Oracle Database Trace Files and the Alert Log
• Shared Server Architecture
  • Dispatcher Request and Response Queues
  • Restricted Operations of the Shared Server
• Dedicated Server Configuration
• Database Resident Connection Pooling
  • Using Database Resident Connection Pooling
• 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

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
• Overview of Instance and Database Startup
  • How an Instance Is Started
  • How a Database Is Mounted
  • What Happens When You Open a Database
• Overview of Database and Instance Shutdown
  • Close a Database
  • Unmount a Database
  • Shut Down an Instance

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
  • Comparison of Read Committed and Serializable Isolation
  • Choice of Isolation Level
• How Oracle Database Locks Data
  • Transactions and Data Concurrency
  • Deadlocks
  • Types of Locks
  • DML Locks
  • DDL Locks
  • Latches and Internal 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
  • 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
• Automatic 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
  • Overview of Services
• Oracle Scheduler
  • What Can the Scheduler Do?

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
  • RMAN and User-Managed Backups
• Problems Requiring Data Repair
  • Media Failures
  • User Errors
• Data Repair
  • Data Recovery Advisor
  • Oracle Flashback Technology
  • Media Recovery

16 Business Intelligence

• Introduction to Data Warehousing and Business Intelligence
  • Characteristics of Data Warehousing
  • Differences Between Data Warehouse and OLTP Systems
  • Data Warehouse Architecture
• 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
• Avoiding Downtime During Planned Maintenance
  • Avoiding Downtime for Data Changes
  • Avoiding Downtime for System Changes
• 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 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-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
  • Privileges
  • Roles
  • Storage Settings and Quotas
• Overview of Transparent Data Encryption
  • Tablespace Encryption
• Overview of Authentication Methods
  • Authentication by the Operating System
  • Authentication by the Network
  • Authentication by Oracle Database
  • Multitier Authentication and Authorization
  • Authentication by the Secure Socket Layer Protocol
  • Authentication of Database Administrators
• Overview of Authorization
  • User Resource Limits and Profiles
  • Introduction to Privileges
  • Introduction to Roles
  • Secure Application Roles
• Overview of Access Restrictions on Tables, Views, Synonyms, or Rows
  • Fine-Grained Access Control
  • Application Context
  • Fine-Grained Auditing
• Overview of Security Policies
  • System Security Policy
  • Data Security Policy
  • User Security Policy
  • Password Management Policy
  • Auditing Policy
• Overview of Database Auditing
  • Types and Records of Auditing

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
  • 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

22 Triggers

• Introduction to Triggers
  • How Triggers Are Used
• Components of a Trigger
  • The Triggering Event or Statement
  • Trigger Restriction
  • Trigger Action
• Types of Triggers
  • Row Triggers and Statement Triggers
  • BEFORE and AFTER Triggers
  • Compound 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

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
  • 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
  • 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
  • flowchart of SQL Statement Execution
  • Description of SQL Statement Processing
  • Processing Other Types of SQL Statements
• Overview of the Optimizer
  • SQL Plan Management (SPM)
  • Execution Plans

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)
  • Overview of the Oracle Type Translator
  • Overview of Pro*C/C++ Precompiler
• 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 Database?
  • Oracle's Java Application Strategy
• Overview of Microsoft Programming Languages
  • Open Database Connectivity
  • Overview of Oracle Objects for OLE
  • 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
  • Length Semantics for Character Datatypes
  • NCHAR and NVARCHAR2 Datatypes
  • Use of Unicode Data in Oracle Database
  • LOB Character Datatypes
  • LONG Datatype
• Overview of Numeric Datatypes
  • NUMBER Datatype
  • Floating-Point Numbers
• 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
  • 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