Oracle Database 11g:
A Beginner’s Guide
Ian Abramson
Michael Abbey
Michael J. Corey
Michelle Malcher
New York Chicago San Francisco
Lisbon London Madrid Mexico City Milan
New Delhi San Juan Seoul Singapore Sydney Toronto
Copyright © 2009 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 sys-
tem, without the prior written permission of the publisher.
ISBN: 978-0-07-160460-4
MHID: 0-07-160460-X
The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-160459-8, MHID: 0-07-160459-6.
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 trade-
mark. 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 [email protected].
Information has been obtained by Publisher from sources believed to be reliable. However, because of the possibility of human or mechan-
ical 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 con-
tained 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, dis-
tribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s prior consent. You may use the work for

community, Abbey is a frequent presenter at COLLABORATE, Oracle OpenWorld,
and regional user group meetings.
Michael J. Corey is the founder and CEO of Ntirety—The Database Administration
Experts. Michael’s roots go back to Oracle version 3.0. Michael is a past president of the
Independent Oracle Users group (www.ioug.org) and the original Oracle Press author.
Michael is a frequent speaker at business and technology events and has presented all
over the world. Check out Michael’s blog at http://michaelcorey.ntirety.com.
Michelle Malcher is a Senior Database Administrator with
over ten years’ experience in database development, design,
and administration. She has expertise in performance tuning,
security, data modeling, and database architecture of very large database
environments. She is a contributing author for the IOUG Best Practices Tip
Booklet. Michelle is enthusiastically involved with the Independent Oracle
User Group and is director of Special Interest Groups. She enjoys presenting and
sharing ideas about Oracle Database topics at technology conferences and user
group meetings. She can be reached at [email protected].
About the Reviewers
Carl Dudley has worked closely with Oracle for a number of years and presents regularly at international
conferences on Oracle database technology. He is currently a consultant database administrator and has
research interests in database performance, disaster planning, and security. Carl is a director of the UK
Oracle User Group, received Oracle Magazine’s Editors’ Choice Award for Database Administrator of the
Year in 2003 for services to the Oracle community, and achieved Oracle ACE status in 2007.
Ted Falcon, based in Toronto, Canada, is CEO of BDR Business Data Reporting Inc. He has ten
years’ experience in business intelligence reporting systems, specializing in the Cognos suite of tools.
Contents
ACKNOWLEDGMENTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
1 Database Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Critical Skill 1.1 Define a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Critical Skill 1.2 Learn the Oracle Database 11g Architecture . . . . . . . 4

Tablespace Quotas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Synonyms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Default User Environments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Critical Skill 1.7 Work with Object and System Privileges . . . . . . . . . . 25
Select . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Insert . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Update . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Delete . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
System Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Critical Skill 1.8 Introduce Yourself to the Grid . . . . . . . . . . . . . . . . . . 27
Critical Skill 1.9 Tie It All Together . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Chapter 1 Mastery Check. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
2 Installing Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Critical Skill 2.1 Research and Plan the Installation . . . . . . . . . . . . . . . 36
Define System Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Linux Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Critical Skill 2.2 Set Up the Operating System . . . . . . . . . . . . . . . . . . . 42
Project 2-1 Configure Kernel Parameters . . . . . . . . . . . . . . . . . . . . . . 44
Critical Skill 2.3 Get Familiar with Linux . . . . . . . . . . . . . . . . . . . . . . . 47
Critical Skill 2.4 Choose Components to Install . . . . . . . . . . . . . . . . . . 48
Critical Skill 2.5 Install the Oracle Software . . . . . . . . . . . . . . . . . . . . . 49
Database Configuration Assistant . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Verify the Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Chapter 2 Mastery Check. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
3 Connecting to Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Critical Skill 3.1 Use Oracle Net Services . . . . . . . . . . . . . . . . . . . . . . 66
Network Protocols . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Optimize Network Bandwidth . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

