Oracle 8 i

Concepts

Release 2 (8.1.6)

December 1999

Part No. A76965-01

Concepts, Release 2 (8.1.6)

Part No. A76965-01

Copyright 1999, Oracle Corporation. All rights reserved.

Primary Authors: Lefty Leverenz, Diana Rehfield, Cathy Baird

Contributing Authors: Lance Ashdown, Steve Bobrowski, Cynthia Chin-Lee, Cindy Closkey, BillCreekbaum, Jason Durbin, John Frazzini, Richard Mateosian, Denis Raphaely, John Russell, DannySokolsky, Randy Urbano

Contributors: Richard Allen, David Anderson, Andre Bakker, Mark Bauer, Ruth Baylis, Bill Bridge, AtifChaudry, Jeff Cohen, Michele Cyran, Benoit Dageville, Mary Ann Davidson, Sandy Dreskin, AhmedEzzat, Jim Finnerty, Diana Lorentz, Anurag Gupta, Gary Hallmark, Michael Hartstein, Terry Hayes, AlexHo, Chin Hong, Ken Jacobs, Sandeep Jain, Amit Jasuja, Hakan Jakobsson, Bob Jenkins, Ashok Joshi,Mohan Kamath, Jonathan Klein, R. Kleinro, Robert Kooi, Vishu Krishnamurthy, MuralidharKrishnaprasad, Andre Kruglikov, Tirthankar Lahiri, Juan Loaiza, Brom Mahbod, William Maimone,Andrew Mendelsohn, Reza Monajjemi, Mark Moore, Rita Moran, Bhagat Nainani, Denise Oertel, BruceOlsen, Robert Pang, Mark Porter, Maria Pratt, Tuomas Pystynen, Ann Rhee, Patrick Ritto, Hasan Rizvi,Sriram Samu, Hari Sankar, Gordon Smith, Mark Smith, Leng Leng Tan, Lynne Thieme, Alvin To, AlexTsukerman, William Waddington, Joyo Wijaya, Linda Willis, Andrew Witkowski, Mohamed Zait

Graphic Designer: Valarie Moore

The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or otherinherently dangerous applications. It shall be the licensees responsibility to take all appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if thePrograms are used for such purposes, and Oracle disclaims liability for any damages caused by suchuse of the Programs.

The Programs (which include both the software and documentation) contain proprietary information ofOracle Corporation; they are provided under a license agreement containing restrictions on use anddisclosure and are also protected by copyright, patent, and other intellectual and industrial propertylaws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.

The information contained in this document is subject to change without notice. If you find anyproblems in the documentation, please report them to us in writing. Oracle Corporation does notwarrant that this document is error free. Except as may be expressly permitted in your license agreementfor these Programs, no part of these Programs may be reproduced or transmitted in any form or by anymeans, electronic or mechanical, for any purpose, without the express written permission of OracleCorporation.

If the Programs are delivered to the U.S. Government or anyone licensing or using the Programs onbehalf of the U.S. Government, the following notice is applicable:

Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercialcomputer software" and use, duplication, and disclosure of the Programs including documentation, shallbe subject to the licensing restrictions set forth in the applicable Oracle license agreement. Otherwise,Programs delivered subject to the Federal Acquisition Regulations are "restricted computer software"and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR 52.227-19,Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500 OracleParkway, Redwood City, CA 94065.

Oracle is a registered trademark, and Net8, Oracle Call Interface, Oracle7, Oracle8, Oracle8i, OracleDesigner, Oracle Enterprise Manager, Oracle Forms, Oracle Parallel Server, Oracle Server Manager,SQL*Loader, LogMiner, PL/SQL, Pro*C, Pro*C/C++, SQL*Net and SQL*Plus, and Trusted Oracle are

trademarks or registered trademarks of Oracle Corporation. All other company or product namesmentioned are used for identification purposes only and may be trademarks of their respective owners.

Contents

Send Us Your Comments .............................................................................................................. xxvii

Preface ........................................................................................................................................................ xxix

Part I What Is Oracle?

1 Introduction to the Oracle Server

Introduction to Databases and Information Management ......................................................... 1-2The Oracle Server ......................................................................................................................... 1-4

Database Structure and Space Management ................................................................................ 1-5Logical Database Structures........................................................................................................ 1-5Physical Database Structures ...................................................................................................... 1-8

Memory Structure and Processes................................................................................................... 1-12Memory Structures..................................................................................................................... 1-12Process Architecture................................................................................................................... 1-15The Program Interface ............................................................................................................... 1-19An Example of How Oracle Works.......................................................................................... 1-19

The Object-Relational Model for Database Management........................................................ 1-20The Relational Model ................................................................................................................. 1-21The Object-Relational Model .................................................................................................... 1-21Schemas and Schema Objects ................................................................................................... 1-22The Data Dictionary ................................................................................................................... 1-29

Data Concurrency and Consistency .............................................................................................. 1-29Concurrency ................................................................................................................................ 1-29

v

Read Consistency........................................................................................................................ 1-30Locking Mechanisms.................................................................................................................. 1-31

Distributed Processing and Distributed Databases................................................................... 1-32Client-Server Architecture: Distributed Processing .............................................................. 1-32Multi-Tier Architecture: Application Servers......................................................................... 1-33Distributed Databases ................................................................................................................ 1-34

Startup and Shutdown Operations................................................................................................ 1-36Database Security.............................................................................................................................. 1-37

Security Mechanisms.................................................................................................................. 1-38Privileges...................................................................................................................................... 1-40

Database Backup and Recovery ..................................................................................................... 1-44Why Is Recovery Important? .................................................................................................... 1-44Types of Failures ......................................................................................................................... 1-44Structures Used for Recovery ................................................................................................... 1-46Basic Recovery Steps .................................................................................................................. 1-49Recovery Manager ...................................................................................................................... 1-50

Data Access......................................................................................................................................... 1-50SQLThe Structured Query Language .................................................................................. 1-51Transactions................................................................................................................................. 1-52PL/SQL ........................................................................................................................................ 1-55Data Integrity............................................................................................................................... 1-57

Part II Database Structures

2 The Data Dictionary

Introduction to the Data Dictionary ................................................................................................ 2-2The Structure of the Data Dictionary......................................................................................... 2-3SYS, the Owner of the Data Dictionary ..................................................................................... 2-3

How the Data Dictionary Is Used .................................................................................................... 2-3How Oracle Uses the Data Dictionary....................................................................................... 2-4How Users and DBAs Can Use the Data Dictionary............................................................... 2-5

The Dynamic Performance Tables ................................................................................................... 2-7

vi

3 Tablespaces and Datafiles

Introduction to Databases, Tablespaces, and Datafiles ............................................................... 3-2Allocating More Space for a Database....................................................................................... 3-3

Tablespaces........................................................................................................................................... 3-6The SYSTEM Tablespace ............................................................................................................. 3-6Using Multiple Tablespaces ........................................................................................................ 3-7Space Management in Tablespaces............................................................................................ 3-7Online and Offline Tablespaces.................................................................................................. 3-9Read-Only Tablespaces.............................................................................................................. 3-11Temporary Tablespaces ............................................................................................................. 3-12Transporting Tablespaces between Databases....................................................................... 3-14

Datafiles .............................................................................................................................................. 3-16Datafile Contents ........................................................................................................................ 3-17Size of Datafiles........................................................................................................................... 3-17Offline Datafiles .......................................................................................................................... 3-17Temporary Datafiles................................................................................................................... 3-18

4 Data Blocks, Extents, and Segments

Introduction to Data Blocks, Extents, and Segments................................................................... 4-2Data Blocks .......................................................................................................................................... 4-3

Data Block Format ........................................................................................................................ 4-3An Introduction to PCTFREE, PCTUSED, and Row Chaining.............................................. 4-5

Extents ................................................................................................................................................. 4-12When Extents Are Allocated..................................................................................................... 4-12Determining the Number and Size of Extents........................................................................ 4-12How Extents Are Allocated....................................................................................................... 4-14When Extents Are Deallocated................................................................................................. 4-15

Segments............................................................................................................................................. 4-18Data Segments............................................................................................................................. 4-18Index Segments ........................................................................................................................... 4-19Temporary Segments ................................................................................................................. 4-19Rollback Segments...................................................................................................................... 4-21

vii

Part III The Oracle Instance

5 Database and Instance Startup and Shutdown

Introduction to an Oracle Instance .................................................................................................. 5-2The Instance and the Database ................................................................................................... 5-3Connecting with Administrator Privileges............................................................................... 5-3Parameter Files.............................................................................................................................. 5-4

Instance and Database Startup ......................................................................................................... 5-5Starting an Instance ...................................................................................................................... 5-6Mounting a Database .................................................................................................................. 5-6Opening a Database...................................................................................................................... 5-8

Database and Instance Shutdown ................................................................................................. 5-10Closing a Database ..................................................................................................................... 5-10Dismounting a Database............................................................................................................ 5-11Shutting Down an Instance ....................................................................................................... 5-11

6 Distributed Processing

Introduction to Oracle Client/Server Architecture ....................................................................... 6-2Distributed Processing....................................................................................................................... 6-2Net8 ........................................................................................................................................................ 6-5

How Net8 Works .......................................................................................................................... 6-5The Network Listener .................................................................................................................. 6-6

Multi-Tier Architecture...................................................................................................................... 6-7Clients............................................................................................................................................. 6-8Application Servers ...................................................................................................................... 6-8Database Servers ........................................................................................................................... 6-9

7 Memory Architecture

Introduction to Oracle Memory Structures.................................................................................... 7-2System Global Area (SGA) ............................................................................................................... 7-2

The Database Buffer Cache.......................................................................................................... 7-3The Redo Log Buffer..................................................................................................................... 7-6The Shared Pool ............................................................................................................................ 7-7

viii

The Large Pool ............................................................................................................................ 7-12Size of the SGA............................................................................................................................ 7-13Controlling the SGAs Use of Memory.................................................................................... 7-14

Program Global Areas (PGA) ......................................................................................................... 7-15Contents of a PGA ...................................................................................................................... 7-15Size of a PGA............................................................................................................................... 7-16

Sort Areas ........................................................................................................................................... 7-17Virtual Memory ................................................................................................................................. 7-18Software Code Areas ........................................................................................................................ 7-18

8 Process Architecture

