Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Using Your Sybex Electronic Book
To realize the full potential of this Sybex electronic book, you must have Adobe Acrobat Reader with
Search installed on your computer. To find out if you have the correct version of Acrobat Reader, click on
the Edit menu—Search should be an option within this menu file. If Search is not an option in the Edit
menu, please exit this application and install Adobe Acrobat Reader with Search from this CD (double-
click on rp500enu.exe in the Adobe folder).
Navigation
To search, click the Search Query button on the toolbar
or choose Edit >Search > Query to open the Search window. In
the Adobe Acrobat Search dialog’s text field, type the text you
want to find and click Search.
Use the Search Next button (Control+U) and Search
Previous button (Control+Y) to go to other matches in
the book. The Search command also has powerful tools for
limiting and expanding the definition of the term you are
searching for. Refer to Acrobat's online Help (Help > Plug-In
Help > Using Acrobat Search) for more information.
www.sybex.com
Click here to begin using
your Sybex Elect ronic Book!
Search
Navigate throught the book by clicking on the headings that appear in the left panel;
the corresponding page from the book displays in the right panel.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ORACLE8i
™
Robert G. Freeman
Mark D. Blomberg
San Francisco • London
MASTERING

written permission of the publisher.
Library of Congress Card Number: 2001091742
ISBN: 0-7821-2929-3
SYBEX and the SYBEX logo are either registered trade-
marks or trademarks of SYBEX Inc. in the United States
and/or other countries.
Mastering is a trademark of SYBEX Inc.
Screen reproductions produced with SnagIt.
SnagIt is a registered trademark of TechSmith
Corporation.
Screen reproductions produced with FullShot 99.
FullShot 99 © 1991-1999 Inbit Incorporated. All rights
reserved.
FullShot is a trademark of Inbit Incorporated.
TRADEMARKS: SYBEX has attempted throughout this
book to distinguish proprietary trademarks from
descriptive terms by following the capitalization style
used by the manufacturer.
The author and publisher have made their best efforts
to prepare this book, and the content is based upon
final release software whenever possible. Portions of
the manuscript may be based upon pre-release versions
supplied by software manufacturer(s). The author and
the publisher make no representation or warranties of
any kind with regard to the completeness or accuracy
of the contents herein and accept no liability of any
kind including but not limited to performance, mer-
chantability, fitness for any particular purpose, or any
losses or damages of any kind caused or alleged to be
caused directly or indirectly from this book.

statements of condition, disclaimers, limitations or
warranties (“End-User License”), those End-User
Licenses supersede the terms and conditions herein as
to that particular Software component. Your purchase,
acceptance, or use of the Software will constitute your
acceptance of such End-User Licenses.
By purchase, use or acceptance of the Software you fur-
ther agree to comply with all export laws and regula-
tions of the United States as such laws and regulations
may exist from time to time.
Software Support
Components of the supplemental Software and any
offers associated with them may be supported by the
specific Owner(s) of that material, but they are not
supported by SYBEX. Information regarding any avail-
able support may be obtained from the Owner(s) using
the information provided in the appropriate read.me
files or listed elsewhere on the media.
Should the manufacturer(s) or other Owner(s) cease to
offer support or decline to honor any offer, SYBEX
bears no responsibility. This notice concerning support
for the Software is provided for your information only.
SYBEX is not the agent or principal of the Owner(s),
and SYBEX is in no way responsible for providing any
support for the Software, nor is it liable or responsible
for any support provided, or not provided, by the
Owner(s).
Warranty
SYBEX warrants the enclosed media to be free of physi-
cal defects for a period of ninety (90) days after purchase.

The exclusion of implied warranties is not permitted
by some states. Therefore, the above exclusion may
not apply to you. This warranty provides you with spe-
cific legal rights; there may be other rights that you
may have that vary from state to state. The pricing of
the book with the Software by SYBEX reflects the allo-
cation of risk and limitations on liability contained in
this agreement of Terms and Conditions.
Shareware Distribution
This Software may contain various programs that are
distributed as shareware. Copyright laws apply to both
shareware and ordinary commercial software, and the
copyright Owner(s) retains all rights. If you try a share-
ware program and continue using it, you are expected
to register it. Individual programs differ on details of
trial periods, registration, and payment. Please observe
the requirements stated in appropriate files.
Copy Protection
The Software in whole or in part may or may not be
copy-protected or encrypted. However, in all cases,
reselling or redistributing these files without autho-
rization is expressly forbidden except as specifically
provided for by the Owner(s) therein.
Copyright ©2002 SYBEX, Inc., Alameda, CA
www.sybex.com
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
While this book was being written, the cowardly attacks on
the World Trade Center, the Pentagon, and in Pennsylvania
took place. Thus, this book is dedicated to all those who
died there and in other terrorist tragedies throughout the

Of course, there are countless people who helped and didn’t even know it. Special
thanks to Steve Adams, Tim Stippler, Charles Pack, Mike Ault, Pete Sharman, John B.,
KG, and numerous other awesome people in the Oracle community!
Special thanks to the folks I work with: Nancy Von Dolteren, Yang Jiang, Don
Mongeon, Bob Just, Bill Barker, Wendy Hausler, John King, Bill Sullivan, Gunjan
Nath, Richard McClain, Nirupam Majumdar, and Maritza Gonzalez. You are all great!
Thanks to those who provided moral support (and allowed me to not hold boards for
board breaking while I was writing this!): Mrs. Skutnik, Mr. Alfaro, and all those at
Master Clark’s Karate America in Jacksonville. Thanks to our many friends, who
always support us.
Finally, thanks to my wife and my five kids, who came into my work area about
once a week just to refresh their memories of what I look like.
Robert Freeman
Copyright ©2002 SYBEX, Inc., Alameda, CA
www.sybex.com
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
I would like to take this opportunity to thank the staff at Sybex for all their hard
work through the long months of this project. The staff at Sybex were helpful,
resourceful, and most of all, knowledgeable.
I would also like to thank my wife and family for being tolerant of my absence and
not making the appropriate time to spend with them. So thank you to my wife Ann,
and daughters Rebecca and Dana.
Finally, I would like to thank Robert Freeman for bringing me into this venture and
giving me the opportunity to realize one of my personal goals.
To all involved, a truly grateful THANK YOU.
Mark D. Blomberg
Copyright ©2002 SYBEX, Inc., Alameda, CA
www.sybex.com
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
W