Critical Skill 3.6 Use Oracle Configuration Files . . . . . . . . . . . . . . . . . 87
Critical Skill 3.7 Use Administration Tools . . . . . . . . . . . . . . . . . . . . . . 89
The Oracle Enterprise Manager/Grid Control . . . . . . . . . . . . . . . . . 89
The Oracle Net Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
The OEM Console . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
The OEM Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
The Oracle Net Configuration Assistant . . . . . . . . . . . . . . . . . . . . . 91
The Oracle Internet Directory Configuration Assistant . . . . . . . . . . 92
Command-Line Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
The Oracle Advanced Security Option . . . . . . . . . . . . . . . . . . . . . 94
Dispatchers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Project 3-1 Test a Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Critical Skill 3.8 Use Profiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Critical Skill 3.9 Network in a Multi-tiered Environment . . . . . . . . . . . 98
Critical Skill 3.10 Install the Oracle 11g Client Software . . . . . . . . . . . 99
Chapter 3 Mastery Check. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
4 SQL: Structured Query Language . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Critical Skill 4.1 Learn the SQL Statement Components . . . . . . . . . . . . 106
DDL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
DML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Critical Skill 4.2 Use Basic Insert and Select Statements . . . . . . . . . . . . 108
Insert . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Select . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Critical Skill 4.3 Use Simple Where Clauses . . . . . . . . . . . . . . . . . . . . 111
A Where Clause with and/or . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Contents
vii
The Where Clause with NOT . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
The Where Clause with a Range Search . . . . . . . . . . . . . . . . . . . . 115
The Where Clause with a Search List . . . . . . . . . . . . . . . . . . . . . . . 116

Critical Skill 4.11 Use Set Operators: Union, Intersect, Minus . . . . . . . 147
Union . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
Union All . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
Intersect . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
Minus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Project 4-4 Use the Union Function in Your SQL . . . . . . . . . . . . . . . . 149
Critical Skill 4.12 Use Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Critical Skill 4.13 Learn Sequences: Just Simple Stuff . . . . . . . . . . . . . . 152
Critical Skill 4.14 Employ Constraints: Linkage to Entity Models, Types,
Deferred, Enforced, Gathering Exceptions . . . . . . . . . . . . . . . . . . . . 153
Linkage to Entity Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
viii
Oracle Database 11g: A Beginner’s Guide
Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
Deferred . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Critical Skill 4.15 Format Your Output with SQL*Plus . . . . . . . . . . . . . 156
Page and Line Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Page Titles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Page Footers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Formatting Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Project 4-5 Format Your SQL Output . . . . . . . . . . . . . . . . . . . . . . . . . 157
Writing SQL*Plus Output to a File . . . . . . . . . . . . . . . . . . . . . . . . . 160
Chapter 4 Mastery Check. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
5 PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Critical Skill 5.1 Define PL/SQL and Learn Why We Use It . . . . . . . . . 164
Critical Skill 5.2 Describe the Basic PL/SQL Program Structure . . . . . . 166
Critical Skill 5.3 Define PL/SQL Data Types . . . . . . . . . . . . . . . . . . . . . 168
Valid Characters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Arithmetic Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
The varchar2 Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171

Change Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Schedule Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Network Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Critical Skill 6.3 Understand the Oracle Database 11g Infrastructure . . 212
Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Storage Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
Critical Skill 6.4 Operate Modes of an Oracle Database 11g . . . . . . . . 216
Modes of Operation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Database and Instance Shutdown . . . . . . . . . . . . . . . . . . . . . . . . . 217
Critical Skill 6.5 Get Started with Oracle Enterprise Manager . . . . . . . . 219
Instance Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
User Sessions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
Resource Consumer Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
Schema, Security, and Storage Management . . . . . . . . . . . . . . . . . 221
Distributed Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Warehouse Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Other Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Critical Skill 6.6 Manage Database Objects . . . . . . . . . . . . . . . . . . . . . 223
Control Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
Redo Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
Undo Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
Schema Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
Critical Skill 6.7 Manage Space . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
Archive Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Tablespaces and Data Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Critical Skill 6.8 Manage Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Create a User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Edit Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Critical Skill 6.9 Manage Privileges for Database Users . . . . . . . . . . . . 231

