1. OCA/OCP Oracle Database 11g All-in-One Exam Guide (Exam 1Z0-051, 1Z0-052, and 1Z0-053) McGraw-Hill is an independent entity from Oracle Corporation. This publication and CD may be used in assisting students to prepare for the OCA exams 1Z0-051 and 1Z0-052, and the OCP exam 1Z0-053. Neither Oracle Corporation nor The McGraw-Hill Companies warrant that use of this publication will ensure passing the relevant exam.

2. About the Authors John Watson (Oxford, UK) works for BPLC Management Consultants, teaching and consulting throughout Europe and Africa. He was with Oracle University for several years in South Africa, and before that worked for a number of companies, government departments, and NGOs in England and Europe. He is OCP qualified in both database and application server administration. John is the author of several books and numerous articles on technology and has 25 years of experience in IT. Roopesh Ramklass (South Africa), OCP, is an Oracle specialist who has worked in a range of contexts. He was part of Oracles Support team and taught at Oracle University in South Africa for many years. As an independent consultant and manager of his own consulting business, he designed and developed software and training courses based on a wide spectrum of Oracle technologies., including the database, application server, and business intelligence products. Roopesh is a co-author of the OCA Oracle Database 11g: SQL Fundamentals I Exam Guide (Oracle Press, 2008) and has more than 12 years of experience in the IT industry. About the Technical Editors Gavin Powell (Cartersville, GA) is a consultant and technical writer with 20 years of experience in the IT industry. He has worked as a programmer, developer, analyst, data modeler, and database administrator in numerous industries. Bruce Swart (South Africa) works for 2Cana Solutions and has over 14 years of experience in IT. While maintaining a keen interest for teaching others, he has performed several roles, including developer, analyst, team leader, administrator, project manager, consultant, and lecturer. He is OCP qualified in both database and developer roles. He has taught at Oracle University in South Africa for several years and has also spoken at numerous local Oracle User Group conferences. His passion is helping others achieve greatness. April Wells (Austin, TX) is an experienced Oracle DBA who holds multiple DBA OCP certifications. She currently manages Oracle databases and Oracle data warehouses at NetSpend corporation in Austin, Texas. Previously, April has worked for Oracle Corporation in Austin, Texas, as on-site support at Dell, at Corporate Systems in Amarillo, Texas, and at U.S. Steel in Pennsylvania and Minnesota. 3. OCA/OCP Oracle Database 11g All-in-One Exam Guide (Exam 1Z0-051, 1Z0-052, and 1Z0-053) John Watson Roopesh Ramklass New York Chicago San Francisco Lisbon London Madrid Mexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto 4. Copyright 2010 by The McGraw-Hill Companies, Inc. All rights reserved. Except as permitted under the United States Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of the publisher. ISBN: 978-0-07-162921-8 MHID: 0-07-162921-1 The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-162918-8, MHID: 0-07-162918-1. All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trademarked name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of infringement of the trademark. Where such designations appear in this book, they have been printed with initial caps. McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate training programs. To contact a representative please e-mail us at bulksales@mcgraw-hill.com. Information has been obtained by Publisher from sources believed to be reliable. However, because of the possibility of human or mechanical error by our sources, Publisher, or others, Publisher does not guarantee to the accuracy, adequacy, or completeness of any information included in this work and is not responsible for any errors or omissions or the results obtained from the use of such information. Oracle Corporation does not make any representations or warranties as to the accuracy, adequacy, or completeness of any information contained in this Work, and is not responsible for any errors or omissions. TERMS OF USE This is a copyrighted work and The McGraw-Hill Companies, Inc. (McGraw-Hill) and its licensors reserve all rights in and to the work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hills prior consent. You may use the work for your own noncommercial and personal use; any other use of the work is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these terms. THE WORK IS PROVIDED AS IS. McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the functions contained in the work will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or for any damages resulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that result from the use of or inability to use the work, even if any of them has been advised of the possibility of such damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise. Disclaimer: This eBook does not include the ancillary media that was packaged with the original printed version of the book. 5. If there are other Oracle users at your location who would like to receive their own subscription to Oracle Magazine, please photo- copy this form and pass it along. Three easy ways to subscribe: Web oracle.com/oraclemagazine Fax +1.847.763.9638 Mail P.O. Box 1263, Skokie, IL 60076-8263 1 2 3 FREE SUBSCRIPTIONGET YOUR TO ORACLE MAGAZINE Oracle Magazine is essential gear for todays information technology professionals. Stay informed and increase your productivity with every issue of Oracle Magazine. Inside each free bimonthly issue youll get: Copyright 2008, Oracle and/or its afliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its afliates. Other names may be trademarks of their respective owners. 6. WHAT IS THE PRIMARY BUSINESS ACTIVITY OF YOUR FIRM AT THIS LOCATION? (check one only) o 01 Aerospace and Defense Manufacturing o 02 Application Service Provider o 03 Automotive Manufacturing o 04 Chemicals o 05 Media and Entertainment o 06 Construction/Engineering o 07 Consumer Sector/Consumer Packaged Goods o 08 Education o 09 Financial Services/Insurance o 10 Health Care o 11 High Technology Manufacturing, OEM o 12 Industrial Manufacturing o 13 Independent Software Vendor o 14 Life Sciences (biotech, pharmaceuticals) o 15 Natural Resources o 16 Oil and Gas o 17 Professional Services o 18 Public Sector (government) o 19 Research o 20 Retail/Wholesale/Distribution o 21 Systems Integrator, VAR/VAD o 22 Telecommunications o 23 Travel and Transportation o 24 Utilities (electric, gas, sanitation, water) o 98 Other Business and Services _________ WHICH OF THE FOLLOWING BEST DESCRIBES YOUR PRIMARY JOB FUNCTION? (check one only) CORPORATE MANAGEMENT/STAFF o 01 Executive Management (President, Chair, CEO, CFO, Owner, Partner, Principal) o 02 Finance/Administrative Management (VP/Director/ Manager/Controller, Purchasing, Administration) o 03 Sales/Marketing Management (VP/Director/Manager) o 04 Computer Systems/Operations Management (CIO/VP/Director/Manager MIS/IS/IT, Ops) IS/IT STAFF o 05 Application Development/Programming Management o 06 Application Development/Programming Staff o 07 Consulting o 08 DBA/Systems Administrator o 09 Education/Training o 10 Technical Support Director/Manager o 11 Other Technical Management/Staff o 98 Other WHAT IS YOUR CURRENT PRIMARY OPERATING PLATFORM (check all that apply) o 01 Digital Equipment Corp UNIX/VAX/VMS o 02 HP UNIX o 03 IBM AIX o 04 IBM UNIX o 05 Linux (Red Hat) o 06 Linux (SUSE) o 07 Linux (Oracle Enterprise) o 08 Linux (other) o 09 Macintosh o 10 MVS o 11 Netware o 12 Network Computing o 13 SCO UNIX o 14 Sun Solaris/SunOS o 15 Windows o 16 Other UNIX o 98 Other 99 o None of the Above DO YOU EVALUATE, SPECIFY, RECOMMEND, OR AUTHORIZE THE PURCHASE OF ANY OF THE FOLLOWING? (check all that apply) o 01 Hardware o 02 Business Applications (ERP, CRM, etc.) o 03 Application Development Tools o 04 Database Products o 05 Internet or Intranet Products o 06 Other Software o 07 Middleware Products 99 o None of the Above IN YOUR JOB, DO YOU USE OR PLAN TO PUR- CHASE ANY OF THE FOLLOWING PRODUCTS? (check all that apply) SOFTWARE o 01 CAD/CAE/CAM o 02 Collaboration Software o 03 Communications o 04 Database Management o 05 File Management o 06 Finance o 07 Java o 08 Multimedia Authoring o 09 Networking o 10 Programming o 11 Project Management o 12 Scientific and Engineering o 13 Systems Management o 14 Workflow HARDWARE o 15 Macintosh o 16 Mainframe o 17 Massively Parallel Processing o 18 Minicomputer o 19 Intel x86(32) o 20 Intel x86(64) o 21 Network Computer o 22 Symmetric Multiprocessing o 23 Workstation Services SERVICES o 24 Consulting o 25 Education/Training o 26 Maintenance o 27 Online Database o 28 Support o 29 Technology-Based Training o 30 Other 99 o None of the Above WHAT IS YOUR COMPANYS SIZE? (check one only) o 01 More than 25,000 Employees o 02 10,001 to 25,000 Employees o 03 5,001 to 10,000 Employees o 04 1,001 to 5,000 Employees o 05 101 to 1,000 Employees o 06 Fewer than 100 Employees DURING THE NEXT 12 MONTHS, HOW MUCH DO YOU ANTICIPATE YOUR ORGANIZATION WILL SPEND ON COMPUTER HARDWARE, SOFTWARE, PERIPHERALS, AND SERVICES FOR YOUR LOCATION? (check one only) o 01 Less than $10,000 o 02 $10,000 to $49,999 o 03 $50,000 to $99,999 o 04 $100,000 to $499,999 o 05 $500,000 to $999,999 o 06 $1,000,000 and Over WHAT IS YOUR COMPANYS YEARLY SALES REVENUE? (check one only) o 01 $500, 000, 000 and above o 02 $100, 000, 000 to $500, 000, 000 o 03 $50, 000, 000 to $100, 000, 000 o 04 $5, 000, 000 to $50, 000, 000 o 05 $1, 000, 000 to $5, 000, 000 WHAT LANGUAGES AND FRAMEWORKS DO YOU USE? (check all that apply) o 01 Ajax o 13 Python o 02 C o 14 Ruby/Rails o 03 C++ o 15 Spring o 04 C# o 16 Struts o 05 Hibernate o 17 SQL o 06 J++/J# o 18 Visual Basic o 07 Java o 98 Other o 08 JSP o 09 .NET o 10 Perl o 11 PHP o 12 PL/SQL WHAT ORACLE PRODUCTS ARE IN USE AT YOUR SITE? (check all that apply) ORACLE DATABASE o 01 Oracle Database 11g o 02 Oracle Database 10g o 03 Oracle9i Database o 04 Oracle Embedded Database (Oracle Lite, Times Ten, Berkeley DB) o 05 Other Oracle Database Release ORACLE FUSION MIDDLEWARE o 06 Oracle Application Server o 07 Oracle Portal o 08 Oracle Enterprise Manager o 09 Oracle BPEL Process Manager o 10 Oracle Identity Management o 11 Oracle SOA Suite o 12 Oracle Data Hubs ORACLE DEVELOPMENT TOOLS o 13 Oracle JDeveloper o 14 Oracle Forms o 15 Oracle Reports o 16 Oracle Designer o 17 Oracle Discoverer o 18 Oracle BI Beans o 19 Oracle Warehouse Builder o 20 Oracle WebCenter o 21 Oracle Application Express ORACLE APPLICATIONS o 22 Oracle E-Business Suite o 23 PeopleSoft Enterprise o 24 JD Edwards EnterpriseOne o 25 JD Edwards World o 26 Oracle Fusion o 27 Hyperion o 28 Siebel CRM ORACLE SERVICES o 28 Oracle E-Business Suite On Demand o 29 Oracle Technology On Demand o 30 Siebel CRM On Demand o 31 Oracle Consulting o 32 Oracle Education o 33 Oracle Support o 98 Other 99 o None of the Above YOU MUST ANSWER ALL10 QUESTIONS BELOW. 1 2 3 4 5 6 7 8 9 08014004 s i g n a t u r e ( r e q u i r e d ) d a t e x From time to time, Oracle Publishing allows our partners exclusive access to our e-mail addresses for special promo- tions and announcements. To be included in this program, please check this circle. If you do not wish to be included, you will only receive notices about your subscription via e-mail. Oracle Publishing allows sharing of our postal mailing list with selected third parties. If you prefer your mailing address not to be included in this program, please check this circle. If at any time you would like to be removed from either mailing list, please contact Customer Service at +1.847.763.9635 or send an e-mail to oracle@halldata.com. If you opt in to the sharing of information, Oracle may also provide you with e-mail related to Oracle products, services, and events. If you want to completely unsubscribe from any e-mail communication from Oracle, please send an e-mail to: unsubscribe@oracle-mail.com with the following in the subject line: REMOVE [your e-mail address]. For complete information on Oracle Publishings privacy practices, please visit oracle.com/html/privacy/html n a m e t i t l e c o m p a n y e - m a i l a d d r e s s s t r e e t / p . o . b o x c i t y / s t a t e / z i p o r p o s t a l c o d e t e l e p h o n e c o u n t r y f a x Want your own FREE subscription? Yes, please send me a FREE subscription Oracle Magazine. No. Would you like to receive your free subscription in digital format instead of print if it becomes available? Yes No To receive a free subscription to Oracle Magazine, you must fill out the entire card, sign it, and date it (incomplete cards cannot be processed or acknowledged).You can also fax your application to +1.847.763.9638. Or subscribe at our Web site at oracle.com/oraclemagazine 10 7. LICENSE AGREEMENT THIS PRODUCT (THE PRODUCT) CONTAINS PROPRIETARY SOFTWARE, DATA AND INFORMATION (INCLUDING DOCUMENTATION) OWNED BY THE McGRAW-HILL COMPANIES, INC. (McGRAW-HILL) AND ITS LICENSORS. YOUR RIGHT TO USE THE PRODUCT IS GOVERNED BY THE TERMS AND CONDITIONS OF THIS AGREEMENT. LICENSE: Throughout this License Agreement, you shall mean either the individual or the entity whose agent opens this package. You are granted a non-exclusive and non-transferable license to use the Product subject to the following terms: (i) If you have licensed a single user version of the Product, the Product may only be used on a single computer (i.e., a single CPU). If you licensed and paid the fee applicable to a local area network or wide area network version of the Product, you are subject to the terms of the following subparagraph (ii). (ii) If you have licensed a local area network version, you may use the Product on unlimited workstations located in one single building selected by you that is served by such local area network. If you have licensed a wide area network version, you may use the Product on unlimited workstations located in multiple buildings on the same site selected by you that is served by such wide area network; provided, however, that any building will not be considered located in the same site if it is more than five (5) miles away from any building included in such site. In addition, you may only use a local area or wide area network version of the Product on one single server. If you wish to use the Product on more than one server, you must obtain written authorization from McGraw-Hill and pay additional fees. (iii) You may make one copy of the Product for back-up purposes only and you must maintain an accurate record as to the location of the back-up at all times. COPYRIGHT; RESTRICTIONS ON USE AND TRANSFER: All rights (including copyright) in and to the Product are owned by McGraw-Hill and its licensors. You are the owner of the enclosed disc on which the Product is recorded. You may not use, copy, decompile, disassemble, reverse engineer, modify, reproduce, create derivative works, transmit, distribute, sublicense, store in a database or retrieval system of any kind, rent or transfer the Product, or any portion thereof, in any form or by any means (including electronically or otherwise) except as expressly provided for in this License Agreement. You must reproduce the copyright notices, trademark notices, legends and logos of McGraw-Hill and its licensors that appear on the Product on the back-up copy of the Product which you are permitted to make hereunder. All rights in the Product not expressly granted herein are reserved by McGraw-Hill and its licensors. TERM: This License Agreement is effective until terminated. It will terminate if you fail to comply with any term or condition of this License Agreement. Upon termination, you are obligated to return to McGraw-Hill the Product together with all copies thereof and to purge all copies of the Product included in any and all servers and computer facilities. DISCLAIMER OF WARRANTY: THE PRODUCT AND THE BACK-UP COPY ARE LICENSED AS IS. McGRAW-HILL, ITS LICENSORS AND THE AUTHORS MAKE NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE RESULTS TO BE OBTAINED BY ANY PERSON OR ENTITY FROM USE OF THE PRODUCT, ANY INFORMATION OR DATA INCLUDED THEREIN AND/OR ANY TECHNICAL SUPPORT SERVICES PROVIDED HEREUNDER, IF ANY (TECHNICAL SUPPORT SERVICES). McGRAW-HILL, ITS LICENSORS AND THE AUTHORS MAKE NO EXPRESS OR IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE OR USE WITH RESPECT TO THE PRODUCT. McGRAW-HILL, ITS LICENSORS, AND THE AUTHORS MAKE NO GUARANTEE THAT YOU WILL PASS ANY CERTIFICATION EXAM WHATSOEVER BY USING THIS PRODUCT. NEITHER McGRAW-HILL, ANY OF ITS LICENSORS NOR THE AUTHORS WARRANT THAT THE FUNCTIONS CONTAINED IN THE PRODUCT WILL MEET YOUR REQUIREMENTS OR THAT THE OPERATION OF THE PRODUCT WILL BE UNINTERRUPTED OR ERROR FREE. YOU ASSUME THE ENTIRE RISK WITH RESPECT TO THE QUALITY AND PERFORMANCE OF THE PRODUCT. LIMITED WARRANTY FOR DISC: To the original licensee only, McGraw-Hill warrants that the enclosed disc on which the Product is recorded is free from defects in materials and workmanship under normal use and service for a period of ninety (90) days from the date of purchase. In the event of a defect in the disc covered by the foregoing warranty, McGraw-Hill will replace the disc. LIMITATION OF LIABILITY: NEITHER McGRAW-HILL, ITS LICENSORS NOR THE AUTHORS SHALL BE LIABLE FOR ANY INDIRECT, SPECIAL OR CONSEQUENTIAL DAMAGES, SUCH AS BUT NOT LIMITED TO, LOSS OF ANTICIPATED PROFITS OR BENEFITS, RESULTING FROM THE USE OR INABILITY TO USE THE PRODUCT EVEN IF ANY OF THEM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. THIS LIMITATION OF LIABILITY SHALL APPLY TO ANY CLAIM OR CAUSE WHATSOEVER WHETHER SUCH CLAIM OR CAUSE ARISES IN CONTRACT, TORT, OR OTHERWISE. Some states do not allow the exclusion or limitation of indirect, special or consequential damages, so the above limitation may not apply to you. U.S. GOVERNMENT RESTRICTED RIGHTS: Any software included in the Product is provided with restricted rights subject to subparagraphs (c), (1) and (2) of the Commercial Computer Software-Restricted Rights clause at 48 C.F.R. 52.227-19. The terms of this Agreement applicable to the use of the data in the Product are those under which the data are generally made available to the general public by McGraw-Hill. Except as provided herein, no reproduction, use, or disclosure rights are granted with respect to the data included in the Product and no right to modify or create derivative works from any such data is hereby granted. GENERAL: This License Agreement constitutes the entire agreement between the parties relating to the Product. The terms of any Purchase Order shall have no effect on the terms of this License Agreement. Failure of McGraw-Hill to insist at any time on strict compliance with this License Agreement shall not constitute a waiver of any rights under this License Agreement. This License Agreement shall be construed and governed in accordance with the laws of the State of New York. If any provision of this License Agreement is held to be contrary to law, that provision will be enforced to the maximum extent permissible and the remaining provisions will remain in full force and effect. 8. Thank you, Silvia, for helping me do this (and for giving me a reason for living). John Ameetha, a more loving and caring companion to share this journey through life, I could not have found. Roopesh 9. This page intentionally left blank 10. CONTENTS AT A GLANCE Part I Oracle Database 11g Administration Chapter 1 Architectural Overview of Oracle Database 11g . . . . . . . . . . . . . . 3 Chapter 2 Installing and Creating a Database . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Chapter 3 Instance Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Chapter 4 Oracle Networking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 Chapter 5 Oracle Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 Chapter 6 Oracle Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203 Part II SQL Chapter 7 DDL and Schema Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 Chapter 8 DML and Concurrency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315 Chapter 9 Retrieving, Restricting, and Sorting Data Using SQL . . . . . . . . . . . . 367 Chapter 10 Single-Row and Conversion Functions . . . . . . . . . . . . . . . . . . . . . . 419 Chapter 11 Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459 Chapter 12 SQL Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481 Chapter 13 Subqueries and Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . 515 vii 11. OCA/OCP Oracle Database 11g All-in-One Exam Guide viii Part III Advanced Database Administration Chapter 14 Configuring the Database for Backup and Recovery . . . . . . . . . . . 543 Chapter 15 Back Up with RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 577 Chapter 16 Restore and Recover with RMAN . . . . . . . . . . . . . . . . . . . . . . . . . 607 Chapter 17 Advanced RMAN Facilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641 Chapter 18 User-Managed Backup, Restore, and Recovery . . . . . . . . . . . . . . . . 677 Chapter 19 Flashback . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699 Chapter 20 Automatic Storage Management . . . . . . . . . . . . . . . . . . . . . . . . . . . 747 Chapter 21 The Resource Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 773 Chapter 22 The Scheduler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 805 Chapter 23 Moving and Reorganizing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . 831 Chapter 24 The AWR and the Alert System . . . . . . . . . . . . . . . . . . . . . . . . . . . 865 Chapter 25 Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 891 Chapter 26 Globalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 937 Chapter 27 The Intelligent Infrastructure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 965 Appendix About the CD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 983 Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 987 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1003 12. CONTENTS Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix Part I Oracle Database 11g Administration Chapter 1 Architectural Overview of Oracle Database 11g . . . . . . . . . . . . . . 3 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Oracle Product Stack . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Oracle Server Family . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Oracle Development Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Oracle Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Prerequisite Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Oracle Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 SQL Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Operating System Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Single-Instance Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Single-Instance Database Architecture . . . . . . . . . . . . . . . . . . . . . 13 Distributed Systems Architectures . . . . . . . . . . . . . . . . . . . . . . . . 16 Instance Memory Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 The Database Buffer Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 The Log Buffer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 The Shared Pool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 The Large Pool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 The Java Pool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 The Streams Pool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Instance Process Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 SMON, the System Monitor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 PMON, the Process Monitor . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 DBWn, the Database Writer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 LGWR, the Log Writer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 CKPT, the Checkpoint Process . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 MMON, the Manageability Monitor . . . . . . . . . . . . . . . . . . . . . . 34 ix 13. OCA/OCP Oracle Database 11g All-in-One Exam Guide x MMNL, the Manageability Monitor Light . . . . . . . . . . . . . . . . . . 35 MMAN, the Memory Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 ARCn, the Archiver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 RECO, the Recoverer Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Some Other Background Processes . . . . . . . . . . . . . . . . . . . . . . . 37 Database Storage Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 The Physical Database Structures . . . . . . . . . . . . . . . . . . . . . . . . . 41 The Logical Database Structures . . . . . . . . . . . . . . . . . . . . . . . . . . 45 The Data Dictionary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Single-Instance Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Instance Memory Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Instance Process Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Database Storage Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Chapter 2 Installing and Creating a Database . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Identify the Tools for Administering an Oracle Database . . . . . . . . . . . 56 The Oracle Universal Installer . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 Database Creation and Upgrade Tools . . . . . . . . . . . . . . . . . . . . 60 Tools for Issuing Ad Hoc SQL: SQL*Plus and SQL Developer . 61 Oracle Enterprise Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Other Administration Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Plan an Oracle Database Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Choice of Operating System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Hardware and Operating System Resources . . . . . . . . . . . . . . . . 69 Optimal Flexible Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 Environment Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 Install the Oracle Software by Using the Oracle Universal Installer (OUI) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Create a Database by Using the Database Configuration Assistant . . . 77 The Instance, the Database, and the Data Dictionary . . . . . . . . . 78 Using the DBCA to Create a Database . . . . . . . . . . . . . . . . . . . . . 79 The Scripts and Other Files Created by the DBCA . . . . . . . . . . . 84 The DBCAs Other Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 Identify the Tools for Administering an Oracle Database . . . . . 91 Plan an Oracle Database Installation . . . . . . . . . . . . . . . . . . . . . 92 Install the Oracle Software by Using the Oracle Universal Installer (OUI) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 Create a Database by Using the Database Configuration Assistant . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 14. Contents xi Chapter 3 Instance Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Set Database Initialization Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 101 Static and Dynamic Parameters and the Initialization Parameter File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 The Basic Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 Describe the Stages of Database Startup and Shutdown . . . . . . . . . . . . 108 Starting and Connecting to Database Control . . . . . . . . . . . . . . 108 Starting the Database Listener . . . . . . . . . . . . . . . . . . . . . . . . . . . 110 Starting SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 Database Startup and Shutdown . . . . . . . . . . . . . . . . . . . . . . . . . 112 Use the Alert Log and Trace Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121 Use Data Dictionary and Dynamic Performance Views . . . . . . . . . . . . 123 The Data Dictionary Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 The Dynamic Performance Views . . . . . . . . . . . . . . . . . . . . . . . . 124 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 Describe the Stages of Database Startup and Shutdown . . . . . . 126 Set Database Initialization Parameters . . . . . . . . . . . . . . . . . . . . 126 Use the Alert Log and Trace Files . . . . . . . . . . . . . . . . . . . . . . . . . 127 Use Data Dictionary and Dynamic Performance Views . . . . . . . 127 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130 Chapter 4 Oracle Networking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 Configure and Manage the Oracle Network . . . . . . . . . . . . . . . . . . . . . 134 Oracle Net and the Client-Server Paradigm . . . . . . . . . . . . . . . . . 134 A Word on Oracle Net and Communication Protocols . . . . . . . 136 Establishing a Session . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 Creating a Listener . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 Database Registration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 Techniques for Name Resolution . . . . . . . . . . . . . . . . . . . . . . . . . 143 The Listener Control Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 Configuring Service Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 Filenames and the TNSADMIN Environment Variable . . . . . . . 151 Database Links . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 Use the Oracle Shared Server Architecture . . . . . . . . . . . . . . . . . . . . . . . 157 The Limitations of Dedicated Server Architecture . . . . . . . . . . . . 157 The Shared Server Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . 159 Configuring Shared Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 When to Use the Shared Server . . . . . . . . . . . . . . . . . . . . . . . . . . 162 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 Configure and Manage the Oracle Network . . . . . . . . . . . . . . . . 165 Use the Oracle Shared Server Architecture . . . . . . . . . . . . . . . . . 165 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 15. OCA/OCP Oracle Database 11g All-in-One Exam Guide xii Chapter 5 Oracle Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 Overview of Tablespaces and Datafiles . . . . . . . . . . . . . . . . . . . . . . . . . 172 The Oracle Data Storage Model . . . . . . . . . . . . . . . . . . . . . . . . . . 172 Segments, Extents, Blocks, and Rows . . . . . . . . . . . . . . . . . . . . . . 174 File Storage Technologies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178 Create and Manage Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 Tablespace Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 Altering Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186 Dropping Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191 Oracle-Managed Files (OMF) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191 Space Management in Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 Extent Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 Segment Space Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197 Overview of Tablespaces and Datafiles . . . . . . . . . . . . . . . . . . . . 197 Create and Manage Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . 198 Space Management in Tablespaces . . . . . . . . . . . . . . . . . . . . . . . 198 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 Chapter 6 Oracle Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203 Create and Manage Database User Accounts . . . . . . . . . . . . . . . . . . . . . 204 User Account Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205 Authentication Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 Creating Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213 Grant and Revoke Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216 System Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216 Object Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219 Create and Manage Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223 Creating and Granting Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223 Predefined Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224 Enabling Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225 Create and Manage Profiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229 Password Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229 Resource Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230 Creating and Assigning Profiles . . . . . . . . . . . . . . . . . . . . . . . . . . 231 Database Security and Principle of Least Privilege . . . . . . . . . . . . . . . . 234 Public Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234 Security-Critical Instance Parameters . . . . . . . . . . . . . . . . . . . . . . 235 Work with Standard Database Auditing . . . . . . . . . . . . . . . . . . . . . . . . . 240 Auditing SYSDBA Activity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241 Database Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241 Value-Based Auditing with Triggers . . . . . . . . . . . . . . . . . . . . . . . 244 Fine-Grained Auditing (FGA) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245 16. Contents xiii Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248 Create and Manage Database User Accounts . . . . . . . . . . . . . . . 248 Grant and Revoke Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248 Create and Manage Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249 Create and Manage Profiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249 Database Security and Principle of Least Privilege . . . . . . . . . . . 249 Work with Standard Database Auditing . . . . . . . . . . . . . . . . . . . 249 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253 Part II SQL Chapter 7 DDL and Schema Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 Categorize the Main Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . 260 Object Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260 Naming Schema Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261 Object Namespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262 List the Data Types That Are Available for Columns . . . . . . . . . . . . . . . 263 Create a Simple Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266 Creating Tables with Column Specifications . . . . . . . . . . . . . . . . 267 Creating Tables from Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . 268 Altering Table Definitions after Creation . . . . . . . . . . . . . . . . . . 269 Dropping and Truncating Tables . . . . . . . . . . . . . . . . . . . . . . . . . 270 Create and Use Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273 Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275 Why Indexes Are Needed? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275 Types of Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276 Creating and Using Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281 Modifying and Dropping Indexes . . . . . . . . . . . . . . . . . . . . . . . . 282 Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283 The Types of Constraint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283 Defining Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286 Constraint State . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288 Constraint Checking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290 Why Use Views at All? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291 Simple and Complex Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293 CREATE VIEW, ALTER VIEW, and DROP VIEW . . . . . . . . . . . . . . 294 Synonyms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295 Sequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298 Creating Sequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298 Using Sequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303 Categorize the Main Database Objects . . . . . . . . . . . . . . . . . . . . 303 List the Data Types That Are Available for Columns . . . . . . . . . . 303 17. OCA/OCP Oracle Database 11g All-in-One Exam Guide xiv Create a Simple Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304 Create and Use Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . 304 Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304 Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304 Synonyms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305 Sequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311 Chapter 8 DML and Concurrency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315 Data Manipulation Language (DML) Statements . . . . . . . . . . . . . . . . . 316 INSERT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316 UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320 DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323 TRUNCATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325 MERGE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326 DML Statement Failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328 Control Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330 Database Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330 Executing SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331 Transaction Control: COMMIT, ROLLBACK, SAVEPOINT, SELECT FOR UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335 Identify and Administer PL/SQL Objects . . . . . . . . . . . . . . . . . . . . . . . . 340 Stored and Anonymous PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . 340 PL/SQL Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341 Monitor and Resolve Locking Conflicts . . . . . . . . . . . . . . . . . . . . . . . . . 346 Shared and Exclusive Locks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346 The Enqueue Mechanism . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347 Lock Contention . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347 Deadlocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350 Overview of Undo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351 Transactions and Undo Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352 Managing Undo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354 Error Conditions Related to Undo . . . . . . . . . . . . . . . . . . . . . . . . 354 Parameters for Undo Management, and Retention Guarantee . 355 Sizing and Monitoring the Undo Tablespace . . . . . . . . . . . . . . . 356 Creating and Managing Undo Tablespaces . . . . . . . . . . . . . . . . . 358 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359 Describe Each Data Manipulation Language (DML) Statement 359 Control Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360 Manage Data Using DML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360 Identify and Administer PL/SQL Objects . . . . . . . . . . . . . . . . . . 360 Monitor and Resolve Locking Conflicts . . . . . . . . . . . . . . . . . . . . 360 Overview of Undo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360 18. Contents xv Transactions and Undo Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361 Managing Undo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365 Chapter 9 Retrieving, Restricting, and Sorting Data Using SQL . . . . . . . . . . . . 367 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367 List the Capabilities of SQL SELECT Statements . . . . . . . . . . . . . . . . . . 368 Introducing the SQL SELECT Statement . . . . . . . . . . . . . . . . . . . 368 The DESCRIBE Table Command . . . . . . . . . . . . . . . . . . . . . . . . . 369 Capabilities of the SELECT Statement . . . . . . . . . . . . . . . . . . . . . 370 Data Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371 Create the Demonstration Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375 The HR and WEBSTORE Schemas . . . . . . . . . . . . . . . . . . . . . . . . 375 Demonstration Schema Creation . . . . . . . . . . . . . . . . . . . . . . . . 379 Execute a Basic SELECT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381 Syntax of the Primitive SELECT Statement . . . . . . . . . . . . . . . . . 382 Rules Are Meant to Be Followed . . . . . . . . . . . . . . . . . . . . . . . . . . 383 SQL Expressions and Operators . . . . . . . . . . . . . . . . . . . . . . . . . . 386 NULL Is Nothing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390 Limit the Rows Retrieved by a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . 392 The WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392 Comparison Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395 Boolean Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400 Precedence Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402 Sort the Rows Retrieved by a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403 The ORDER BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403 Ampersand Substitution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405 Substitution Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406 Define and Verify . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412 List the Capabilities of SQL SELECT Statements . . . . . . . . . . . . . 412 Execute a Basic SELECT Statement . . . . . . . . . . . . . . . . . . . . . . . . 412 Limit the Rows Retrieved by a Query . . . . . . . . . . . . . . . . . . . . . . 413 Sort the Rows Retrieved by a Query . . . . . . . . . . . . . . . . . . . . . . . 413 Ampersand Substitution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 414 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 416 Chapter 10 Single-Row and Conversion Functions . . . . . . . . . . . . . . . . . . . . . . 419 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419 Describe and Use Character, Number, and Date Functions in SQL . . . 420 Defining a Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420 Types of Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420 Using Case Conversion Functions . . . . . . . . . . . . . . . . . . . . . . . . 421 Using Character Manipulations Functions . . . . . . . . . . . . . . . . . 423 19. OCA/OCP Oracle Database 11g All-in-One Exam Guide xvi Using Numeric Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427 Working with Dates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429 Describe Various Types of Conversion Functions Available in SQL . . . 434 Conversion Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 434 Use the TO_CHAR, TO_NUMBER, and TO_DATE Conversion Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436 Using the Conversion Functions . . . . . . . . . . . . . . . . . . . . . . . . . 436 Apply Conditional Expressions in a SELECT Statement . . . . . . . . . . . . 444 Nested Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444 Conditional Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453 Describe Various Types of Functions Available in SQL . . . . . . . . 453 Use Character, Number, and Date Functions in SELECT Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453 Describe Various Types of Conversion Functions Available in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454 Use the TO_CHAR, TO_NUMBER, and TO_DATE Conversion Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454 Apply Conditional Expressions in a SELECT Statement . . . . . . . 454 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457 Chapter 11 Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459 The Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460 Definition of Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 460 Using Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461 Group Data Using the GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . . 465 Creating Groups of Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465 The GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 466 Grouping by Multiple Columns . . . . . . . . . . . . . . . . . . . . . . . . . . 468 Nested Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 470 Include or Exclude Grouped Rows Using the HAVING Clause . . . . . . 471 Restricting Group Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472 The HAVING Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 475 Describe the Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 475 Identify the Available Group Functions . . . . . . . . . . . . . . . . . . . . 475 Group Data Using the GROUP BY Clause . . . . . . . . . . . . . . . . . . 475 Include or Exclude Grouped Rows Using the HAVING Clause . . 476 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 476 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 478 Chapter 12 SQL Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481 Write SELECT Statements to Access Data from More Than One Table Using Equijoins and Nonequijoins . . . . . . . . . . . . . . . . . . . . . . . . . 482 Types of Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 482 20. Contents xvii Joining Tables Using SQL:1999 Syntax . . . . . . . . . . . . . . . . . . . . 487 Qualifying Ambiguous Column Names . . . . . . . . . . . . . . . . . . . 487 The NATURAL JOIN Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 489 The Natural JOIN USING Clause . . . . . . . . . . . . . . . . . . . . . . . . . 492 The Natural JOIN ON Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492 N-Way Joins and Additional Join Conditions . . . . . . . . . . . . . . . 495 Nonequijoins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 496 Join a Table to Itself Using a Self-Join . . . . . . . . . . . . . . . . . . . . . . . . . . 498 Joining a Table to Itself Using the JOIN . . . ON Clause . . . . . . . 498 View Data That Does Not Meet a Join Condition by Using Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500 Inner Versus Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500 Left Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 501 Right Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 503 Full Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 503 Generate a Cartesian Product of Two or More Tables . . . . . . . . . . . . . . 505 Creating Cartesian Products Using Cross Joins . . . . . . . . . . . . . . 506 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 508 Write SELECT Statements to Access Data from More Than One Table Using Equijoins and Nonequijoins . . . . . . . . . . . 508 Join a Table to Itself Using a Self-Join . . . . . . . . . . . . . . . . . . . . . 509 View Data That Does Not Meet a Join Condition Using Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509 Generate a Cartesian Product of Two or More Tables . . . . . . . . . 509 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 510 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 512 Chapter 13 Subqueries and Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . 515 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 515 Define Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 516 Describe the Types of Problems That the Subqueries Can Solve . . . . . 517 Use of a Subquery Result Set for Comparison Purposes . . . . . . 517 Generate a Table from Which to SELECT . . . . . . . . . . . . . . . . . . 518 Generate Values for Projection . . . . . . . . . . . . . . . . . . . . . . . . . . . 518 Generate Rows to Be Passed to a DML Statement . . . . . . . . . . . . 519 List the Types of Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 520 Single- and Multiple-Row Subqueries . . . . . . . . . . . . . . . . . . . . . 520 Correlated Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 521 Write Single-Row and Multiple-Row Subqueries . . . . . . . . . . . . . . . . . . 524 Describe the Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 525 Sets and Venn Diagrams . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 525 Set Operator General Principles . . . . . . . . . . . . . . . . . . . . . . . . . . 526 Use a Set Operator to Combine Multiple Queries into a Single Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529 The UNION ALL Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529 The UNION Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 530 21. OCA/OCP Oracle Database 11g All-in-One Exam Guide xviii The INTERSECT Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 530 The MINUS Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531 More Complex Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531 Control the Order of Rows Returned . . . . . . . . . . . . . . . . . . . . . . . . . . . 533 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533 Define Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533 Describe the Types of Problems That the Subqueries Can Solve . 533 List the Types of Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534 Write Single-Row and Multiple-Row Subqueries . . . . . . . . . . . . 534 Describe the Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534 Use a Set Operator to Combine Multiple Queries into a Single Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534 Control the Order of Rows Returned . . . . . . . . . . . . . . . . . . . . . . 534 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 535 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539 PART III Advanced Database Administration Chapter 14 Configuring the Database for Backup and Recovery . . . . . . . . . . . 543 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 543 Backup and Recovery Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 544 Categories of Failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 546 Statement Failure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 546 User Process Failure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547 Network Failure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 548 User Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 549 Media Failure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 551 Instance Failure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552 Instance Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552 The Mechanics of Instance Recovery . . . . . . . . . . . . . . . . . . . . . . 553 The Impossibility of Database Corruption . . . . . . . . . . . . . . . . . 554 Tuning Instance Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555 The MTTR Advisor and Checkpoint Auto-Tuning . . . . . . . . . . . . 555 Checkpointing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 557 Preparing the Database for Recoverability . . . . . . . . . . . . . . . . . . . . . . . 558 Protecting the Controlfile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 558 Protecting the Online Redo Log Files . . . . . . . . . . . . . . . . . . . . . 560 Archivelog Mode and the Archiver Process . . . . . . . . . . . . . . . . . 563 Protecting the Archive Redo Log Files . . . . . . . . . . . . . . . . . . . . . 566 The Flash Recovery Area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 567 Recovery Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 567 Configure the Flash Recovery Area . . . . . . . . . . . . . . . . . . . . . . . . 568 Flash Recovery Area Space Usage . . . . . . . . . . . . . . . . . . . . . . . . . 569 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 570 Identify the Types of Failure That Can Occur in an Oracle Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 570 Describe Ways to Tune Instance Recovery . . . . . . . . . . . . . . . . . . 571 22. Contents xix Identify the Importance of Checkpoints, Redo Log Files, and Archived Log Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 571 Configure ARCHIVELOG Mode . . . . . . . . . . . . . . . . . . . . . . . . . . 571 Configure Multiple Archive Log File Destinations to Increase Availability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 571 Overview of the Flash Recovery Area . . . . . . . . . . . . . . . . . . . . . . 572 Configure the Flash Recovery Area . . . . . . . . . . . . . . . . . . . . . . . . 572 Use the Flash Recovery Area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 572 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 572 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 575 Chapter 15 Back Up with RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 577 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 577 Backup Concepts and Terminology . . . . . . . . . . . . . . . . . . . . . . . . . . . . 578 Using the RMAN BACKUP Command to Create Backups . . . . . . . . . . 580 Server-Managed Consistent Backups . . . . . . . . . . . . . . . . . . . . . . 580 Server-Managed Open Backups . . . . . . . . . . . . . . . . . . . . . . . . . . 582 Incremental Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 583 Image Copies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 588 Protect Your Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 588 Parallelizing Backup Operations . . . . . . . . . . . . . . . . . . . . . . . . . 590 Encrypting Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591 Configuring RMAN Defaults . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 592 Managing and Monitoring RMAN Backups . . . . . . . . . . . . . . . . . . . . . . 594 The LIST, REPORT, and DELETE Commands . . . . . . . . . . . . . . . 594 Archival Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 596 The Dynamic Performance Views . . . . . . . . . . . . . . . . . . . . . . . . 597 Crosschecking Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 597 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599 Create Consistent Database Backups . . . . . . . . . . . . . . . . . . . . . . 599 Back Up Your Database Without Shutting It Down . . . . . . . . . . 599 Create Incremental Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599 Automate Database Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600 Manage Backups, View Backup Reports, and Monitor the Flash Recovery Area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600 Define, Apply, and Use a Retention Policy . . . . . . . . . . . . . . . . . 600 Create Image File Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600 Create a Whole Database Backup . . . . . . . . . . . . . . . . . . . . . . . . 600 Enable Fast Incremental Backup . . . . . . . . . . . . . . . . . . . . . . . . . 600 Create Duplex Backups and Back Up Backup Sets . . . . . . . . . . . 600 Create an Archival Backup for Long-Term Retention . . . . . . . . . 601 Create a Multisection, Compressed, and Encrypted Backup . . . 601 Report On and Maintain Backups . . . . . . . . . . . . . . . . . . . . . . . . 601 Configure Backup Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 601 Allocate Channels to Use in Backing Up . . . . . . . . . . . . . . . . . . . 601 Configure Backup Optimization . . . . . . . . . . . . . . . . . . . . . . . . . 601 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 602 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 604 23. OCA/OCP Oracle Database 11g All-in-One Exam Guide xx Chapter 16 Restore and Recover with RMAN . . . . . . . . . . . . . . . . . . . . . . . . . 607 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 607 The Data Recovery Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 608 The Health Monitor and the ADR . . . . . . . . . . . . . . . . . . . . . . . . 608 The Capabilities and Limitations of the DRA . . . . . . . . . . . . . . . 609 Using the Data Recovery Advisor . . . . . . . . . . . . . . . . . . . . . . . . . 610 Database Restore and Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613 Complete Recovery from Data File Loss Using RMAN . . . . . . . . . . . . . 614 Recovery of Datafiles in Noarchivelog Mode . . . . . . . . . . . . . . . 614 Recovery of a Noncritical File in Archivelog Mode . . . . . . . . . . . 616 Recovering from Loss of a Critical Datafile . . . . . . . . . . . . . . . . . 619 Incomplete Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 620 Autobackup and Restore of the Controlfile . . . . . . . . . . . . . . . . . . . . . . 623 Using Image Copies for Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 627 Block Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629 Detection of Corrupt Blocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629 Block Media Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 630 The BLOCK RECOVER Command . . . . . . . . . . . . . . . . . . . . . . . . 630 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631 Describe the Data Recovery Advisor . . . . . . . . . . . . . . . . . . . . . . . 631 Use the Data Recovery Advisor to Perform Recovery (Controlfile, Redo Log File, and Datafile) . . . . . . . . . . . . . . . 631 Perform Complete Recovery from a Critical or Noncritical Data File Loss Using RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . 631 Perform Incomplete Recovery Using RMAN . . . . . . . . . . . . . . . . 632 Recover Using Incrementally Updated Backups . . . . . . . . . . . . . 632 Switch to Image Copies for Fast Recovery . . . . . . . . . . . . . . . . . . 632 Recover Using a Backup Control File . . . . . . . . . . . . . . . . . . . . . . 632 Perform Block Media Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . 633 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 633 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637 Chapter 17 Advanced RMAN Facilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641 The Recovery Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 642 The Need for a Recovery Catalog . . . . . . . . . . . . . . . . . . . . . . . . . 642 Creating and Connecting to the Catalog . . . . . . . . . . . . . . . . . . . 643 The Virtual Private Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 645 Protecting and Rebuilding the Catalog . . . . . . . . . . . . . . . . . . . . 646 Stored Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 649 Using RMAN to Create Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 651 Tablespace Point-in-Time Recovery (TSPITR) . . . . . . . . . . . . . . . . . . . . 654 The TSPITR Methodology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 654 Perform Automated TSPITR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 655 RMAN Performance and Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . 658 Monitoring RMAN Sessions and Jobs . . . . . . . . . . . . . . . . . . . . . 658 24. Contents xxi Tuning RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 662 Tuning the BACKUP Command . . . . . . . . . . . . . . . . . . . . . . . . . 665 Configure RMAN for Asynchronous I/O . . . . . . . . . . . . . . . . . . . 665 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 667 Identify Situations That Require an RMAN Recovery Catalog . . . 667 Create and Configure a Recovery Catalog . . . . . . . . . . . . . . . . . . 667 Synchronize the Recovery Catalog . . . . . . . . . . . . . . . . . . . . . . . . 667 Create and Use RMAN Stored Scripts . . . . . . . . . . . . . . . . . . . . . 668 Back Up the Recovery Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . 668 Create and Use a Virtual Private Catalog . . . . . . . . . . . . . . . . . . . 668 Create a Duplicate Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . 668 Use a Duplicate Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 669 Restore a Database onto a New Host . . . . . . . . . . . . . . . . . . . . . . 669 Perform Disaster Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 669 Identify the Situations That Require TSPITR . . . . . . . . . . . . . . . . 669 Perform Automated TSPITR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 670 Monitor RMAN Sessions and Jobs . . . . . . . . . . . . . . . . . . . . . . . . 670 Tune RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 670 Configure RMAN for Asynchronous I/O . . . . . . . . . . . . . . . . . . . 671 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 671 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 674 Chapter 18 User-Managed Backup, Restore, and Recovery . . . . . . . . . . . . . . . . 677 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 677 Backup and Recovery in One Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 678 User-Managed Database Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 678 Backup in Noarchivelog Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . 678 Backup in Archivelog Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 680 Backup of the Password and Parameter Files . . . . . . . . . . . . . . . . . . . . . 682 Media Failure That Does Not Affect Datafiles . . . . . . . . . . . . . . . . . . . . 683 Recovery from Loss of a Multiplexed Controlfile . . . . . . . . . . . . 683 Recovery from Loss of a Multiplexed Online Redo Log File . . . . 685 Recovery from Loss of a Tempfile . . . . . . . . . . . . . . . . . . . . . . . . . 688 Recovery from Loss of Datafiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 688 Recovery of Datafiles in Noarchivelog Mode . . . . . . . . . . . . . . . 688 Recovery of a Noncritical Datafile in Archivelog Mode . . . . . . . 689 Recovering a Critical Datafile in Archivelog Mode . . . . . . . . . . . 690 User-Managed Incomplete Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . 691 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 693 Recover from a Lost TEMP File . . . . . . . . . . . . . . . . . . . . . . . . . . . 693 Recover from a Lost Redo Log Group . . . . . . . . . . . . . . . . . . . . . 693 Recover from the Loss of a Password File . . . . . . . . . . . . . . . . . . 694 Perform User-Managed Complete Database Recovery . . . . . . . . 694 Perform User-Managed Incomplete Database Recovery . . . . . . . 694 Perform User-Managed Backups . . . . . . . . . . . . . . . . . . . . . . . . . 694 Identify the Need for Backup Mode . . . . . . . . . . . . . . . . . . . . . . . 695 Back Up and Recover a Controlfile . . . . . . . . . . . . . . . . . . . . . . . 695 25. OCA/OCP Oracle Database 11g All-in-One Exam Guide xxii Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 695 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 697 Chapter 19 Flashback . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699 The Different Flashback Technologies . . . . . . . . . . . . . . . . . . . . . . . . . . 700 Flashback Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 700 Flashback Query, Transaction, and Table . . . . . . . . . . . . . . . . . . 701 Flashback Drop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 701 Flashback Data Archive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 702 When to Use Flashback Technology . . . . . . . . . . . . . . . . . . . . . . 702 Flashback Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 704 Flashback Database Architecture . . . . . . . . . . . . . . . . . . . . . . . . . 704 Configuring Flashback Database . . . . . . . . . . . . . . . . . . . . . . . . . 705 Monitoring Flashback Database . . . . . . . . . . . . . . . . . . . . . . . . . . 707 Using Flashback Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 709 Limiting the Amount of Flashback Data Generated . . . . . . . . . . 713 Flashback Drop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 715 The Implementation of Flashback Drop . . . . . . . . . . . . . . . . . . . 715 Using Flashback Drop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 717 Managing the Recycle Bin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 720 Flashback Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 723 Basic Flashback Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 724 Flashback Table Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 725 Flashback Versions Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 728 Flashback Transaction Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . 729 Flashback and Undo Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 735 The Flashback Data Archive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 736 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 739 Restore Dropped Tables from the Recycle Bin . . . . . . . . . . . . . . . 739 Perform Flashback Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 739 Use Flashback Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 739 Perform Flashback Table Operations . . . . . . . . . . . . . . . . . . . . . . 740 Configure and Monitor Flashback Database and Perform Flashback Database Operations . . . . . . . . . . . . . . . . . . . . . . . 740 Set Up and Use a Flashback Data Archive . . . . . . . . . . . . . . . . . . 740 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 740 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 744 Chapter 20 Automatic Storage Management . . . . . . . . . . . . . . . . . . . . . . . . . . . 747 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 747 The Purpose of a Logical Volume Manager . . . . . . . . . . . . . . . . . . . . . . 748 RAID Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 748 Volume Sizes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 749 Choice of RAID Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 750 ASM Compared with Third-Party LVMs . . . . . . . . . . . . . . . . . . . . 750 26. Contents xxiii The ASM Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 751 The Cluster Synchronization Service . . . . . . . . . . . . . . . . . . . . . . 751 The ASM Disks and Disk Groups . . . . . . . . . . . . . . . . . . . . . . . . . 751 The ASM Instance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 753 The RDBMS Instance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 754 The ASM Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 755 Creating Raw Devices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 756 Creating, Starting, and Stopping an ASM Instance . . . . . . . . . . . . . . . . 757 Creating ASM Disk Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 760 Creating and Using ASM Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 762 ASM and RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 763 The ASMCMD Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 765 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 766 Describe Automatic Storage Management (ASM) . . . . . . . . . . . . 766 Set Up Initialization Parameter Files for ASM and Database Instances . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 767 Start Up and Shut Down ASM Instances . . . . . . . . . . . . . . . . . . . 767 Administer ASM Disk Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . 767 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 768 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 771 Chapter 21 The Resource Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 773 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 773 The Need for Resource Management . . . . . . . . . . . . . . . . . . . . . . . . . . . 774 The Resource Manager Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . 775 Consumer Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 775 Resource Manager Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 776 Resource Manager Configuration Tools . . . . . . . . . . . . . . . . . . . . 778 Managing Users and Consumer Groups . . . . . . . . . . . . . . . . . . . . . . . . 781 Resource Manager Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 781 CPU Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 782 Use of the Ratio CPU Method . . . . . . . . . . . . . . . . . . . . . . . . . . . 786 The Active Session Pool Method . . . . . . . . . . . . . . . . . . . . . . . . . 787 Limiting the Degree of Parallelism . . . . . . . . . . . . . . . . . . . . . . . 789 Controlling Jobs by Execution Time . . . . . . . . . . . . . . . . . . . . . . 790 Terminating Sessions by Idle Time . . . . . . . . . . . . . . . . . . . . . . . 791 Restricting Generation of Undo Data . . . . . . . . . . . . . . . . . . . . . 793 Automatic Consumer Group Switching . . . . . . . . . . . . . . . . . . . . . . . . . 794 Adaptive Consumer Group Mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . 795 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 799 Understand the Database Resource Manager . . . . . . . . . . . . . . . 799 Create and Use Database Resource Manager Components . . . . 800 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 800 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 803 27. OCA/OCP Oracle Database 11g All-in-One Exam Guide xxiv Chapter 22 The Scheduler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 805 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 805 The Scheduler Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 806 Scheduler Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 807 Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 807 Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 809 Schedules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 809 Job Classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 810 Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 811 Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 812 Creating and Scheduling Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 813 A Self-Contained Job . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 813 Using Programs and Schedules . . . . . . . . . . . . . . . . . . . . . . . . . . 814 Event-Driven Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 817 Job Chains . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 819 Lightweight Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 819 Using Classes, Windows, and the Resource Manager . . . . . . . . . . . . . . 822 Using Job Classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 823 Using Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 824 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 826 Create a Job, Program, and Schedule . . . . . . . . . . . . . . . . . . . . . . 826 Use a Time-Based or Event-Based Schedule for Executing Scheduler Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 826 Create Lightweight Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 826 Use Job Chains to Perform a Series of Related Tasks . . . . . . . . . 826 Create Windows and Job Classes . . . . . . . . . . . . . . . . . . . . . . . . . 827 Use Advanced Scheduler Concepts to Prioritize Jobs . . . . . . . . . 827 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 827 Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 829 Chapter 23 Moving and Reorganizing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . 831 Exam Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 831 SQL*Loader . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 832 External Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 834 Directories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 835 Using External Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 835 Data Pump . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 838 Data Pump Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 839 Directories and File Locations . . . . . . . . . . . . . . . . . . . . . . . . . . . 840 Direct Path or External Table Path? . . . . . . . . . . . . . . . . . . . . . . . 840 Using Data Pump Export and Import . . . . . . . . . . . . . . . . . . . . . . . . . . 841 Capabilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 841 Using Data Pump with the Command-Line Utilities . . . . . . . . . 842 Using Data Pump with Database Control . . . . . . . . . . . . . . . . . . 843 Tablespace Export and Import . . . . . . . . . . . . . . . . . . . . . . . . . . . 845 Resumable Space Allocation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 850 28. Contents xxv Segment Reorganization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 852 Row Chaining and Migration . . . . . . . . . . . . . . . . . . . . . . . . . . . . 853 Segment Shrink . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 855 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 859 Describe and Use Methods to Move Data (Directory Objects, SQL*Loader, External Tables) . . . . . . . . . . . . . . . . . . . . . . . . . 859 Explain the General Architecture of Oracle Data Pump . . . . . . . 860 Use Data Pump Export and Import to Move Data Between Oracle Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 860 Describe the Concepts of Transportable Tablespaces and Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 860 Manage Resumable Space Allocation . . . . . . . . . . . . . . . . . . . . . 860 Reclaim Wasted Space from Tables and Indexes by Using the Segment Shrink Functionality . . . . . . . . . . . . . . . . . . . . . . 861 Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .