p. 1

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 doubleclick on rp500enu.exe in the adobe folder navigation 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 search 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 click here to begin using your sybex elect ronic book www.sybex.com

[close]

p. 2

oca/ocp introduction to oracle9itm sql study guide chip dawes biju thomas san francisco · london copyright ©2002 sybex inc alameda ca www.sybex.com

[close]

p. 3

associate publishers richard mills and neil edde acquisitions editor christine mcgeever developmental editor elizabeth hurley editors marilyn smith and carol henry production editor leslie e h light technical editors ashok hanumanth and damir bersinic graphic illustrator tony jonick electronic publishing specialist jill niles proofreaders emily hsuan david nash laurie o connell and nancy riddiough indexer ted laux cd coordinator dan mummert cd technician kevin ly book designer bill gibson cover designer archer design cover photographer photo researchers copyright © 2002 sybex inc 1151 marina village parkway alameda ca 94501 world rights reserved 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 without the prior agreement and written permission of the publisher library of congress card number 2001099188 isbn 0-7821-4062-9 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 the cd interface was created using macromedia director copyright 1994 1997-1999 macromedia inc for more information on macromedia and macromedia director visit http www.macromedia.com internet screen shots using microsoft internet explorer reprinted by permission from microsoft corporation sybex is an independent entity from oracle corporation and is not affiliated with oracle corporation in any manner this publication may be used in assisting students to prepare for an oracle certified associate/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 manufacturers 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 copyright ©2002 sybex inc alameda ca www.sybex.com

[close]

p. 4

to our valued readers in a certcities.com article dated december 15 2001 oracle certification was ranked #2 in a list of the 10 hottest certifications for 2002 this shouldn t come as a surprise especially when you consider the fact that the ocp program nearly tripled in size from 30,000 to 80,000 in the last year oracle continues to expand its dominance in the database market and as companies begin integrating oracle9i systems into their it infrastructure you can be assured of high demand for professionals with the oracle certified associate and oracle certified professional certifications sybex is proud to have helped thousands of oracle certification candidates prepare for the exams over the years and we are excited about the opportunity to continue to provide professionals like you with the skills needed to succeed in the highly competitive it industry our authors and editors have worked hard to ensure that the oracle9i study guide you hold in your hands is comprehensive in-depth and pedagogically sound we re confident that this book will meet and exceed the demanding standards of the certification marketplace and help you the oracle9i certification candidate succeed in your endeavors good luck in pursuit of your oracle9i certification neil edde associate publisher certification sybex inc sybex inc 1151 marina village parkway alameda ca 94501 tel 510/523-8233 fax 510/523-2373 http www.sybex.com copyright ©2002 sybex inc alameda ca www.sybex.com

[close]

p. 5

software license agreement terms and conditions the media and/or any online materials accompanying this book that are available now or in the future contain programs and/or text files the software to be used in connection with the book sybex hereby grants to you a license to use the software subject to the terms that follow your purchase acceptance or use of the software will constitute your acceptance of such terms the software compilation is the property of sybex unless otherwise indicated and is protected by copyright to sybex or other copyright owners as indicated in the media files the owners you are hereby granted a single-user license to use the software for your personal noncommercial use only you may not reproduce sell distribute publish circulate or commercially exploit the software or any portion thereof without the written consent of sybex and the specific copyright owners of any component software included on this media in the event that the software or components include specific license requirements or end-user agreements 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 further agree to comply with all export laws and regulations of the united states as such laws and regulations may exist from time to time reusable code in this book the authors created reusable code in this publication expressly for reuse for readers sybex grants readers permission to reuse for any purpose the code found in this publication or its accompanying cd-rom so long as all of the authors are attributed in any application containing the reusable code and the code itself is never sold or commercially exploited as a stand-alone product software support components of the supplemental software and any offers associated with them may be supported by the specific owners of that material but they are not supported by sybex information regarding any available support may be obtained from the owners using the information provided in the appropriate read.me files or listed elsewhere on the media should the manufacturers or other owners 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 owners 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 owners warranty sybex warrants the enclosed media to be free of physical defects for a period of ninety 90 days after purchase the software is not available from sybex in any other form or media than that enclosed herein or posted to www.sybex.com if you discover a defect in the media during this warranty period you may obtain a replacement of identical format at no charge by sending the defective media postage prepaid with proof of purchase to sybex inc product support department 1151 marina village parkway alameda ca 94501 web http www.sybex.com after the 90-day period you can obtain replacement media of identical format by sending us the defective disk proof of purchase and a check or money order for $10 payable to sybex disclaimer sybex makes no warranty or representation either expressed or implied with respect to the software or its contents quality performance merchantability or fitness for a particular purpose in no event will sybex its distributors or dealers be liable to you or any other party for direct indirect special incidental consequential or other damages arising out of the use of or inability to use the software or its contents even if advised of the possibility of such damage in the event that the software includes an online update feature sybex further disclaims any obligation to provide this feature for any specific duration other than the initial posting 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 specific 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 allocation 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 owners retains all rights if you try a shareware 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 copyprotected or encrypted however in all cases reselling or redistributing these files without authorization is expressly forbidden except as specifically provided for by the owners therein copyright ©2002 sybex inc alameda ca www.sybex.com