Critical Skill 7.3 Write a Database Backup. . . . . . . . . . . . . . . . . . . . . . . 254
Critical Skill 7.4 Back Up Archived Redo Logs. . . . . . . . . . . . . . . . . . . . 256
Critical Skill 7.5 Get Started with Oracle Data Pump . . . . . . . . . . . . . . . 257
Critical Skill 7.6 Use Oracle Data Pump Export . . . . . . . . . . . . . . . . . . . 258
Critical Skill 7.7 Work with Oracle Data Pump Import. . . . . . . . . . . . . . 264
Critical Skill 7.8 Use Traditional Export and Import . . . . . . . . . . . . . . . . 269
Critical Skill 7.9 Get Started with Recovery Manager . . . . . . . . . . . . . . . 270
RMAN Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Set Up a Recovery Catalog and Target Database . . . . . . . . . . . . . . . 274
Key RMAN Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
Backups. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
RMAN Using Enterprise Manager . . . . . . . . . . . . . . . . . . . . . . . . . . 278
Performing Backups. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281
Restore and Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282
Project 7-1 RMAN End to End . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283
Chapter 7 Mastery Check. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
8 High Availability: RAC, ASM, and Data Guard . . . . . . . . . . . . . . . . . 287
Critical Skill 8.1 Define High Availability . . . . . . . . . . . . . . . . . . . . . . . 288
Critical Skill 8.2 Understand Real Application Clusters . . . . . . . . . . . . 289
Critical Skill 8.3 Install RAC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290
Critical Skill 8.4 Test RAC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Workload Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
ASM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
Critical Skill 8.5 Set Up the ASM Instance . . . . . . . . . . . . . . . . . . . . . . 297
Project 8-1 Install ASMLib . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Critical Skill 8.6 Create ASM Disk Groups . . . . . . . . . . . . . . . . . . . . . . 302
Project 8-2 Create Disk Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303
Critical Skill 8.7 Use ASMCMD and ASMLIB . . . . . . . . . . . . . . . . . . . . 304
Contents
xi

Project 9-2 Use Analytic SQL Functions and Models . . . . . . . . . . . . . 370
Chapter 9 Mastery Check. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372
A Mastery Check Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
Chapter 1: Database Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . 376
Chapter 2: Installing Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379
Chapter 3: Connecting to Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
Chapter 4: SQL: Structured Query Language . . . . . . . . . . . . . . . . . . . . 381
Chapter 5: PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384
Chapter 6: The Database Administrator . . . . . . . . . . . . . . . . . . . . . . . . 385
Chapter 7: Backup and Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387
Chapter 8: High Availability: RAC, ASM, and Data Guard . . . . . . . . . . 390
Chapter 9: Large Database Features . . . . . . . . . . . . . . . . . . . . . . . . . . . 391
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395
xii
Oracle Database 11g: A Beginner’s Guide
Acknowledgments
I
an Abramson: I would like to thank all of those who are part of my life
and who have been part of this great adventure. I would like to thank
my family: my wife, Susan, is my true partner who puts up with me being
me; and of course my two joys in life, my daughters Baila and Jillian—
they have become two wonderful and intelligent women, and I am so
proud and expect that their dreams will all be within their reach. To my friends, the
people who are part of my everyday journey and whom I am so lucky to have as part
of my life: Michael Brown, Chris Clarke, Marc Allaire, Marshall Lucatch, Jim Boutin,
Kevin Larose, Al Murphy, Ken Sheppard, Terry Butts, Andrew Allaire, Mark Kerzner,
Michael Abbey, Michael Corey, Ted Falcon, Moti Fishman, Tom Tishler, Carol McGury
and everyone at the IOUG, and Jack Chadirdjian—you are all an important part of my
life, and I am honored to know each of you and call you all friends.
Michael Abbey: Thanks to my wife, Sandy; and my children, Ben, Naomi, Nathan,

