Oracle9i Database Concepts

738 Pages·2001·6.522 MB·English

Oracle9i
Database Concepts
Release 1 (9.0.1)
July 2001
Part No. A88856-02
Oracle9i Database Concepts, Release 1 (9.0.1)
Part No. A88856-02
Copyright © 2001, Oracle Corporation. All rights reserved.
Primary Author: Lenore McGee Luscher
Contributors: Patrick Amor, Lance Ashdown, Cathy Baird, Lee Barton, Mehul Bastawala, Mark Bauer,
Ruth Baylis, Neerja Bhatt, Allen Brumm, Ted Burroughs, Chandra Chandrasekar, Gary Chen, Eugene
Chong, Michele Cyran, Amit Ganesh, John Haydu, Brian Hirano, Thuvan Hoang, Bob Jenkins, Vishy
Karra, Ravikanth Kasamsetty, Susan Kotsovolos, Julie Laffrenzen, Tirthankar Lahiri, Paul Lane, Simon
Law, Yunrui Li, Diana Lorentz, Karen McKeen, Ben Meng, Magdi Morsi, Ari Mozes, Sreedhar
Mukkamalla, Subramanian Muralidhar, Ravi Murthy, Sujatha Muthulingam, Bhagat Nainani, Gary Ngai,
Jeffrey Olkin, Kant Patel, Ananth Raghavan, Jack Raitto, Beck Reitmeyer, Ann Rhee, John Russell, Vivian
Schupmann, Ravi Shankar, Lei Sheng, Mark Smith, Richard Smith, Ekrem Soylemez, Marie St. Gelais,
James Stenoish, Debbie Steiner, Sankar Subramanian, Seema Sundara, Bob Thome, Ashish Thusoo,
Anh-Tuan Tran, Stephen J. Vivian, Daniel Wong, Adiel Yoaz, Qin Yu
Graphic Designer: Valarie Moore
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 find 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.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and Oracle Net Services, Oracle Call Interface, Oracle7, Oracle8,
Oracle8i, Oracle9i, Oracle Designer, Oracle Enterprise Manager, Oracle Forms, Oracle Parallel Server,
Oracle Secure Network Services, Oracle Server Manager, Real Application Clusters, 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. Other names may be trademarks of their respective
owners.
Contents
Send Us Your Comments................................................................................................................. xxi
Preface......................................................................................................................................................... xxiii
Part I What Is Oracle?
1 Introduction to the Oracle Server
Introduction to Databases and Information Management......................................................... 1-2
The Oracle Server......................................................................................................................... 1-4
Database Structure and Space Management................................................................................. 1-5
Logical Database Structures........................................................................................................ 1-5
Physical Database Structures...................................................................................................... 1-8
Memory Structure and Processes................................................................................................... 1-12
Memory Structures..................................................................................................................... 1-12
Process Architecture................................................................................................................... 1-15
The Program Interface Mechanism.......................................................................................... 1-19
An Example of How Oracle Works......................................................................................... 1-19
Processes and Threads on Windows NT................................................................................. 1-21
The Object-Relational Model for Database Management........................................................ 1-21
The Relational Model................................................................................................................. 1-21
The Object-Relational Model.................................................................................................... 1-22
Type Inheritance......................................................................................................................... 1-22
Description of Schemas and Schema Objects......................................................................... 1-24
Data Dictionary........................................................................................................................... 1-31
iii
Data Concurrency and Consistency Overview........................................................................... 1-31
Concurrency................................................................................................................................ 1-31
Read Consistency........................................................................................................................ 1-32
Locking Mechanisms.................................................................................................................. 1-33
Quiesce Database........................................................................................................................ 1-34
Startup and Shutdown Operations............................................................................................... 1-35
2 Oracle Server Features
Internet Computing and Distributed Databases.......................................................................... 2-2
Client/Server Architecture: Distributed Processing............................................................... 2-2
Multitier Architecture: Application Servers............................................................................. 2-3
Distributed Databases.................................................................................................................. 2-3
Replication..................................................................................................................................... 2-5
Heterogeneous Services............................................................................................................... 2-7
Advanced Queuing Overview.......................................................................................................... 2-8
Uses for Message Queuing.......................................................................................................... 2-8
Advantages of Oracle Advanced Queuing............................................................................... 2-9
Data Warehousing Overview.......................................................................................................... 2-10
Data Access Overview...................................................................................................................... 2-11
SQL—The Structured Query Language.................................................................................. 2-11
Transactions................................................................................................................................. 2-13
PL/SQL—Procedural Language Extension to SQL............................................................... 2-15
Data Integrity.............................................................................................................................. 2-17
Database Security Overview.......................................................................................................... 2-19
Security Mechanisms.................................................................................................................. 2-20
Privileges...................................................................................................................................... 2-21
Database Backup and Recovery Overview.................................................................................. 2-25
Why Recovery Is Important...................................................................................................... 2-25
Types of Failures......................................................................................................................... 2-25
Structures Used for Recovery................................................................................................... 2-27
Basic Recovery Steps.................................................................................................................. 2-30
Transparent Application Failover (TAF)................................................................................. 2-31
Recovery Manager...................................................................................................................... 2-32
Instance Recovery Tuning......................................................................................................... 2-32
Introduction to Oracle9i Data Guard............................................................................................ 2-34
iv
Background of Oracle9i Data Guard....................................................................................... 2-34
Overview of Oracle9i Data Guard........................................................................................... 2-37
How the Data Guard Components Work Together.............................................................. 2-39
Disaster Recovery Server and DRMON.................................................................................. 2-41
Limiting Data Loss..................................................................................................................... 2-42
LogMiner SQL-Based Log Analyzer Overview.......................................................................... 2-43
Part II Database Structures
3 Data Blocks, Extents, and Segments
Introduction to Data Blocks, Extents, and Segments................................................................... 3-2
Data Blocks Overview....................................................................................................................... 3-3
Data Block Format........................................................................................................................ 3-4
Free Space Management.............................................................................................................. 3-6
PCTFREE, PCTUSED, and Row Chaining................................................................................ 3-8
Extents Overview.............................................................................................................................. 3-13
When Extents Are Allocated..................................................................................................... 3-13
Determine the Number and Size of Extents........................................................................... 3-13
How Extents Are Allocated....................................................................................................... 3-14
When Extents Are Deallocated................................................................................................. 3-16
Segments Overview.......................................................................................................................... 3-18
Introduction to Data Segments................................................................................................. 3-19
Introduction to Index Segments............................................................................................... 3-20
Introduction to Temporary Segments..................................................................................... 3-20
Automatic Undo Management................................................................................................. 3-22
Introduction to Rollback Segments.......................................................................................... 3-24
4 Tablespaces, Datafiles, and Control Files
Introduction to Tablespaces, Datafiles, and Control Files.......................................................... 4-2
Allocate More Space for a Database.......................................................................................... 4-3
Tablespaces Overview........................................................................................................................ 4-7
The SYSTEM Tablespace............................................................................................................. 4-7
Undo Tablespaces......................................................................................................................... 4-8
Default Temporary Tablespace.................................................................................................. 4-9
v
Multiple Tablespace Usage....................................................................................................... 4-10
Space Management in Tablespaces.......................................................................................... 4-11
Nonstandard Block Sizes........................................................................................................... 4-13
Online and Offline Tablespaces................................................................................................ 4-14
Read-Only Tablespaces.............................................................................................................. 4-15
Temporary Tablespaces for Sorts............................................................................................. 4-16
Transport of Tablespaces between Databases........................................................................ 4-17
Datafiles Overview........................................................................................................................... 4-18
Datafile Contents........................................................................................................................ 4-19
Size of Datafiles........................................................................................................................... 4-19
Offline Datafiles.......................................................................................................................... 4-19
Temporary Datafiles................................................................................................................... 4-20
Control Files Overview.................................................................................................................... 4-20
Control File Contents................................................................................................................. 4-20
Multiplexed Control Files.......................................................................................................... 4-22
5 The Data Dictionary
Introduction to the Data Dictionary................................................................................................ 5-2
The Structure of the Data Dictionary......................................................................................... 5-3
SYS, the Owner of the Data Dictionary..................................................................................... 5-3
How the Data Dictionary Is Used.................................................................................................... 5-3
How Oracle Uses the Data Dictionary....................................................................................... 5-4
How Users and DBAs Can Use the Data Dictionary............................................................... 5-5
The Dynamic Performance Tables .................................................................................................. 5-7
Database Object Metadata................................................................................................................ 5-7
Part III The Oracle Instance
6 Database and Instance Startup and Shutdown
Introduction to an Oracle Instance ................................................................................................. 6-2
The Instance and the Database................................................................................................... 6-3
Connection with Administrator Privileges............................................................................... 6-3
Initialization Parameter Files...................................................................................................... 6-4
Instance and Database Startup......................................................................................................... 6-6
vi
How an Instance Is Started.......................................................................................................... 6-6
How a Database Is Mounted....................................................................................................... 6-7
What Happens When You Open a Database............................................................................ 6-9
Database and Instance Shutdown................................................................................................. 6-11
Close a Database......................................................................................................................... 6-11
Unmount a Database.................................................................................................................. 6-11
Shut Down an Instance.............................................................................................................. 6-12
7 Distributed Processing
Client/Server Architecture................................................................................................................ 7-2
Multitier Architecture........................................................................................................................ 7-2
Clients............................................................................................................................................. 7-3
Application Servers...................................................................................................................... 7-3
Database Servers........................................................................................................................... 7-4
Distributed Processing Overview................................................................................................... 7-4
Oracle Net Services............................................................................................................................. 7-8
How Oracle Net Services Works................................................................................................ 7-9
The Listener................................................................................................................................... 7-9
Oracle Internet Directory................................................................................................................ 7-10
8 Memory Architecture
Introduction to Oracle Memory Structures................................................................................... 8-2
System Global Area (SGA) Overview............................................................................................ 8-3
Dynamic SGA................................................................................................................................ 8-4
The Database Buffer Cache......................................................................................................... 8-6
The Redo Log Buffer.................................................................................................................. 8-10
The Shared Pool.......................................................................................................................... 8-10
The Large Pool............................................................................................................................ 8-14
Control of the SGA’s Use of Memory...................................................................................... 8-15
Other SGA Initialization Parameters....................................................................................... 8-15
Program Global Areas (PGA) Overview...................................................................................... 8-16
Content of the PGA.................................................................................................................... 8-16
SQL Work Areas......................................................................................................................... 8-18
PGA Memory Management for Dedicated Mode................................................................. 8-19
Dedicated and Shared Servers....................................................................................................... 8-20
vii
Software Code Areas........................................................................................................................ 8-21
9 Process Architecture
Introduction to Processes.................................................................................................................. 9-2
Multiple-Process Oracle Systems............................................................................................... 9-2
Types of Processes........................................................................................................................ 9-2
User Processes Overview................................................................................................................... 9-4
Connections and Sessions............................................................................................................ 9-4
Oracle Processes Overview............................................................................................................... 9-5
Server Processes............................................................................................................................ 9-5
Background Processes.................................................................................................................. 9-5
Trace Files and the Alert Log.................................................................................................... 9-14
Shared Server Architecture............................................................................................................. 9-15
Internet Scalability...................................................................................................................... 9-17
Dispatcher Request and Response Queues............................................................................. 9-17
Shared Server Processes (Snnn)................................................................................................ 9-19
Restricted Operations of the Shared Server............................................................................ 9-20
Dedicated Server Configuration.................................................................................................... 9-21
The Program Interface...................................................................................................................... 9-22
Program Interface Structure...................................................................................................... 9-23
Program Interface Drivers......................................................................................................... 9-23
Communications Software for the Operating System........................................................... 9-24
10 Database Resource Management
Introduction to the Database Resource Manager....................................................................... 10-2
Database Resource Manager Terms......................................................................................... 10-3
Example of a Simple Resource Plan......................................................................................... 10-4
How the Database Resource Manager Works............................................................................. 10-5
Resource Control......................................................................................................................... 10-5
Database Integration.................................................................................................................. 10-6
Performance Overhead.............................................................................................................. 10-7
Resource Plans and Resource Consumer Groups....................................................................... 10-7
Activation of a Resource Plan................................................................................................... 10-8
Groups of Resource Plans.......................................................................................................... 10-9
Resource Allocation Methods and Resource Plan Directives................................................ 10-11
viii
Resource Plan Directives......................................................................................................... 10-11
CPU Resource Allocation........................................................................................................ 10-13
Interaction with Operating-System Resource Control............................................................ 10-17
Dynamic Reconfiguration....................................................................................................... 10-18
Part IV The Object-Relational DBMS
11 Schema Objects
Introduction to Schema Objects.................................................................................................... 11-2
Tables................................................................................................................................................... 11-4
How Table Data Is Stored.......................................................................................................... 11-5
Nulls Indicate Absence of Value.............................................................................................. 11-9
Default Values for Columns...................................................................................................... 11-9
Partitioned Tables..................................................................................................................... 11-11
Nested Tables............................................................................................................................ 11-12
Temporary Tables..................................................................................................................... 11-12
External Tables.......................................................................................................................... 11-14
Views................................................................................................................................................. 11-15
Storage for Views...................................................................................................................... 11-17
How Views Are Used.............................................................................................................. 11-17
The Mechanics of Views.......................................................................................................... 11-18
Dependencies and Views........................................................................................................ 11-19
Updatable Join Views............................................................................................................... 11-20
Object Views in an Oracle Database...................................................................................... 11-20
Inline Views............................................................................................................................... 11-21
Materialized Views......................................................................................................................... 11-21
Define Constraints on Views.................................................................................................. 11-22
Refresh Materialized Views.................................................................................................... 11-23
Materialized View Logs........................................................................................................... 11-23
Dimensions...................................................................................................................................... 11-24
The Sequence Generator............................................................................................................... 11-25
Synonyms......................................................................................................................................... 11-26
Indexes.............................................................................................................................................. 11-27
Unique and Nonunique Indexes............................................................................................ 11-29
Composite Indexes................................................................................................................... 11-29
ix
Indexes and Keys...................................................................................................................... 11-30
Indexes and Nulls..................................................................................................................... 11-30
Function-Based Indexes........................................................................................................... 11-31
How Indexes Are Stored.......................................................................................................... 11-33
How Indexes Are Searched..................................................................................................... 11-37
Key Compression...................................................................................................................... 11-44
Reverse Key Indexes................................................................................................................. 11-46
Bitmap Indexes.......................................................................................................................... 11-47
Bitmap Join Indexes.................................................................................................................. 11-52
Index-Organized Tables................................................................................................................. 11-57
Benefits of Index-Organized Tables....................................................................................... 11-58
Index-Organized Tables with Row Overflow Area............................................................. 11-59
Secondary Indexes on Index-Organized Tables................................................................... 11-60
Bitmap Indexes on Index-Organized Tables......................................................................... 11-60
Partitioned Index-Organized Tables...................................................................................... 11-61
B-tree Indexes on UROWID Columns for Heap- and Index-Organized Tables.............. 11-61
Index-Organized Table Applications..................................................................................... 11-62
Application Domain Indexes........................................................................................................ 11-62
Clusters............................................................................................................................................. 11-62
Hash Clusters................................................................................................................................... 11-64
12 Partitioned Tables and Indexes
Introduction to Partitioning............................................................................................................ 12-2
Partition Key................................................................................................................................ 12-4
Partitioned Tables....................................................................................................................... 12-4
Partitioned Index-Organized Tables........................................................................................ 12-5
Partitioning Methods....................................................................................................................... 12-5
Range Partitioning...................................................................................................................... 12-7
List Partitioning........................................................................................................................... 12-8
Hash Partitioning........................................................................................................................ 12-9
Composite Partitioning............................................................................................................ 12-10
When to Partition a Table........................................................................................................ 12-11
Partitioned Indexes......................................................................................................................... 12-11
Local Partitioned Indexes........................................................................................................ 12-12
Global Partitioned Indexes...................................................................................................... 12-12
x