Expert Oracle database 10g administration

Sam R. Alapati

Expert Oracle Database

10g Administration

4517FM.qxd 8/19/05 10:24 AM Page i

Expert Oracle Database 10g Administration

Copyright © 2005 by Sam R. Alapati

All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,

electronic or mechanical, including photocopying, recording, or by any information storage or retrieval

system, without the prior written permission of the copyright owner and the publisher.

ISBN (pbk): 1-59059-451-7

Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1

Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence

of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark

owner, with no intention of infringement of the trademark.

Lead Editor: Tony Davis

Technical Reviewer: John Watson

Development Editors: Robert Denn and Matthew Moodie

Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell, Tony Davis,

Jason Gilmore, Jonathan Hassell, Chris Mills, Dominic Shakeshaft, and Jim Sumser

Associate Publisher: Grace Wong

Project Manager: Beckie Stones and Tracy Brown Collins

Copy Edit Manager: Nicole LeClerc

Copy Editors: Andy Carroll, Marilyn Smith, and Susannah Pfalzer

Assistant Production Director: Kari Brooks-Copony

Production Editor: Ellie Fountain

Compositor: Dina Quan

Proofreaders: Lori Bring and Liz Welch

Indexer: John Collin

Interior Designer: Van Winkle Design Group

Cover Designer: Kurt Krames

Manufacturing Director: Tom Debolski

Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor,

New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail [email protected], or

visit http://www.springeronline.com.

For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley,

CA 94710. Phone 510-549-5930, fax 510-549-5939, e-mail [email protected], or visit http://www.apress.com.

The information in this book is distributed on an “as is” basis, without warranty. Although every precaution

has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to

any person or entity with respect to any loss or damage caused or alleged to be caused directly or indi￾rectly by the information contained in this work.

The source code for this book is available to readers at http://www.apress.com in the Source Code section.

4517FM.qxd 8/19/05 10:24 AM Page ii

To my grandfather, Alapati Pullayya, and grandmother, Bollu Seethamma,

for their love, affection, strength, and wisdom

4517FM.qxd 8/19/05 10:24 AM Page iii

4517FM.qxd 8/19/05 10:24 AM Page iv

Contents

About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxiii

About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxiv

Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxv

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxvii

PART 1 ■ ■ ■ Background, Data Modeling, and

UNIX/Linux

■CHAPTER 1 The Oracle DBA’s World . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

The Oracle DBA’s Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

Different DBA Job Classifications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

Types of Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

Background and Training . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

The Daily Routine of a Typical Oracle DBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

Some General Advice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

■CHAPTER 2 Relational Database Modeling and Database Design . . . . . . . 19

Relational Databases: A Brief Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

The Relational Database Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

Relational Database Life Cycle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

Reverse-Engineering a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

Object-Relational and Object Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

■CHAPTER 3 Essential UNIX (and Linux) for the Oracle DBA . . . . . . . . . . . . . . 43

Overview of UNIX and Linux Operating Systems . . . . . . . . . . . . . . . . . . . . . . . . . . 43

Understanding the UNIX Shell(s) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

Overview of Basic UNIX Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48

Navigating Files and Directories in UNIX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

Writing and Editing Files with the vi Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

Extracting and Sorting Text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

Shell Scripting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

Dealing with UNIX Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

UNIX System Administration and the Oracle DBA . . . . . . . . . . . . . . . . . . . . . . . . . 75 v

4517FM.qxd 8/19/05 10:24 AM Page v

Disks and Storage in UNIX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85

RAID Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88

New Storage Technologies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92

PART 2 ■ ■ ■ Oracle Database 10g Architecture,

Schema, and Transaction Management

■CHAPTER 4 Introduction to the Oracle Database 10g Architecture . . . . . 99

Oracle Database Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99

Oracle Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113

Oracle Memory Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119

A Simple Oracle Database Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130

