Oracle 8i

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 Reheld, Cathy Baird

Contributing Authors: Lance Ashdown, Steve Bobrowski, Cynthia Chin-Lee, Cindy Closkey, Bill Creekbaum, Jason Durbin, John Frazzini, Richard Mateosian, Denis Raphaely, John Russell, Danny Sokolsky, Randy Urbano Contributors: Richard Allen, David Anderson, Andre Bakker, Mark Bauer, Ruth Baylis, Bill Bridge, Atif Chaudry, Jeff Cohen, Michele Cyran, Benoit Dageville, Mary Ann Davidson, Sandy Dreskin, Ahmed Ezzat, Jim Finnerty, Diana Lorentz, Anurag Gupta, Gary Hallmark, Michael Hartstein, Terry Hayes, Alex Ho, Chin Hong, Ken Jacobs, Sandeep Jain, Amit Jasuja, Hakan Jakobsson, Bob Jenkins, Ashok Joshi, Mohan Kamath, Jonathan Klein, R. Kleinro, Robert Kooi, Vishu Krishnamurthy, Muralidhar Krishnaprasad, Andre Kruglikov, Tirthankar Lahiri, Juan Loaiza, Brom Mahbod, William Maimone, Andrew Mendelsohn, Reza Monajjemi, Mark Moore, Rita Moran, Bhagat Nainani, Denise Oertel, Bruce Olsen, 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, Alex Tsukerman, 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 other inherently dangerous applications. It shall be the licensees responsibility to take all appropriate failsafe, backup, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and Oracle disclaims liability for any damages caused by such use of the Programs. The Programs (which include both the software and documentation) contain proprietary information of Oracle Corporation; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited. The information contained in this document is subject to change without notice. If you nd any problems in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this document is error free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation. If the Programs are delivered to the U.S. Government or anyone licensing or using the Programs on behalf of the U.S. Government, the following notice is applicable: Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial computer software" and use, duplication, and disclosure of the Programs including documentation, shall be 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 Oracle Parkway, Redwood City, CA 94065. Oracle is a registered trademark, and Net8, Oracle Call Interface, Oracle7, Oracle8, Oracle8i, Oracle Designer, 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 names mentioned are used for identication purposes only and may be trademarks of their respective owners.

ContentsSend Us Your Comments .............................................................................................................. xxvii Preface........................................................................................................................................................ xxix Part I 1 What Is Oracle?

Introduction to the Oracle ServerIntroduction to Databases and Information Management......................................................... The Oracle Server ......................................................................................................................... Database Structure and Space Management ................................................................................ Logical Database Structures........................................................................................................ Physical Database Structures ...................................................................................................... Memory Structure and Processes................................................................................................... Memory Structures..................................................................................................................... Process Architecture................................................................................................................... The Program Interface ............................................................................................................... An Example of How Oracle Works.......................................................................................... The Object-Relational Model for Database Management........................................................ The Relational Model ................................................................................................................. The Object-Relational Model .................................................................................................... Schemas and Schema Objects ................................................................................................... The Data Dictionary ................................................................................................................... Data Concurrency and Consistency .............................................................................................. Concurrency ................................................................................................................................ 1-2 1-4 1-5 1-5 1-8 1-12 1-12 1-15 1-19 1-19 1-20 1-21 1-21 1-22 1-29 1-29 1-29

v

Read Consistency........................................................................................................................ Locking Mechanisms.................................................................................................................. Distributed Processing and Distributed Databases................................................................... Client-Server Architecture: Distributed Processing .............................................................. Multi-Tier Architecture: Application Servers......................................................................... Distributed Databases ................................................................................................................ Startup and Shutdown Operations................................................................................................ Database Security.............................................................................................................................. Security Mechanisms.................................................................................................................. Privileges...................................................................................................................................... Database Backup and Recovery ..................................................................................................... Why Is Recovery Important? .................................................................................................... Types of Failures ......................................................................................................................... Structures Used for Recovery ................................................................................................... Basic Recovery Steps .................................................................................................................. Recovery Manager ...................................................................................................................... Data Access......................................................................................................................................... SQLThe Structured Query Language .................................................................................. Transactions................................................................................................................................. PL/SQL ........................................................................................................................................ Data Integrity...............................................................................................................................

1-30 1-31 1-32 1-32 1-33 1-34 1-36 1-37 1-38 1-40 1-44 1-44 1-44 1-46 1-49 1-50 1-50 1-51 1-52 1-55 1-57

Part II 2

Database Structures

The Data DictionaryIntroduction to the Data Dictionary................................................................................................ The Structure of the Data Dictionary ......................................................................................... SYS, the Owner of the Data Dictionary ..................................................................................... How the Data Dictionary Is Used .................................................................................................... How Oracle Uses the Data Dictionary....................................................................................... How Users and DBAs Can Use the Data Dictionary............................................................... The Dynamic Performance Tables................................................................................................... 2-2 2-3 2-3 2-3 2-4 2-5 2-7

