Oracle8

Concepts
Release 8.0
December, 1997
Part No. A58227-01
Oracle8 Concepts
Part No. A58227-01
Release 8.0
Copyright © 1997 Oracle Corporation. All rights reserved.
Primary Author: Lefty Leverenz
Contributors: Richard Allen, David Anderson, Andre Bakker, Steve Bobrowski, Bill Bridge, Atif
Chaudry, Cynthia Chin-Lee, Cindy Closkey, Jeff Cohen, Benoit Dageville, Sandy Dreskin, Jason Durbin,
Ahmed Ezzat, Diana Foch-Lorentz, John Frazzini, Anurag Gupta, Gary Hallmark, Michael Hartstein,
Terry Hayes, Alex Ho, Chin Hong, Ken Jacobs, Sandeep Jain, Amit Jasuja, Hakan Jakobsson, Robert
Jenkins, Jr., Ashok Joshi, Jonathan Klein, R. Kleinro, Robert Kooi, Vishu Krishnamurthy, Andre Kruglikov,
Tirthankar Lahiri, Juan Loaiza, Brom Mahbod, Richard Mateosian, William Maimone, Andrew Mendel-
sohn, Reza Monajjemi, Mark Moore, Rita Moran, Denise Oertel, Mark Porter, Maria Pratt, Tuomas
Pystynen, Patrick Ritto, Hasan Rizvi, Sriram Samu, Hari Sankar, Gordon Smith, Danny Sokolsky, 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 inher-
ently dangerous applications. It shall be licensee's responsibility to take all appropriate fail-safe, back
up, 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 Pro-
grams.
This Program contains proprietary information of Oracle Corporation; it is provided under a license
agreement containing restrictions on use and disclosure and is also protected by copyright patent and
other intellectual property law. Reverse engineering of the software is prohibited.
The information contained in this document is subject to change without notice. If you find any problems

Physical Database Structures.................................................................................................... 1-11
Memory Structure and Processes................................................................................................... 1-13
Memory Structures..................................................................................................................... 1-13
Process Architecture................................................................................................................... 1-16
The Program Interface ............................................................................................................... 1-19
An Example of How Oracle Works.......................................................................................... 1-19
Data Concurrency and Consistency .............................................................................................. 1-20
Concurrency ................................................................................................................................ 1-20
Read Consistency........................................................................................................................ 1-21
Locking Mechanisms.................................................................................................................. 1-22
Distributed Processing and Distributed Databases................................................................... 1-23
Client/Server Architecture: Distributed Processing ............................................................. 1-23
iv
Distributed Databases ................................................................................................................ 1-24
Table Replication ........................................................................................................................ 1-26
Oracle and Net8 .......................................................................................................................... 1-26
Startup and Shutdown Operations................................................................................................ 1-27
Database Security.............................................................................................................................. 1-27
Security Mechanisms.................................................................................................................. 1-28
Trusted Oracle............................................................................................................................. 1-34
Database Backup and Recovery..................................................................................................... 1-34
Why Is Recovery Important? .................................................................................................... 1-34
Types of Failures......................................................................................................................... 1-35
Structures Used for Recovery ................................................................................................... 1-37
Basic Recovery Steps .................................................................................................................. 1-39
The Recovery Manager .............................................................................................................. 1-40
The Object-Relational Model for Database Management........................................................ 1-40
The Relational Model ................................................................................................................. 1-41
The Object-Relational Model..................................................................................................... 1-41
Schemas and Schema Objects.................................................................................................... 1-42

Temporary Tablespaces............................................................................................................. 3-10
Datafiles .............................................................................................................................................. 3-11
Datafile Contents ........................................................................................................................ 3-12
Size of Datafiles........................................................................................................................... 3-12
Offline Datafiles.......................................................................................................................... 3-12
4 The Data Dictionary
An Introduction to the Data Dictionary ......................................................................................... 4-2
The Structure of the Data Dictionary.............................................................................................. 4-2
SYS, the Owner of the Data Dictionary.......................................................................................... 4-3
How the Data Dictionary Is Used.................................................................................................... 4-3
How Oracle Uses the Data Dictionary ...................................................................................... 4-3
How Oracle Users Can Use the Data Dictionary..................................................................... 4-5
The Dynamic Performance Tables................................................................................................... 4-7
vi
Part III The Oracle Instance
5 Database and Instance Startup and Shutdown
Overview of an Oracle Instance....................................................................................................... 5-2
The Instance and the Database ................................................................................................... 5-2
Connecting with Administrator Privileges............................................................................... 5-3
Parameter Files.............................................................................................................................. 5-4
Instance and Database Startup......................................................................................................... 5-5
Starting an Instance ...................................................................................................................... 5-5
Mounting a Database ................................................................................................................... 5-6
Opening a Database...................................................................................................................... 5-7
Database and Instance Shutdown ................................................................................................... 5-8
Closing a Database ....................................................................................................................... 5-8
Dismounting a Database.............................................................................................................. 5-8
Shutting Down an Instance ......................................................................................................... 5-9
6 Memory Structures
Introduction to Oracle Memory Structures.................................................................................... 6-2