Data Consistency and Data Concurrency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131

Backup and Recovery Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133

The Oracle Data Dictionary and the Dynamic Performance Views . . . . . . . . . . 135

Talking to the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137

Oracle Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139

Scheduling and Resource-Management Tools . . . . . . . . . . . . . . . . . . . . . . . . . . 139

Automatic Database Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140

Common Manageability Infrastructure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141

Efficient Managing and Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143

■CHAPTER 5 Schema Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145

Types of SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145

Oracle Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148

Creating and Managing Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149

Oracle Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175

Special Oracle Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182

Oracle Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196

Managing Database Integrity Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202

Using Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207

Using Materialized Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209

Using the SQL Access Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214

Using Synonyms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220

Using Sequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222

Using Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223

Viewing Object Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224

vi ■CONTENTS

4517FM.qxd 8/19/05 10:24 AM Page vi

■CHAPTER 6 Oracle Transaction Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225

Oracle Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225

Transaction Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228

Transaction Concurrency Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229

Isolation Levels and the ISO Transaction Standard . . . . . . . . . . . . . . . . . . . . . . . 231

Oracle’s Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232

Implementing Oracle’s Concurrency Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235

Using Undo Data to Provide Read Consistency . . . . . . . . . . . . . . . . . . . . . . . . . . 243

Flashback Error Correction Using Undo Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254

Flashback Using the DBMS_FLASHBACK Package . . . . . . . . . . . . . . . . . . . . . . . 256

Flashback Transaction Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261

Discrete Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267

Autonomous Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267

Resumable Space Allocation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269

Managing Long Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273

PART 3 ■ ■ ■ Installing Oracle Database 10g, and

Creating and Upgrading Databases

■CHAPTER 7 Installing the Oracle Database 10g RDBMS . . . . . . . . . . . . . . . . 279

Installing Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279

Following the Optimal Flexible Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281

Performing Preinstallation Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288

A Final Checklist for the Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300

After the Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309

Uninstalling Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312

■CHAPTER 8 Upgrading to Oracle Database 10g . . . . . . . . . . . . . . . . . . . . . . . . . . 315

Routes to Oracle Database 10g . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315

Upgrade Methods and Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315

Upgrading with the DBUA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318

Upgrading Manually . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322

After the Upgrade . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328

■CHAPTER 9 Creating an Oracle Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329

Getting Ready to Create the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329

Creating the Parameter File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333

Creating a New Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358

Using a Server Parameter File (SPFILE) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374

Starting Up and Shutting Down the Database from SQL*Plus . . . . . . . . . . . . . . 378

■CONTENTS vii

4517FM.qxd 8/19/05 10:24 AM Page vii

PART 4 ■ ■ ■ Connectivity and User Management

■CHAPTER 10 Connectivity and Networking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391

Oracle Networking and Database Connectivity . . . . . . . . . . . . . . . . . . . . . . . . . . 391

Networking Concepts: How Oracle Networking Works . . . . . . . . . . . . . . . . . . . . 393

Establishing Oracle Connectivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396

The Oracle Client . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397

The Instant Client . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399

The Listener and Connectivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400

Naming and Connectivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405

Oracle and Java Database Connectivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 416

■CHAPTER 11 User Management and Database Security . . . . . . . . . . . . . . . . . . 421

Managing Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422

The Database Resource Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431

Controlling Access to Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 442

Auditing Database Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461

Authenticating Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471

Enterprise User Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 476

Database Security Dos and Don’ts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 482

■CHAPTER 12 Using SQL*Plus and iSQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491

Starting a SQL*Plus Session . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491

Exiting SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495

SQL*Plus and SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 496

Key SQL*Plus “Working” Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 508

Commands for Formatting SQL*Plus Output and Creating Reports . . . . . . . . . . 512

Creating Command Files in SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 514

Editing Within SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 519

Key SQL*Plus Database Administration Commands . . . . . . . . . . . . . . . . . . . . . . 524