Introduction to Processes .................................................................................................................. 8-2Multiple-Process Oracle Systems ............................................................................................... 8-2Types of Processes ........................................................................................................................ 8-2

User Processes...................................................................................................................................... 8-4Connections and Sessions............................................................................................................ 8-4

Oracle Processes ................................................................................................................................. 8-5Server Processes............................................................................................................................ 8-5Background Processes.................................................................................................................. 8-5Trace Files and the ALERT File ................................................................................................ 8-15

Multi-Threaded Server Configuration.......................................................................................... 8-16Dispatcher Request and Response Queues............................................................................. 8-17Shared Server Processes............................................................................................................. 8-20Artificial Deadlocks.................................................................................................................... 8-20Restricted Operations of the Multi-Threaded Server ............................................................ 8-21An Example of Oracle Using the Multi-Threaded Server .................................................... 8-21

Dedicated Server Configuration .................................................................................................... 8-23An Example of Oracle Using Dedicated Server Processes ................................................... 8-25Pre-Spawned Dedicated Processes .......................................................................................... 8-27

The Program Interface ..................................................................................................................... 8-27Program Interface Structure...................................................................................................... 8-27The Program Interface Drivers ................................................................................................. 8-28Operating System Communications Software ....................................................................... 8-28

ix

9 Database Resource Management

Introduction to the Database Resource Manager ......................................................................... 9-2Resource Consumer Groups and Resource Plans......................................................................... 9-3

What Are Resource Consumer Groups? ................................................................................... 9-3What Are Resource Plans?........................................................................................................... 9-4

Resource Allocation Methods........................................................................................................... 9-5CPU Allocation for Resource Plans: Emphasis Method ......................................................... 9-5Parallel Degree Limit for Resource Plans: Absolute Method................................................. 9-6

Resource Plan Directives ................................................................................................................... 9-7Examples ............................................................................................................................................... 9-7

Using Resource Consumer Groups and Resource Plans ........................................................ 9-7Using Subplans ............................................................................................................................. 9-9Using Multi-Level Resource Plans ........................................................................................... 9-10Using the Parallel Degree Limit Resource Plan Directive .................................................... 9-10Summary...................................................................................................................................... 9-11

Using the Database Resource Manager ........................................................................................ 9-11

Part IV The Object-Relational DBMS

10 Schema Objects

Introduction to Schema Objects..................................................................................................... 10-2Tables................................................................................................................................................... 10-3

How Table Data Is Stored.......................................................................................................... 10-4Nulls.............................................................................................................................................. 10-8Default Values for Columns...................................................................................................... 10-9Nested Tables ............................................................................................................................ 10-10Temporary Tables ..................................................................................................................... 10-11

Views ................................................................................................................................................. 10-12Storage for Views...................................................................................................................... 10-13How Views Are Used............................................................................................................... 10-14The Mechanics of Views .......................................................................................................... 10-15Dependencies and Views......................................................................................................... 10-16Updatable Join Views............................................................................................................... 10-16Object Views .............................................................................................................................. 10-17

x

Inline Views............................................................................................................................... 10-17Materialized Views......................................................................................................................... 10-18

Refreshing Materialized Views............................................................................................... 10-19Materialized View Logs........................................................................................................... 10-19

Dimensions ...................................................................................................................................... 10-20The Sequence Generator ............................................................................................................... 10-21Synonyms ......................................................................................................................................... 10-22Indexes .............................................................................................................................................. 10-23

Unique and Nonunique Indexes ............................................................................................ 10-24Composite Indexes ................................................................................................................... 10-24Indexes and Keys...................................................................................................................... 10-25Indexes and Nulls..................................................................................................................... 10-25Function-Based Indexes........................................................................................................... 10-26How Indexes Are Stored ......................................................................................................... 10-28Key Compression...................................................................................................................... 10-31Reverse Key Indexes ................................................................................................................ 10-33Bitmap Indexes.......................................................................................................................... 10-34

Index-Organized Tables ................................................................................................................ 10-39Benefits of Index-Organized Tables....................................................................................... 10-41Index-Organized Tables with Row Overflow Area............................................................. 10-41Secondary Indexes on Index-Organized Tables................................................................... 10-42Additional Features of Index-Organized Tables.................................................................. 10-42Applications of Interest for Index-Organized Tables.......................................................... 10-43

Application Domain Indexes ....................................................................................................... 10-45Indextypes ................................................................................................................................. 10-46Domain Indexes ........................................................................................................................ 10-47User-Defined Operators .......................................................................................................... 10-48

Clusters ............................................................................................................................................. 10-49Performance Considerations................................................................................................... 10-52Format of Clustered Data Blocks............................................................................................ 10-53The Cluster Key ........................................................................................................................ 10-53The Cluster Index ..................................................................................................................... 10-54

Hash Clusters................................................................................................................................... 10-54How Data Is Stored in a Hash Cluster................................................................................... 10-55Hash Key Values....................................................................................................................... 10-57

xi

Hash Functions.......................................................................................................................... 10-58Allocation of Space for a Hash Cluster.................................................................................. 10-59Single Table Hash Clusters...................................................................................................... 10-61

11 Partitioned Tables and Indexes

Introduction to Partitioning ............................................................................................................ 11-2What Is Partitioning?.................................................................................................................. 11-2Advantages of Partitioning ....................................................................................................... 11-5Manual Partitioning with Partition Views............................................................................ 11-11