of the database and how to interpret them. That’s what mastering Oracle database
administration, and this book, are about.
INTRODUCTION
“
Copyright ©2002 SYBEX, Inc., Alameda, CA
www.sybex.com
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
INTRODUCTION
xxviii
This text goes beyond basic administration tasks, though it covers those as well.
Within these pages you will find nuggets of our experience that might help you. We
hope that you will also take away from this book the fact that administration of an
Oracle database is a multifaceted job. Finally, remember that the best approach to
management of Oracle is a proactive one. Don’t sit and wait for problems to occur.
Is This Book for You?
We assume that the reader has fundamental knowledge of an Oracle database. If you are
a beginning DBA with little or no understanding of Oracle, you should carefully read
Chapter 1 before going further. In addition, we strongly suggest that you read a selec-
tion of the following books; these are in order from basic to advanced skills coverage:
• Oracle DBA 101 by Marlene L. Theriault, et al
• The Sybex Oracle Certified Professional (OCP) series of certification study
guides, including
• OCP: Oracle8i DBA SQL and PL/SQL Study Guide, 0-7821-2682-0
(Sybex, 2000)
• OCP: Oracle8i DBA Architecture & Administration and Backup & Recovery Study
Guide, 0-7821-2683-9 (Sybex, 2001)
• OCP: Oracle8i DBA Performance Tuning and Network Administration Study
Guide, 0-7821-2684-7 (Sybex, 2000)
Though this set of books is designed primarily for those working toward the
Oracle OCP exams, the study guides really are a good introduction to the Oracle

you are an Oracle support customer, you will have access to this site. Use it to
search out help with existing Oracle bugs, to find Oracle documents, and to
access Oracle forums on various subjects.
www.ixora.com.au
The Ixora site provides a great wealth of Unix-related
Oracle internal knowledge, as well as scripts that you can use to monitor the
health of your database.
What You Need to Know
As you pick up this book, understand that we have not devoted a great number of its
pages to examining every fundamental detail of the Oracle database environment.
You should already be comfortable with putting together SQL statements, and you
should know what SQL*Plus is.
Although the book as a whole is not designed for beginners, beginning DBAs will
find Chapter 1 to be a quick primer that may well be enough to help them on the way
to mastering Oracle8i database administration.
If you are a junior DBA, this book is right up your alley. You’ll be comfortable
already with what’s involved in starting and stopping the database and working with
simple queries. You’ll have some understanding of what the data dictionary is, and
perhaps even be familiar with parts of it. It’s our hope that you’ll eat this book up and
that it will give you the knowledge you need to become a truly great DBA.
If you’re already a master DBA, we hope this text will be a trusted reference, and
perhaps provide insight into some aspects of database administration that you want
to improve.
INTRODUCTION
Copyright ©2002 SYBEX, Inc., Alameda, CA
www.sybex.com
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
INTRODUCTION
xxx
Conventions Used in This Book

Add and subtract from them and incorporate them into your own database manage-
ment activities. Experiment and enjoy!
What’s On the CD
The CD is chock full of goodies!
First, you’ll find a complete electronic edition of the book.
All nine appendices, which are not in the printed book, are on the CD. You’ll find
the Quick Reference appendices especially helpful. They’re all in easy-to-search .PDF
format.
We’ve also provided some trial versions of products that can help you better man-
age your database. Note that these demos require that you purchase a license for con-
tinued use; please read the provided supporting documentation and respect the rights
of the vendors who were kind enough to provide these files. All of the following are
from RevealNet Labs (Quest Software), www.revealnet.com:
• The Formatter Plus tool, which will do a quick syntax check of your SQL. It
works on code that has no Oracle syntax errors and is compilable or executable
on Oracle, but it is more permissive than the PL/SQL compiler or SQL*Plus
environment.
• The RevealNet Knowledge Base for Oracle Administration, to help you find solu-
tions for your questions about Oracle database administration.
• The Active PL/SQL Knowledge Base combines a comprehensive PL/SQL refer-
ence with an extensive PL/SQL Code Library. It offers a valuable source of tech-
nical expertise and a substantial library of procedures and functions.
Also included on the CD is the Oracle8i table, index, and SGA sizing spreadsheet
discussed in Chapter 3 of the book, compliments of Mike Ault and TUSC.
NOTE
For late-breaking information about the CD, including additional files and utili-
ties, see README.TXT in the root directory of the CD.
INTRODUCTION
Copyright ©2002 SYBEX, Inc., Alameda, CA
www.sybex.com

Management
FEATURING:
What is a DBA? 4
Introducing Oracle8i 5
Oracle internals 13
The physilogical Oracle 17
Fundamental Oracle principles 19
Environment settings 21
The Oracle SQL interface tools 22
Using Oracle SQL 29
Oracle PL/SQL 39
Copyright ©2002 SYBEX, Inc., Alameda, CA
www.sybex.com
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.