Using SQL to Generate SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 525

iSQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 526

PART 5 ■ ■ ■ Data Loading, Backup, and Recovery

■CHAPTER 13 Loading and Transforming Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539

An Overview of Extraction, Transformation, and Loading . . . . . . . . . . . . . . . . . . 539

Using the SQL*Loader Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 541

Using External Tables to Load Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 559

viii ■CONTENTS

4517FM.qxd 8/19/05 10:24 AM Page viii

Transforming Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 570

Using Oracle Streams for Replication and Information Sharing . . . . . . . . . . . . . 583

■CHAPTER 14 Using Data Pump Export and Import . . . . . . . . . . . . . . . . . . . . . . . . 589

Introduction to the Data Pump Technology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589

Performing Data Pump Exports and Imports . . . . . . . . . . . . . . . . . . . . . . . . . . . . 598

Monitoring a Data Pump Job . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 621

Using the Data Pump API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 622

Transportable Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 623

■CHAPTER 15 Backing Up Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631

Backing Up Oracle Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631

Examining the Flash Recovery Area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640

The Recovery Manager (RMAN) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 648

Backing Up the Control File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 679

The Oracle Backup Tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 680

User-Managed Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 686

Database Corruption Detection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 692

Enhanced Data Protection for Disaster Recovery . . . . . . . . . . . . . . . . . . . . . . . . 695

■CHAPTER 16 Database Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699

Types of Database Failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699

The Oracle Recovery Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 701

Performing Recovery with RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 707

Typical Media Recovery Scenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 711

Cloning a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 726

Techniques for Granular Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 730

Flashback Techniques and Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 736

Using Restore Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 750

Repairing Data Corruption and Trial Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . 752

Troubleshooting Recovery Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 754

PART 6 ■ ■ ■ Managing the Operational Oracle

Database

■CHAPTER 17 Automatic Management and Online Capabilities . . . . . . . . . . 759

The Automatic Database Diagnostic Monitor (ADDM) . . . . . . . . . . . . . . . . . . . . . 759

Automatic Shared Memory Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 774

Automatic Optimizer Statistics Collection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 780

■CONTENTS ix

4517FM.qxd 8/19/05 10:24 AM Page ix

Automatic Storage Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 783

Automatic Space Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 799

Online Capabilities of Oracle Database 10g . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 811

■CHAPTER 18 Managing and Monitoring the Operational Database . . . . . . 823

Types of Oracle Performance Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 823

Server-Generated Alerts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 828

The Automatic Workload Repository (AWR) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 834

Active Session History (ASH) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 845

The Management Advisory Framework . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 849

Working with the Undo and the MTTR Advisors . . . . . . . . . . . . . . . . . . . . . . . . . . 854

Managing the Online Redo Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 855

Managing Database Links . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 858

Copying Files with the Database Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 860

Mapping Oracle Files to Physical Devices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 862

Using the Oracle Scheduler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 863

■CHAPTER 19 Using Oracle Enterprise Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . 883

Oracle Enterprise Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 883

OEM Architecture and Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 886

OEM Database Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 886

OEM Grid Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 899

■CHAPTER 20 Managing Oracle Databases on Windows and

Linux Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 909

Oracle Database 10g and Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 909

Essential Differences Between Managing Oracle on Windows and UNIX . . . . . 912

Installing Oracle Database 10g on a Windows System . . . . . . . . . . . . . . . . . . . . 919

The Windows Registry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 920

Managing Oracle on Windows Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 921

Uninstalling Oracle on Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 930

Oracle and Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 931

PART 7 ■ ■ ■ Performance Tuning

■CHAPTER 21 Improving Database Performance:

SQL Query Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 937

An Approach to Oracle Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 937

Optimizing Oracle Query Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 939

Query Optimization and the Oracle Cost-Based Optimizer . . . . . . . . . . . . . . . . . 943

x ■CONTENTS