vi

3

Tablespaces and DatafilesIntroduction to Databases, Tablespaces, and Datales ............................................................... Allocating More Space for a Database....................................................................................... Tablespaces........................................................................................................................................... The SYSTEM Tablespace ............................................................................................................. Using Multiple Tablespaces ........................................................................................................ Space Management in Tablespaces............................................................................................ Online and Offline Tablespaces.................................................................................................. Read-Only Tablespaces.............................................................................................................. Temporary Tablespaces ............................................................................................................. Transporting Tablespaces between Databases....................................................................... Datales .............................................................................................................................................. Datafile Contents ........................................................................................................................ Size of Datafiles........................................................................................................................... Offline Datafiles .......................................................................................................................... Temporary Datafiles................................................................................................................... 3-2 3-3 3-6 3-6 3-7 3-7 3-9 3-11 3-12 3-14 3-16 3-17 3-17 3-17 3-18

4

Data Blocks, Extents, and SegmentsIntroduction to Data Blocks, Extents, and Segments................................................................... Data Blocks .......................................................................................................................................... Data Block Format ........................................................................................................................ An Introduction to PCTFREE, PCTUSED, and Row Chaining.............................................. Extents ................................................................................................................................................. When Extents Are Allocated..................................................................................................... Determining the Number and Size of Extents........................................................................ How Extents Are Allocated....................................................................................................... When Extents Are Deallocated ................................................................................................. Segments............................................................................................................................................. Data Segments............................................................................................................................. Index Segments ........................................................................................................................... Temporary Segments ................................................................................................................. Rollback Segments...................................................................................................................... 4-2 4-3 4-3 4-5 4-12 4-12 4-12 4-14 4-15 4-18 4-18 4-19 4-19 4-21

vii

Part III The Oracle Instance 5 Database and Instance Startup and ShutdownIntroduction to an Oracle Instance .................................................................................................. The Instance and the Database ................................................................................................... Connecting with Administrator Privileges............................................................................... Parameter Files.............................................................................................................................. Instance and Database Startup......................................................................................................... Starting an Instance ...................................................................................................................... Mounting a Database .................................................................................................................. Opening a Database...................................................................................................................... Database and Instance Shutdown ................................................................................................. Closing a Database ..................................................................................................................... Dismounting a Database............................................................................................................ Shutting Down an Instance ....................................................................................................... 5-2 5-3 5-3 5-4 5-5 5-6 5-6 5-8 5-10 5-10 5-11 5-11

6

Distributed ProcessingIntroduction to Oracle Client/Server Architecture....................................................................... Distributed Processing....................................................................................................................... Net8........................................................................................................................................................ How Net8 Works .......................................................................................................................... The Network Listener .................................................................................................................. Multi-Tier Architecture...................................................................................................................... Clients............................................................................................................................................. Application Servers ...................................................................................................................... Database Servers ........................................................................................................................... 6-2 6-2 6-5 6-5 6-6 6-7 6-8 6-8 6-9

7

Memory ArchitectureIntroduction to Oracle Memory Structures.................................................................................... System Global Area (SGA) ............................................................................................................... The Database Buffer Cache.......................................................................................................... The Redo Log Buffer..................................................................................................................... The Shared Pool ............................................................................................................................ 7-2 7-2 7-3 7-6 7-7

viii

The Large Pool ............................................................................................................................ Size of the SGA............................................................................................................................ Controlling the SGAs Use of Memory.................................................................................... Program Global Areas (PGA) ......................................................................................................... Contents of a PGA ...................................................................................................................... Size of a PGA............................................................................................................................... Sort Areas ........................................................................................................................................... Virtual Memory................................................................................................................................. Software Code Areas........................................................................................................................

7-12 7-13 7-14 7-15 7-15 7-16 7-17 7-18 7-18

8

Process ArchitectureIntroduction to Processes .................................................................................................................. Multiple-Process Oracle Systems ............................................................................................... Types of Processes ........................................................................................................................ User Processes...................................................................................................................................... Connections and Sessions............................................................................................................ Oracle Processes ................................................................................................................................. Server Processes............................................................................................................................ Background Processes.................................................................................................................. Trace Files and the ALERT File ................................................................................................ Multi-Threaded Server Conguration.......................................................................................... Dispatcher Request and Response Queues............................................................................. Shared Server Processes............................................................................................................. Artificial Deadlocks.................................................................................................................... Restricted Operations of the Multi-Threaded Server ............................................................ An Example of Oracle Using the Multi-Threaded Server .................................................... Dedicated Server Conguration .................................................................................................... An Example of Oracle Using Dedicated Server Processes ................................................... Pre-Spawned Dedicated Processes .......................................................................................... The Program Interface ..................................................................................................................... Program Interface Structure...................................................................................................... The Program Interface Drivers ................................................................................................. Operating System Communications Software ....................................................................... 8-2 8-2 8-2 8-4 8-4 8-5 8-5 8-5 8-15 8-16 8-17 8-20 8-20 8-21 8-21 8-23 8-25 8-27 8-27 8-27 8-28 8-28

ix

9

Database Resource ManagementIntroduction to the Database Resource Manager ......................................................................... Resource Consumer Groups and Resource Plans......................................................................... What Are Resource Consumer Groups? ................................................................................... What Are Resource Plans?........................................................................................................... Resource Allocation Methods........................................................................................................... CPU Allocation for Resource Plans: Emphasis Method ......................................................... Parallel Degree Limit for Resource Plans: Absolute Method................................................. Resource Plan Directives ................................................................................................................... Examples ............................................................................................................................................... Using Resource Consumer Groups and Resource Plans ........................................................ Using Subplans ............................................................................................................................. Using Multi-Level Resource Plans ........................................................................................... Using the Parallel Degree Limit Resource Plan Directive .................................................... Summary ...................................................................................................................................... Using the Database Resource Manager ........................................................................................ 9-2 9-3 9-3 9-4 9-5 9-5 9-6 9-7 9-7 9-7 9-9 9-10 9-10 9-11 9-11

Part IV 10

The Object-Relational DBMS

Schema ObjectsIntroduction to Schema Objects..................................................................................................... 10-2 Tables................................................................................................................................................... 10-3 How Table Data Is Stored.......................................................................................................... 10-4 Nulls.............................................................................................................................................. 10-8 Default Values for Columns...................................................................................................... 10-9 Nested Tables ............................................................................................................................ 10-10 Temporary Tables ..................................................................................................................... 10-11 Views ................................................................................................................................................. 10-12 Storage for Views...................................................................................................................... 10-13 How Views Are Used............................................................................................................... 10-14 The Mechanics of Views .......................................................................................................... 10-15 Dependencies and Views......................................................................................................... 10-16 Updatable Join Views............................................................................................................... 10-16 Object Views .............................................................................................................................. 10-17

x

Inline Views............................................................................................................................... Materialized Views......................................................................................................................... Refreshing Materialized Views............................................................................................... Materialized View Logs........................................................................................................... Dimensions ...................................................................................................................................... The Sequence Generator ............................................................................................................... Synonyms ......................................................................................................................................... Indexes .............................................................................................................................................. Unique and Nonunique Indexes ............................................................................................ Composite Indexes ................................................................................................................... Indexes and Keys...................................................................................................................... Indexes and Nulls..................................................................................................................... Function-Based Indexes........................................................................................................... How Indexes Are Stored ......................................................................................................... Key Compression...................................................................................................................... Reverse Key Indexes ................................................................................................................ Bitmap Indexes.......................................................................................................................... Index-Organized Tables ................................................................................................................ Benefits of Index-Organized Tables....................................................................................... Index-Organized Tables with Row Overflow Area............................................................. Secondary Indexes on Index-Organized Tables................................................................... Additional Features of Index-Organized Tables.................................................................. Applications of Interest for Index-Organized Tables.......................................................... Application Domain Indexes ....................................................................................................... Indextypes ................................................................................................................................. Domain Indexes ........................................................................................................................ User-Defined Operators .......................................................................................................... Clusters ............................................................................................................................................. Performance Considerations................................................................................................... Format of Clustered Data Blocks............................................................................................ The Cluster Key ........................................................................................................................ The Cluster Index ..................................................................................................................... Hash Clusters................................................................................................................................... How Data Is Stored in a Hash Cluster................................................................................... Hash Key Values.......................................................................................................................

10-17 10-18 10-19 10-19 10-20 10-21 10-22 10-23 10-24 10-24 10-25 10-25 10-26 10-28 10-31 10-33 10-34 10-39 10-41 10-41 10-42 10-42 10-43 10-45 10-46 10-47 10-48 10-49 10-52 10-53 10-53 10-54 10-54 10-55 10-57

xi

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

11

Partitioned Tables and IndexesIntroduction to Partitioning............................................................................................................ 11-2 What Is Partitioning?.................................................................................................................. 11-2 Advantages of Partitioning ....................................................................................................... 11-5 Manual Partitioning with Partition Views............................................................................ 11-11 Basic Partitioning Model ............................................................................................................... 11-11 Range Partitioning .................................................................................................................... 11-13 Hash Partitioning...................................................................................................................... 11-15 Composite Partitioning ............................................................................................................ 11-16 Partition and Subpartition Names ......................................................................................... 11-18 Partitioning and Subpartitioning Columns and Keys......................................................... 11-18 Partition Bounds for Range Partitioning ............................................................................... 11-19 Equipartitioning........................................................................................................................ 11-23 Rules for Partitioning Tables and Indexes................................................................................. 11-26 Table Partitioning ..................................................................................................................... 11-26 Index Partitioning ..................................................................................................................... 11-28 Partitioning of Tables with LOB Columns............................................................................ 11-37 Partitioning Index-Organized Tables and Their Secondary Indexes ................................ 11-41 DML Partition Locks and Subpartition Locks .......................................................................... 11-45 DML Partition Locks ................................................................................................................ 11-45 DML Subpartition Locks ......................................................................................................... 11-46 Performance Considerations for Oracle Parallel Server ..................................................... 11-46 Maintenance Operations ............................................................................................................... 11-47 Partition Maintenance Operations ......................................................................................... 11-47 Managing Indexes..................................................................................................................... 11-59 Privileges for Partitioned Tables and Indexes ...................................................................... 11-62 Auditing for Partitioned Tables and Indexes ....................................................................... 11-63 Partition-Extended and Subpartition-Extended Table Names............................................... 11-63 PARTITION and SUBPARTITION Specifications ............................................................... 11-63 Viewing Partitions or Subpartitions as Tables ..................................................................... 11-64 Using Partition- and Subpartition-Extended Table Names................................................ 11-64

xii

12

Built-In DatatypesIntroduction to Oracle Datatypes .................................................................................................. Character Datatypes ......................................................................................................................... CHAR Datatype .......................................................................................................................... VARCHAR2 and VARCHAR Datatypes ................................................................................ Column Lengths for Character Datatypes and NLS Character Sets ................................... NCHAR and NVARCHAR2 Datatypes .................................................................................. LOB Character Datatypes.......................................................................................................... LONG Datatype.......................................................................................................................... NUMBER Datatype .......................................................................................................................... Internal Numeric Format........................................................................................................... DATE Datatype ............................................................................................................................... Using Julian Dates .................................................................................................................... Date Arithmetic......................................................................................................................... Centuries and the Year 2000 ................................................................................................... LOB Datatypes ................................................................................................................................ BLOB Datatype ......................................................................................................................... CLOB and NCLOB Datatypes ................................................................................................ BFILE Datatype ......................................................................................................................... RAW and LONG RAW Datatypes ............................................................................................... ROWID and UROWID Datatypes............................................................................................... The ROWID Pseudocolumn.................................................................................................... Physical Rowids........................................................................................................................ Logical Rowids.......................................................................................................................... Rowids in Non-Oracle Databases .......................................................................................... ANSI, DB2, and SQL/DS Datatypes .......................................................................................... Data Conversion.............................................................................................................................. 12-2 12-5 12-5 12-6 12-6 12-7 12-7 12-7 12-8 12-9 12-10 12-11 12-11 12-12 12-12 12-13 12-14 12-14 12-15 12-15 12-16 12-16 12-20 12-23 12-23 12-24

13

User-Defined DatatypesIntroduction to User-Dened Datatypes...................................................................................... Complex Data Models ............................................................................................................... Multimedia Datatypes ............................................................................................................... User-Dened Datatypes .................................................................................................................. Object Types ................................................................................................................................ Collection Types ....................................................................................................................... 13-2 13-2 13-3 13-3 13-4 13-11

xiii

Application Interfaces.................................................................................................................... SQL.............................................................................................................................................. PL/SQL ...................................................................................................................................... Pro*C/C++................................................................................................................................. OCI .............................................................................................................................................. OTT ............................................................................................................................................. JPublisher ................................................................................................................................... JDBC............................................................................................................................................ SQLJ ............................................................................................................................................

13-13 13-13 13-13 13-14 13-14 13-15 13-15 13-16 13-16

14

Object ViewsIntroduction to Object Views ......................................................................................................... Advantages of Object Views ..................................................................................................... Dening Object Views..................................................................................................................... Using Object Views .......................................................................................................................... Updating Object Views.................................................................................................................... Updating Nested Table Columns in Views ............................................................................ 14-2 14-2 14-3 14-4 14-5 14-5

Part V 15

Data Access

SQL and PL/SQLIntroduction to Structured Query Language ............................................................................... 15-2 SQL Statements ........................................................................................................................... 15-3 Identifying Nonstandard SQL .................................................................................................. 15-6 Recursive SQL ............................................................................................................................. 15-7 Cursors ......................................................................................................................................... 15-7 Shared SQL .................................................................................................................................. 15-7 Parsing.......................................................................................................................................... 15-8 SQL Processing.................................................................................................................................. 15-9 Overview of SQL Statement Execution ................................................................................... 15-9 DML Statement Processing ..................................................................................................... 15-11 DDL Statement Processing ...................................................................................................... 15-15 Controlling Transactions ......................................................................................................... 15-15 PL/SQL .............................................................................................................................................. 15-16

xiv

How PL/SQL Executes............................................................................................................ Language Constructs for PL/SQL ......................................................................................... Stored Procedures..................................................................................................................... External Procedures ................................................................................................................. PL/SQL Server Pages......................................................................................................................

15-16 15-18 15-19 15-21 15-21

16

Transaction ManagementIntroduction to Transactions........................................................................................................... Statement Execution and Transaction Control....................................................................... Statement-Level Rollback .......................................................................................................... Oracle and Transaction Management ........................................................................................... Committing Transactions .......................................................................................................... Rolling Back Transactions ......................................................................................................... Savepoints.................................................................................................................................... The Two-Phase Commit Mechanism....................................................................................... Discrete Transaction Management ................................................................................................ Autonomous Transactions............................................................................................................. Autonomous PL/SQL Blocks ................................................................................................. Transaction Control Statements in Autonomous Blocks .................................................... 16-2 16-3 16-4 16-5 16-5 16-6 16-7 16-8 16-9 16-10 16-10 16-11

17

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

xv

Oracle Supplied Packages ....................................................................................................... How Oracle Stores Procedures and Packages............................................................................ Compiling Procedures and Packages .................................................................................... Storing the Compiled Code in Memory ................................................................................ Storing Procedures or Packages in Database........................................................................ How Oracle Executes Procedures and Packages ....................................................................... Verifying User Access .............................................................................................................. Verifying Procedure Validity .................................................................................................. Executing a Procedure .............................................................................................................

17-17 17-17 17-17 17-18 17-18 17-19 17-19 17-19 17-20

18

Advanced QueuingIntroduction to Message Queuing................................................................................................. Oracle Advanced Queuing.............................................................................................................. Queuing Models.......................................................................................................................... Queuing Entities ......................................................................................................................... Features of Advanced Queuing................................................................................................ 18-2 18-3 18-4 18-5 18-9

19

TriggersIntroduction to Triggers ................................................................................................................... 19-2 How Triggers Are Used............................................................................................................. 19-3 Parts of a Trigger ............................................................................................................................... 19-6 Triggering Event or Statement.................................................................................................. 19-7 Trigger Restriction ...................................................................................................................... 19-8 Trigger Action ............................................................................................................................. 19-8 Types of Triggers ............................................................................................................................... 19-9 Row Triggers and Statement Triggers ..................................................................................... 19-9 BEFORE and AFTER Triggers ................................................................................................ 19-10 INSTEAD OF Triggers ............................................................................................................. 19-13 Triggers on System Events and User Events ........................................................................ 19-19 Trigger Execution ............................................................................................................................ 19-22 The Execution Model for Triggers and Integrity Constraint Checking ............................ 19-22 Data Access for Triggers .......................................................................................................... 19-24 Storage of PL/SQL Triggers.................................................................................................... 19-26 Execution of Triggers ............................................................................................................... 19-26 Dependency Maintenance for Triggers ................................................................................. 19-26

xvi

20

Oracle Dependency ManagementIntroduction to Dependency Issues .............................................................................................. Resolving Schema Object Dependencies .................................................................................... Compiling Views and PL/SQL Program Units ..................................................................... Function-Based Index Dependencies....................................................................................... Dependency Management and Nonexistent Schema Objects................................................. Shared SQL Dependency Management ..................................................................................... Local and Remote Dependency Management........................................................................... Managing Local Dependencies............................................................................................... Managing Remote Dependencies........................................................................................... 20-2 20-4 20-5 20-7 20-8 20-10 20-10 20-10 20-11

Part VI 21

Optimization of SQL Statements

The OptimizerIntroduction to Optimization ......................................................................................................... Execution Plans ........................................................................................................................... Execution Order.......................................................................................................................... Optimizer Plan Stability ............................................................................................................ Cost-Based Optimization ................................................................................................................ Goal of the Cost-Based Approach ............................................................................................ Statistics for Cost-Based Optimization .................................................................................... When to Use the Cost-Based Approach ................................................................................ Extensible Optimization................................................................................................................ User-Defined Statistics............................................................................................................. User-Defined Selectivity .......................................................................................................... User-Defined Costs................................................................................................................... Rule-Based Optimization.............................................................................................................. 21-2 21-2 21-6 21-7 21-8 21-8 21-9 21-17 21-18 21-19 21-19 21-19 21-20

Part VII 22

Parallel SQL and Direct-Load INSERT

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

xvii

Varieties of Direct-Load INSERT Statements.............................................................................. 22-3 Serial and Parallel INSERT........................................................................................................ 22-3 Logging Mode ............................................................................................................................. 22-5 Additional Considerations for Direct-Load INSERT................................................................. 22-8 Index Maintenance ..................................................................................................................... 22-8 Space Considerations ................................................................................................................. 22-9 Locking Considerations ........................................................................................................... 22-10 Restrictions on Direct-Load INSERT .......................................................................................... 22-10

23

Parallel Execution of SQL StatementsIntroduction to Parallel Execution of SQL Statements.............................................................. 23-2 Operations That Can Be Parallelized ....................................................................................... 23-2 How Oracle Parallelizes Operations ........................................................................................ 23-3 Process Architecture for Parallel Execution ................................................................................. 23-5 The Parallel Execution Server Pool .......................................................................................... 23-7 How Parallel Execution Servers Communicate ..................................................................... 23-9 Parallelizing SQL Statements .................................................................................................. 23-10 Setting the Degree of Parallelism ................................................................................................ 23-15 How Oracle Determines the Degree of Parallelism for Operations .................................. 23-16 Balancing the Work Load ........................................................................................................ 23-19 Parallelization Rules for SQL Statements.............................................................................. 23-20 Parallel Query .................................................................................................................................. 23-28 Parallel Queries on Index-Organized Tables........................................................................ 23-29 Parallel Queries on Object Types............................................................................................ 23-30 Parallel DDL .................................................................................................................................... 23-31 DDL Statements That Can Be Parallelized............................................................................ 23-31 CREATE TABLE ... AS SELECT in Parallel........................................................................... 23-32 Recoverability and Parallel DDL ............................................................................................ 23-33 Space Management for Parallel DDL..................................................................................... 23-34 Parallel DML.................................................................................................................................... 23-36 Advantages of Parallel DML over Manual Parallelism ...................................................... 23-37 When to Use Parallel DML...................................................................................................... 23-38 Enabling Parallel DML............................................................................................................. 23-39 Transaction Model for Parallel DML ..................................................................................... 23-40 Recovery for Parallel DML ...................................................................................................... 23-41

xviii

Space Considerations for Parallel DML ................................................................................ Lock and Enqueue Resources for Parallel DML .................................................................. Restrictions on Parallel DML .................................................................................................. Parallel Execution of Functions.................................................................................................... Afnity .............................................................................................................................................. Affinity and Parallel Queries .................................................................................................. Affinity and Parallel DML....................................................................................................... Other Types of Parallelism ...........................................................................................................

23-42 23-43 23-44 23-47 23-49 23-49 23-50 23-51

Part VIII 24

Data Protection

Data Concurrency and ConsistencyIntroduction to Data Concurrency and Consistency in a Multiuser Environment.............. Preventable Phenomena and Transaction Isolation Levels.................................................. Locking Mechanisms.................................................................................................................. How Oracle Manages Data Concurrency and Consistency...................................................... Multiversion Concurrency Control.......................................................................................... Statement-Level Read Consistency.......................................................................................... Transaction-Level Read Consistency....................................................................................... Read Consistency in the Oracle Parallel Server ..................................................................... Oracle Isolation Levels............................................................................................................... Comparing Read Committed and Serializable Isolation .................................................... Choosing an Isolation Level.................................................................................................... How Oracle Locks Data ................................................................................................................. Transactions and Data Concurrency ..................................................................................... Deadlocks................................................................................................................................... Types of Locks........................................................................................................................... DML Locks ................................................................................................................................ DDL Locks ................................................................................................................................. Latches and Internal Locks...................................................................................................... Explicit (Manual) Data Locking.............................................................................................. Oracle Lock Management Services ........................................................................................ 24-2 24-2 24-3 24-4 24-4 24-6 24-6 24-7 24-7 24-10 24-13 24-15 24-16 24-17 24-19 24-20 24-29 24-30 24-32 24-40

xix

25

Data IntegrityIntroduction to Data Integrity ........................................................................................................ 25-2 Types of Data Integrity .............................................................................................................. 25-3 How Oracle Enforces Data Integrity........................................................................................ 25-4 Introduction to Integrity Constraints............................................................................................ 25-5 Advantages of Integrity Constraints........................................................................................ 25-5 The Performance Cost of Integrity Constraints...................................................................... 25-7 Types of Integrity Constraints........................................................................................................ 25-7 NOT NULL Integrity Constraints ............................................................................................ 25-7 UNIQUE Key Integrity Constraints ......................................................................................... 25-8 PRIMARY KEY Integrity Constraints.................................................................................... 25-11 Referential Integrity Constraints ............................................................................................ 25-13 CHECK Integrity Constraints ................................................................................................. 25-21 The Mechanisms of Constraint Checking.................................................................................. 25-21 Default Column Values and Integrity Constraint Checking.............................................. 25-24 Deferred Constraint Checking ..................................................................................................... 25-24 Constraint Attributes................................................................................................................ 25-24 SET CONSTRAINTS Mode ..................................................................................................... 25-25 Unique Constraints and Indexes ............................................................................................ 25-25 Constraint States ............................................................................................................................. 25-26 Modifying Constraint States ................................................................................................... 25-27

26