of operations must be reduced. Oracle 11g is a release that addresses
many of these concerns and provides a database that can help
organizations move forward without boundaries. With the release of
Oracle Database 11g: A Beginner’s Guide, we bring back together the Abramson,
Abbey, and Corey team that has been writing these books for over 13 years. That
time slice is pale compared to the length of time the Oracle database software has
been embracing the information highway. Recently Oracle celebrated its 30th
anniversary with the customary hoopla and fanfare…justifiably so.
One cannot rub shoulders with fellow information technologists without experiencing
Oracle’s technology, and quite a piece of technology it is! In the beginning, there was a
database, and then came development tools. The Oracle product line added components
at an ever more accelerating rate. This book is all about the foundation underneath just
about everything running the Oracle technology stack—the database. Regardless of what
corner of the technology you work with, being familiar with the underpinnings of the
database technology makes you a better practitioner.
Where has Oracle been, and where is it going? The former question is not that hard
to answer, the latter a mystery until it unfolds. In 1979 we saw the first commercial SQL
RDBMS offering from a new company in Redwood Shores, California—Software
Development Laboratories. Close to two years later, the company morphed into
Relational Software, Inc. in Menlo Park, not far from its origin. The VAX hardware
platform was the initial home of the database offering. The rest of the story of this
company, now known as Oracle Corporation, is revolutionary—all the way from the
first read-consistent database (1984), through its first full suite of applications (1992),
to the first web database offering (1997). The calendar year 2000 saw the first Internet
development suite, followed not long thereafter by the release of Enterprise Grid
computing with Database 10g in 2003. The acquisitions path emerged strongly in
2004 with the purchase of PeopleSoft, and it did not stop there. Significant technology
xv
xvi
Oracle Database 11g: A Beginner’s Guide

chapter, enabling you to apply your newly acquired knowledge and skills
immediately.
■ Ask the Expert questions and answers appear throughout the chapters to
make the subject more interactive and personal.
■ Progress Checks are quick, numbered self-assessment sections where you
can easily check your progress by answering questions and getting immediate
feedback with the provided answers.
■ Mastery Checks at the end of each chapter test proficiency in concepts
and technology details covered in the chapter through multiple-choice,
fill-in-the blank, true/false, and short-answer questions.
This book introduces you to many aspects of the Oracle database software.
Chapter 1 starts with the concept of a database and how Oracle is structured so
that you understand the fundamentals. Chapter 2 covers installing the software that
you are going to need to try things out. We have provided a step-by-step guide to
installing the software on Linux, but if you wish to install it on another platform, this
chapter will help you understand the choices that you need to make when installing
the database.
Once your database is installed, you will need to communicate with it; in order
to do this, you may need to install Oracle client software to access the database.
Chapter 3 on connecting to Oracle will guide you through the tasks that can often
be complex, but we provide information on how to keep it simple.
Once the database is installed and you can communicate with it, you need to
speak the languages that the database understands. We provide you with a solid
introduction to Structured Query Language (SQL) in Chapter 4, as well as Oracle’s
own programming language, PL/SQL, in Chapter 5. These two chapters will help
you create robust interactions with the database to get data into and out of your
database.
The administration of the Oracle database is largely a function of the people who
work closely with Oracle’s software. Thus, we provide you with a deep introduction
to these functions and features. In Chapter 6 we will show you what database

CRITICAL SKILLS
1.1 Define a Database
1.2 Learn the Oracle Database 11g
Architecture
1.3 Learn the Basic Oracle Database
11g Data Types
1.4 Work with Tables
1.5 Work with Stored Programmed
Objects
1.6 Become Familiar with Other
Important Items in Oracle
Database 11g
1.7 Work with Object and System
Privileges
1.8 Introduce Yourself to the Grid
1.9 Tie It All Together
2
Oracle Database 11g: A Beginner’s Guide
his chapter is the start of your Oracle Database 11g journey. The
Oracle database is a complex product and you will need to learn the
basics first. From this point forward, we will walk you through the
skills that you’ll need to begin working with Oracle Database 11g.
We’ll begin at the core of this product, with the fundamentals of a
database. This chapter will also give you an understanding of the contents of your
database and prepare you to move into the more complex areas of Oracle Database
11g technology.
CRITICAL SKILL 1.1
Define a Database
Oracle Database 11g is the latest offering from Oracle. Perhaps you have heard a lot
of hype about Oracle Database 11g, and perhaps not. Regardless of your experience, 11g