4517FM.qxd 8/19/05 10:24 AM Page x

Writing Efficient SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 957

How the DBA Can Help Improve SQL Processing . . . . . . . . . . . . . . . . . . . . . . . . 967

SQL Performance Tuning Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 974

Using the EXPLAIN PLAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 974

The SQL Tuning Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 994

A Simple Approach to Tuning SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . 999

■CHAPTER 22 Performance Tuning: Tuning the Instance . . . . . . . . . . . . . . . . . 1001

An Introduction to Instance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1001

Automatic Performance Tuning vs. Dynamic Performance Views . . . . . . . . . . 1003

Tuning Oracle Memory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1004

Evaluating System Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1024

Measuring I/O Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1030

Measuring Instance Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1032

A Simple Approach to Instance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1066

PART 8 ■ ■ ■ The Data Dictionary, Dynamic Views,

and the Oracle-Supplied Packages

■CHAPTER 23 The Oracle Data Dictionary and the

Dynamic Performance Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1083

The Oracle Data Dictionary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1083

Using the Static Data Dictionary Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1084

Using the Dynamic Performance Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1115

■CHAPTER 24 Using Oracle PL/SQL Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1145

Overview of the Oracle-Supplied PL/SQL Packages . . . . . . . . . . . . . . . . . . . . . 1145

DBMS_FILE_TRANSFER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1146

DBMS_MONITOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1148

UTL_COMPRESS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1148

UTL_MAIL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1149

DBMS_TDB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1150

DBMS_JOB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1151

DBMS_APPLICATION_INFO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1153

DBMS_CRYPTO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1155

DBMS_SESSION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1155

DBMS_SYSTEM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1156

DBMS_OUTPUT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1159

DBMS_REPAIR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1160

DBMS_OUTLN and DBMS_OUTLN_EDIT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1162

■CONTENTS xi

4517FM.qxd 8/19/05 10:24 AM Page xi

DBMS_SPACE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1163

DBMS_SPACE_ADMIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1164

DBMS_PROFILER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1165

DBMS_ERRLOG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1167

UTL_FILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1168

UTL_SMTP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1172

DBMS_SHARED_POOL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1173

DBMS_WM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1174

DBMS_RLMGR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1175

Oracle Packages in Earlier Chapters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1175

■APPENDIX A Oracle Database 10g SQL and PL/SQL: A Brief Primer . . . . 1183

The Oracle Database 10g Sample Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . 1183

Oracle Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1185

SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1186

Abstract Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1201

PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1203

Using Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1207

Procedures, Functions, and Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1209

Oracle XML DB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1210

Oracle and Java . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1214

■INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1217

xii ■CONTENTS

4517FM.qxd 8/19/05 10:24 AM Page xii

About the Author

■SAM R. ALAPATI is an experienced Oracle DBA who holds the Oracle OCP DBA

certification and the Hewlett-Packard UNIX System Administrator certification.

He currently manages Oracle databases at the Boy Scouts of America’s national

office in Los Colinas, Texas. Previously, Alapati worked for AMR Holdings

(Sabre) and the Blanch Company in Dallas. Alapati was a senior principal con￾sultant for Oracle Corporation in New York and worked at NBC and Lehman

Brothers on behalf of Oracle. Alapati’s other DBA experience, which includes

Sybase and DB2 databases, consists of assignments with Lewco Securities and

AT&T in New Jersey. Sam can be reached at [email protected].

About the Technical Reviewer

■JOHN WATSON was born, bred, and schooled in Oxford, England, and what he laughingly calls his

career has been in London, then Germany, and now he’s based in South Africa.

All John’s work has been in IT, starting with the PC revolution twenty years ago, but deep down

inside he’s still some sort of organic free-range hippy. John first came across Oracle with version 5,

but he couldn’t make it do anything, and it was only with version 7 that he really got to grips with it.

After seven years full time with Oracle Corporation, John now works for a small Oracle consulting