The Program Interface Drivers ................................................................................................. 7-27
Operating System Communications Software....................................................................... 7-28
Part IV The Object-Relational DBMS
8 Schema Objects
Overview of Schema Objects ........................................................................................................... 8-2
Tables..................................................................................................................................................... 8-3
How Table Data Is Stored............................................................................................................ 8-4
Nulls ............................................................................................................................................... 8-7
Default Values for Columns........................................................................................................ 8-8
Nested Tables................................................................................................................................ 8-9
Views................................................................................................................................................... 8-10
Storage for Views........................................................................................................................ 8-11
How Views Are Used ................................................................................................................ 8-11
The Mechanics of Views ............................................................................................................ 8-12
Dependencies and Views .......................................................................................................... 8-13
viii
Updatable Join Views................................................................................................................. 8-13
Object Views................................................................................................................................ 8-14
The Sequence Generator ................................................................................................................. 8-14
Synonyms ........................................................................................................................................... 8-15
Indexes ................................................................................................................................................ 8-17
Unique and Non-Unique Indexes ............................................................................................ 8-17
Composite Indexes ..................................................................................................................... 8-18
Indexes and Keys........................................................................................................................ 8-19
How Indexes Are Stored............................................................................................................ 8-19
Reverse Key Indexes................................................................................................................... 8-22
Bitmap Indexes............................................................................................................................ 8-23
Index-Organized Tables................................................................................................................... 8-28
Benefits of Index-Organized Tables......................................................................................... 8-29
Index-Organized Tables with Row Overflow Area............................................................... 8-29

Table Partitioning ....................................................................................................................... 9-21
Index Partitioning....................................................................................................................... 9-22
DML Partition Locks........................................................................................................................ 9-30
Performance Considerations for Oracle Parallel Server ....................................................... 9-31
Maintenance Operations ................................................................................................................. 9-31
Partition Maintenance Operations ........................................................................................... 9-32
Managing Indexes ...................................................................................................................... 9-38
Privileges for Partitioned Tables and Indexes........................................................................ 9-41
Auditing for Partitioned Tables and Indexes ......................................................................... 9-42
SQL Extension: Partition-Extended Table Name........................................................................ 9-42
Examples of Partition-Extended Table Names....................................................................... 9-43
10 Built-In Datatypes
Oracle Datatypes............................................................................................................................... 10-2
Character Datatypes................................................................................................................... 10-2
NUMBER Datatype.................................................................................................................... 10-5
DATE Datatype........................................................................................................................... 10-7
LOB Datatypes ............................................................................................................................ 10-9
RAW and LONG RAW Datatypes......................................................................................... 10-11
ROWID Datatype...................................................................................................................... 10-12
MLSLABEL Datatype............................................................................................................... 10-16
Summary of Oracle Datatype Information ........................................................................... 10-17
ANSI, DB2, and SQL/DS Datatypes ........................................................................................... 10-19
Data Conversion.............................................................................................................................. 10-20
x
11 User-Defined Datatypes (Objects Option)
Introduction ....................................................................................................................................... 11-2
Complex Data Models................................................................................................................ 11-2
Multimedia Datatypes ............................................................................................................... 11-3
User-Defined Datatypes................................................................................................................... 11-3
Object Types ................................................................................................................................ 11-4

Privileges on Type Access and Object Access ...................................................................... 12-12
Dependencies and Incomplete Types......................................................................................... 12-13
Completing Incomplete Types ............................................................................................... 12-14
Type Dependencies of Tables ................................................................................................. 12-15
Import/Export of User-Defined Types......................................................................................... 12-15
13 Object Views
Introduction ....................................................................................................................................... 13-2
Advantages of Object Views..................................................................................................... 13-2
Defining Object Views..................................................................................................................... 13-2
Using Object Views.......................................................................................................................... 13-4
Updating Object Views ................................................................................................................... 13-4
Part V Data Access
14 SQL and PL/SQL
Structured Query Language (SQL)................................................................................................ 14-2
SQL Statements........................................................................................................................... 14-3
Identifying Nonstandard SQL.................................................................................................. 14-6
Recursive SQL............................................................................................................................. 14-6
Cursors ......................................................................................................................................... 14-6
Shared SQL.................................................................................................................................. 14-7
Parsing.......................................................................................................................................... 14-7
SQL Processing.................................................................................................................................. 14-8
Overview of SQL Statement Execution................................................................................... 14-8
DML Statement Processing ..................................................................................................... 14-10
DDL Statement Processing...................................................................................................... 14-14
Controlling Transactions ......................................................................................................... 14-14
PL/SQL.............................................................................................................................................. 14-15
How PL/SQL Executes............................................................................................................ 14-15
Language Constructs for PL/SQL ......................................................................................... 14-17
Stored Procedures..................................................................................................................... 14-18
External Procedures ................................................................................................................. 14-19

