Table Of ContentOCP:
Oracle 10g
New Features
for Administrators:
Study Guide
Bob Bryla
Biju Thomas
SYBEX®
4355.book Page i Thursday, September 16, 2004 8:09 PM
OCP:
Oracle 10g New Features
for Administrators
Study Guide
4355.book Page ii Thursday, September 16, 2004 8:09 PM
4355.book Page iii Thursday, September 16, 2004 8:09 PM
OCP:
Oracle 10g New Features
for Administrators
Study Guide
Bob Bryla
Biju Thomas
San Francisco • London
4355.book Page iv Thursday, September 16, 2004 8:09 PM
Associate Publisher: Neil Edde
Acquisitions and Developmental Editor: Jeff Kellum
Production Editor: Erica Yee
Technical Editors: Joe Johnson, Bob Wahl
Copy Editor: Kim Wimpsett
Compositor: Happenstance Type-O-Rama
Graphic Illustrator: Jeff Wilson, Happenstance Type-O-Rama
CD Coordinator: Dan Mummert
CD Technician: Kevin Ly
Proofreaders: Amy Rasmussen, Nancy Riddiough
Indexer: Nancy Guenther
Book Designer: Bill Gibson
Cover design: Archer Design
Cover photograph: Photodisc and Victor Arre
Copyright © 2004 SYBEX Inc., 1151 Marina Village Parkway, Alameda, CA 94501. World rights reserved. The
author(s) created reusable code in this publication expressly for reuse by readers. Sybex grants readers limited per-
mission to reuse the code found in this publication or its accompanying CD-ROM so long as the author(s) are
attributed in any application containing the reusable code and the code itself is never distributed, posted online
by electronic transmission, sold, or commercially exploited as a stand-alone product. Aside from this specific
exception concerning reusable code, no part of this publication may be stored in a retrieval system, transmitted,
or reproduced in any way, including but not limited to photocopy, photograph, magnetic, or other record, with-
out the prior agreement and written permission of the publisher.
Library of Congress Card Number: 2004109303
ISBN: 0-7821-4355-5
SYBEX and the SYBEX logo are either registered trademarks or trademarks of SYBEX Inc. in the United States
and/or other countries.
Screen reproductions produced with FullShot 99. FullShot 99 © 1991-1999 Inbit Incorporated. All rights reserved.
FullShot is a trademark of Inbit Incorporated.
Netscape Communications, the Netscape Communications logo, Netscape, and Netscape Navigator are trade-
marks of Netscape Communications Corporation.
Netscape Communications Corporation has not authorized, sponsored, endorsed, or approved this publication
and is not responsible for its content. Netscape and the Netscape Communications Corporate Logos are trade-
marks and trade names of Netscape Communications Corporation. All other product names and/or logos are
trademarks of their respective owners.
Internet screen shot(s) using Microsoft Internet Explorer 6.0 reprinted by permission from Microsoft Corporation.
SYBEX is an independent entity from Oracle Corporation and is not affiliated with Oracle Corporation in any man-
ner. This publication may be used in assisting students to prepare for an Oracle Certified Professional exam. Neither
Oracle Corporation nor SYBEX warrants that use of this publication will ensure passing the relevant exam. Oracle
is either a registered trademark or a trademark of Oracle Corporation in the United States and/or other countries.
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, merchantability, 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.
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
4355.book Page v Thursday, September 16, 2004 8:09 PM
Software License Agreement: Terms and Conditions
The media and/or any online materials accompanying Warranty
this book that are available now or in the future contain
SYBEX warrants the enclosed media to be free of physical
programs and/or text files (the “Software”) to be used in
defects for a period of ninety (90) days after purchase. The
connection with the book. SYBEX hereby grants to you
Software is not available from SYBEX in any other
a license to use the Software, subject to the terms that
form or media than that enclosed herein or posted to
follow. Your purchase, acceptance, or use of the Soft-
www.sybex.com. If you discover a defect in the media
ware will constitute your acceptance of such terms.
during this warranty period, you may obtain a replace-
The Software compilation is the property of SYBEX
ment of identical format at no charge by sending the defec-
unless otherwise indicated and is protected by copyright
tive media, postage prepaid, with proof of purchase to:
to SYBEX or other copyright owner(s) as indicated in the
media files (the “Owner(s)”). You are hereby granted a
SYBEX Inc.
single-user license to use the Software for your personal,
Product Support Department
noncommercial use only. You may not reproduce, sell,
1151 Marina Village Parkway
distribute, publish, circulate, or commercially exploit the
Alameda, CA 94501
Software, or any portion thereof, without the written
Web: http://www.sybex.com
consent of SYBEX and the specific copyright owner(s) of
any component software included on this media.
After the 90-day period, you can obtain replacement
In the event that the Software or components include
media of identical format by sending us the defective
specific license requirements or end-user agreements,
disk, proof of purchase, and a check or money order for
statements of condition, disclaimers, limitations or war-
$10, payable to SYBEX.
ranties (“End-User License”), those End-User Licenses
supersede the terms and conditions herein as to that par- Disclaimer
ticular Software component. Your purchase, acceptance,
SYBEX makes no warranty or representation, either
or use of the Software will constitute your acceptance of
expressed or implied, with respect to the Software or its
such End-User Licenses.
contents, quality, performance, merchantability, or fit-
By purchase, use or acceptance of the Software you fur-
ness for a particular purpose. In no event will SYBEX,
ther agree to comply with all export laws and regula-
its distributors, or dealers be liable to you or any other
tions of the United States as such laws and regulations
party for direct, indirect, special, incidental, consequen-
may exist from time to time.
tial, or other damages arising out of the use of or inabil-
Reusable Code in This Book ity to use the Software or its contents even if advised of
the possibility of such damage. In the event that the Soft-
The author(s) created reusable code in this publication
ware includes an online update feature, SYBEX further
expressly for reuse by readers. Sybex grants readers
disclaims any obligation to provide this feature for any
limited permission to reuse the code found in this pub-
specific duration other than the initial posting.
lication, its accompanying CD-ROM or available for
The exclusion of implied warranties is not permitted by
download from our website so long as the author(s) are
some states. Therefore, the above exclusion may not
attributed in any application containing the reusable code
apply to you. This warranty provides you with specific
and the code itself is never distributed, posted online by
legal rights; there may be other rights that you may have
electronic transmission, sold, or commercially exploited
that vary from state to state. The pricing of the book
as a stand-alone product.
with the Software by SYBEX reflects the allocation of
Software Support risk and limitations on liability contained in this agree-
ment of Terms and Conditions.
Components of the supplemental Software and any
offers associated with them may be supported by the Shareware Distribution
specific Owner(s) of that material, but they are not sup-
This Software may contain various programs that are
ported by SYBEX. Information regarding any available
distributed as shareware. Copyright laws apply to both
support may be obtained from the Owner(s) using the
shareware and ordinary commercial software, and the
information provided in the appropriate read.me files or
copyright Owner(s) retains all rights. If you try a share-
listed elsewhere on the media.
ware program and continue using it, you are expected to
Should the manufacturer(s) or other Owner(s) cease to
register it. Individual programs differ on details of trial
offer support or decline to honor any offer, SYBEX
periods, registration, and payment. Please observe the
bears no responsibility. This notice concerning support
requirements stated in appropriate files.
for the Software is provided for your information only.
SYBEX is not the agent or principal of the Owner(s), Copy Protection
and SYBEX is in no way responsible for providing any
The Software in whole or in part may or may not be
support for the Software, nor is it liable or responsible
copy-protected or encrypted. However, in all cases,
for any support provided, or not provided, by the
reselling or redistributing these files without authoriza-
Owner(s).
tion is expressly forbidden except as specifically pro-
vided for by the Owner(s) therein.
4355.book Page vi Thursday, September 16, 2004 8:09 PM
4355.book Page vii Thursday, September 16, 2004 8:09 PM
To Mary Christine and the Kids
Bob Bryla
To Shiji and Joshua
Biju Thomas
4355.book Page viii Thursday, September 16, 2004 8:09 PM
Acknowledgments
I would like to thank all the folks at Sybex who made this a most enjoyable and rewarding
experience, including Erica Yee and Jeff Kellum, who reinforced my attention to detail.
Thanks go to Biju for not letting me write too many of these chapters myself (again). Thanks
also to Kim Wimpsett, who filled in the gaps from my college writing courses, and to Joe
Johnson and Bob Wahl for their insightful comments and suggestions.
This book wouldn’t be possible without the love and support from my family throughout the
long nights and weekends when I still managed to find time to give the kids a bath and read
books before bedtime. I loved every minute of it.
Thanks also to my professional colleagues, both past and present, who provided me with
inspiration, support, and guidance and who pushed me a little further to take a little risk now
and then, starting with that math teacher in high school, whose name eludes me at the moment,
who introduced me to computers on a DEC PDP-8 with a teletype and a paper tape reader.
Bob Bryla
I would like to thank the wonderful people at Sybex for their high-quality work. Thank you,
Jeff (development editor), for supporting me, making valuable comments, and ensuring the
chapters have the smooth flow and transition. I thank Erica Yee (production editor) for making
sure every piece of the book ties together. I thank each one of the professionals at Sybex involved
in the publication of this book for their hard work.
I thank Kim Wimpsett (copy editor) for her patience with my writing. Thank you, Kim; your
edits removed the confusion from several sentences and made a difference to the chapters. I
thank Joe Johnson and Bob Wahl for their technical review and invaluable comments. Bob (Bryla),
thank you for doing the initial study and laying the groundwork for the book.
I owe for the support and encouragement from my colleagues at work. Thank you, Paul,
Wendy, Charles, and Balbir.
Finally, all of this was possible because of the love and support from my beloved wife, Shiji.
Thank you, Shiji, for occupying Joshua while I sat in front of the computer. Thank you, Joshua,
for leaving me alone and playing with “Thomas” when I said that “Appa is working.”
Biju Thomas
4355.book Page ix Thursday, September 16, 2004 8:09 PM
Contents at a Glance
Introduction xv
Assessment Test xxiv
Chapter 1 Installing and Upgrading to Oracle 10g 1
Chapter 2 Moving Data and Managing the Scheduler 55
Chapter 3 Automating Management 133
Chapter 4 General Storage Management 195
Chapter 5 Automated Storage Management 243
Chapter 6 Performance and Application Tuning 317
Chapter 7 Backup, Recovery, and High Availability 351
Chapter 8 Security and SQL Enhancements 409
Appendix A SQL*Plus Enhancements 485
Appendix B New and Obsolete Initialization Parameters 491
Appendix C PL/SQL Enhancements and New Packages 497
Glossary 503
Index 515