[close]

p. 6

to my wife mary chip dawes to my son joshua the new light in my life biju thomas copyright ©2002 sybex inc alameda ca www.sybex.com

[close]

p. 7

acknowledgments i would like to acknowledge jerie dahlman for her help with many of the questions used in this book many thanks to the sybex team elizabeth hurley leslie light and marilyn smith your skill and efforts have made for a fine book lastly thank you to my family mary zachary and charlie for giving me the support i needed to complete this book chip dawes within a couple of days of oracle announcing the beta exams for oracle9i certification sybex contacted me to inquire about my interest in another project i m impressed by the market awareness of sybex and thank sybex for considering me for this project i would like to thank the following wonderful people at sybex for their support and patience christine mcgeever acquisition editor for getting me started on this project her timely call helped me register for the oracle9i beta exams and get my oracle9i certification even before the exams were in production elizabeth hurley development editor for her support and valuable comments leslie light production editor for her patience and understanding and making sure every piece of the book ties together and also keeping us on schedule i know many more people from sybex contributed to this book i thank each one of them for their hard work and the high quality of that work i owe marilyn smith editor for her hard work marilyn your edits and topic rearrangements definitely improved the quality of the chapters thank you i thank carol henry for her edits and comments i thank ashok hanumanth and damir bersinic for their technical review and valuable comments thank you chip for your ideas and suggestions and for taking time to reply to most of the reader comments it would not have been possible for me to participate in this project if my parents had not come over to the u.s from india to take care of our son joshua i thank my parents for taking care of the baby and house for the past five months thank you shiji for your endless support and love last but not least i thank my colleagues for their support and friendship thank you wendy for understanding me so well and all the help you provided thank you all you are the best to work with biju thomas copyright ©2002 sybex inc alameda ca www.sybex.com

[close]

p. 8

introduction there is high demand for professionals in the information technology it industry and oracle certifications are the hottest credential in the database world you have made the right decision to pursue certification because being oracle certified will give you a distinct advantage in this highly competitive market many readers may already be familiar with oracle and do not need an introduction to the oracle database world for those who aren t familiar with the company oracle founded in 1977 sold the first commercial relational database and is now the world s leading database company and second-largest independent software company with revenues of more than $10 billion serving more than 145 countries oracle databases are the de facto standard for large internet sites and oracle advertisers are boastful but honest when they proclaim the internet runs on oracle almost all big internet sites run oracle databases oracle s penetration of the database market runs deep and is not limited to dot-com implementations enterprise resource planning erp application suites data warehouses and custom applications at many companies rely on oracle the demand for dba resources remains higher than others during weak economic times this book is intended to help you on your exciting path toward becoming an oracle9i oracle certified associate oca which is the first step on the path toward oracle certified professional ocp and oracle certified master ocm certification basic knowledge of oracle sql is an advantage when reading this book but is not mandatory using this book and a practice database you can start learning oracle and pass the iz0-007 test introduction to oracle9i sql why become an oracle certified professional the number one reason to become an ocp is to gain more visibility and greater access to the industry s most challenging opportunities oracle certification is the best way to demonstrate your knowledge and skills in oracle database systems the certification tests are scenario-based which is the most effective way to assess your hands-on expertise and critical problemsolving skills copyright ©2002 sybex inc alameda ca www.sybex.com

[close]

p. 9

xxii introduction certification is proof of your knowledge and shows that you have the skills required to support oracle core products the oracle certification program can help a company to identify proven performers who have demonstrated their skills and who can support the company s investment in oracle technology it demonstrates that you have a solid understanding of your job role and the oracle products used in that role ocps are among the best paid in the it industry salary surveys consistently show the ocp certification to yield higher salaries than other certifications including microsoft novell and cisco so whether you are beginning a career changing careers securing your present position or seeking to refine and promote your position this book is for you oracle certifications oracle certifications follow a track that is oriented toward a job role there are database administration database operator and developer tracks within each track oracle has a three-tiered certification program the first tier is the oracle certified associate oca oca certification typically requires you to complete two exams the first via the internet and the second in a proctored environment the second tier is the oracle certified professional ocp which builds upon and requires an oca certification the additional requirements for ocp certification are additional proctored exams the third and highest tier is the oracle certified master ocm ocm certification builds upon and requires ocp certification to achieve ocm certification you must attend two advanced oracle education classroom courses from a specific list of qualifying courses and complete a practicum exam the following material will address only the database administration track because at the time of this writing it was the only 9i track offered by oracle the other tracks have 8 and 8i certifications and will undoubtedly have 9i certifications see the oracle website at http www.oracle.com education/certification for the latest information copyright ©2002 sybex inc alameda ca www.sybex.com

[close]

p. 10

introduction xxiii oracle9i certified database associate the role of the database administrator dba has become a key to success in today s highly complex database systems the best dbas work behind the scenes but are in the spotlight when critical issues arise they plan create maintain and ensure that the database is available for the business they are always watching the database for performance issues and to prevent unscheduled downtime the dba s job requires broad understanding of the architecture of oracle database and expertise in solving problems the oracle9i certified database associate is the entry-level certification for the database administration track and is required to advance toward the more senior certification tiers this certification requires you to pass two exams that demonstrate your knowledge of oracle basics 1z0-007 introduction to oracle9i sql 1z0-031 oracle9i database fundamentals i the 1z0-007 exam introduction to oracle9i sql is offered on the internet the 1z0-031 exam oracle9i database fundamentals i is offered at a sylvan prometric facility oracle9i certified database administrator the ocp tier of the database administration track challenges you to demonstrate your continuing experience and knowledge of oracle technologies the oracle9i certified database administrator certification requires achievement of the certified database associate tier as well as passing the following two exams at a sylvan prometric facility 1z0-032 oracle9i database fundamentals ii 1z0-033 oracle9i database performance tuning oracle9i certified master the oracle9i certified master is the highest level of certification that oracle offers to become a certified master you must first achieve certified database administrator status then complete two advanced instructor-led classes at an oracle education facility and finally pass a hands-on exam at oracle education the classes and practicum exam are offered only at an copyright ©2002 sybex inc alameda ca www.sybex.com

[close]

p. 11

xxiv introduction oracle education facility and may require travel the advanced classes that will count toward your ocm requirement include the following oracle9i program with pl/sql oracle9i advanced pl/sql oracle9i sql tuning workshop oracle9i high availability in an internet environment oracle9i database implement partitioning oracle9i real application clusters implementation oracle9i data warehouse administration oracle9i advanced replication oracle9i enterprise manager more information the most current information about oracle certification can be found at http www.oracle.com/education/certification follow the certification link and choose the track that you are interested in read the candidate guide for the test objectives and test contents and keep in mind that they can change at any time without notice oca/ocp study guides the oracle9i database administration track certification consists of four tests two for oca level and two more for ocp level sybex offers several study guides to help you achieve these certifications oca/ocp introduction to oracle9itm sql study guide exam 1z0-007 introduction to oracle9i sql oca/ocp oracle9itm dba fundamentals i study guide exam 1z0-031 oracle9i database fundamentals i ocp oracle9itm dba fundamentals ii study guide exam 1z0032 oracle9i database fundamentals ii ocp oracle9itm dba performance tuning study guide exam 1z0033 oracle9i database performance tuning additionally these four books are offered in a boxed set ocp oracle9itm dba certification kit copyright ©2002 sybex inc alameda ca www.sybex.com

[close]

p. 12

introduction xxv skills required for dba certification to pass the certification exams you need to master the following skills write sql select statements that display data from either single or multiple tables restrict sort aggregate and manipulate data using both single and group functions create and manage tables views constraints synonyms sequences and indexes create users and roles to control user access and maintain security understand oracle server architecture database and instance understand the physical and logical storage of the database and be able to manage space allocation and growth manage data including its storage loading and reorganization manage redo logs automatic undo and rollback segments use globalization features to choose a database character set and national language support nls parameters configure net8 on the server side and the client side use backup and recovery options archive redo log files and hot backups perform backup and recovery operations using recovery manager rman use data dictionary views and set database parameters configure and use multithreaded server mts and connection manager identify and tune database and sql performance use the tuning/diagnostics tools statspack tkprof and explain plan tune the size of data blocks the shared pool the buffer caches and rollback segments diagnose contention for latches locks and rollback segments copyright ©2002 sybex inc alameda ca www.sybex.com