Benefits of Packages ................................................................................................................. 17-13
Dependency Tracking for Packages....................................................................................... 17-14
xiii
How Oracle Stores Procedures and Packages ........................................................................... 17-15
Compiling Procedures and Packages .................................................................................... 17-15
Storing the Compiled Code in Memory................................................................................ 17-15
Storing Procedures or Packages in Database........................................................................ 17-15
How Oracle Executes Procedures and Packages....................................................................... 17-16
Verifying User Access.............................................................................................................. 17-16
Verifying Procedure Validity.................................................................................................. 17-16
Executing a Procedure ............................................................................................................. 17-17
18 Database Triggers
An Introduction to Triggers ............................................................................................................ 18-2
How Triggers Are Used............................................................................................................. 18-3
Some Cautionary Notes about Triggers.................................................................................. 18-3
Triggers vs. Declarative Integrity Constraints ....................................................................... 18-5
Parts of a Trigger ............................................................................................................................... 18-5
Triggering Event or Statement.................................................................................................. 18-6
Trigger Restriction...................................................................................................................... 18-7
Trigger Action ............................................................................................................................. 18-7
Types of Triggers............................................................................................................................... 18-7
Row Triggers and Statement Triggers..................................................................................... 18-7
BEFORE and AFTER Triggers .................................................................................................. 18-8
Trigger Combinations................................................................................................................ 18-9
INSTEAD OF Triggers............................................................................................................. 18-11
Trigger Execution ............................................................................................................................ 18-14
The Execution Model for Triggers and Integrity Constraint Checking............................ 18-14
Data Access for Triggers.......................................................................................................... 18-16
Storage of Triggers ................................................................................................................... 18-17
Execution of Triggers ............................................................................................................... 18-17

Transforming ORs into Compound Queries ........................................................................ 20-19
Transforming Complex Statements into Join Statements................................................... 20-22
Optimizing Statements That Access Views .......................................................................... 20-24
Optimizing Compound Queries............................................................................................. 20-36
Optimizing Distributed Statements ....................................................................................... 20-39
Choosing an Optimization Approach and Goal....................................................................... 20-40
The OPTIMIZER_MODE Initialization Parameter.............................................................. 20-40
Statistics in the Data Dictionary.............................................................................................. 20-41
The OPTIMIZER_GOAL Parameter of the ALTER SESSION Command........................ 20-41
The FIRST_ROWS, ALL_ROWS, CHOOSE, and RULE Hints........................................... 20-42
PL/SQL and the Optimizer Goal ........................................................................................... 20-42
xv
Choosing Access Paths................................................................................................................... 20-42
Access Methods ........................................................................................................................ 20-43
Access Paths .............................................................................................................................. 20-45
Choosing Among Access Paths.............................................................................................. 20-58
Optimizing Join Statements ......................................................................................................... 20-63
Join Operations ......................................................................................................................... 20-63
Choosing Execution Plans for Join Statements .................................................................... 20-69
Views in Outer Joins................................................................................................................. 20-72
Optimizing Anti-Joins and Semi-Joins ...................................................................................... 20-74
Optimizing “Star” Queries ........................................................................................................... 20-75
Star Query Example ................................................................................................................. 20-75
Tuning Star Queries ................................................................................................................. 20-75
Star Transformation ................................................................................................................. 20-76
Part VI Parallel SQL and Direct-Load INSERT
21 Direct-Load INSERT
Introduction to Direct-Load INSERT............................................................................................ 21-2
Advantages of Direct-Load INSERT........................................................................................ 21-2
INSERT ... SELECT Statements................................................................................................. 21-3