Controlling Database AccessIntroduction to Database Security................................................................................................. 26-2 Schemas, Database Users, and Security Domains...................................................................... 26-2 User Authentication.......................................................................................................................... 26-3 Authentication by the Operating System................................................................................ 26-4 Authentication by the Network................................................................................................ 26-4 Authentication by the Oracle Database................................................................................... 26-7 Multi-Tier Authentication and Authorization........................................................................ 26-9 Authentication by the Secure Socket Layer Protocol........................................................... 26-12 Authentication of Database Administrators......................................................................... 26-13 User Tablespace Settings and Quotas ......................................................................................... 26-14 Default Tablespace.................................................................................................................... 26-14 Temporary Tablespace............................................................................................................. 26-15

xx

Tablespace Access and Quotas ............................................................................................... The User Group PUBLIC............................................................................................................... User Resource Limits and Proles............................................................................................... Types of System Resources and Limits ................................................................................. Profiles........................................................................................................................................ Licensing........................................................................................................................................... Concurrent Usage Licensing................................................................................................... Named User Licensing.............................................................................................................

26-15 26-16 26-17 26-17 26-20 26-20 26-21 26-22

27

Privileges, Roles, and Security PoliciesIntroduction to Privileges ............................................................................................................... System Privileges........................................................................................................................ Schema Object Privileges........................................................................................................... Table Security Topics ................................................................................................................. View Security Topics.................................................................................................................. Procedure Security Topics......................................................................................................... Type Security Topics ................................................................................................................ Roles .................................................................................................................................................. Common Uses for Roles .......................................................................................................... The Mechanisms of Roles ........................................................................................................ Granting and Revoking Roles................................................................................................. Who Can Grant or Revoke Roles?.......................................................................................... Naming Roles............................................................................................................................ Security Domains of Roles and Users.................................................................................... PL/SQL Blocks and Roles ....................................................................................................... Data Definition Language Statements and Roles ................................................................ Predefined Roles ....................................................................................................................... The Operating System and Roles ........................................................................................... Roles in a Distributed Environment ...................................................................................... Fine-Grained Access Control........................................................................................................ Dynamic Predicates.................................................................................................................. Security Policy Example .......................................................................................................... Application Context ....................................................................................................................... 27-2 27-2 27-3 27-5 27-6 27-7 27-12 27-17 27-18 27-19 27-19 27-20 27-20 27-20 27-20 27-21 27-22 27-23 27-23 27-23 27-24 27-24 27-25

xxi

28

AuditingIntroduction to Auditing ................................................................................................................. 28-2 Auditing Features ....................................................................................................................... 28-2 Auditing Mechanisms ................................................................................................................ 28-4 Statement Auditing .......................................................................................................................... 28-7 Privilege Auditing ............................................................................................................................ 28-7 Schema Object Auditing ................................................................................................................. 28-8 Schema Object Audit Options for Views and Procedures .................................................... 28-8 Focusing Statement, Privilege, and Schema Object Auditing ................................................. 28-9 Auditing Successful and Unsuccessful Statement Executions............................................. 28-9 Auditing BY SESSION versus BY ACCESS .......................................................................... 28-10 Auditing By User ...................................................................................................................... 28-12

29

Database RecoveryIntroduction to Database Recovery ............................................................................................... 29-2 Errors and Failures ..................................................................................................................... 29-2 Structures Used for Database Recovery ....................................................................................... 29-6 Database Backups ....................................................................................................................... 29-7 The Redo Log............................................................................................................................... 29-7 Rollback Segments...................................................................................................................... 29-8 Control Files................................................................................................................................. 29-8 Rolling Forward and Rolling Back................................................................................................ 29-8 The Redo Log and Rolling Forward......................................................................................... 29-9 Rollback Segments and Rolling Back....................................................................................... 29-9 Improving Recovery Performance ............................................................................................... 29-10 Performing Recovery in Parallel............................................................................................. 29-11 Fast-Start Fault Recovery......................................................................................................... 29-14 Masking Failures with Transparent Application Failover.................................................. 29-15 Recovery Manager .......................................................................................................................... 29-16 Recovery Catalog ...................................................................................................................... 29-16 Parallelization............................................................................................................................ 29-17 Report Generation .................................................................................................................... 29-18 Database Archiving Modes........................................................................................................... 29-18 NOARCHIVELOG Mode (Media Recovery Disabled) ....................................................... 29-18 ARCHIVELOG Mode (Media Recovery Enabled)............................................................... 29-19

xxii

Control Files..................................................................................................................................... Control File Contents ............................................................................................................... Multiplexed Control Files........................................................................................................ Database Backups........................................................................................................................... Whole Database Backups ........................................................................................................ Partial Database Backups ........................................................................................................ The Export and Import Utilities ............................................................................................. Read-Only Tablespaces and Backup...................................................................................... Survivability .................................................................................................................................... Planning for Disaster Recovery .............................................................................................. Managed Standby Database....................................................................................................