These relationships can be described in plain English for a fictitious computer parts
store in the following example:
■ Each geographical location that the store does business in is uniquely
identified by a quad_id.
■ Each manufacturer that supplies parts is uniquely identified by a ten-character
manufacturer_id. When a new manufacturer is registered with the system, it
is assigned a quad_id based on its location.
■ Each item in the store’s inventory is uniquely identified by a ten-character
part_id and must be associated with a valid manufacturer_id.
Based on these three points, practitioners commonly develop statements similar to
the following to describe the relationships between locations, manufacturers, and parts:
■ A one-to-many relationship Locations and manufacturers— more than
one manufacturer can reside in a specified location.
■ A many-to-many relationship Manufacturers and computer parts—the
store purchases many different parts from each manufacturer.
These two relationships are established as data is captured in the store’s database
and other relationships can be deduced as a result—for example, one can safely say
FIGURE 1-1.
The players in the Oracle Database 11g solution
4
Oracle Database 11g: A Beginner’s Guide
“parts are manufactured in one or more locations based on the fact that there are
many manufacturers supplying many different products.” Oracle has always been a
relational database product, commanding a significant percentage of market share
compared to its major competition. Let’s get started and look at the Oracle Database
11g architecture.
CRITICAL SKILL 1.2
Learn the Oracle Database 11g Architecture
As with many new software experiences, there is some jargon that we should get
out of the way before starting this section.

5
The Control Files
Oracle’s control files are binary files containing information about the assortment of
files that come together to support Oracle Database 11g. They contain information
that describes the names, locations, and sizes of the database files. Oracle insists
there is only one control file, but knowledgeable technicians have two or three and
sometimes more. As Oracle Database 11g is started, the control files are read and the
files described therein are opened to support the running database.
The Online Redo Logs
As sessions interact with Oracle Database 11g, the details of their activities are
recorded in the online redo logs. Redo logs may be thought of as transaction logs;
these logs collect transactions. A transaction is a unit of work, passed to the database
for processing. The following listing shows a few activities that can be referred to as
two transactions:
Begin of transaction #1
create some new information
update some existing information
create some more new information
delete some information
save all the work that has been accomplished
End of transaction #1
Begin transaction #2
update some information
back out the update by not saving the changed data
End transaction #2
Oracle Database 11g insists that there are at least two online redo logs to
support the instance. In fact, most databases have two or more redo log groups
with each group having the same number of equally sized members.
The System Tablespace
Tablespace is a fancy Oracle Database 11g name for a database file. Think of it as a

Oracle does to assemble a result set for a query operation is done in memory. A
result set is a collection of data that qualifies for inclusion in a query passed to
Oracle. If the amount of memory allocated for query processing is insufficient to
accommodate all the activities required to assemble data, Oracle uses this default
temporary tablespace as its secondary work area for many activities, including sorting.
Undo Tablespace
As sessions interact with Oracle Database 11g, they create, change, and delete data.
Undo is the act of restoring data to a previous state. Suppose one’s address
is changed from 123 Any Street to 456 New Street via a screen in the personnel
application. The user who is making the change has not yet saved the transaction.
Until that transaction is saved (referred to as committed in the world of Oracle
Database 11g) or abandoned (referred to as rolled back in the same world), Oracle
maintains a copy of the changed data in its undo tablespace.
The Server Parameter File
Oracle Database 11g sometimes calls the server parameter file its spfile. This is
where its startup parameters are defined and the values in this file determine the