Oracle Essentials
Oracle Database 11g
Other Oracle resources from O’Reilly
Related titles
Mastering Oracle SQL
Optimizing Oracle
Performance
Oracle Application Server 10g
Essentials
Oracle DBA Pocket Guide
Oracle in a Nutshell
Oracle PL/SQL Programming
Oracle SQL*Plus: The Defini-
tive Guide
Oracle Books
Resource Center
oracle.oreilly.com is a complete catalog of O’Reilly’s books on
Oracle and related technologies, including sample chapters and
code examples.
oreillynet.com is the essential portal for developers interested in
open and emerging technologies, including new platforms, pro-
gramming languages, and operating systems.
Conferences
O’Reilly brings diverse innovators together to nurture the ideas
that spark revolutionary industries. We specialize in document-
ing the latest tools and systems, translating the innovator’s
knowledge into useful skills for those in the trenches. Visit
conferences.oreilly.com for our upcoming events.
Safari Bookshelf (safari.oreilly.com) is the premier online refer-
ence library for programmers and IT professionals. Conduct

are also available for most titles (safari.oreilly.com). For more information, contact our
corporate/institutional sales department: (800) 998-9938 or
Editors:
Colleen Gorman and Deborah Russell
Production Editor:
Sumita Mukherji
Production Services:
Tolman Creek Design
Interior Designer:
David Futato
Cover Designer:
Karen Montgomery
Illustrator:
Robert Romano
Printing History:
October 1999: First Edition. Originally published under the title
Oracle Essentials: Oracle8 and Oracle8i
June 2001: Second Edition. Originally published under the title
Oracle Essentials: Oracle9i, Oracle8i and Oracle8
February 2004: Third Edition. Originally published under the title
Oracle Essentials: Oracle Database 10g
November 2007: Fourth Edition.
Nutshell Handbook, the Nutshell Handbook logo, and the O’Reilly logo are registered trademarks of
O’Reilly Media, Inc. Oracle Essentials: Oracle Database 11g, the image of cicadas, and related trade
dress are trademarks of O’Reilly Media, Inc.
Oracle
®
and all Oracle-based trademarks and logos are trademarks or registered trademarks of Oracle
Corporation, Inc. in the United States and other countries. O’Reilly Media, Inc. is independent of
Oracle Corporation. Java™ and all Java-based trademarks and logos are trademarks or registered

Data Movement Features 18
Database Performance Features 20
Database Management Features 23
Database Security Features 27
Oracle Development Tools 28
Embedded Databases 31
2. Oracle Architecture
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
33
Databases and Instances 33
Deploying Physical Components 38
Instance Memory and Processes 48
The Data Dictionary 54
3. Installing and Running Oracle
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
56
Installing Oracle 56
Creating a Database 59
Configuring Oracle Net 63
viii | Table of Contents
Starting Up the Database 68
Shutting Down the Database 69
Accessing a Database 70
Oracle at Work 77
4. Oracle Data Structures
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
82
Datatypes 82
Basic Data Structures 90
Additional Data Structures 98

Database Resource Manager 184
Table of Contents | ix
8. Oracle Multiuser Concurrency
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
186
Basics of Concurrent Access 187
Oracle and Concurrent User Access 190
Oracle’s Isolation Levels 191
Oracle Concurrency Features 192
How Oracle Handles Locking 194
Concurrent Access and Performance 197
Workspaces 198
9. Oracle and Transaction Processing
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
201
OLTP Basics 201
Oracle’s OLTP Heritage 205
Architectures for OLTP 206
Oracle Features for OLTP 211
High Availability 217
Oracle Streams and Advanced Queuing 218
Object Technologies and Distributed Components 221
10. Oracle Data Warehousing and Business Intelligence
. . . . . . . . . . . . . . . . . . .
222
Business Intelligence Basics 223
Data Warehouse Design 227
Query Optimization 230
Analytics, OLAP, and Data Mining in the Database 233
Managing the Data Warehouse 236

14. Oracle Extended Datatypes
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
318
Object-Oriented Development 318
Extensibility Features and Options 324
Using the Extensibility Framework in Oracle 329
15. Beyond the Oracle Database
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
331
Application Express 331
Oracle Fusion Middleware 332
Oracle SOA Suite 345
A. What’s New in This Book for Oracle Database 11g
. . . . . . . . . . . . . . . . . . . . .
349
B. Additional Resources
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
356
Index
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
365
xi
-
Preface
1
We dedicate this book to the memory of one of our original coauthors, Jonathan
Stern. Jonathan unexpectedly passed away in March of 2007. Yet his memory lives
on for those of us who knew him and, in many ways, for those who will read this
book. Let us explain.
The original outline for this book was first assembled at the ubiquitous coffee shop