[close]

p. 13

xxvi introduction tips for taking the ocp exam use the following tips to help you prepare for and pass each exam each ocp test contains about 60­80 questions to be completed in 90 minutes answer the questions you know first so that you do not run out of time many questions on the exam have answer choices that at first glance look identical read the questions carefully do not just jump to conclusions make sure that you clearly understand exactly what each question asks most of the test questions are scenario-based some of the scenarios contain nonessential information and exhibits you need to be able to identify what s important and what s not important do not leave any questions unanswered there is no negative scoring after selecting an answer you can mark a difficult question or one that you re unsure of and come back to it later when answering questions that you are not sure about use a process of elimination to get rid of the obviously incorrect answers first doing this greatly improves your odds if you need to make an educated guess if you re not sure of your answer mark it for review and then look for other questions that may help you eliminate any incorrect answers at the end of the test you can go back and review the questions that you marked for review where do you take the exam you take the introduction to oracle9i sql exam 1z0-007 via the internet to register for an online oracle certification exam you will need an internet connection of at least 33kbps but a 56kbps lan or broadband connection is recommended you will also need either internet explorer 5.0 or above or netscape 4.x oracle does not recommend netscape 5.x or 6.x at the time of this writing the online 1z0-007 exam is $90 if you do not have a credit card to use for payment you will need to contact oracle to purchase a voucher you can pay with a certification voucher promotional code or credit card you may take the other exams at any of the more than 800 sylvan prometric authorized testing centers around the world for the location of a copyright ©2002 sybex inc alameda ca www.sybex.com

[close]

p. 14

introduction xxvii testing center near you call 1-800-891-3926 outside the united states and canada contact your local sylvan prometric registration center usually the tests can be taken in any order to register for a proctored oracle certified professional exam at a sylvan prometric test center determine the number of the exam you want to take register with sylvan prometric online at http www.2test.com or in north america by calling 1-800-891-exam 800-891-3926 at this point you will be asked to pay in advance for the exam at the time of this writing the exams are $125 each and must be taken within one year of payment when you schedule the exam you ll get instructions regarding all appointment and cancellation procedures the id requirements and information about the testing-center location you can schedule exams up to six weeks in advance or as soon as one working day before the day you wish to take it if something comes up and you need to cancel or reschedule your exam appointment contact sylvan prometric at least 24 hours in advance what does this book cover this book covers everything you need to pass the introduction to oracle9i sql exam this exam is part of the oracle9i certified database associate certification tier in the database administration track it teaches you the basics of oracle and sql each chapter begins with a list of exam objectives chapter 1 starts with the fundamentals of sql and describes how to construct simple queries chapter 2 discusses sqlplus oracle s tool to interact with the database chapter 3 discusses the single-row functions available in oracle with details on how and where to use them chapter 4 explains data aggregations oracle s built-in group functions and nesting of functions chapter 5 explains how data from multiple tables can be combined via joins and subqueries copyright ©2002 sybex inc alameda ca www.sybex.com

[close]

p. 15

xxviii introduction chapter 6 explores how to manipulate data adding combining and removing data from tables this chapter also covers how transaction control works chapter 7 discusses creating tables with the various datatypes and options available to store data chapter 8 describes how to create and manage views chapter 9 discusses database objects other than tables or views including sequences synonyms and indexes chapter 10 covers security and user access including user account maintenance and the different types of oracle privileges each chapter ends with review questions that are specifically designed to help you retain the knowledge presented to really nail down your skills read and answer each question carefully how to use this book this book can provide a solid foundation for the serious effort of preparing for the oca database administration exam track to best benefit from this book use the following study method 1 take the assessment test immediately following this introduction the answers are at the end of the test carefully read over the explanations for any questions you get wrong and note which chapters the material comes from this information should help you plan your study strategy 2 study each chapter carefully making sure that you fully understand the information and the test objectives listed at the beginning of each chapter pay extra close attention to any chapter related to questions you missed in the assessment test 3 complete all hands-on exercises in the chapter referring to the chapter so that you understand the reason for each step you take if you do not have an oracle database available be sure to study the examples carefully answer the review questions related to that chapter the answers appear at the end of each chapter after the review questions section 4 note the questions that confuse or trick you and study those sections of the book again copyright ©2002 sybex inc alameda ca www.sybex.com

[close]