Basic Partitioning Model ............................................................................................................... 11-11Range Partitioning .................................................................................................................... 11-13Hash Partitioning...................................................................................................................... 11-15Composite Partitioning............................................................................................................ 11-16Partition and Subpartition Names ......................................................................................... 11-18Partitioning and Subpartitioning Columns and Keys......................................................... 11-18Partition Bounds for Range Partitioning............................................................................... 11-19Equipartitioning........................................................................................................................ 11-23

Rules for Partitioning Tables and Indexes ................................................................................. 11-26Table Partitioning ..................................................................................................................... 11-26Index Partitioning ..................................................................................................................... 11-28Partitioning of Tables with LOB Columns............................................................................ 11-37Partitioning Index-Organized Tables and Their Secondary Indexes ................................ 11-41

DML Partition Locks and Subpartition Locks .......................................................................... 11-45DML Partition Locks ................................................................................................................ 11-45DML Subpartition Locks ......................................................................................................... 11-46Performance Considerations for Oracle Parallel Server ..................................................... 11-46

Maintenance Operations ............................................................................................................... 11-47Partition Maintenance Operations ......................................................................................... 11-47Managing Indexes..................................................................................................................... 11-59Privileges for Partitioned Tables and Indexes ...................................................................... 11-62Auditing for Partitioned Tables and Indexes ....................................................................... 11-63

Partition-Extended and Subpartition-Extended Table Names............................................... 11-63PARTITION and SUBPARTITION Specifications ............................................................... 11-63Viewing Partitions or Subpartitions as Tables ..................................................................... 11-64Using Partition- and Subpartition-Extended Table Names................................................ 11-64

xii

12 Built-In Datatypes

Introduction to Oracle Datatypes .................................................................................................. 12-2Character Datatypes ......................................................................................................................... 12-5

CHAR Datatype.......................................................................................................................... 12-5VARCHAR2 and VARCHAR Datatypes ................................................................................ 12-6Column Lengths for Character Datatypes and NLS Character Sets ................................... 12-6NCHAR and NVARCHAR2 Datatypes .................................................................................. 12-7LOB Character Datatypes.......................................................................................................... 12-7LONG Datatype.......................................................................................................................... 12-7

NUMBER Datatype .......................................................................................................................... 12-8Internal Numeric Format........................................................................................................... 12-9

DATE Datatype ............................................................................................................................... 12-10Using Julian Dates .................................................................................................................... 12-11Date Arithmetic......................................................................................................................... 12-11Centuries and the Year 2000 ................................................................................................... 12-12

LOB Datatypes ................................................................................................................................ 12-12BLOB Datatype ......................................................................................................................... 12-13CLOB and NCLOB Datatypes ................................................................................................ 12-14BFILE Datatype......................................................................................................................... 12-14

RAW and LONG RAW Datatypes ............................................................................................... 12-15ROWID and UROWID Datatypes............................................................................................... 12-15

The ROWID Pseudocolumn.................................................................................................... 12-16Physical Rowids........................................................................................................................ 12-16Logical Rowids.......................................................................................................................... 12-20Rowids in Non-Oracle Databases .......................................................................................... 12-23

ANSI, DB2, and SQL/DS Datatypes .......................................................................................... 12-23Data Conversion.............................................................................................................................. 12-24

13 User-Defined Datatypes

Introduction to User-Defined Datatypes ...................................................................................... 13-2Complex Data Models ............................................................................................................... 13-2Multimedia Datatypes ............................................................................................................... 13-3

User-Defined Datatypes .................................................................................................................. 13-3Object Types ................................................................................................................................ 13-4Collection Types ....................................................................................................................... 13-11

xiii

Application Interfaces.................................................................................................................... 13-13SQL.............................................................................................................................................. 13-13PL/SQL ...................................................................................................................................... 13-13Pro*C/C++................................................................................................................................. 13-14OCI.............................................................................................................................................. 13-14OTT ............................................................................................................................................. 13-15JPublisher ................................................................................................................................... 13-15JDBC............................................................................................................................................ 13-16SQLJ ............................................................................................................................................ 13-16

14 Object Views

Introduction to Object Views ......................................................................................................... 14-2Advantages of Object Views ..................................................................................................... 14-2

Defining Object Views..................................................................................................................... 14-3Using Object Views .......................................................................................................................... 14-4Updating Object Views.................................................................................................................... 14-5

Updating Nested Table Columns in Views ............................................................................ 14-5

Part V Data Access

15 SQL and PL/SQL

Introduction to Structured Query Language ............................................................................... 15-2SQL Statements ........................................................................................................................... 15-3Identifying Nonstandard SQL .................................................................................................. 15-6Recursive SQL ............................................................................................................................. 15-7Cursors ......................................................................................................................................... 15-7Shared SQL .................................................................................................................................. 15-7Parsing.......................................................................................................................................... 15-8

SQL Processing.................................................................................................................................. 15-9Overview of SQL Statement Execution ................................................................................... 15-9DML Statement Processing ..................................................................................................... 15-11DDL Statement Processing ...................................................................................................... 15-15Controlling Transactions ......................................................................................................... 15-15

PL/SQL .............................................................................................................................................. 15-16