29-22 29-22 29-23 29-24 29-24 29-26 29-27 29-27 29-27 29-28 29-28

Part IX 30

Distributed Databases and Replication

Distributed Database ConceptsIntroduction to Distributed Database Architecture................................................................... Homogenous Distributed Database Systems ......................................................................... Heterogeneous Distributed Database Systems ...................................................................... Client-Server Database Architecture ....................................................................................... Database Links .................................................................................................................................. What Are Database Links? ...................................................................................................... Why Use Database Links? ....................................................................................................... Global Database Names in Database Links .......................................................................... Names for Database Links ...................................................................................................... Types of Database Links.......................................................................................................... Users of Database Links........................................................................................................... Creation of Database Links: Examples .................................................................................. Schema Objects and Database Links...................................................................................... Database Link Restrictions ...................................................................................................... Distributed Database Administration........................................................................................ Site Autonomy .......................................................................................................................... Distributed Database Security ................................................................................................ Auditing Database Links......................................................................................................... Administration Tools ............................................................................................................... Transaction Processing in a Distributed System ...................................................................... 30-2 30-2 30-5 30-6 30-9 30-10 30-13 30-13 30-15 30-16 30-17 30-20 30-21 30-23 30-23 30-24 30-24 30-31 30-31 30-33

xxiii

Remote SQL Statements........................................................................................................... Distributed SQL Statements .................................................................................................... Shared SQL for Remote and Distributed Statements .......................................................... Remote Transactions ................................................................................................................ Distributed Transactions.......................................................................................................... Two-Phase Commit Mechanism............................................................................................. Database Link Resolution........................................................................................................ Schema Object Name Resolution............................................................................................ Global Name Resolution in Views, Synonyms, and Procedures ....................................... Distributed Database Application Development..................................................................... Transparency in a Distributed Database System ................................................................. Remote Procedure Calls........................................................................................................... Distributed Query Optimization ............................................................................................ National Language Support.......................................................................................................... Client-Server Environment...................................................................................................... Homogeneous Distributed Environment.............................................................................. Heterogeneous Distributed Environment.............................................................................

30-34 30-34 30-35 30-35 30-35 30-36 30-36 30-39 30-42 30-44 30-44 30-46 30-47 30-47 30-48 30-48 30-49

31

ReplicationIntroduction to Replication............................................................................................................. 31-2 Applications That Use Replication................................................................................................ 31-3 Replication Objects, Groups, and Sites........................................................................................ 31-4 Types of Replication Environments .............................................................................................. 31-5 Multimaster Replication ............................................................................................................ 31-5 Snapshot Replication.................................................................................................................. 31-7 Multimaster and Snapshot Hybrid Configurations............................................................. 31-11 Administration Tools for a Replication Environment ............................................................. 31-12 Oracle Replication Manager.................................................................................................... 31-13 Replication Management API ................................................................................................. 31-14 Replication Catalog................................................................................................................... 31-14 Distributed Schema Management .......................................................................................... 31-14 Replication Conicts ...................................................................................................................... 31-15 Other Options for Multimaster Replication.............................................................................. 31-16 Synchronous Replication ......................................................................................................... 31-16 Procedural Replication............................................................................................................. 31-16

xxiv

Part X A

Appendix

Operating System-Specific Information

Index

xxv

xxvi

Send Us Your CommentsOracle8i Concepts, Release 8.1.6Part No. A76965-01

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

Did you nd 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 nd 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:s s s

E-mail - infodev@us.oracle.com FAX - 1-650-506-7228. Attn: Oracle Server Documentation Postal service: Oracle Corporation Server Documentation Manager 500 Oracle Parkway Redwood Shores, CA 94065 USA 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 this publication. Your input is an important part of the information used for revision. You can send comments to us in the following wayss s s

E-mail - infodev@us.oracle.com FAX -1-650-506-7228. Attn: Oracle Server Documentation Postal service Oracle Corporation Server Documentation Manager 500 Oracle Parkway Redwood Shores, CA 94065 USA

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:s s s s s

Did you nd 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 nd 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

PrefaceThis two-volume manual describes all features of the Oracle server, an objectrelational database management system. It describes how the Oracle server functions and lays a conceptual foundation for much of the practical information contained in other Oracle server manuals. Information in this manual applies to the Oracle server running on all operating systems.

Oracle8i and Oracle8i Enterprise EditionOracle8i Concepts contains information that describes the functionality of the Oracle8i (also known as the standard edition) and the Oracle8i Enterprise Edition products. 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 must have the Enterprise Edition and the Parallel Server option. For information about the differences between Oracle8i and the Oracle8i Enterprise Edition and the features and options that are available to you, see Getting to Know Oracle8i.

xxix

AudienceThis manual is written for database administrators, system administrators, and database application developers. What You Should Already Know You should be familiar with relational database concepts and with the operating system environment under which you are running Oracle. As a p