Recovery for Parallel DML...................................................................................................... 22-34
Space Considerations for Parallel DML ................................................................................ 22-35
Lock and Enqueue Resources for Parallel DML................................................................... 22-36
Restrictions on Parallel DML .................................................................................................. 22-37
Affinity.............................................................................................................................................. 22-40
Other Types of Parallelism............................................................................................................ 22-42
Part VII Data Protection
23 Data Concurrency and Consistency
Data Concurrency and Consistency in a Multiuser Environment .......................................... 23-2
Preventable Phenomena and Transaction Isolation Levels.................................................. 23-2
Locking Mechanisms.................................................................................................................. 23-3
How Oracle Manages Data Concurrency and Consistency...................................................... 23-4
Multiversion Concurrency Control.......................................................................................... 23-4
Statement-Level Read Consistency .......................................................................................... 23-5
Transaction-Level Read Consistency....................................................................................... 23-6
Oracle Isolation Levels............................................................................................................... 23-6
xvii
Setting the Isolation Level ......................................................................................................... 23-7
Comparing Read Committed and Serializable Isolation...................................................... 23-9
Choosing an Isolation Level.................................................................................................... 23-12
How Oracle Locks Data ................................................................................................................. 23-14
Transactions and Data Concurrency ..................................................................................... 23-15
Deadlocks................................................................................................................................... 23-16
Types of Locks........................................................................................................................... 23-18
DML (Data) Locks .................................................................................................................... 23-19
DDL Locks (Dictionary Locks) ............................................................................................... 23-26
Latches and Internal Locks...................................................................................................... 23-28
Explicit (Manual) Data Locking.............................................................................................. 23-29
Oracle Lock Management Services ........................................................................................ 23-40
24 Data Integrity

Temporary Tablespace............................................................................................................... 25-8
Tablespace Access and Quotas ................................................................................................. 25-8
The User Group PUBLIC................................................................................................................. 25-9
User Resource Limits and Profiles............................................................................................... 25-10
Types of System Resources and Limits ................................................................................. 25-10
Profiles........................................................................................................................................ 25-13
Licensing........................................................................................................................................... 25-14
Concurrent Usage Licensing................................................................................................... 25-14
Named User Licensing............................................................................................................. 25-15
26 Privileges and Roles
Privileges ............................................................................................................................................ 26-2
System Privileges........................................................................................................................ 26-2
Schema Object Privileges........................................................................................................... 26-3
Roles .................................................................................................................................................. 26-10
Common Uses for Roles........................................................................................................... 26-11
The Mechanisms of Roles ........................................................................................................ 26-13
Granting and Revoking Roles................................................................................................. 26-13
Who Can Grant or Revoke Roles?.......................................................................................... 26-13
Naming Roles ............................................................................................................................ 26-14
Security Domains of Roles and Users.................................................................................... 26-14
Named PL/SQL Blocks and Roles ......................................................................................... 26-14
Data Definition Language Statements and Roles................................................................. 26-14
Predefined Roles ....................................................................................................................... 26-16
xix
The Operating System and Roles ........................................................................................... 26-16
Roles in a Distributed Environment ...................................................................................... 26-16
27 Auditing
Introduction to Auditing................................................................................................................. 27-2
Auditing Features....................................................................................................................... 27-2
Auditing Mechanisms................................................................................................................ 27-4

Control Files..................................................................................................................................... 28-19
Control File Contents ............................................................................................................... 28-19
Multiplexed Control Files........................................................................................................ 28-20
Database Backups........................................................................................................................... 28-21
Whole Database Backups......................................................................................................... 28-21
Partial Database Backups ........................................................................................................ 28-22
The Export and Import Utilities.............................................................................................. 28-23
Read-Only Tablespaces and Backup...................................................................................... 28-23
Survivability .................................................................................................................................... 28-24
Planning for Disaster Recovery .............................................................................................. 28-24
Standby Database ..................................................................................................................... 28-24
Part VIII Distributed Processing and Distributed Databases
29 Distributed Processing
Oracle Client/Server Architecture.................................................................................................. 29-2
Distributed Processing..................................................................................................................... 29-2
Net8...................................................................................................................................................... 29-5
How Net8 Works ........................................................................................................................ 29-5
30 Distributed Databases
Oracle’s Distributed Database Architecture................................................................................ 30-2
Clients and Servers..................................................................................................................... 30-2
The Network................................................................................................................................ 30-4
Databases and Database Links.................................................................................................. 30-4
Database Links ............................................................................................................................ 30-6
Schema Object Name Resolution.............................................................................................. 30-6
Connecting Between Oracle Server Versions ......................................................................... 30-7
Distributed Databases and Distributed Processing............................................................... 30-7
Distributed Databases and Database Replication.................................................................. 30-7
Heterogeneous Distributed Databases......................................................................................... 30-8
xxi
Transparent SQL Access............................................................................................................ 30-8

Replication Objects, Groups, Sites, and Catalogs ................................................................ 31-17
Oracle’s Advanced Replication Architecture ....................................................................... 31-19
Replication Administrators, Propagators, and Receivers................................................... 31-22
xxii
Replication Conflicts ................................................................................................................ 31-22
Unique Advanced Replication Options................................................................................. 31-26
Part IX Appendix
A Operating System-Specific Information
Index
xxiii
Send Us Your Comments
Oracle8 Concepts, Release 8.0
Part No. A58227-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.
■
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:
■
electronic mail - [email protected]
■