xiv

How PL/SQL Executes............................................................................................................ 15-16Language Constructs for PL/SQL ......................................................................................... 15-18Stored Procedures..................................................................................................................... 15-19External Procedures ................................................................................................................. 15-21

PL/SQL Server Pages...................................................................................................................... 15-21

16 Transaction Management

Introduction to Transactions........................................................................................................... 16-2Statement Execution and Transaction Control....................................................................... 16-3Statement-Level Rollback .......................................................................................................... 16-4

Oracle and Transaction Management ........................................................................................... 16-5Committing Transactions .......................................................................................................... 16-5Rolling Back Transactions ......................................................................................................... 16-6Savepoints.................................................................................................................................... 16-7The Two-Phase Commit Mechanism....................................................................................... 16-8

Discrete Transaction Management ................................................................................................ 16-9Autonomous Transactions............................................................................................................. 16-10

Autonomous PL/SQL Blocks ................................................................................................. 16-10Transaction Control Statements in Autonomous Blocks .................................................... 16-11

17 Procedures and Packages

Introduction to Stored Procedures and Packages ....................................................................... 17-2Stored Procedures and Functions ............................................................................................ 17-2Packages....................................................................................................................................... 17-4

Procedures and Functions ............................................................................................................... 17-6Definer Rights and Invoker Rights .......................................................................................... 17-7Benefits of Procedures................................................................................................................ 17-8Procedure Guidelines............................................................................................................... 17-10Anonymous PL/SQL Blocks versus Stored Procedures..................................................... 17-11Standalone Procedures ............................................................................................................ 17-11Dependency Tracking for Stored Procedures ...................................................................... 17-11External Procedures ................................................................................................................. 17-12

Packages............................................................................................................................................ 17-12Benefits of Packages ................................................................................................................. 17-16Dependency Tracking for Packages....................................................................................... 17-17

xv

Oracle Supplied Packages ....................................................................................................... 17-17How Oracle Stores Procedures and Packages............................................................................ 17-17

Compiling Procedures and Packages .................................................................................... 17-17Storing the Compiled Code in Memory ................................................................................ 17-18Storing Procedures or Packages in Database........................................................................ 17-18

How Oracle Executes Procedures and Packages ....................................................................... 17-19Verifying User Access .............................................................................................................. 17-19Verifying Procedure Validity.................................................................................................. 17-19Executing a Procedure ............................................................................................................. 17-20

18 Advanced Queuing

Introduction to Message Queuing ................................................................................................. 18-2Oracle Advanced Queuing .............................................................................................................. 18-3

Queuing Models.......................................................................................................................... 18-4Queuing Entities ......................................................................................................................... 18-5Features of Advanced Queuing................................................................................................ 18-9

19 Triggers

Introduction to Triggers ................................................................................................................... 19-2How Triggers Are Used............................................................................................................. 19-3

Parts of a Trigger ............................................................................................................................... 19-6Triggering Event or Statement.................................................................................................. 19-7Trigger Restriction ...................................................................................................................... 19-8Trigger Action ............................................................................................................................. 19-8

Types of Triggers ............................................................................................................................... 19-9Row Triggers and Statement Triggers ..................................................................................... 19-9BEFORE and AFTER Triggers ................................................................................................ 19-10INSTEAD OF Triggers ............................................................................................................. 19-13Triggers on System Events and User Events ........................................................................ 19-19

Trigger Execution ............................................................................................................................ 19-22The Execution Model for Triggers and Integrity Constraint Checking............................ 19-22Data Access for Triggers .......................................................................................................... 19-24Storage of PL/SQL Triggers.................................................................................................... 19-26Execution of Triggers ............................................................................................................... 19-26Dependency Maintenance for Triggers ................................................................................. 19-26

xvi

20 Oracle Dependency Management

Introduction to Dependency Issues .............................................................................................. 20-2Resolving Schema Object Dependencies .................................................................................... 20-4

Compiling Views and PL/SQL Program Units ..................................................................... 20-5Function-Based Index Dependencies....................................................................................... 20-7

Dependency Management and Nonexistent Schema Objects ................................................. 20-8Shared SQL Dependency Management ..................................................................................... 20-10Local and Remote Dependency Management........................................................................... 20-10

Managing Local Dependencies............................................................................................... 20-10Managing Remote Dependencies........................................................................................... 20-11

Part VI Optimization of SQL Statements

21 The Optimizer

Introduction to Optimization ......................................................................................................... 21-2Execution Plans........................................................................................................................... 21-2Execution Order.......................................................................................................................... 21-6Optimizer Plan Stability ............................................................................................................ 21-7

Cost-Based Optimization ................................................................................................................ 21-8Goal of the Cost-Based Approach ............................................................................................ 21-8Statistics for Cost-Based Optimization.................................................................................... 21-9When to Use the Cost-Based Approach ................................................................................ 21-17

Extensible Optimization................................................................................................................ 21-18User-Defined Statistics............................................................................................................. 21-19User-Defined Selectivity .......................................................................................................... 21-19User-Defined Costs................................................................................................................... 21-19

Rule-Based Optimization.............................................................................................................. 21-20

Part VII Parallel SQL and Direct-Load INSERT

22 Direct-Load INSERT