company and spends his time equally on teaching Oracle courses all over Africa and Europe; con￾sulting; and research and development. But what he really likes is to be at home with his wife, cats,

dogs, and vegetable patch; they live on two acres outside Johannesburg.

xiii

4517FM.qxd 8/19/05 10:24 AM Page xiii

Acknowledgments

My first debt in writing this book is to my father Dr. Alapati Appa Rao, who is responsible for my

love for education and books. This book is a direct outcome of the early scholarly interest nurtured

by him, as well as his support and encouragement for writing the Oracle 9i book, which is this

book’s predecessor.

John Watson, the Technical Reviewer for the book, did a superb job in not merely catching

technical errors, but also in prodding me to explain several concepts clearly and accurately. I’ve

gained immensely from John’s collaboration on this book.

I am indebted to the trailblazing Gary Cornell, Publisher of Apress, for taking the lead in pub￾lishing both the predecessor to this book as well as this one. Dominic Shakeshaft kindly helped sort

out various issues that came up during the writing of the book, and I appreciate his lending his con￾siderable talents to this project. I am fortunate to have had the highly accomplished Tony Davis as

the Lead Editor for this book. Tony has provided masterly editorial support and pulled many a

chestnut out of the fire during the last year. Tony has the knack for synthesizing complex issues and

suggesting solutions with admirable efficiency and grace. Beckie Stones, Project Manager, cheer￾fully and very efficiently planned and implemented the project plan. Beckie had the unenvious task

of guiding this long book through several iterations of writing and editing. Thanks Beckie, for saving

the project from my tendency to write incessantly, and for letting the book see the light of day now,

rather than a year or so later! Thanks are also due to Tracy Brown Collins, who was the Project

Manager during an early stage.

Several people contributed to the editing of various sections of this book, and I thank them all

for their help in improving the book’s quality. Robert Denn, Development Editor, worked admirably

to make sure that the contents of all the chapters flowed together in a coherent fashion. Matthew

Moodie pitched in to help at a critical time by ably editing a few chapters. All three Copy Editors—

Andy Carroll, Marilyn Smith, and Susannah Pfalzer—did a marvelous job in improving the quality

of the book. While it’s not fair to single out one of these three for special mention, I feel obliged to

offer my special thanks to Andy, for working on the vast majority of the chapters with great dili￾gence and acumen. Susannah worked extremely capably on several chapters as well, and I admire

her devotion to accuracy and quality. This book is a much better offering due to the conscientious

efforts of Andy and Susannah. Although I didn’t deal with them directly this time around, I’m sure

the book benefited in several ways from the contributions of Nicole LeClerc, Copy Edit Manager,

and Grace Wong, the Associate Publisher.

Ellie Fountain, the Production Editor, has been simply superb in the way she managed her

task. Ellie deserves thanks for enhancing the production quality of the book and working towards

minimizing errors. I’m very appreciative of the diligent efforts of Assistant Production Director Kari

Brooks-Copony, Compositor Dina Quan, and Indexer John Collin. The Proofreaders, Lori Bring and

Liz Welch, saved me from some particularly insidious errors. My thanks to Kurt Krames for design￾ing the beautiful cover, and to Manufacturing Director Tom Debolski, who is responsible for

numerous issues during the printing of the book.

My special thanks to my colleagues at the Boy Scouts of America national office in Texas. Nate

Langston, Director of the Information Systems Division, has consistently encouraged us to stay at

the forefront of technological change. By stressing the adoption of the most advanced technology

available (including Oracle Database 10g!) in his role as the CIO, Nate has propelled the Boy Scouts

into the ranks of the leading organizations in the United States in the use of information technology.

I am very thankful to Dave Cambell, Director of Technical Services, for his confidence in me and for xiv

4517FM.qxd 8/19/05 10:24 AM Page xiv