OCA E-Book

Biju Thomas

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 1461

Veröffentlichungsjahr: 2011

Beschreibung

Candidates for the Oracle Certified Associate in Oracle Database 11g need to pass Exams 1Z0-051 and 1Z0-052 to achieve their certification--this guide prepares you for taking both those exams. This two-in-one study guide covers the Oracle Certified Associate certification for Oracle database 11g and reviews exam topics such as restricting and sorting data, using conversion functions and conditional expressions, displaying data from multiple tables, and exploring the Oracle database architecture. Plus, you'll learn how to create and maintain an Oracle database, perform database backups and recovery, move and manipulate data, prepare the database environment, and more. Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file. For Instructors: Teaching supplements are available for this title.

Leseprobe

Table of Contents

Cover

Title Page

Credits

Copyright

Publisher's Note

Dedication

Acknowledgments

About the Author

Introduction

SQL Fundamentals I Assessment Test

Answers to SQL Fundamentals I Assessment Test

Administration I Assessment Test

Answers to Administration I Assessment Test

Part I

Chapter 1

SQL Fundamentals

Writing Simple Queries

Accepting Values at Runtime

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Chapter 2

Single-Row Function Fundamentals

Using Single-Row Character Functions

Using Single-Row Numeric Functions

Using Single-Row Date Functions

Using Single-Row Conversion Functions

Using Other Single-Row Functions

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Chapter 3

Group-Function Fundamentals

Utilizing Aggregate Functions

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Chapter 4

Writing Multiple-Table Queries

Using Set Operators

Subqueries

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Chapter 5

Using DML Statements

Understanding Transaction Control

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Chapter 6

Database Objects Overview

Built-in Datatypes

Creating Tables

Modifying Tables

Managing Constraints

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Chapter 7

Creating and Modifying Views

Creating and Managing Sequences

Creating and Managing Synonyms

Creating and Managing Indexes

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Part II

Chapter 8

Oracle Database Fundamentals

Oracle 11g Architecture

Installing Oracle 11g

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Chapter 9

Using DBCA to Create Oracle 11g Databases

Working with Oracle 11g Metadata

Managing Initialization-Parameter Files

Starting Up and Shutting Down an Oracle Instance

Monitoring the Database Alert Log

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Chapter 10

Tablespaces and Data Files Overview

Managing Tablespaces

Managing Data Files

Working with Schema Objects

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Chapter 11

Introducing Network Configurations

An Overview of Oracle Net Features

Configuring Oracle Net on the Server

Configuring Oracle Net for the Client

An Overview of Oracle Shared Server

Oracle Shared Server Infrastructure

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Chapter 12

Creating and Managing User Accounts

Granting and Revoking Privileges

Controlling Resource Usage by Users

Auditing Database Activity

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Chapter 13

Manipulating Data through SQL

Identifying PL/SQL Objects

Monitoring Locks and Resolving Lock Conflicts

Leveraging Undo Management

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Chapter 14

Proactive Database Maintenance

Managing Performance

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Chapter 15

Understanding and Configuring Recovery Components

Performing Backups

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Chapter 16

Understanding Types of Database Failure

Performing Recovery Operations

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Chapter 17

Understanding Data Pump

Loading Data with SQL*Loader

Populating External Tables

Using EM Support Workbench

Using EM to Manage Patches

Summary

Exam Essentials

Review Questions

Answers to Review Questions

Appendix: About the Companion CD

What You’ll Find on the CD

System Requirements

Using the CD

Troubleshooting

Glossary

Index

Perf Card

Acquisitions Editor: Jeff Kellum

Development Editor: Denise Santoro Lincoln

Technical Editors: Arup Nanda and Bob Bryla

Production Editor: Eric Charbonneau

Copy Editor: Kim Wimpsett

Production Manager: Tim Tate

Vice President and Executive Group Publisher: Richard Swadley

Vice President and Publisher: Neil Edde

Media Project Manager 1: Laura Moss-Hollister

Media Associate Producer: Josh Frank

Media Quality Assurance: Kit Malone

Book Designer: Judy Fung

Compositor: Craig Johnson, Happenstance Type-O-Rama

Proofreader: Candace English

Indexer: Ted Laux

Project Coordinator, Cover: Lynsey Stanford

Cover Designer: Ryan Sneed

Copyright © 2009 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-0-470-39512-7

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services or to obtain technical support, please contact our Customer Care Department within the U.S. at [phone], outside the U.S. at [phone] or fax [phone].

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

Library of Congress Cataloging-in-Publication Data

Thomas, Biju. OCA : Oracle database 11g administrator certified associate study guide (1Z0-051 and 1Z0-052) / Biju Thomas. — 1st ed. p. cm. ISBN 978-0-470-39512-7 (paper/cd-rom) 1. Electronic data processing personnel—Certification. 2. Database management—Examinations—Study guides. 3. Oracle (Computer file) I. Title. QA76.3.T5136 2009 005.75’75—dc22 2008052085

TRADEMARKS: Wiley, the Wiley logo, and the Sybex logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

10 9 8 7 6 5 4 3 2 1

Dear Reader,

Thank you for choosing OCA: Oracle Database 11g Administrator Certified Associate Study Guide (1ZO-051 and 1ZO-052). This book is part of a family of premium-quality Sybex books, all of which are written by outstanding authors who combine practical experience with a gift for teaching.

Sybex was founded in 1976. More than thirty years later, we’re still committed to producing consistently exceptional books. With each of our titles we’re working hard to set a new standard for the industry. From the paper we print on, to the authors we work with, our goal is to bring you the best books available.