Introduction to Direct-Load INSERT............................................................................................ 22-2Advantages of Direct-Load INSERT........................................................................................ 22-2INSERT ... SELECT Statements................................................................................................. 22-3

xvii

Varieties of Direct-Load INSERT Statements.............................................................................. 22-3Serial and Parallel INSERT........................................................................................................ 22-3Logging Mode ............................................................................................................................. 22-5

Additional Considerations for Direct-Load INSERT................................................................. 22-8Index Maintenance ..................................................................................................................... 22-8Space Considerations ................................................................................................................. 22-9Locking Considerations ........................................................................................................... 22-10

Restrictions on Direct-Load INSERT .......................................................................................... 22-10

23 Parallel Execution of SQL Statements

Introduction to Parallel Execution of SQL Statements.............................................................. 23-2Operations That Can Be Parallelized ....................................................................................... 23-2How Oracle Parallelizes Operations ........................................................................................ 23-3

Process Architecture for Parallel Execution ................................................................................. 23-5The Parallel Execution Server Pool .......................................................................................... 23-7How Parallel Execution Servers Communicate ..................................................................... 23-9Parallelizing SQL Statements .................................................................................................. 23-10

Setting the Degree of Parallelism ................................................................................................ 23-15How Oracle Determines the Degree of Parallelism for Operations .................................. 23-16Balancing the Work Load ........................................................................................................ 23-19Parallelization Rules for SQL Statements.............................................................................. 23-20

Parallel Query .................................................................................................................................. 23-28Parallel Queries on Index-Organized Tables........................................................................ 23-29Parallel Queries on Object Types............................................................................................ 23-30

Parallel DDL .................................................................................................................................... 23-31DDL Statements That Can Be Parallelized............................................................................ 23-31CREATE TABLE ... AS SELECT in Parallel........................................................................... 23-32Recoverability and Parallel DDL............................................................................................ 23-33Space Management for Parallel DDL..................................................................................... 23-34

Parallel DML.................................................................................................................................... 23-36Advantages of Parallel DML over Manual Parallelism ...................................................... 23-37When to Use Parallel DML...................................................................................................... 23-38Enabling Parallel DML............................................................................................................. 23-39Transaction Model for Parallel DML ..................................................................................... 23-40Recovery for Parallel DML...................................................................................................... 23-41

xviii

Space Considerations for Parallel DML ................................................................................ 23-42Lock and Enqueue Resources for Parallel DML .................................................................. 23-43Restrictions on Parallel DML .................................................................................................. 23-44

Parallel Execution of Functions.................................................................................................... 23-47Affinity .............................................................................................................................................. 23-49

Affinity and Parallel Queries .................................................................................................. 23-49Affinity and Parallel DML....................................................................................................... 23-50

Other Types of Parallelism ........................................................................................................... 23-51

Part VIII Data Protection

24 Data Concurrency and Consistency

Introduction to Data Concurrency and Consistency in a Multiuser Environment.............. 24-2Preventable Phenomena and Transaction Isolation Levels.................................................. 24-2Locking Mechanisms.................................................................................................................. 24-3

How Oracle Manages Data Concurrency and Consistency ...................................................... 24-4Multiversion Concurrency Control.......................................................................................... 24-4Statement-Level Read Consistency.......................................................................................... 24-6Transaction-Level Read Consistency....................................................................................... 24-6Read Consistency in the Oracle Parallel Server ..................................................................... 24-7Oracle Isolation Levels............................................................................................................... 24-7Comparing Read Committed and Serializable Isolation .................................................... 24-10Choosing an Isolation Level.................................................................................................... 24-13

How Oracle Locks Data ................................................................................................................. 24-15Transactions and Data Concurrency ..................................................................................... 24-16Deadlocks................................................................................................................................... 24-17Types of Locks........................................................................................................................... 24-19DML Locks ................................................................................................................................ 24-20DDL Locks ................................................................................................................................. 24-29Latches and Internal Locks...................................................................................................... 24-30Explicit (Manual) Data Locking.............................................................................................. 24-32Oracle Lock Management Services ........................................................................................ 24-40

xix

25 Data Integrity

Introduction to Data Integrity ........................................................................................................ 25-2Types of Data Integrity .............................................................................................................. 25-3How Oracle Enforces Data Integrity........................................................................................ 25-4

Introduction to Integrity Constraints............................................................................................ 25-5Advantages of Integrity Constraints........................................................................................ 25-5The Performance Cost of Integrity Constraints...................................................................... 25-7

Types of Integrity Constraints ........................................................................................................ 25-7NOT NULL Integrity Constraints ............................................................................................ 25-7UNIQUE Key Integrity Constraints ......................................................................................... 25-8PRIMARY KEY Integrity Constraints.................................................................................... 25-11Referential Integrity Constraints ............................................................................................ 25-13CHECK Integrity Constraints ................................................................................................. 25-21

The Mechanisms of Constraint Checking.................................................................................. 25-21Default Column Values and Integrity Constraint Checking.............................................. 25-24

Deferred Constraint Checking ..................................................................................................... 25-24Constraint Attributes................................................................................................................ 25-24SET CONSTRAINTS Mode ..................................................................................................... 25-25Unique Constraints and Indexes ............................................................................................ 25-25

Constraint States ............................................................................................................................. 25-26Modifying Constraint States ................................................................................................... 25-27