Of course, some entire sections describing the complex steps that were once needed
to manage and deploy older releases of the database are no longer relevant and thus
are no longer included. Jonathan would probably view Oracle’s self-managing and
self-tuning improvements as incredible achievements, but would also wonder
whether it is a good thing that people can know even less today about how the data-
base works but still deploy it.
So, we introduce you to the fourth edition of Oracle Essentials. We have made many
changes in this edition. Some, of course, result from changes in features in Oracle
Database 11g and the ways that youcan now use and deploy the latest release of the
database. But we have also made a considerable effort to go back and rewrite parts of
the book that we did not believe possessed the clarity needed by our readers—clarity
that Jonathan would want in such a book. So, he influences us still.
Goals of This Book
Our main goal is to give you a foundation for using the Oracle database effectively
and efficiently. Therefore, we wrote with these principles in mind:
Focus
We’ve tried to concentrate on the most important Oracle issues. Every topic pro-
vides a comprehensive but concise discussion of how Oracle handles an issue
and the repercussions of that action.
Brevity
One of the first decisions we made was to concentrate on principles rather than
syntax. There simply isn’t room for myriad syntax diagrams and examples in this
book.
Uniqueness
We’ve tried to make this an ideal first Oracle book for a wide spectrum of Oracle
users—but not the last! You will very likely have to refer to Oracle documenta-
tion or other, more specific books for more details about using Oracle. However,
we hope this book will act as an accelerator for you. Using the foundation you
get from this book, youcan take detailed information from other sources and
put it to the best use.

About the Fourth Edition (Oracle Database 11g)
The first three editions of this book, covering the Oracle database up to the Oracle
Database 10g version, have been well received, and we were pleased that O’Reilly
Media agreed to publish this fourth edition. In this update to the book, we have
added information describing the latest release of Oracle, Oracle Database 11g.
For the most part, the task of preparing this fourth edition was fairly clear-cut,
because the Oracle Database 11g release is primarily incremental—the new features
in the release extend existing features of the database. We’ve added the information
about these extensions to each of the chapters, wherever this information was most
relevant and appropriate. However, manageability has greatly changed over the
release, and is reflected in many of the most significant changes to content.
xiv
|
Preface
Of course, this fourth edition cannot possibly cover everything that is new in Oracle
Database 11g. In general, we have followed the same guidelines for this edition that
we did for the first three editions. If a new feature does not seem to be broadly
important, we have not necessarily delved into it. As with earlier editions we have
not tried to produce a laundry list of every characteristic of the Oracle database. In
addition, if a feature falls into an area outside the scope of the earlier editions, we
have not attempted to cover it in this edition unless it has assumed new importance.
Structure of This Book
This book is divided into 15 chapters and 2 appendixes, as follows:
Chapter 1, Introducing Oracle, describes the range of Oracle products and features
and provides a brief history of Oracle and relational databases.
Chapter 2, Oracle Architecture, describes the core concepts and structures (e.g., files,
processes, and so on) that are the architectural basis of Oracle.
Chapter 3, Installing and Running Oracle, briefly describes how to install Oracle and
how to configure, start up, and shut down the database and Oracle Net.
Chapter 4, Oracle Data Structures, summarizes the various datatypes supported by

pens when the Oracle database recovers, protecting against system failure, Oracle’s
backup and recovery facilities, and high availability and failover solutions.
Chapter 12, Oracle and Hardware Architecture, describes your choice of computer
architectures, configuration considerations, and deployment strategies for Oracle,
including grid computing.
Chapter 13, Oracle Distributed Databases and Distributed Data, briefly summarizes
the Oracle facilities used in distributed processing including two-phase commits and
Streams Advanced Queuing and replication.
Chapter 14, Oracle Extended Datatypes, describes Oracle’s object-oriented features,
Java’s™ role, web services support, multimedia extensions to the Oracle datatypes,
content management using the database, spatial capabilities, and the extensibility
framework.
Chapter 15, Beyond the Oracle Database, describes Oracle Application Express,
deploying to the Web using the Oracle Application Server and Fusion Middleware,
and the overall use of Oracle in a Service-Oriented Architecture (SOA) environment.
Appendix A, What’s New in This Book for Oracle Database 11g, lists the Oracle
Database 11g changes described in this book.
Appendix B, Additional Resources, lists a variety of additional resources—both
online and offline—so you can do more detailed reading.
Conventions Used in This Book
The following typographical conventions are used in this book:
Italic
Used for file and directory names, emphasis, and the first occurrence of terms
Constant width
Used for code examples and literals
Constant width italic
In code examples, indicates an element (for example, a parameter) that you supply
xvi
|
Preface

permission. Selling or distributing a CD-ROM of examples from O’Reilly books does
require permission. Answering a question by citing this book and quoting example
Preface
|
xvii
code does not require permission. Incorporating a significant amount of example
code from this book into your product’s documentation does require permission.
We appreciate, but do not require, attribution. An attribution usually includes the
title, author, publisher, and ISBN. For example: “Oracle Essentials: Oracle Database
11g, Fourth Edition, by Rick Greenwald, Robert Stackowiak, and Jonathan Stern.
Copyright 2008 O’Reilly Media Inc., 978-0-596-51454-9.”
If you feel your use of code examples falls outside fair use or the permission given
above, feel free to contact us at
Safari® Books Online
When yousee a Safari® Books Online icon on the cover of your
favorite technology book, that means the book is available online
through the O’Reilly Network Safari Bookshelf.
Safari offers a solution that’s better than e-books. It’s a virtual library that lets you
easily search thousands of top tech books, cut and paste code samples, download
chapters, and find quick answers when you need the most accurate, current informa-
tion. Try it for free at .
Acknowledgments
Each of the authors has arrived at this collaboration through a different path, but we
would all like to thank the team at O’Reilly for making this book both possible and a
joy to write. We’d like to thank our first editor for this edition, Colleen Gorman, and
the rest of the O’Reilly crew, especially Sumita Mukherji, the production editor; Rob
Romano, who developed the figures; and Shan Young, who wrote the index. Also,
we’d like to thank our editor from the first three editions, Debby Russell, who was
among the first to see the value in such a book and who stepped in to perform final
editing on the fourth edition as well. It’s incredible how all of these folks were able to

Corps of Engineers. Through personal relationships and email, they have shared a lot
and provided him with incredible opportunities for learning. At Oracle, he especially
thanks members of Andy Mendelsohn’s team who have always been helpful in pro-
viding material ahead of releases, including Mark Townsend, Raymond Roccaforte,
George Lumpkin, Hermann Baer, and many others. Bob also extends special thanks
to his team in Oracle’s Technology Business Unit that includes Louis Nagode, Jim
Bienski, Gayl Czaplicki, Alan Manewitz, Joan Maiorana, Sandrine Ost, and Max Riv-
era. His management continues to recognize the value of such projects, including
Mark Salser and Paul Cross. He’d also like to thank his customers, who have always
had the most practical experience using the products and tools he has worked with
and from whom he continues to learn. Finally, both Bob and Rick would like to
thank Sheila Cepero for adding them to the Oracle Database 11g beta program, an
important factor in enabling this book to appear so shortly after the initial release of
the new database version.
In earlier editions, Jonathan thanked many of his professional contacts in previous
editions, including Murray Golding, Sam Mele, and the Oracle Server Technologies
members and their teams, including Juan Tellez, Ron Weiss, Juan Loaiza, and Carol
Colrain for their help during his years at Oracle. And we thank him for all that he
gave us in too short a life.
1
Chapter 1
CHAPTER 1
Introducing Oracle
1
Where do we start? One of the problems in comprehending a massive product such
as the Oracle database is getting a good sense of how the product works without
getting lost in the details. This book aims to provide a thorough grounding in the
concepts and technologies that form the foundation of Oracle’s Database Server,
currently known as Oracle Database 11g. The book is intended for a wide range of
Oracle database administrators, developers, and users, from the novice to the experi-

• Oracle9i (released in 2001) introduced Real Application Clusters as a replace-
ment for Oracle Parallel Server and added many management and data
warehousing features.
• Oracle Database 10g (released in 2003) enabled deployment of “grid” comput-
ing. A grid is simply a pool of computers and software resources providing
resources for applications on an as-needed basis. To support this style of com-
puting, Oracle added the ability to provision CPUs and data. Oracle Database
10g also further reduced the time, cost, and complexity of database manage-
ment through the introduction of self-managing features such as the Automated
Database Diagnostic Monitor, Automated Shared Memory Tuning, Automated
Storage Management, and Automated Disk Based Backup and Recovery.
• Oracle Database 11g (released in 2007) is the current release. Many of the self-
tuning and managing capabilities are further improved, especially Automatic
Memory Management, partitioning, and security. The lifecycle of database
change management is extended within Oracle’s Enterprise Manager as Oracle
now provides improved diagnosis capabilities and linkage to Oracle Support via
a Support Workbench. This version also features improved online patching
capabilities.
Before we dive into further details, let’s step back and look at how databases evolved,
how we arrived at the relational model, and Oracle’s history. We’ll then take an ini-
tial look at Oracle database packaging and key Oracle features today.
The Evolution of the Relational Database
The relational database concept was described first by Dr. Edgar F. Codd in an IBM
research publication entitled “System R4 Relational” that was published in 1970.
Initially, it was unclear whether any system based on this concept could achieve
commercial success. Nevertheless, a company named Software Development Labora-
tories Relational Software came into being in 1977 and then released a product
The Evolution of the Relational Database
|
3

not be defined. Because relational users don’t need to understand the representation
of data in storage to retrieve it (and many such users create ad hoc queries), ease of
use helped popularize the relational model.
Relational programming is nonprocedural and operates on a set of rows at a time. In
a master-detail relationship between tables, there can be one or many detail rows for
each individual master row, yet the statements used to access, insert, or modify the
data simply describe the set of results. In many early relational databases, data access
required the use of procedural languages that worked one record at a time. Because
of this set orientation, programs access more than one record in a relational data-
base more easily. Relational databases can be used more productively to extract
value from large groups of data.
The contents of the rows in Figure 1-2 are sometimes referred to as records. A col-
umn within a row is referred to as a field. Tables are stored in a database schema,
which is a logical organizational unit within the database. Other logical structures in
the schema often include the following:
Views
Provide a single view of data derived from one or more tables or views. The view
is an alternative interface to the data, which is stored in the underlying table(s)
that make up the view.
Figure 1-2. Relational model with two tables
EMPNO
71712
83321
85332
88888
EMPNAME
Johnson
Smith
Stern
Carter

Synonyms
Provide alternative names for database objects.
Indexes
Provide faster access to table rows.
Database links
Provide links between distributed databases.
The relationships between columns in different tables are typically described through
the use of keys, which are implemented through referential integrity constraints and
their supporting indexes. For example, in Figure 1-2, you can establish a link
between the DEPTNO column in the second table, which is called a foreign key,to
the DEPTNO column in the first table, which is referred to as the primary key of that
table.
Finally, even if youdefine many different indexes for a table, youdon’t have to
understand them or manage the data they contain. Oracle includes a query optimizer
(described in Chapter 4) that chooses whether to use indexes, and the best way to
use those indexes, to access the data for any particular query.
The relational approach lent itself to the Structured Query Language (SQL). SQL
was initially defined over a period of years by IBM Research, but it was Oracle Cor-
poration that first introduced it to the market in 1979. SQL was noteworthy at the
time for being the only language needed for relational databases since you could use
SQL:
• For queries (using a SELECT statement)
• As a Data Manipulation Language or DML (using INSERT, UPDATE, and
DELETE statements)
• As a Data Definition Language or DDL (using CREATE or DROP statements
when adding or deleting tables)
• To set privileges for users or groups (using GRANT or REVOKE statements)
Today, SQL contains many extensions and follows ANSI/ISO standards that define
its basic syntax.
How Oracle Grew

1979 Oracle version 2: first commercially available relational database to use SQL
1983 Oracle version 3: single code base for Oracle across multiple platforms
1984 Oracle version 4: with portable toolset, read consistency
1986 Oracle version 5 generally available: client/server Oracle relational database
1987 CASE and 4GL toolset
1988 Oracle Financial Applications built on relational database
1989 Oracle6 generally available: row-level locking and hot backups
1991 Oracle Parallel Server on massively parallel platforms
1993 Oracle7: with cost-based optimizer
1994 Oracle version 7.1 generally available: parallel operations including query, load, and create index
1996 Universal database with extended SQL via cartridges, thin client, and application server
1997 Oracle8 generally available: object-relational and Very Large Database (VLDB) features
1999 Oracle8i generally available: Java Virtual Machine (JVM) in the database
2000 Oracle9i Application Server generally available: Oracle tools integrated in middle tier
2001 Oracle9i Database Server generally available: Real Application Clusters, OLAP, and data mining in the database