Oracle Essentials Oracle Database 11g

Oracle Essentials

Oracle Database 11g

Other Oracle resources from O’Reilly

Related titles

Mastering Oracle SQL

Oracle DBA Pocket Guide

Optimizing Oracle

Oracle in a Nutshell

Performance

Oracle PL/SQL Programming

Oracle Application Server 10
g

Oracle SQL*Plus: The Defini-

Essentials

tive Guide

Oracle Books

oracle.oreilly.com
is a complete catalog of O’Reilly’s books on
Resource Center

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, programming 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 reference library for programmers and IT professionals. Conduct

searches across more than 1,000 books. Subscribers can zero in on answers to time-critical questions in a matter of seconds.

Read the books on your Bookshelf from cover to cover or sim-

ply flip to the page you need. Try it today for free.

FOURTH EDITION

Oracle Essentials

Oracle Database 11g

Rick Greenwald, Robert Stackowiak, and

Jonathan Stern

Beijing

Cambridge

Farnham

Köln

Paris

Sebastopol

Taipei

Tokyo
Oracle Essentials: Oracle Database 11g, Fourth Edition

by Rick Greenwald, Robert Stackowiak, and Jonathan Stern

Copyright © 2008 O’Reilly Media, Inc. All rights reserved.

Printed in the United States of America.

Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472.

O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions

are also available for most titles (
safari.oreilly.com
). For more information, contact our corporate/institutional sales department: (800) 998-9938 or
[email protected]
.

Editors:
Colleen Gorman and Deborah Russell

Interior Designer:
David Futato

Production Editor:
Sumita Mukherji

Cover Designer:
Karen Montgomery

Production Services:
Tolman Creek Design

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 trademarks of Sun Microsystems, Inc. in the United States and other countries. O’Reilly Media, Inc. is independent of Sun Microsystems. .NET is a registered trademark of Microsoft Corporation.

Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and O’Reilly Media, Inc. was aware of a trademark claim, the designations have been printed in caps or initial caps.

While every precaution has been taken in the preparation of this book, the publisher and authors assume no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein.

This book uses RepKover™, a durable and flexible lay-flat binding.

ISBN-10: 0-596-51454-9

ISBN-13: 978-0-596-51454-9

[M]

In memory of Jonathan

Table of Contents

Preface
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
xi

1. Introducing Oracle
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1

The Evolution of the Relational Database

2

The Oracle Database Family

7

Summary of Oracle Database Features

9

Database Application Development Features

9

Database Connection Features

13

Distributed Database Features

17

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

vii

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

Extended Logic for Data

100

Data Design

102

Constraints

104

Triggers

107

Query Optimization

108

Understanding the Execution Plan

118

SQL Advisors

120

Data Dictionary Tables

120

5. Managing Oracle
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
122

Manageability Features

124

Oracle Enterprise Manager

126

Fragmentation and Reorganization

132

Backup and Recovery

133

Working with Oracle Support

137

6. Oracle Security, Auditing, and Compliance
. . . . . . . . . . . . . . . . . . . . . . . . . . .
139

Security

139

Auditing

150

Compliance

151

7. Oracle Performance
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
154

Performance Tuning Basics

154

Oracle and Disk I/O Resources

160

Oracle and Parallelism

169

Oracle and Memory Resources

176

Oracle and CPU Resources

182

Database Resource Manager

184

viii

|

Table of Contents

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

Other Software for the Data Warehouse

236

The Metadata Challenge

248

Best Practices

249

11. Oracle and High Availability
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
253

What Is High Availability?

254

System Failure

257

Protecting Against System Failure

262

Recovering from Failures

275

Complete Site Failure

281

Data Redundancy Solutions

285

Rolling Upgrades

289

Table of Contents

|

ix

12. Oracle and Hardware Architecture
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
290

System Basics

290

Uniprocessor Systems

292

Symmetric Multiprocessing Systems

293

Clusters

295

Non-Uniform Memory Access Systems

298

Grid Computing

299

Disk and Storage Technology

300

Which Platform Deployment Solution?

302

13. Oracle Distributed Databases and Distributed Data
. . . . . . . . . . . . . . . . . . . .
305

Other books

Teach Me Dirty by Jade West
Girl Code by Davis, LD
George Mills by Stanley Elkin
Bomb Grade by Brian Freemantle
Deep Roots by Beth Cato
The Armada Boy by Kate Ellis