OCA Oracle Database 11g Admin

1153 Pages·2010·13.9 MB·English

by  Biju Thomas

Prepare for two OCA certifi cation
OCA
exams with this one book Covers Both the 1Z0-051 and 1Z0-052 Exams!
FEATURED ON THE CD
Study, practice, and review for OCA certifi cation in Oracle 11g, STUDY GUIDE
Oracle’s new release of its top-selling database software. Inside
Includes Real-World Scenarios, Hands-On Exercises,
this in-depth guide, you’ll fi nd full coverage of essential topics
for two required OCA certifi cation exams: SQL Fundamentals I A O and Leading-Edge Exam Prep Software Featuring:
(1Z0-051) and Administration I (1Z0-052). Explore topics such as
d r (cid:127) Custom Test Engine
basic SQL SELECT statements, group functions, creating schema
m a
objects, restricting and sorting data, and much more with this (cid:127) Hundreds of Sample Questions
c
essential two-in-one guide. Inside, you’ll fi nd: i l
n e (cid:127) Electronic Flashcards for PCs, Pocket PCs,
Full coverage of all exam objectives in a systematic approach, so you i ® and Palm Handhelds
s
can be confi dent you’re getting the instruction you need for the exam D
t
r (cid:127) Entire Book in PDF
SYBEX TEST ENGINE:
Practical hands-on exercises to reinforce critical skills a a
Test your knowledge with advanced
Real-world scenarios that put what you’ve learned in the context testing software. Includes all chapter to ta
review questions and bonus exams.
of actual job roles r b OCA
Challenging review questions in each chapter to prepare you for a
C
exam day s
e e
Exam Essentials, a key feature in each chapter that identifi es critical r
areas you must become profi cient in before taking the exam t 1
i 1
A handy tear card that maps every offi cial exam objective to the fi
g
corresponding chapter in the book, so you can track your exam prep e
objective by objective d