I hope you see all that reflected in these pages. I’d be very interested to hear your comments and get your feedback on how we’re doing. Feel free to let me know what you think about this or any other Sybex book by sending me an email at [email protected], or if you think you’ve found a technical error in this book, please visit http://sybex.custhelp.com. Customer feedback is critical to our efforts at Sybex.

Best regards,

Neil Edde Vice President and Publisher Sybex, an Imprint of Wiley

To the loving memory of my father

To Joshua and Jeanette

Acknowledgments

Thanks first to Jeff Kellum and to Sybex for their faith in me to write this book. I would also like to thank the following wonderful people at Sybex for their support, patience with my slipping schedules, and good work: Denise Santoro Lincoln (developmental editor) for her valuable comments, thoughtful edits, patience, and making sure the chapters have a smooth flow; Eric Charbonneau (production editor) for making sure the various pieces of the book tie together; Candace English for proofreading; and Pete Gaughan for managing the project.

I thank Kim Wimpsett (copy editor) for her edits and making sure the same standard is followed across the book. I’m sure her edits made a difference to the look and feel of the chapters. I also thank Sybex and authors of the Introduction to Oracle9i SQL and Oracle Database 10g Administration I study guides for letting me reuse content from their books.

I am very fortunate and honored to have Arup Nanda as the technical editor. Thank you very much for all your valuable suggestions and for pointing out the errors and inaccuracies in the book. Your comments are invaluable. Thank you, Bob Bryla, for tech-reviewing the book and making sure the chapters are technically accurate.

I could not have completed this book without the endless support and love of my wife, Shiji. Thank you for engaging and taking care of the kids while I spent nights and weekends in front of the computer.

Last but not least, I thank all my colleagues and management at OneNeck IT Services for their friendship and support. My special thanks to Joe Hanna for all the encouragement.

About the Author

Biju Thomas is an Oracle 7.3, Oracle8, Oracle8i, Oracle9i, Oracle 10g, and Oracle 11g OCP certified professional. He is also a certified Oracle Database SQL Expert. Biju has been developing and administering Oracle databases since 1993, starting with Oracle version 6. He is a senior database consultant at OneNeck IT Services Corporation (www.oneneck.com) and resides in Keller, Texas. He maintains a website for DBAs at www.bijoos.com/oracle.

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 Database 11g 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 Oracle databases. 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 annual revenues of more than $22 billion, and is headquartered in Redwood City, California.

Oracle databases are the de facto standard for large Internet sites and mission-critical enterprise applications. Oracle advertisers are boastful but honest when they proclaim, “The Internet runs on Oracle.” Almost all the big Internet sites run on Oracle databases. Oracle’s penetration of the database market runs deep and is not limited to Internet implementations. Enterprise resource planning (ERP) application suites, data warehouses, and custom applications at many large and medium 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 Oracle Database 11g Administrator Certified Associate (OCA), which is the first step on the path toward the Oracle Certified Professional (OCP) and Oracle Certified Master (OCM) certifications. This book covers the two exams required for the OCA certification. Using this book and a practice database, you can start learning Oracle 11g and pass the Oracle Database 11g: SQL Fundamentals I (1Z0-051) and Oracle Database 11g: Administration I (1Z0-052) exams.

Why Become Oracle Certified?

The number-one reason to become OCA or OCP certified 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.

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 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.

The certification tests are scenario-based, which is the most effective way to assess your hands-on expertise and critical problem-solving skills. OCPs are among the best paid in the IT industry. Salary surveys consistently show the OCP certification to yield higher salaries than the other certifications, including Microsoft, Novell, and Cisco.

So, whether you are beginning a career, changing careers, securing your current 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. The certification tracks are Database, Middleware, Applications, and Linux. Within each track, Oracle has a tiered certification program of OCA and OCP. Only the Database track has OCM.

The Database track is clearly for the database administrator job role. The Middleware track has certifications on many products, such as Oracle 10g Application Server, Oracle Essbase, Oracle Forms, Oracle PL/SQL, Oracle WebLogic, and Service-Oriented Architecture (SOA) and is intended for application developers, system administrators, consultants, and architects.

The Applications track is for ERP administrators and functional consultants. This track covers the Oracle E-Business Suite, Siebel, Hyperion, and PeopleSoft applications. The Linux track is for Linux administrators.

For the latest certification information on all of Oracle certification paths, please visit the Oracle website at http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=39&p_org_id=1001&lang=US.

The role of 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 have tools to proactively monitor the database for performance issues and to prevent unscheduled downtime. The DBA’s job requires a broad understanding of the architecture of Oracle Database and an expertise in solving problems.

Sybex has Oracle certification study guides for the Database track. In the following sections, I’ll introduce you to the different tiers in the Oracle Database 11g certification track.

Oracle Database 11g Administrator Certified Associate

The Oracle Certified Associate (OCA) credential is the first step toward achieving the Oracle Certified Professional (OCP) certification. OCA shows that you have the fundamental knowledge and skills to support an Oracle 11g database. This certification requires you to pass two exams that demonstrate your Oracle basics:

1Z0-051: Oracle Database 11g: SQL Fundamentals I1Z0-052: Oracle Database 11g: Administration I

If you have already passed any one of the following tests, you need not take the 1Z0-051 test; you need to pass only 1Z0-052:

1Z0-001: Introduction to Oracle: SQL and PL/SQL1Z0-007: Introduction to Oracle9i SQL1Z0-047: Oracle Database SQL Expert

You can take the 1Z0-051 exam at a testing location or from your home using the Internet. The 1Z0-052 test is offered only at a Prometric facility.