26 Controlling Database Access

Introduction to Database Security ................................................................................................. 26-2Schemas, Database Users, and Security Domains...................................................................... 26-2User Authentication.......................................................................................................................... 26-3

Authentication by the Operating System................................................................................ 26-4Authentication by the Network................................................................................................ 26-4Authentication by the Oracle Database................................................................................... 26-7Multi-Tier Authentication and Authorization........................................................................ 26-9Authentication by the Secure Socket Layer Protocol........................................................... 26-12Authentication of Database Administrators......................................................................... 26-13

User Tablespace Settings and Quotas ......................................................................................... 26-14Default Tablespace.................................................................................................................... 26-14Temporary Tablespace............................................................................................................. 26-15

xx

Tablespace Access and Quotas ............................................................................................... 26-15The User Group PUBLIC............................................................................................................... 26-16User Resource Limits and Profiles............................................................................................... 26-17

Types of System Resources and Limits ................................................................................. 26-17Profiles........................................................................................................................................ 26-20

Licensing........................................................................................................................................... 26-20Concurrent Usage Licensing................................................................................................... 26-21Named User Licensing............................................................................................................. 26-22

27 Privileges, Roles, and Security Policies

Introduction to Privileges ............................................................................................................... 27-2System Privileges........................................................................................................................ 27-2Schema Object Privileges........................................................................................................... 27-3Table Security Topics ................................................................................................................. 27-5View Security Topics.................................................................................................................. 27-6Procedure Security Topics......................................................................................................... 27-7Type Security Topics ................................................................................................................ 27-12

Roles .................................................................................................................................................. 27-17Common Uses for Roles .......................................................................................................... 27-18The Mechanisms of Roles ........................................................................................................ 27-19Granting and Revoking Roles................................................................................................. 27-19Who Can Grant or Revoke Roles?.......................................................................................... 27-20Naming Roles............................................................................................................................ 27-20Security Domains of Roles and Users.................................................................................... 27-20PL/SQL Blocks and Roles ....................................................................................................... 27-20Data Definition Language Statements and Roles ................................................................ 27-21Predefined Roles ....................................................................................................................... 27-22The Operating System and Roles ........................................................................................... 27-23Roles in a Distributed Environment ...................................................................................... 27-23

Fine-Grained Access Control........................................................................................................ 27-23Dynamic Predicates.................................................................................................................. 27-24Security Policy Example .......................................................................................................... 27-24

Application Context ....................................................................................................................... 27-25

xxi

28 Auditing

Introduction to Auditing ................................................................................................................. 28-2Auditing Features ....................................................................................................................... 28-2Auditing Mechanisms................................................................................................................ 28-4

Statement Auditing .......................................................................................................................... 28-7Privilege Auditing ............................................................................................................................ 28-7Schema Object Auditing ................................................................................................................. 28-8

Schema Object Audit Options for Views and Procedures.................................................... 28-8Focusing Statement, Privilege, and Schema Object Auditing ................................................. 28-9

Auditing Successful and Unsuccessful Statement Executions............................................. 28-9Auditing BY SESSION versus BY ACCESS .......................................................................... 28-10Auditing By User ...................................................................................................................... 28-12

29 Database Recovery

Introduction to Database Recovery ............................................................................................... 29-2Errors and Failures ..................................................................................................................... 29-2

Structures Used for Database Recovery ....................................................................................... 29-6Database Backups ....................................................................................................................... 29-7The Redo Log............................................................................................................................... 29-7Rollback Segments...................................................................................................................... 29-8Control Files................................................................................................................................. 29-8

Rolling Forward and Rolling Back ................................................................................................ 29-8The Redo Log and Rolling Forward......................................................................................... 29-9Rollback Segments and Rolling Back....................................................................................... 29-9

Improving Recovery Performance ............................................................................................... 29-10Performing Recovery in Parallel............................................................................................. 29-11Fast-Start Fault Recovery......................................................................................................... 29-14Masking Failures with Transparent Application Failover.................................................. 29-15

Recovery Manager .......................................................................................................................... 29-16Recovery Catalog ...................................................................................................................... 29-16Parallelization............................................................................................................................ 29-17Report Generation .................................................................................................................... 29-18

Database Archiving Modes........................................................................................................... 29-18NOARCHIVELOG Mode (Media Recovery Disabled) ....................................................... 29-18ARCHIVELOG Mode (Media Recovery Enabled)............................................................... 29-19

xxii

Control Files..................................................................................................................................... 29-22Control File Contents ............................................................................................................... 29-22Multiplexed Control Files........................................................................................................ 29-23

Database Backups ........................................................................................................................... 29-24Whole Database Backups ........................................................................................................ 29-24Partial Database Backups ........................................................................................................ 29-26The Export and Import Utilities ............................................................................................. 29-27Read-Only Tablespaces and Backup...................................................................................... 29-27

Survivability .................................................................................................................................... 29-27Planning for Disaster Recovery .............................................................................................. 29-28Managed Standby Database.................................................................................................... 29-28

Part IX Distributed Databases and Replication

30 Distributed Database Concepts

Introduction to Distributed Database Architecture................................................................... 30-2Homogenous Distributed Database Systems ......................................................................... 30-2Heterogeneous Distributed Database Systems ...................................................................... 30-5Client-Server Database Architecture ....................................................................................... 30-6

Database Links .................................................................................................................................. 30-9What Are Database Links? ...................................................................................................... 30-10Why Use Database Links? ....................................................................................................... 30-13Global Database Names in Database Links .......................................................................... 30-13Names for Database Links ...................................................................................................... 30-15Types of Database Links.......................................................................................................... 30-16Users of Database Links........................................................................................................... 30-17Creation of Database Links: Examples .................................................................................. 30-20Schema Objects and Database Links...................................................................................... 30-21Database Link Restrictions ...................................................................................................... 30-23

Distributed Database Administration ........................................................................................ 30-23Site Autonomy .......................................................................................................................... 30-24Distributed Database Security ................................................................................................ 30-24Auditing Database Links......................................................................................................... 30-31Administration Tools ............................................................................................................... 30-31

Transaction Processing in a Distributed System ...................................................................... 30-33

xxiii

Remote SQL Statements........................................................................................................... 30-34Distributed SQL Statements .................................................................................................... 30-34Shared SQL for Remote and Distributed Statements .......................................................... 30-35Remote Transactions ................................................................................................................ 30-35Distributed Transactions.......................................................................................................... 30-35Two-Phase Commit Mechanism............................................................................................. 30-36Database Link Resolution........................................................................................................ 30-36Schema Object Name Resolution............................................................................................ 30-39Global Name Resolution in Views, Synonyms, and Procedures ....................................... 30-42

Distributed Database Application Development..................................................................... 30-44Transparency in a Distributed Database System ................................................................. 30-44Remote Procedure Calls........................................................................................................... 30-46Distributed Query Optimization ............................................................................................ 30-47

National Language Support .......................................................................................................... 30-47Client-Server Environment...................................................................................................... 30-48Homogeneous Distributed Environment.............................................................................. 30-48Heterogeneous Distributed Environment............................................................................. 30-49

31 Replication

Introduction to Replication............................................................................................................. 31-2Applications That Use Replication................................................................................................ 31-3Replication Objects, Groups, and Sites ........................................................................................ 31-4Types of Replication Environments .............................................................................................. 31-5

Multimaster Replication ............................................................................................................ 31-5Snapshot Replication.................................................................................................................. 31-7Multimaster and Snapshot Hybrid Configurations............................................................. 31-11

Administration Tools for a Replication Environment ............................................................. 31-12Oracle Replication Manager.................................................................................................... 31-13Replication Management API ................................................................................................. 31-14Replication Catalog................................................................................................................... 31-14Distributed Schema Management .......................................................................................... 31-14

Replication Conflicts ...................................................................................................................... 31-15Other Options for Multimaster Replication .............................................................................. 31-16

Synchronous Replication ......................................................................................................... 31-16Procedural Replication............................................................................................................. 31-16

xxiv

Part X Appendix

A Operating System-Specific Information

Index

xxv

xxvi

Send Us Your Comments

Oracle8 i Concepts, Release 8.1.6

Part No. A76965-01

Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of thispublication. Your input is an important part of the information used for revision.

Did you find any errors? Is the information clearly presented? Do you need more information? If so, where? Are the examples correct? Do you need more examples? What features did you like most about this manual?

If you find any errors or have any other suggestions for improvement, please indicate the chapter,section, and page number (if available). You can send comments to us in the following ways:

E-mail - infodev@us.oracle.com FAX - 1-650-506-7228. Attn: Oracle Server Documentation Postal service:

Oracle CorporationServer Documentation Manager500 Oracle ParkwayRedwood Shores, CA 94065USA

If you would like a reply, please give your name, address, and telephone number below.

If you have problems with the software, please contact your local Oracle Support Services.

xxvii

Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of thispublication. Your input is an important part of the information used for revision.

You can send comments to us in the following ways

E-mail - infodev@us.oracle.com FAX -1-650-506-7228. Attn: Oracle Server Documentation Postal service

Oracle CorporationServer Documentation Manager500 Oracle ParkwayRedwood Shores, CA 94065USA

Please provide the following information:

Name:

Title:

Company:

Department:

E-Mail Address:

Postal Address:

Phone Number:

Book Title:

Part Number:

If you like, you can use the following questionnaire to give us feedback:

Did you find any errors? Is the information clearly presented? Do you need more information? If so, where? Are the examples correct? Do you need more examples? What features did you like most about this manual?

If you find any errors or have any other suggestions for improvement, please indicate the chapter,section, and page number (if available).

If you have problems with the software, please contact your local Oracle Support Services.

xxviii

Preface

This two-volume manual describes all features of the Oracle server, an object-relational database management system. It describes how the Oracle serverfunctions and lays a conceptual foundation for much of the practical informationcontained in other Oracle server manuals. Information in this manual applies to theOracle server running on all operating systems.

Oracle8 i and Oracle8 i Enterprise EditionOracle8i Concepts contains information that describes the functionality of theOracle8i (also known as the standard edition) and the Oracle8i Enterprise Editionproducts. Oracle8i and Oracle8i Enterprise Edition have the same basic features.However, several advanced features are available only with the Enterprise Edition,and some of these are optional. For example, to use application failover, you musthave the Enterprise Edition and the Paralle