ELECTRONIC FLASHCARDS: A Oracle Database 11g
®
Look inside for complete coverage Reinforce your understanding with
s
fl ashcards that can run on your PC,
s
of all exam objectives. Pocket PC, or Palm handheld. o
Administrator Certifi ed
c
Also on CD, you’ll fi nd the entire
i
www.sybex.com book in searchable and printable PDF. a
ABOUT THE AUTHOR Study anywhere, any time, and t Associate
approach the exam with confi dence. e
Biju Thomas, OCA, OCP, is a senior database administrator with more than 15 years
of Oracle application development and database management experience. He has CATEGORY Thomas STUDY GUIDE
coauthored fi ve Oracle certifi cation books published by Sybex and has written articles COMPUTERS/Certifi cation Guides
for multiple Oracle technical publications.
Exam 1Z0-051
ISBN: 978-0-470-39512-7
Exam 1Z0-052
Exams 1Z0-051 Biju Thomas
and 1Z0-052
$89.99 US
SERIOUS SKILLS.
$107.99 CN
95127bindex.indd 1086 2/18/09 6:41:41 AM
OCA: Oracle Database 11g Administrator Certified
Associate Study Guide
1Z0-051 Exam Objectives
Objective chapter
retrieving Data Using the SQL SeLect Statement
List the capabilities of SQL SELECT statements 1
Execute a basic SELECT statement 1
restricting and Sorting Data
Limit the rows that are retrieved by a query 1
Sort the rows that are retrieved by a query 1
Use ampersand substitution to restrict and sort output at runtime 1
Using Single-row Functions to customize Output
Describe various types of functions available in SQL 2
Use character, number, and date functions in SELECT statements 2
Using conversion Functions and conditional expressions
Describe various types of conversion functions that are available in SQL 2
Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions 2
Apply conditional expressions in a SELECT statement 2
reporting aggregated Data Using the Group Functions
Identify the available group functions 3
Describe the use of group functions 3
Group data by using the GROUP BY clause 3
Include or exclude grouped rows by using the HAVING clause 3
Displaying Data from Multiple tables
Write SELECT statements to access data from more than one table using 4
equijoins and nonequijoins
Join a table to itself by using a self-join 4
View data that generally does not meet a join condition by using outer joins 4
Generate a Cartesian product of all rows from two or more tables 4
Using Subqueries to Solve Queries
Define subqueries 4
Describe the types of problems that the subqueries can solve 4
List the types of subqueries 4
Write single-row and multiple-row subqueries 4
Exam specifications and content are subject to change at any time without
prior notice and at Oracle’s sole discretion. Please visit Oracle’s website (http://
education.oracle.com) for the most current information on their exam content.
95127bperfcard.indd 1 2/17/09 5:08:51 PM
Objective chapter
Using the Set Operators
Describe set operators 4
Use a set operator to combine multiple queries into a single query 4
Control the order of rows returned 4
Manipulating Data
Describe each data manipulation language (DML) statement 5
Insert rows into a table 5
Update rows in a table 5
Delete rows from a table 5
Control transactions 5
Using DDL Statements to create and Manage tables
Categorize the main database objects 6
Review the table structure 6
List the data types that are available for columns 6
Create a simple table 6
Explain how constraints are created at the time of table creation 6
Describe how schema objects work 6
creating Other Schema Objects
Create simple and complex views 7
Retrieve data from views 7
Create, maintain, and use sequences 7
Create and maintain indexes 7
Create private and public synonyms 7
1Z0-052 Exam Objectives
Objective chapter
exploring the Oracle Database architecture
Explain the Memory Structures 8
Describe the Process Structures 8
Overview of Storage Structures 8
preparing the Database environment
Identify the tools for Administering an Oracle Database 8
Plan an Oracle Database installation 8
Install the Oracle software by using Oracle Universal Installer (OUI) 8
95127bperfcard.indd 2 2/17/09 5:08:51 PM
Objective chapter
creating an Oracle Database
Create a database by using the Database Configuration Assistant (DBCA) 9
Managing the Oracle instance
Setting database initialization parameters 9
Describe the stages of database startup and shutdown 9
Using alert log and trace files 9
Using data dictionary and dynamic performance views 9
configuring the Oracle Network environment
Configure and Manage the Oracle Network 11
Using the Oracle Shared Server architecture 11
Managing Database Storage Structures
Overview of tablespace and datafiles 10
Create and manage tablespaces 10
Space management in tablespaces 10
administering User Security
Create and manage database user accounts 12
Grant and revoke privileges 12
Create and manage roles 12
Create and manage profiles 12
Managing Schema Objects
Create and Modify tables 10
Manage Constraints 10
Create indexes 10
Create and use temporary tables 10
Managing Data and concurrency
Manage data using DML 13
Identify and administer PL/SQL objects 13
Monitor and resolve locking conflicts 13
Managing Undo Data
Overview of Undo 13
Transactions and undo data 13
Managing undo 13
implementing Oracle Database Security
Database Security and Principle of Least Privilege 12
Work with Standard Database Auditing 12
Exam specifications and content are subject to change at any time without
prior notice and at Oracle’s sole discretion. Please visit Oracle’s website (http://
education.oracle.com) for the most current information on their exam content.
95127bperfcard.indd 3 2/17/09 5:08:51 PM
Objective chapter
Database Maintenance
Use and manage optimizer statistics 14
Use and manage Automatic Workload Repository (AWR) 14
Use advisory framework 14
Manage Alerts and Thresholds 14
performance Management
Use Automatic Memory Management 14
Use Memory Advisors 14
Troubleshoot invalid and unusable objects 14
intelligent infrastructure enhancements
Use the Enterprise Manager Support Workbench 17
Managing Patches 17
backup and recovery concepts
Identify the types of failure that can occur in an Oracle database 16
Describe ways to tune instance recovery 16
Identify the importance of checkpoints, redo log files, and archived log files 15
Overview of flash recovery area 15
Configure ARCHIVELOG mode 15
performing Database backups
Create consistent database backups 15
Back up your database without shutting it down 15
Create incremental backups 15
Automate database backups 15
Manage backups, view backup reports and monitor the flash recovery area 15
performing Database recovery
Overview of Data Recovery Advisor 16
Use Data Recovery Advisor to Perform recovery (Control file, Redo log file, 16
and Data file)
Moving Data
Describe and use methods to move data (Directory objects, SQL*Loader, 17
External Tables)
Explain the general architecture of Oracle Data Pump 17
Use Data Pump Export and Import to move data between Oracle databases 17
95127bperfcard.indd 4 2/17/09 5:08:51 PM
OCA
Oracle® Database 11g
Administrator
Certified Associate
Study Guide
95127ffirs.indd 1 2/18/09 1:02:54 PM
95127ffirs.indd 2 2/18/09 1:02:55 PM
OCA
Oracle® Database 11g
Administrator
Certified Associate
Study Guide
Biju Thomas
95127ffirs.indd 3 2/18/09 1:02:55 PM
Disclaimer: This eBook does not include ancillary media that was packaged with the
printed version of the book.
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 Permis-
sions 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. Fur-
ther, 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
95127ffirs.indd 4 2/18/09 1:02:55 PM