Oracle 11g A Beginner's Guide

User Manual: Pdf

Open the PDF directly: View PDF PDF.
Page Count: 433 [warning: Documents this large are best viewed by clicking the View PDF Link!]

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 bulksales@mcgraw-hill.com.
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
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, INCLUD-
ING 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 WAR-
RANTIES 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 pos-
sibility 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.
This book is dedicated to all those who have
helped us learn and become better professionals.
We share this with all of you.
About the Authors
Ian Abramson is the current president for the Independent Oracle Users Group
(IOUG). Based in Toronto, Canada, he is an experienced industry and technical
consultant, providing expert guidance in implementing solutions for clients in
telecommunications, CRM, utilities, and other industries. His focus includes the
Oracle product set, as well as other leading technologies and their use in optimizing
data warehouse design and deployment. He is also a regular speaker at various
technology conferences, including COLLABORATE, Oracle OpenWorld, and
other local and regional events.
Michael Abbey is a recognized authority on database administration, installation,
development, application migration, performance tuning, and implementation.
Working with Ian Abramson and Michael Corey, he has coauthored works in
the Oracle Press series for over 14 years. Active in the international Oracle user
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 michelle_malcher@ioug.org.
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 11gArchitecture . . . . . . . 4
The Control Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
The Online Redo Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
The System Tablespace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
The Sysaux Tablespace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Default Temporary Tablespace . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Undo Tablespace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
The Server Parameter File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Background Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Project 1-1 Review the Oracle Database 11gArchitecture . . . . . . . . . 9
The Database Administrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Critical Skill 1.3 Learn the Basic Oracle Database 11gData Types . . . 11
varchar2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
number . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
timestamp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
clob . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
blob . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Critical Skill 1.4 Work with Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Tables Related to part_master . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Critical Skill 1.5 Work with Stored Programmed Objects . . . . . . . . . . . 16
Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
v
Critical Skill 1.6 Become Familiar with Other Important Items
in Oracle Database 11g . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
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
Maintain Connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Define a Location . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Critical Skill 3.2 Learn the Difference Between Dedicated
and Shared Server Architectures . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Dedicated Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Shared Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Set Dispatchers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Views to Monitor the Shared Server . . . . . . . . . . . . . . . . . . . . . . . . 76
Critical Skill 3.3 Define Connections . . . . . . . . . . . . . . . . . . . . . . . . . . 77
vi Oracle Database 11g: A Beginner’s Guide
A Connect Descriptor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Define a Connect Descriptor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
The Oracle Connection Manager . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Session Multiplexing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Firewall Access Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Critical Skill 3.4 Use the Oracle Net Listener . . . . . . . . . . . . . . . . . . . . 80
Password Authentication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Multiple Listeners . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Connection Pooling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Critical Skill 3.5 Learn Naming Methods . . . . . . . . . . . . . . . . . . . . . . . 83
Directory Naming Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Directory Information Trees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Distinguished Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
How to Find the Directory Naming Information . . . . . . . . . . . . . . 85
Net Service Alias Entries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
The Local Naming Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
The Easy Naming Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
The External Naming Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Which Naming Method to Use . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
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 11gClient 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
The Where Clause with a Pattern Search . . . . . . . . . . . . . . . . . . . . 116
The Where Clause: Common Operators . . . . . . . . . . . . . . . . . . . . 117
Critical Skill 4.4 Use Basic Update and Delete Statements . . . . . . . . . . 118
Update . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Delete . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Critical Skill 4.5 Order Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Critical Skill 4.6 Employ Functions: String, Numeric, Aggregate
(No Grouping) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
String Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Numeric Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Critical Skill 4.7 Use Dates and Data Functions (Formatting and
Chronological) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
Date Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
Special Formats with the Date Data Type . . . . . . . . . . . . . . . . . . . 127
Nested Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Critical Skill 4.8 Employ Joins (ANSI vs. Oracle): Inner, Outer, Self . . . 129
Inner Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
Project 4-1 Join Data Using Inner and Outer Joins . . . . . . . . . . . . . . . 134
Project 4-2 Join Data Using ANSI SQL Joins . . . . . . . . . . . . . . . . . . . 137
Self-Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Critical Skill 4.9 Learn the Group By and Having Clauses . . . . . . . . . . 140
Group By . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Having . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Project 4-3 Group Data in Your Select Statements . . . . . . . . . . . . . . . 141
Critical Skill 4.10 Learn Subqueries: Simple and Correlated
Comparison with Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Simple Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Correlated Subqueries with Joins . . . . . . . . . . . . . . . . . . . . . . . . . . 146
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
The Number Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
The Date Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
The Boolean Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Critical Skill 5.4 Write PL/SQL Programs in SQL*Plus . . . . . . . . . . . . . 174
Project 5-1 Create a PL/SQL Program . . . . . . . . . . . . . . . . . . . . . . . . . 176
SQL in Your PL/SQL Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
PL/SQL Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
The Cursor FOR Loop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Critical Skill 5.5 Handle Error Conditions in PL/SQL . . . . . . . . . . . . . . 181
Error Handling Using Oracle-Supplied Variables . . . . . . . . . . . . . . 185
Critical Skill 5.6 Include Conditions in Your Programs . . . . . . . . . . . . . 187
Program Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
Project 5-2 Use Conditions and Loops in PL/SQL . . . . . . . . . . . . . . . . 195
Critical Skill 5.7 Create Stored Procedures—How and Why . . . . . . . . . 196
Critical Skill 5.8 Create and Use Functions . . . . . . . . . . . . . . . . . . . . . 201
Project 5-3 Create and Use a Function . . . . . . . . . . . . . . . . . . . . . . . . 201
Critical Skill 5.9 Call PL/SQL Programs . . . . . . . . . . . . . . . . . . . . . . . . 203
Chapter 5 Mastery Check. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
6 The Database Administrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
Critical Skill 6.1 Learn What a DBA Does . . . . . . . . . . . . . . . . . . . . . . 208
Critical Skill 6.2 Perform Day-to-Day Operations . . . . . . . . . . . . . . . . . 209
Architecture and Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Capacity Planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Backup and Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Performance and Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Contents ix
Managing Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Storage Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Change Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Schedule Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Network Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Critical Skill 6.3 Understand the Oracle Database 11gInfrastructure . . 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
Grant Authority . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232
Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Profiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
Project 6-1 Create Essential Objects . . . . . . . . . . . . . . . . . . . . . . . . . . 235
Chapter 6 Mastery Check. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
7 Backup and Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
Critical Skill 7.1 Understand Oracle Backup and Recovery
Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240
Where Do I Start? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240
Backup Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Oracle Binaries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Parameter Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
xOracle Database 11g: A Beginner’s Guide
Control Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Redo Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Undo Segments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Checkpoints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Archive Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Data Files, Tablespaces, Segments, Extents, and Blocks . . . . . . . . . . 245
Dump Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Critical Skill 7.2 Learn about Oracle User-Managed Backup
and Recovery. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Types of User-Managed Backups. . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Cold Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Hot Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Recovery from a Cold Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Recovery from a Hot Backup. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
Seven Steps to Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
Recovery Using Backup Control Files . . . . . . . . . . . . . . . . . . . . . . . 253
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
Critical Skill 8.8 Convert an Existing Database to ASM . . . . . . . . . . . . . 306
Critical Skill 8.9 Understand Data Guard . . . . . . . . . . . . . . . . . . . . . . . 308
Critical Skill 8.10 Explain Data Guard Protection Modes . . . . . . . . . . . 309
Critical Skill 8.11 Create a Physical Standby Server . . . . . . . . . . . . . . . 312
Project 8-3 Create a Physical Standby Server . . . . . . . . . . . . . . . . . . . 313
Chapter 8 Mastery Check. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
9 Large Database Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
Critical Skill 9.1 Learn to Identify a Very Large Database . . . . . . . . . . . 318
Critical Skill 9.2 Why and How to Use Data Partitioning . . . . . . . . . . . 319
Why Use Data Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319
Implement Data Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323
Project 9-1 Create a Range-Partitioned Table and
a Local-Partitioned Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340
Critical Skill 9.3 Compress Your Data . . . . . . . . . . . . . . . . . . . . . . . . . 344
Data Compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
Index Key Compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346
Critical Skill 9.4 Use Parallel Processing to Improve Performance . . . . 347
Parallel Processing Database Components . . . . . . . . . . . . . . . . . . . 347
Parallel Processing Configuration . . . . . . . . . . . . . . . . . . . . . . . . . 348
Invoke Parallel Execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350
Critical Skill 9.5 Use Materialized Views . . . . . . . . . . . . . . . . . . . . . . . 351
Uses for Materialized Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352
Query Rewrite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
When to Create Materialized Views . . . . . . . . . . . . . . . . . . . . . . . 354
Create Materialized Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
Critical Skill 9.6 Use SQL Aggregate and Analysis Functions . . . . . . . . 356
Aggregation Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356
Analysis Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
Other Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
Critical Skill 9.7 Create SQL Models . . . . . . . . . . . . . . . . . . . . . . . . . . 367
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
Ian 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,
and Jordan; as well as two new-found wonders of my life—a granddaughter named
Annabelle and a daughter-in-law Lindsay.
Michael Corey: Special thanks to my friend, Ian Abramson, whose hard work
and efforts made this book happen.
Michelle Malcher: I would like to thank my junior DBAs, Mandy and Emily, for
their fun breaks from work to enjoy life. Thanks to my husband for putting up with
the long hours I spend sitting in front of a computer. Special thanks to Ian Abramson
for getting me involved with this book and his support and encouragement. Thanks
to all involved in the IOUG; keep sharing ideas and working with each other to
sharpen each others’ skills and grow careers.
Ted Falcon: I would like to acknowledge those people whose love and support
have allowed me to get to where I am today. First and foremost are my wife, Vanessa;
and our 3 children, Mya, Matthew, and Noah. Thank you for everything that you do
to enrich and fulfill my life. I love you all more than you know. To my parents, Mel
and Tita, thank you for your continued guidance and love. To my brother, Adrian,
our battles on the basketball court are legendary. Your quest to one day beat me is
xiii
xiv Oracle Database 11g: A Beginner’s Guide
inspiring. I love you, little brother. To my huge extended family, thank you for your
love and support. To my friends—you are all family, especially Bruha. To my friend
Garth Gray, who guided me through the halls of U of T Scarborough and to this
crazy world of IT. Thank you for the advice, the support, and the drives down to ITI.
Finally, to my friends and colleagues whom I’ve met throughout my career, especially
Ian Abramson; thank you for your friendship, guidance, Raptor tickets, and for
allowing me to be a part of this book.
Introduction
The release of Oracle Database 11gis one that comes with much
anticipation. We are at a time when data is exploding and the cost
of operations must be reduced. Oracle 11gis 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 10gin 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
acquisitions are now common for this software giant, with Stellent Inc., Hyperion
Solutions Corporation, and, more recently, BEA Systems. As of the publication date of
this book, Oracle has acquired over 40 companies, making their products a significant
component of its growth strategy.
The database will always be the backbone of Oracle’s product line—hence the
fifth release of this successful suite of works: Oracle Database 11g: A Beginner’s
Guide. What many people find so fascinating about the Oracle technology stack
is how you can bury yourself in such a small part of the database offering. The part
that you are familiar with compared to the complete technology stack can be likened
to a little itty-bitty street corner compared to the network of intersections in a
thriving urban metropolis. Many of us live and breathe our piece of the database
technology, never having the opportunity to experience the features and functionality
leveraged elsewhere. That is why we wrote this Beginner’s Guide. Our main audience
is just that, the beginner, but there are also chapters in this book that cater to the
information needs of seasoned veterans with the technology.
In the earliest days of the Beginner’s Guide, we continually heard two dramatically
opposing opinions about the same thing. On one hand, some people said “One
thing I really like about the Oracle database software is that it’s so easy to tune”; on
the other hand, some claimed “One thing I really hate the Oracle database software
is that it’s so hard to tune.” Exactly where you align yourself as you get further and
further into this book remains to be seen; suffice it to say, the material covered in
Oracle Database 11g: A Beginner’s Guide will help you make more informed
decisions and adopt better best practices now and in the future. Oracle Database
is a powerful tool, and this book will be your first step toward empowerment and
your future of becoming an Oracle expert.
This book features the following elements, which enable you to check your
progress and understanding of the concepts and details of the product:
■Critical Skills listed at the beginning of each chapter highlight what you will
learn by the end of the chapter.
■Step-by-step Projects reinforce the concepts and skills learned in each
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
administrators (DBAs) do on a daily, weekly, and other basis. In Chapter 7 we
provide guidance on how to do backups and, in case things really go wrong with
your database, how to restore your old database.
Oracle 11ghas many features that are at the leading edge of technology, and
Oracle Rapid Application Clusters (RAC) and Automatic Storage Management (ASM)
are important technology in the order to support the high-availability needs of
today’s applications. Take time in Chapter 8 to become familiar with all of this
technology to ensure that you understand how today’s databases are deployed and
optimized for performance and availability.
Finally, in Chapter 9 we discuss features that apply to large databases. As you
will learn or are already aware, databases are growing at an exponential rate. We
need to use the facilities of the database that address this growth and ensure that we
optimize the investment an organization makes in its Oracle software. This book
closes by discussing many of the features that will become everyday necessities in
your Oracle job.
There is one thing you must keep in mind as you travel around the pages of this
book: Oracle Database 11gis a complex product with many, many more features
and facilities than we can discuss here. We have chosen topics based on our own
experiences of what Oracle customers use 90% of the time, but realize that this is
just the start of a very interesting journey. As we say, “You have to start
somewhere.”
Oracle Database is an exciting product, and one that will provide you with
limitless chances to learn more about it. This book is one of your first steps; we
hope you take from it the curiosity to dig deeper into the topics.
Introduction xvii
This page intentionally left blank
Chapter
1
Database Fundamentals
CRITICAL SKILLS
1.1 Define a Database
1.2 Learn the Oracle Database 11g
Architecture
1.3 Learn the Basic Oracle Database
11gData 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
2Oracle Database 11g: A Beginner’s Guide
his chapter is the start of your Oracle Database 11gjourney. 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
11gtechnology.
CRITICAL SKILL 1.1
Define a Database
Oracle Database 11gis 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
is a rich, full-featured software intended to revolutionize the way many companies
do their database business. Think of a database as the Fort Knox for your information.
A database is an electronic collection of information designed to meet a handful of
needs:
1. What is a database? Databases provide one-stop shopping for all your
data storage requirements, no matter whether the information has to do
with human resources, finance, inventory, sales, or something else. The
database can contain any amount of data, from very little to very big. Data
volumes in excess of many hundreds of gigabytes are commonplace in this
day and age, where a gigabyte is 1,073,741,824 bytes.
2. What must it be able to do? Databases must provide mechanisms for
retrieving data quickly as applications interact with their contents. It is one
thing to store tax information for the 300 million citizens of a country, but it’s
another kettle of fish to retrieve that data, as required, in a short time period.
3. How is it suitable for corporate data? Databases allow the sharing of
corporate data such that personnel data is shared amongst one’s payroll,
benefits, and pension systems. A familiar adage in the database industry
is “write once, read many.” Databases are a manifestation of that
saying—one’s name, address, and other basic personnel information are
stored in one place and read by as many systems requiring these details.
Figure 1-1 shows, in a nutshell, the components that come together to deliver the
corporate database management solution affectionately called Oracle Database 11g.
There is a great deal of academic interest in the database industry, because the
theory of the relational database is founded in relational algebra. As data is entered
into and stored in Oracle Database 11g, the relationships it has to other data are
T
Chapter 1: Database Fundamentals 3
defined as well. This allows the assembling of required data as applications run.
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
4Oracle 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
11garchitecture.
CRITICAL SKILL 1.2
Learn the Oracle Database 11gArchitecture
As with many new software experiences, there is some jargon that we should get
out of the way before starting this section.
■Startup This is the act of issuing the appropriate commands to make an
Oracle Database 11gaccessible to applications. After a startup activity
completes, the database is referred to as opened. Once opened, the
database moves to the next step where it is started. At this point, the
database is ready to use.
■Shutdown This is the act of stopping Oracle Database 11g. When Oracle
Database 11gis shut down, nobody can access the data in its files.
■Instance This is a set of processes that run in a computer’s memory and
provide access to the many files that come together to define themselves as
Oracle Database 11g.
■Background processes These are processes that support access to an
Oracle Database 11gthat has been started, playing a vital role in Oracle’s
database implementation. Various background processes are spawned
when the database is started and each performs a handful of tasks until a
database is shut down.
Let’s look at the assortment of files and background processes that support
Oracle Database 11g.
NOTE
In order to work with the code snippets and the sample
schemas we discuss throughout this book, you will need
to have the Oracle Database 11gsoftware installed and
the first database successfully created. The Database
Configuration Assistant (dbca) is the fastest way to set up
your first database. Most of the time you simply accept
the defaults suggested on the dbca screens. If you have
any problems with either the software installation or the
dbca, please either consult a more senior colleague or
surf MetaLink (http://metalink.oracle.com) to get
assistance (after supplying appropriate login credentials).
Chapter 1: Database Fundamentals 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 11gis 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 11ginsists 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 11gname for a database file. Think of it as a
space where a table resides. As an Oracle Database 11gis created, a system tablespace
is built that contains Oracle’s data dictionary. As Oracle Database 11goperates, it
continually gets operational information out of its data dictionary. As records are
created, this system tablespace defines attributes of the data it stores, such as
■Data types These are the characteristics of data stored in the database.
Are they numeric, alphanumeric, or perhaps binary of some video or audio
format?
■Field size This is the maximum allowable size for fields as they are
populated by the applications. This is where, for example, a country
description is defined as from 1 to 30 characters long, containing only letters.
6Oracle Database 11g: A Beginner’s Guide
■Ownership Who owns the information as the database data files are
populated?
■Viewing and manipulation rights Who is allowed to look at the data and
what are the types of activities that each database user can perform on that
data?
The system tablespace is a very close cousin of the sysaux tablespace discussed
next.
The Sysaux Tablespace
Many of the tools and options that support the Oracle Database 11gactivities store
their objects in this sysaux tablespace. This is mandatory as a database is created.
The Oracle Enterprise Manager (OEM) Grid Control repository used to go in its own
oem_repository tablespace, but with Oracle Database 11g(and its predecessors), its
objects now reside in sysaux.
Default Temporary Tablespace
As the dbca does its thing, a tablespace is created that serves as the default location
for intermediary objects Oracle Database 11gbuilds as it processes SQL statements.
SQL stands for structured query language, an industry standard in the database
arena, which is used to retrieve, create, change, and update data. Most of the work
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 11gsometimes calls the server parameter file its spfile. This is
where its startup parameters are defined and the values in this file determine the
Chapter 1: Database Fundamentals 7
environment that database operates in. As one starts an Oracle instance, the spfile is
read and various memory structures are allocated based on its contents.
Background Processes
Essentially, background processes facilitate access to Oracle Database 11gand
support the instance while it is running. These are the main background processes;
many of their names haven’t changed over the past few releases prior to Oracle
Database 11g.
■The database writer (dbw0) process This process (named dbwr in earlier
versions of Oracle Database) is responsible for writing the contents of
database buffers to disk. As sessions interact with Oracle Database 11g, all
the information they use passes through Oracle’s database buffers, a segment
of memory allocated for this activity.
■The log writer (lgw0) process This process (named lgwr in previous
versions of Oracle Database) manages the writing of information to the
online redo logs. A log buffer area is set aside in memory where information
destined for the online redo logs is staged. The transfer of this information
from memory to disk is handled by this process.
■The checkpoint process (ckpt) This is responsible for updating information
in Oracle Database 11g’s files during a checkpoint activity. A checkpoint is
the activity of writing information from memory to the appropriate locations
in Oracle Database 11g. Think of a checkpoint as a stake in the ground
allowing the restoration of a system to a specific point in time. The checkpoint
process may trigger lgw0 and dbw0 to do their specialized tasks.
■The system monitor (smon) process This is the gatekeeper of consistency
as Oracle Database 11gruns. Consistency defines the interrelatedness of
the database components with one another. A consistent instance must be
established every time Oracle Database 11gstarts, and it is smon’s job to
continually enforce and reestablish this consistency. Plainly put: an
inconsistent database is trouble!
■The process monitor (pmon) This is responsible for cleaning up any
resources that may have been tied up by aborted sessions interacting with
the database. The famous CTRL-ALT-DEL that people tend to use to reboot a
personal computer can leave resources tied up in Oracle Database 11g. It is
pmon’s job to free up these resources.
■The job queue coordination (cjq0) process This is responsible for
spawning job processes from Oracle Database 11g’s internal job queue.
Oracle Database 11gdoes some self-management using its job queue, and
users of the database can create jobs and have them submitted to this cjq0
coordinator.
■The archiver (arc0) process This is responsible for copying online redo
logs to a secondary storage location before they are reused by the next set
of transactions. In the “Online Redo Logs” section of this chapter, we
discuss how Oracle Database 11ginsists there are at least two online redo
logs. Suppose we call these groups A and B. Oracle Database 11guses
these two groups in a cyclical fashion, moving back and forth from A to B
to A to B and so on. The arc0 process, when and if instructed, will make a
copy of a file from log group A before allowing it to be reused.
Figure 1-2 illustrates the way the architecture components we have described
come together to support Oracle Database 11g. Oracle Database 11gis opened and
then started, and the control files are read to get its bearings. Then the online redo
logs and the assortment of tablespaces listed in the control files are acquired. As the
instance comes to life, the background processes take over and manage the
operations of the database from there.
8Oracle Database 11g: A Beginner’s Guide
FIGURE 1-2. Tablespaces, support processes, and infrastructure files
Project 1-1 Review the Oracle Database 11g
Architecture
There are many types of files that come together to support Oracle Database 11g.
In this section, we have discussed control files, online redo logs, the system
tablespace, and an assortment of datafiles and tablespaces that support the database.
As well, we have looked at the series of background processes that allow users to
interact with Oracle Database 11g. In this brief project, you will apply what you
have learned about the processes that support Oracle Database 11g. As you descend
into the land of Oracle Database 11g, you’ll find that this information is crucial to
your understanding of this remarkable software solution.
Step by Step
1. There are a few pieces missing in the following diagram of the infrastructure
of files that support Oracle Database 11g. Fill in the missing text where
required.
2. The second diagram shows a partial makeup of the background processes
with Oracle Database 11g. Complete the missing text where indicated
by broken lines.
Chapter 1: Database Fundamentals 9
Review the Oracle Database 11gArchitecture
Project 1-1
(continued)
10 Oracle Database 11g: A Beginner’s Guide
Project Summary
You don’t need to master Oracle Database 11garchitecture to become fluent with
the software. Just as an electrician needs the assistance of a good set of blueprints, the
Oracle Database 11gtechnical person should understand some of the inner
workings of the software. A peek under the covers, as brief as it may have been in
this section, is a good path to follow while becoming familiar with what Oracle
Database 11gis all about.
Before moving on to discuss Oracle Database 11gdata types, let’s spend a
minute looking at the database administrator, the ultimate director of the operations
of the database.
The Database Administrator
This privileged user of Oracle Database 11gis commonly the most experienced
technician in the shop, with some exceptions. Often, recent adopters of the Oracle
technology have little or no in-house experience, and one or more employees may find
themselves targets of the familiar directive “So, you’re the new Oracle Database 11g
DBA!” One scrambles to find sources for technical knowledge when thrust into this
role. What better place to be than reading Oracle Database 11g: A Beginner’s Guide?
The following list outlines common responsibilities of the Oracle Database 11gDBA:
■Installation and configuration The DBA must install and customize the
Oracle Database 11gsoftware and any assorted programs that will run
alongside and access the database.
Chapter 1: Database Fundamentals 11
■Create datafiles and tablespaces The DBA decides which application the
data will reside in.
■Create and manage accounts The DBA sets up the accounts with the
usernames and passwords that interact with the database.
■Tuning The DBA tweaks the environment that Oracle Database 11g
operates in by adjusting initialization parameters using the system parameter
file.
■Configure backups Alongside recovery testing, the DBA performs this
activity to ensure the usability and integrity of system backups.
■Work with developers This is an ongoing process for DBAs, to ensure that
the code they write is optimal and that they use the server’s resources as
efficiently as possible.
■Stay current DBAs keep abreast of the emerging technology and are
involved in scoping out future directions based on the enhancements
delivered with new software releases.
■Work with Oracle Support Services DBAs initiate service requests (SRs) to
engage support engineers in problem-solving endeavors. The front-end of
the SR creation process is called MetaLink (described earlier in the chapter).
■Maximize resource efficiency The DBA must tune Oracle Database 11g
so that applications can coexist with one another on the same server and
share that machine’s resources efficiently.
■Liaise with the system administrators DBAs must ensure that the appropriate
disk space and processor power are available and properly utilized.
As with most lists, after reading the preceding bullet points, you may wonder
what else DBAs do with their time. As you work with Oracle Database 11g, you’ll
experience other activities that will plug any loopholes that may exist in the
previous list.
CRITICAL SKILL 1.3
Learn the Basic Oracle
Database 11gData Types
Very early in one’s journey through the world of Oracle Database 11g, it becomes
time to learn its common data types. Regardless of your past experience in information
technology, data types are nothing new. Let’s look at the most common type of data
that can be stored in Oracle Database 11g; keep in mind that the list is much longer
than the one presented here.
varchar2
By far the most common data type, varchar2 allows storage of just about any
character that can be entered from a computer keyboard. In earlier software
solutions, this was commonly referred to as alphanumeric data. The maximum
length of varchar2 is 4000 bytes or characters, and it possible to store numeric data
in this data type. This is a variable length character string, with no storing of trailing
insignificant white space:
create table ... (
name varchar2(30),
city varchar2(30),
...
...
state varchar2(2));
If a program or SQL statement tries to store a set of characters in a varchar2 field
that is longer than the field’s specification, an error is returned, and the statement
stops running and returns control back to you.
number
The number data type allows the storing of integer as well as integer/decimal digits.
When non-integer data is stored, the total number of significant digits of the number
is referred to as precision, while the portion to the right is called scale or decimal
places. For example, the number 29.1963 has a precision of 6 and a scale of 4. The
maximum precision is 38 and the maximum scale is 127. The confusing part of the
specification of a number data type comes into play when storing non-integer
information. Table 1-1 illustrates this concept.
When defining a number data type with decimal places, it’s important to know
that the maximum integer portion of the number data type is the difference between
the two numbers specified. The specification (6,3) allows for two, not six, integer
12 Oracle Database 11g: A Beginner’s Guide
Number Specification Column Length (Precision) Decimal Digits (Scale)
(3,2) 3 2
(6,3) 6 3
(17,12) 17 12
TABLE 1-1. Number Data Type Specification
Chapter 1: Database Fundamentals 13
digits. If more decimal digits are received than the column definition permits, it
rounds the value before storage.
date
The date data type stores time and date information, with the time component
rounded to the nearest full second.
There are many, many functions available to be performed on date fields as they
are extracted from an Oracle Database 11g.
When date columns are selected from Oracle Database 11g, it is common to
perform a function on their values to make them more readable. By default, the time
component of a date column is not displayed without manipulating its contents using
a to_char function, described in Chapter 4. By default the general display format for a
date is DD-MON-YY (day, month, and year). This format may be changed via the
NLS_DATE_FORMAT parameter or by using a display format function.
timestamp
The timestamp data type is a close relative of date. The major advantage is that the
timestamp stores information about the second to a much higher accuracy. In this
time when every subsecond counts, the timestamp can be a valuable asset. There is
a time component in this data type, displayed with the data without the need for the
to_char function. This listing illustrates this concept:
SQL> create table timestamp_test (ts timestamp);
Table created.
SQL> insert into timestamp_test values (sysdate);
1 row created.
SQL> select * from timestamp_test;
TS
--------------------------------------------------------------------------
14-DEC-09 05.25.07.000000 PM
SQL> create table date_test (d date);
Table created.
SQL> insert into date_test values (sysdate);
1 row created.
SQL> select * from date_test;
TS
---------
14-DEC-06
clob
The clob data type allows storage of very large objects in excess of four gigabytes in
size. Since this is a true character data type, it is very similar to the varchar2 data
type except for its much larger maximum size.
blob
The blob data type permits storage of large unstructured binary objects. Sound and
video are examples of blob data.
It’s now time to have a look at the most common object in Oracle Database
11g: the table. After that, we will have a look at a few types of programming units
written using SQL, which a person can store in Oracle Database 11g.
CRITICAL SKILL 1.4
Work with Tables
The best way to think of a table in a relational database such as Oracle Database
11gis to see it as a spreadsheet with rows and columns. With this in mind, note the
following:
■Rows are often referred to as records.
■Each column has a name unique to the table that it resides in.
■The intersection of each row and column, referred to as a cell in a
spreadsheet, is called a field in Oracle Database 11g.
Picture the following SQL statement, which creates a table (the line numbers are
not part of the code):
1- create table part_master (
2- id number(8) not null,
3- manufacturer_code number(4) not null,
4- inception date not null,
5- description varchar2(60) not null,
6- unit_price number(6,2) not null,
7- in_stock varchar2(1));
Let’s pick apart the code and highlight the main points in Table 1-2.
Table 1-2 mentions the concept of a relational database. Let’s inspect a few
other tables and see how they are related to one another.
Tables Related to part_master
The manufacturer_code column in part_master points to a record in manufacturer.
Also, some columns in manufacturer may end up being related to column values
in other tables. Figure 1-3 illustrates these relationship concepts, the heart of the
Oracle Database 11gimplementation.
14 Oracle Database 11g: A Beginner’s Guide
Chapter 1: Database Fundamentals 15
Line Important Points
1 The table has a unique name, from 1 to 30 characters. It is stored in
Oracle Database’s data dictionary in uppercase.
2 The ID column is numeric with anywhere from one to eight digits. The
application that creates and keeps track of parts may insist that the first
character of the ID be a digit between 1 and 9. Since the field is defined
as numeric, if the leading digit were a 0, the part ID would only be
seven digits long.
3 The manufacturer_code is the only manufacturer information stored in
part_master. Further information about who made the product is in a
related table—hence, the terminology relational database.
4 inception, as a date field, contains a date and time specification, though it
will display a default month abbreviation and a two-character year unless
some manual manipulation is performed (for example, 12-NOV-05).
5 description is a free-form field with a variable length of up to 30 characters.
6 unit_price can accommodate up to four integer and two decimal digits.
7 in_stock is a one-character flag of sorts; thus, the system designers can
decide to use an indicator like a “1” or “X” to represent items that are in
stock. Notice how this is the only one of seven fields in the
PART_MASTER table that can be left blank.
TABLE 1-2. part_master Table Definitions
FIGURE 1-3. Relationships to part_master
16 Oracle Database 11g: A Beginner’s Guide
Suppose someone wanted to know where in the country a certain part was
manufactured. By looking at Figure 1-3, that information is not readily available in
part_master. However, part_master has a manufacturer_code. So, a person would
traverse to manufacturer using manufacturer_code to get a location_id. Armed with
that value, one then proceeds to location to get a quadrant column value. After this
navigation is complete, a person would know where a specific part is built. Table
1-3 maps out this journey.
As illustrated in Table 1-3, you can deduce that part 33499909 comes from the
Pacific Northwest—a deduction that is made by following the relationships between
matching columns in the three tables in question.
CRITICAL SKILL 1.5
Work with Stored Programmed Objects
Oracle Database 11goffers the ability to store user-defined programming units in
the data dictionary, called stored objects. These programming units are written in
PL/SQL, the topic of Chapter 5. Without worrying about what goes inside these
objects, let’s do an overview of what they are all about.
Views
Views are predefined subsets of data from an Oracle Database 11gtable. The SQL
query that builds the view is stored in the data dictionary and need not be reassembled
every time the view is used. Suppose a personnel application stores the location of
all employees in its EMPLOYEE_MASTER table in the loc_id column. With Oracle
Database 11g, you can define a view called emp_hq as follows:
create or replace view emp_hq
as select * from employee_master
where loc_id = '2';
EMP_HQ becomes a valid object of the select statement just as if it were a table
of its own.
Table Part Number Column Value Related Column Value
part_master 33499909 manufacturer_code 3490
manufacturer 3490 location_id 5
location 5 quadrant Pacific Northwest
TABLE 1-3. Following Relationships Between Tables
Chapter 1: Database Fundamentals 17
NOTE
At this point, you should realize that views are
generally built based on more than one table. A view
provides the perfect environment to predefine join
conditions between tables to ensure that they adhere
to business rules and perform at an optimal level.
Ask the Expert
Q: What is the major difference between the clob and blob data types in
Oracle Database 11g?
A: The clob stores only alphanumeric data, whereas the blob can
accommodate any type of data, including sound and video.
Q: When specifying the number data type, how is the total length of the
field determined?
A: The total length of a numeric field is determined by the digit(s) to the left
of the comma if the specification includes an integer and decimal component.
For example, number(4,1) denotes a maximum of four digits, of which one
digit can be stored to the right of the decimal point.
Q: Which of the Oracle Database 11gbackground processes is responsible
for writing information from memory into the database files?
A: This is the job of the database writer, or dbw0, process.
Q: Where does Oracle Database 11gread its environment from as it is started?
A: The startup parameters are read from the system parameter file, which can
be a binary file stored in Oracle Database 11g.
Q: As sessions interact with the data in Oracle Database 11g, what role
does the undo tablespace play in the architecture of the software?
A: When transactions change the contents of information in Oracle Database
11g’s tables, this special tablespace keeps a “before image” of the changes in
case the operator decides to back out before saving newly entered
information.
18 Oracle Database 11g: A Beginner’s Guide
Triggers
Just as their name implies, triggers are stored objects that fire based on the execution
of certain events. Suppose a payroll application wants to audit salary increases: a
trigger is created that fires when the salary column in hr_master is updated. The
trigger could do the following:
1. Create a record in sal_audit.
2. Trap the time and date of the transaction.
3. Place the user’s login ID in the doer column.
4. Place the old salary value in the old_sal column.
5. Place the new salary value in the new_sal column.
Code in the trigger traps the event by specifying on update. While triggers are
commonly used for auditing, the types of activities they can initiate are endless.
NOTE
Triggers cannot exist independently of an Oracle
Database 11gtable. They are associated with one
and only one table and, if a table is dropped, so is
the trigger.
Triggers, as well as procedures, packages, and functions described next, are
most commonly written using PL/SQL. The PL/SQL programming language is the
topic of Chapter 5.
Procedures
Procedures perform specific tasks as applications interact with Oracle Database
11g. If there are a number of interrelated activities to carry out in one place, a
procedure is an ideal way to do this. Procedures can accept parameters when
invoked and can interact with objects in Oracle Database 11g. They encapsulate
related activities into single programming units that simplify logic and share data
values as they perform various activities. They offer extremely flexible features,
many of which are not available with triggers.
Functions
Functions are very close relatives of procedures, except that they return a value to
the code that called them. Oracle Database 11gdelivers many functions out of the
box and developers can create their own functions to augment what is delivered
with the software. Suppose you want to strip all the vowels out of a name with a
function. You can pass in a name (for instance, Bellissimo) and gets back the text
“Bllssm” when the function completes its work. Let’s look at the get_age function,
which operates based on the following logic:
given a date of birth (format DD-MON-YYYY)
using an SQL function
get the months between today's date and the date passed in
divide the number of months by 12
truncate the results (giving the span in years between the 2 dates)
pass integer back
Packages
Packages roll functions and procedures together into a cohesive programming unit.
Often, developers prefer to bundle like functionality together since it makes logical
sense to call one larger unit and have it perform a series of tasks. Let’s look at the
CREATE_EMPLOYEE package in Table 1-4.
Chapter 1: Database Fundamentals 19
Component Name Type Work Accomplished
give_holidays Procedure Creates the default holiday quota based
on the new person’s rank in the company.
notify_benefits Procedure Creates a record in the BEN_QUEUE
table to alert the benefits people of the
new employee.
is_under_25 Function Returns a “1” if the new employee is
under 25 years old as of December 31
of the year they were hired.
is_over_59 Function Returns a “1” if the new employee is 60
years old or older as of the calendar date
of hire.
TABLE 1-4. Members of the CREATE_EMPLOYEE Package
Progress Check
1. Oracle Database 11gis referred to as a relational database.Why is the
word relational used?
2. What is the maximum length of a varchar2 data type that can be stored in
Oracle Database 11g?
3. What is data consistency when referred to as a feature of Oracle Database
11g? Give an example.
4. What types of stored objects can be encapsulated into an Oracle Database
11gpackage?
5. What is the fundamental difference between a procedure and a function in
Oracle Database 11g?
6. Data in the system tablespace is often referred to as metadata—or data
about data. Name at least two types of metadata in the system tablespace.
7. What is the difference between the timestamp and date data types?
8. What type of information and data ends up being stored in the sysaux
tablespace?
20 Oracle Database 11g: A Beginner’s Guide
Progress Check Answers
1. The word relational is used because Oracle Database 11gdefines the relationships between
tables. It is these relationships that allow applications to navigate an assortment of tables and
assemble results from more than one table.
2. The varchar2 data type can accommodate up to 4000 characters.
3. Data consistency refers to the ability to ensure that related items of information are manipulated
in a similar fashion. Suppose an application assigns a department to a new employee as a two-digit
number field. Sometime down the road, due to company growth, the department identifier is changed
to three digits. All the data where this used-to-be two-character identifier is stored must be changed
to reflect the expansion of the department codes.
4. Packages can contain a mixture of one or more functions and procedures.
5. A procedure receives from zero to many parameters as it is invoked and then goes about its
business until the end of its code segment. A function, on the other hand, accepts one or more
parameters as it is called and returns a value to the code from where it was invoked. The procedure
passes nothing back to its caller.
6. Metadata defines items such as the names of tables in the database, the owners of the tables, the
data types of the columns in each table, and who is allowed to look at what data.
7. When columns are displayed, they use the date data type, containing a day/month/year component,
whereas, by default, the timestamp data type columns contain a time-of-day component as well.
8. The sysaux tablespace contains tables required to manage Oracle Database, such as the items
required to support OEM Grid Control.
Chapter 1: Database Fundamentals 21
CRITICAL SKILL 1.6
Become Familiar with Other Important Items
in Oracle Database 11g
So far, you’ve had a brief look at tables, views, tablespaces, and a handful of stored
objects, such as views, triggers, procedures, packages, and functions. Let’s round
out this introduction to Oracle Database 11garchitecture by covering a few other
items commonly encountered. The following discussion is a hodgepodge of things
that are necessary for a person’s understanding of the Oracle Database 11g
architecture and operations. Keep in mind that you must also spend a bit of time
looking at the role of the database administrator, affectionately called the DBA, who
is the gatekeeper of the database and the person responsible for its smooth
operation.
NOTE
You’ll get a more detailed look at the DBA in
Chapter 6, with more information on how DBAs go
about carrying out their administrative chores.
Indexes
Tables are made up of rows and columns, which are the basis of all objects in
Oracle Database 11g. As applications interact with the database, they often retrieve
vast amounts of data. Suppose MyYP, a fictitious Internet company, provided
Yellow Pages listings for North America, and the data was stored primarily in a table
called YP_MASTER. Each row in the YP_MASTER table is uniquely identified by
a combination of company name, municipality, and geographic location (state
or province). As words are retrieved from the database to satisfy online queries,
indexes would provide a quick access path to the qualifying data. Specific index
characteristics are relevant to the power they deliver in Oracle Database 11g. For
instance:
■They are built on one or more columns in a table using simple SQL
statements.
■They are separate from the tables that they are built on and can be dropped
without affecting the data in the table itself. On the contrary, when a table
is dropped, any indexes it has disappear with the table.
■The function they perform can be likened to the index in a book. If one
were looking for a specific topic in a textbook, the best place to start would
be the index—it provides a shortcut to the information being sought. If one
imagined that YP_MASTER were a book rather than a table, finding Y&M
Plumbing in Pensacola, Florida would be faster using the index than reading
the book from the start to the 25th letter of the alphabet. The names on the
corner of the pages in a phone book are like an index.
22 Oracle Database 11g: A Beginner’s Guide
■Indexes occupy space in the database; even though there are ways to keep
their space to a minimum, extra space is required and must be pre-allocated.
Users
Most of us are familiar with usernames and passwords from our experience logging
into corporate networks and other secure systems. Oracle Database 11gimplements
the same mechanism with login credentials and privileges given out by the database
administrator. Once accounts are created, people initiate connections to Oracle
Database 11gand work with their own data and other users’ data where the
appropriate privileges have been given out. We discuss object privileges in the
“Work with Object and System Privileges” section immediately following this one.
NOTE
With Oracle Database 11g, the terminology user,
account, and schema are used synonymously.
Once an account is created, it is often given the rights to occupy space in one or
more Oracle Database 11gtablespaces. This is discussed in the next section.
Tablespace Quotas
As additional nonsystem tablespaces are created, the database administrator gives
out quotas that allow users to occupy space therein. Tablespace quotas are given
out using an SQL statement with three parts:
■The username to whom the quota is being given.
■The name of the tablespace within which the username is being permitted
to create tables.
■The amount of that quota—whether it’s mentioned in absolute bytes (for
example, 500,000) or more commonly in quantities of megabytes (500MB,
for instance). Unlimited quotas can be allowed using the keyword
unlimited.
Regardless of how a quota is given out, the SQL statement passed to Oracle
Database 11gresembles the following:
SQL*Plus: Release 11.1.0.1.0 - Production on Sun Mar 11 10:29:42 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle11g Enterprise Edition Release 11.1.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Chapter 1: Database Fundamentals 23
SQL> alter user hr quota 500m on hr_data;
User altered.
SQL> alter user ap quota unlimited on ap_idx;
User altered.
Synonyms
You’ll remember that in the “Work with Tables” section we discussed that the key
was passing Oracle Database 11gthe create table keywords. In a nutshell, table
creation is undertaken after establishing a successful connection to the database,
and then, with appropriate privileges in place, defining a table. One of the key
concepts with all database management systems is sharing data. Since it is key to
only have one copy of a table and to have its contents shared amongst applications,
synonyms are a way to reference other people’s data.
Suppose you wanted to use the PART_MASTER table in an application owned
by a user other than the owner. That owner would permit us to work with the table’s
data, and then we would create a synonym to reference its contents. The code
would resemble the following:
SQL*Plus: Release 11.1.0.1.0 - Production on Sun Mar 11 10:29:42 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle11g Enterprise Edition Release 11.1.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> create synonym part_master for inv.part_master;
Synonym created.
SQL> select count(*)
2 from
part_master
3 where in_stock is not null;
COUNT(*)
-------------
13442
The preceding SQL statement references an object called part_master. Depending
on how your access is defined, the way that you reach the physical table may be
different. When you are the owner of the table and you use the table’s name in the
from clause, Oracle understands that you would like to use your own table. If you
do not own a table by that name, Oracle then looks in a list of table synonyms or
pointers to a table with that name owned by someone else. This process of using
synonyms is a transparent operation. If you do not own the table by the name or no
synonym exists, you will receive an error. There are actually two kinds of synonyms:
■Private synonyms are created in one account and are only usable by the
creator.
■Public synonyms are created by a central privileged user and are available
to anyone able to connect to Oracle Database 11g.
24 Oracle Database 11g: A Beginner’s Guide
NOTE
One needs the appropriate object privileges to be
able to work with someone else’s data using a
private or public synonym. The synonym itself does
not imply that the appropriate privileges can be
circumvented.
Roles
Often it makes sense to group similar users together to streamline the organization
of people who use Oracle Database 11g. Using roles, the DBA can logically lump
personnel together and give out object privileges to roles rather than individual users.
Roles can be password protected, though in most implementations they do not have
this level of complexity.
Default User Environments
As accounts are created by the DBA, users are given a default environment to use
unless some specifics are coded as they interact with Oracle Database 11g. Users
are commonly set up with the following default environment settings:
■The default tablespace is where tables are placed unless the create table
statement explicitly points at a nondefault tablespace that the user has a
quota for.
■Temporary tablespaces are the tablespaces where users perform sort and
merge operations while the Oracle Database 11gengine is processing
queries.
Users can be given membership in one or more roles and have their default
profile changed as well. As users are created, they do not automatically inherit a
default tablespace; one must be manually given out during or following the user
creation statement. Users do automatically point at a temporary tablespace, as
discussed in the “Default Temporary Tablespace” section of this chapter, unless
manually pointed elsewhere.
NOTE
With Oracle Database 11gone is now able to set
the default tablespace for the entire database
instance. This is done via an “ALTER DATABASE
DEFAULT TABLESPACE tablespace-name;”
command. You will need to have the privileges
to be able to perform this operation.
Progress Check
1. Name at least four tasks handled by the Oracle Database 11gadministrator.
2. What is the difference between public and private synonyms?
3. What is meant by a user’s default tablespace?
4. What two units of measurement are commonly used to specify a tablespace
quota?
5. Where do DBAs go to create iTARs, where assistance is requested from
Oracle’s support organization?
6. Which of the following—procedures, packages, or triggers—cannot exist
independent of a table to which they belong?
CRITICAL SKILL 1.7
Work with Object and System Privileges
It’s next to impossible to work with data in Oracle Database 11gwithout looking at
object privileges. In this section, we are going to look at these privileges as well as a
suite of system privileges closely related to managing Oracle Database 11g. The
four main object privileges are select, insert, update, and delete, all discussed in the
next four sections. Oracle Database 11guses the term grant when referring to giving
out both object and system privileges.
Select
This is the primary and most commonly used privilege, permitting other users to
view your data. There are three parts to grant statements:
■The keywords grant select on.
■The name of the object upon which the privileges are being given out.
■The recipient of the grant.
Chapter 1: Database Fundamentals 25
Progress Check Answers
1. Installation, upgrades, tuning, and environment setup are four of many tasks performed by the DBA.
2. A private synonym can only be referenced in a SQL statement by the user who created and owns
the synonym. A public synonym, created by a centralized user such as a DBA, is available to all users.
3. The default tablespace is the one within which users occupy space by default, unless another
tablespace is mentioned as a table is created.
4. Quota on tablespaces is usually given out using bytes or megabytes as units of measurement.
5. The DBA goes to MetaLink to request assistance from Oracle’s support organization.
6. Triggers cannot exist on their own without association with an Oracle Database 11gtable.
26 Oracle Database 11g: A Beginner’s Guide
Once the select privilege has been given out, the recipients, using a private or
public synonym as described earlier in the “Synonyms” section of this chapter, can
reference your objects in their SQL statements.
Insert
This privilege allows users to create rows in tables belonging to other users. The
creator of new rows in other users’ objects is bound by the same rules used if they
owned the objects themselves. They must adhere to the boundaries defined by the
data types of the columns in the rows they create. For example, when rows are
inserted into a table that has a column defined as type DATE, they must ensure that
valid date type data is placed in the column so defined. As rows are created in an
Oracle Database 11gtable, the transaction must be committed to the database
before the row becomes part of the information available to other users. With
Oracle Database 11g, we use the term commit the same way the word save is
used with other types of software.
Update
This privilege allows a person to change the contents of columns in rows belonging
to other tables. The SQL update statement can change the value of data in one or
more columns. As with insert activity, the update transactions need to commit their
work to make it permanent in the Oracle Database 11gfiles.
Delete
Delete operations interact with one or more rows in Oracle Database 11gtables
and must be followed by a commit as well to write the results of the transaction to
the database files.
You will see more in Chapter 4 about how SQL statements are constructed using
the four keywords in the previous sections. SQL statements are subject to rigorous
syntax requirements which, if not followed, return an assortment of Oracle errors.
Just as with other programming languages you may be familiar with, the SQL statement
processing engine is very strict with reserved words and the placement of the pieces
that come together to form an SQL transaction. Let’s briefly discuss system privileges
that allow certain users of Oracle Database 11gto perform secure activities.
System Privileges
We have mentioned the database administrator in a number of places in this
introductory chapter. Classically, secure operations are performed by the DBAs;
however, one can grant system privileges to specified users so that they can perform
Chapter 1: Database Fundamentals 27
selected activities themselves. The following list illustrates a few examples of these
secure operations:
■Alter system There are a number of modes that Oracle Database 11gcan
operate from. The modes are toggled using alter system. For example, this
privilege can be given out to Jane by issuing the command grant alter
system to jane.
■Create user/alter user Often, the DBA wants to partition some of the user
creation activities between a handful of users of Oracle Database 11g. This
is done by giving out the create user system privilege. Once new users are
created, you often don’t want to tweak their environment; this can be
accomplished by issuing the grant alter user statement to one or more users
of the database.
■Create session/table/trigger Sometimes when new users are created, they
are given the create session system privilege which allows them to connect
to Oracle Database 11g. In many cases, depending on how new users are
created, they are not allowed to build any objects until they receive the
create table system privilege. As well, many users are not capable of
defining triggers until they receive the create trigger system privilege.
System privileges were introduced with early releases of Oracle7 (circa 1993)
and have played a useful role in the division of labor in the database since their
inception. Now it’s time to get into the meat of the seventh letter of the alphabet,
g, that throughout this chapter has followed the two-digit version number of this
software release—11.
CRITICAL SKILL 1.8
Introduce Yourself to the Grid
As many have heard, the “g” in Oracle Database 11gstands for grid. Grid computing
is a technology that allows for seamless and massively scalable access to a distributed
network of diverse yet homogenous computer types. Oracle Database 11gis the
glue permitting different vendors’ computers to work together providing a seemingly
endless supply of shared computer resources. Oracle sees the grid as revolutionizing
the way companies go about doing their business. Grid computing targets the
delivery of information as a utility, similar to the way electrical and telephone
services are currently delivered to the public—hence the term grid. The industry
as a whole, but Oracle in particular, sees a delivery method from the grid such
that consumers will only pay for what they use. Interlaced computers will allow idle
28 Oracle Database 11g: A Beginner’s Guide
capacity to be leveraged by the grid to provide for a form of parallel processing on
steroids. The following are the major players that enable the Oracle grid technology:
■Real Application Clusters (RAC) Involves a suite of networked computers
sharing a common Oracle Database 11gand running platform-independent
clusterware, the glue that makes the interconnect between the clustered
nodes so transparent.
■Automatic Storage Management (ASM) A front-end management system
that can group disks from an assortment of manufacturers together to form a
Ask the Expert
Q: Name the four main object privileges used in Oracle Database 11g.
A: The four most common privileges are select, insert, update, and delete.
Q: Placing an Oracle Database 11gin a state where it can be accessed by
applications is referred to as what activity?
A: Putting an Oracle Database 11gin a normal operating mode for
day-to-day access by a company’s applications is referred to as startup.
Q: How many integer and decimal digits can a field defined in the data
dictionary as number(10,2) accommodate?
A: The field would be able to store up to eight integer digits and two decimal
digits.
Q: When Oracle Database 11gis passed the value “Beginner ” for storage
in a varchar2 column, how does it deal with trailing insignificant spaces?
A: The trailing spaces are trimmed before the information is stored in the
database. Though not as common as varchar2, the char data type can be used
to store trailing spaces.
Q: What would Oracle Database 11gstore as a value in a number(6,2) field
when passed the value 9.8882?
A: It would store 9.89 in a number(6,2) field when passed 9.8882.
suite of disks that is available to all computers on the grid. ASM encapsulates
the complete life cycle of disk management and allocation into a centralized
GUI interface.
■Oracle Resource Manager Provides a framework within which administrators
can control the computing resources of nodes on the grid.
■Oracle Scheduler Allows the handing out of jobs to members of the grid
to facilitate the execution of business tasks anywhere and everywhere
where idle resources exist.
■Oracle Streams Assists the processing requirements whereby copies of
data need to be streamed between nodes in the grid, providing the
mechanisms to keep data in sync on one database with the database from
which the data originated. Oracle Streams’ tight integration with the Oracle
Database 11gengine facilitates this synchronization and delivers a
preferred method of replication.
Figure 1-4 illustrates the primary differences between grid computing and
traditional approaches to providing computer services.
Chapter 1: Database Fundamentals 29
FIGURE 1-4. Differences between traditional and grid computing
The following points reinforce the details of the two scenarios depicted in
Figure 1-4:
■The three applications at the top of the figure each have a dedicated server,
each with its own dedicated disk. If the Linux server were to go out of
service, the pension application would grind to a halt. There is no built-in
mechanism for pension system processing to carry on another server.
■The three applications at the bottom are interlaced with one another. The
benefits application can be hosted on from one to three of the available
servers. As well, the database files that support these three applications can
reside upon (and be read from) any of the nine disks in the grid’s disk farm.
The browser-based OEM Grid Control holds the whole thing together. With the
implementation of the ASM component of Oracle Database 11g, disks are managed
by OEM, database instances are managed by OEM, clusterware is managed through
OEM; the list is endless. Figure 1-5 shows the first OEM screen that appears after
entering appropriate login credentials.
NOTE
There is an OEM configuration program (called
emca) that must be successfully run before you can
access the browser-based OEM. The screen shown
in your version of Oracle Database 11gmay be
somewhat different than the one shown in Figure
1-4. The look and feel of the OEM Grid Control
screens can change significantly between minor
releases of the software.
30 Oracle Database 11g: A Beginner’s Guide
FIGURE 1-5. OEM startup
Chapter 1: Database Fundamentals 31
CRITICAL SKILL 1.9
Tie It All Together
Now that was quite a journey! We have covered database fundamentals, with an
Oracle Database 11gflavor. Relational database management systems have been
around for a few decades, and the release of Oracle Database 11gis a landmark
in the industry. There have been many academic discussions about the grid
technology— some claim Oracle Database 11gis a grid implementation, while
others don’t. Regardless of which side of the fence you’re on, Oracle Database 11g
is a big step. Let’s pull it all together and spend a bit of time on the big picture.
Oracle Database 11gis a collection of special files created using its database
configuration assistant and then completing the work using OEM Grid Control.
Access to these database files is facilitated by a set of shared memory processes
referred to as an instance. Many technicians use this term synonymously with
database. There is nothing wrong with that because, even though they are
technically different pieces, they cannot survive without each other.
Relationships between objects in the database are defined in the data
dictionary— hence the familiar term relational database. It is these relationships that
provide the power and allow Oracle Database 11gto store vast amounts of data.
Storing that data is one thing—retrieving it for applications in a quick and complete
fashion is another story. Data retrieval is one of the strengths of the Oracle Database
11gengine.
Over the next eight chapters, we will be delving into more details of the Oracle
Database 11goffering, paying specific attention to the following:
■Installing Oracle In order to become productive with Oracle, it is
valuable to learn the steps that are necessary to install and connect to the
database. This will help you get started as well as prepare you for the
samples within the book.
■Networking The glue that holds many systems together and allows
computers to communicate with one another in widely diverse and
separated locations.
■SQL—Structured Query Language This is the way we communicate with
Oracle Database 11g. Whatever the programming language (from C to
Java), SQL is all the database engine understands.
■PL/SQL A programming language native to the Oracle Database 11g
engine, providing more procedural capabilities that can amplify and
enhance the functionality of SQL.
■The Database Administrator The person who is the gatekeeper of Oracle
Database 11gand the one responsible for its smooth operation and optimal
performance.
32 Oracle Database 11g: A Beginner’s Guide
■Backup and Recovery Two areas critical to the smooth operation of
Oracle Database 11g. Oracle Database 11g’s Recovery Manager (referred
to as RMAN) is the fundamental building block in its backup and recovery
implementation. In addition, your backup and recovery strategy is one that
will need to be considered as input to your Disaster Recovery strategy.
■High Availability The need to support databases and applications for
extended and uninterrupted times requires Oracle to be able to support
these demands. This chapter focuses on Rapid Application Clusters (RAC)
and Automatic Storage Management (ASM) to serve as the foundation to
your high availability solutions.
■Large Database Features Oracle Database 11gexpands on an already
solid offering in this area. With Oracle9i, they boasted the ability to support
a database of up to 500 petabytes. Oracle Database 11gexpands that upper
limit to many exabytes, a staggering number, to say the least—where an
exabyte is 1,152,921,504,606,846,976 bytes—or about one trillion million!
þChapter 1 Mastery Check
1. The __________ background process is primarily responsible for writing
information to the Oracle Database 11gfiles.
2. How many online redo log groups are required to start an Oracle Database
11g?
A. 3
B. 2
C. 4
D. 1
3. Of the following four items of information, which one is not stored in
Oracle Database 11g’s control files?
A. The name of the database files
B. The creator of the database
C. The location of the database files
D. The sizes of the database files
4. What is the function of a default temporary tablespace in the support of
Oracle Database 11g?
Chapter 1: Database Fundamentals 33
5. Differentiate between an Oracle Database 11gand an instance.
6. Activities such as allocating space in the database and user management
are commonly performed by the DBA. What feature in Oracle Database
11gallows some of these secure operations to be carried out by non-DBA
users? How are these rights given out?
7. As a user of Oracle Database 11gis created, you often specify a default
tablespace. In this context, what does default tablespace mean?
A. The system tablespace
B. A tablespace the user can occupy space in without a private or public
synonym
C. The tablespace within which objects are created if a location
(tablespace) is not explicitly mentioned as a table is created
8. The __________ GUI interface is used to create a new database.
9. What happens when one tries to store the text “Madagascar” in a field with
a specification of varchar2(8)?
10. What is the most common way one uses triggers in Oracle Database 11g?
Give an example of this activity.
11. What programming language, native to Oracle Database 11g, is used to
create stored objects such as triggers and functions?
A. SQL*Plus
B. OEM Grid Control
C. Basic
D. PL/SQL
12. What is the role of the sysaux tablespace in Oracle Database 11g?
13. The clob and blob data types differ in all but one of the following three
ways. Which one does not apply to the differences between the two data
types?
A. The clob holds standard alphanumeric data, whereas the blob may store
binary information.
B. The blob contains a time (hour/minute) component, but the clob does not.
C. The blob contains unstructured free-form data, whereas the rules
governing the type of information that can be stored in the clob are
more stringent.
14. There are many ways to replicate data from one node to another. What
main feature does Oracle Streams provide that is missing from many other
methods?
15. What does the acronym SQL stand for?
A. Structured Query Language
B. Simple Query Language
C. Straightforward Question-based Learning
34 Oracle Database 11g: A Beginner’s Guide
Chapter
2
Installing Oracle
CRITICAL SKILLS
2.1 Research and Plan the Installation
2.2 Set Up the Operating System
2.3 Get Familiar with Linux
2.4 Choose Components to Install
2.5 Install the Oracle Software
36 Oracle Database 11g: A Beginner’s Guide
nowledge of the technology behind Oracle and an understanding its
tables and columns are basic skills needed for working with Oracle;
however, you need to install the software to take advantage of all of
the functionality of the product. There are many product options and
features that you will need to select during installation and for this
chapter we suggest you consider installing all options for your educational use of
the product. Are you ready to install the Oracle software? You are probably not as
ready as you think, unless you have already done some research and prepared the
environment. Downloading or obtaining the media for the Oracle software is the
easy part, but do you know which operating system you are using and have you
set up the configurations for Oracle?
Oracle has a proven track record on a variety of operating systems. Oracle 11g
was first released on the Linux platform (among other Unix-based platforms), then
followed closely by a release for Windows, which is the normal pattern in which
Oracle develops and releases products. We suggest that you get the latest version of
the software from the Oracle Technology Network (otn.oracle.com) for your chosen
platform.
It is also critical to be able to configure or verify the operating system configuration
for successful installs and, ultimately, well-running and performing systems. We
recommend, therefore, that you have administrator access to the server that you plan to
install the software on. Being able to tune or discuss the issues regarding the operating
system comes in handy when you’re looking at configurations for the system as a whole;
deciding when the minimum is not enough and starting to understand where the
dependencies are is a valuable part of successful implementations. This chapter will
walk you through some basic steps for installing the Oracle software on Linux, including
gathering the information needed for the system requirements as well as completing a
checklist for a Linux installation.
CRITICAL SKILL 2.1
Research and Plan the Installation
Oracle provides some critical information needed for installing the Oracle software,
both with the software itself and in the Oracle support site MetaLink notes. These
are important documents for successful installs that can help you with the planning
and research (for example, the Quick Installation Guide, Database Readme, and
Database Release Notes) and they contain hardware requirements, prerequisites, and
the setup to be done before and after installation. Also, information on installation
issues for Oracle software can be obtained from most recent release notes, which
contain possible workarounds or updated steps for the installs.
Gathering information on what is required to install the software and running it
effectively is a very important first step. Neglecting to do this by going straight to the
install can mean a lot more work, due to the potential need for uninstalling and
K
Chapter 2: Installing Oracle 37
reinstalling parts of the software. Important information you’ll want to watch for:
what is needed for the operating system to be configured for Oracle to run and the
fact that the initial settings are minimum values to be adjusted for larger systems.
With Linux and Unix environments, there are kernel parameters and settings that
need to be adjusted when the system starts up. The uses for these parameters range
from being able to allocate shared memory for Oracle to the number of processes
that are allowed to run on the server. Failure to set parameters and verify the needed
system requirements may allow the software to be installed, but it could prevent the
database from starting up because it is unable to get the system resources that are
needed. Each operating system has a particular set of configurations that it needs.
There are also patches that need to be in synch with the version of Oracle that is
to be installed. So, knowing the requirements that are needed and gathering the
needed patches and parameter values for application to the operating system are
critical to the install.
Define System Requirements
Hardware minimum requirements are related to processors, memory, and the disk
needed to install OS and Oracle software. For Oracle 11g, at least 1GB of RAM is
required and the Oracle software requires 3.5 to 5GB of disk space. The processors
themselves can be verified in pre-installation checks. Other hardware requirements,
such as network adapters or additional disks, all depend on the environment that is
being set up: for example, systems with Real Application Clusters or other additional
features.
In taking a closer look at how to prepare the operating system for an Oracle
install, let’s use Linux as an example from installation to configuration. You’ll start
the Linux installation by obtaining the media and starting up the server with the
Linux CDs inserted. Oracle Enterprise Linux has included all the needed packages
with the standard install. After a bootup of the server, the install screens will come
up and walk you through the simple process of setting up the operating system. The
following figures and comments explain the Linux install screens, as well as a basic
configuration to get started with Oracle on Linux.
Linux Installation
Most of the beginning screens simply step through and configure the language
and very basic server settings. The first install screen that might require additional
information is disk partition, which has two options: to leave as is or customize.
These include partitions for swap, root, tmp, or other file system mount points. The
file system mount points are the disk mounts for the directories where the software
will be installed and the databases will eventually be created. Bare minimum
partitions are swap and another device for the file mount points. Figure 2-1 shows
the install screen with the standard disk partitions, which is definitely enough to get
you started with the Oracle install on Linux.
38 Oracle Database 11g: A Beginner’s Guide
In Figure 2-1, you can see one logical device for the swap space, which is the
memory area on the disk allowing for programs to swap to virtual memory when
they need more memory than the physical memory contains. There is another
device for the boot area, the initial partition of the disk, and then the rest of the disk
under the root directory can be used for creating the directories for tmp, var, home,
oracle, and so on. Even if it isn’t defined on separate devices, the directories that
Linux needs, such as the tmp and var directories, will be created during install.
However, you will need to create a directory for the Oracle software before you
install it.
As shown in Figure 2-1, the devices are configured based on the disk available
to the server, and the values and sizes can be adjusted at this point. The swap space
can be adjusted later, but it is just as easy to configure here. Also, new mount points
or file systems can be created on the root directory, depending on your needs. After
setting the values and mount points, click on Next for the next configuration screen.
After the disk partitions install screen, you need to make a choice about boot
loaders. In Linux, a boot loader is the piece that loads the operating system into
memory (there are usually a couple of them in use). The boot loaders in this case
are LILO, Linux Loader, and Grand Unified Bootloader (GRUB). The GRUB boot
loader is the default for RedHat and Oracle Linux and can be selected if needed.
FIGURE 2-1. Linux install disk partitions
Chapter 2: Installing Oracle 39
The network configuration and devices are the next step. Plug in the IP address
and edit the network device on the installation screen, shown in Figure 2-2. This
can also be done after installation, using the network settings. This is where the host
name is defined as shown with the domain name; the miscellaneous settings are
dependent on the network settings and configurations. Figure 2-2 also shows where
to manually enter the server name with the domain and the miscellaneous settings
with the Gateway, Primary, and Secondary DNS.
The next couple of installation screens go through the root password and time
zone information; the proper time zone for your location just needs to be chosen
when going through the installation. Choose and remember the root password
carefully. At this point, there is no other way to login to the OS without the root
password. The default install will include packages for generic use, but it should
also be selected to support software development for this system. The option to
customize can be done here or the needed packages can also be installed afterward.
During the install, the packages can just be selected and then verified after the
install to ensure that they are completed. The required packages for Oracle
Enterprise Linux 5.0 (based on the current installation guide) are as follows:
■binutils-2.17.50.0.6-2.e15
■compat-libstdc++-33-3.2.3-61
FIGURE 2-2. Linux network
40 Oracle Database 11g: A Beginner’s Guide
■elfutils-libelf-0.125-3.e15
■elfutils-libelf-devel-0.125
■glibc-2.5-12
■glibc-common-2.5-12
■glibc-devel-2.5-12
■glibc-headers-2.5-12
■gcc-4.1.1-52
■gcc-c++-4.1.1-52
■libaio-0.3.106
■libaio-devel-0.3.106
■libgcc-4.1.1-52
■libstdc++-4.1.1
■libstdc++-devel-4.1.1-52.e15
■make-3.81-1.1
■sysstat-7.0.0
■unixODBC-2.2.11
■unixODBC-devel-2.2.11
After the installation, the packages should be verified by running the command
at the Linux prompt:
rpm –q unixODBC-devel-2.2.11
If the packages were not installed or if the installation failed, then the following
commands can be run to install packages as needed from the Linux source files.
Here is an example install command for this package:
rpm –ivh unixODBC-devel-2*rpm
These steps are seen in Figure 2-3; the rpm –q used to verify the packages shows
that the unixODBC package was not installed, so executing the command to install
the package completes this step. This step is repeated for each of the packages that
are required.
Chapter 2: Installing Oracle 41
Progress Check
1. What documents are provided for system requirements and installation
instructions by Oracle?
2. What is an important first step for installing Oracle software?
3. When installing Linux, which devices are needed when configuring the disk?
4. What is the command for verifying that a package is installed?
5. How much RAM is required for Oracle 11g?
FIGURE 2-3. Linux package install
Progress Check Answers
1. Quick Installation Guide, Database Readme, Database Release Notes
2. Gathering information on system requirements
3. You need two disk devices: swap and / (root) for the file mount points
4. rpm –q package_name
5. 1GB
CRITICAL SKILL 2.2
Set Up the Operating System
After Linux is installed and rebooted, information screens walk you through the
license information and an option to create a user. Taking this opportunity to create
another user besides root is a recommended practice. This is because the Oracle
user is needed along with groups for the Oracle software inventory and dba group.
Oracle software should be installed under the Oracle user created here, but
additional users can also be created now, as needed, for supporting different
pieces of the software or for different options that may be installed.
Figure 2-4 shows the creation of groups using command line in the terminal
window that is needed for Oracle installs. The groups are created using the groupadd
command and then associated with a user id using usermod with the listed parameters
and options. Also seen in this figure are the ways that the passwords for the user can
be easily changed with the passwd command. Adding another ID, such as osasm to
manage the Automatic Storage Management instance, can be accomplished with
the command useradd.
The users and groups can be reviewed and managed using the User Manager in
System Tools. This interface is also available for creating new users and for associating
the groups to the user instead of using the command line in the terminal window.
Any users created using this tool and even the users created by the command line,
as in Figure 2-4, also have a home directory that is created for them at the same
time they are created. The Add User and Add Group in the User Management Tool
do the same task as the useradd and groupadd commands; the tools in Linux make
it easy to perform these tasks in a way that is most familiar, with either the command
lines or user interfaces.
42 Oracle Database 11g: A Beginner’s Guide
FIGURE 2-4. Create groups and users
Chapter 2: Installing Oracle 43
The Oracle user needs certain other permissions along with resource permissions
for the software to run well. This is done by adding the Oracle user to the
/etc/security/limits.conf and session information to the /etc/pam.d/login file. The
file limits.conf, as listed next, shows that the Oracle user has been added with
resources to nproc and nofile parameters at the end of the file. These files can be
edited with Notepad or a similar editing program when you open the files by using
Explorer windows:
limits.conf file:
#<domain> <type> <item> <value>
#
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
# End of file
/etc/pam.d/login file:
#%PAM-1.0
auth [user_unknown=ignore success=ok ignore=ignore default=bad]
pam_security.so
auth include system-auth
account required pam_nologin.so
account include system-auth
password include system-auth
Ask the Expert
Q: Are there security concerns with adding users? Do you really need any
other users besides root and oracle?
A: Security for the operating system is important. Make sure that logins are
created for users to install the Oracle software and that additional users are
created for the purpose of allowing database administrators to log in and
maintain Oracle and the database. Anyone logging into the system should have
their own user ID; use of the Oracle login should be limited to installation and
patching of the software. Different users should have permissions to access only
areas that they should be allowed to access in order to perform tasks as needed.
File system permissions should only be granted to the groups and users that
need the access, and should be limited where possible. Research best practices
for security and don’t share logins or root passwords. Keep the environment
secure by limiting access to the Oracle software directories; make sure it is not
open to just any user who is able to log into the server.
44 Oracle Database 11g: A Beginner’s Guide
# pam_selinux.so close should be the first session rule
session required pam_selinux.so close
session include system-auth
session required pam_loginuid.so
session optional pam_console.so
# pam_selinux.so open should only be followed by sessions to be
executed in the user context
session required pam_selinux.so open
session optional pam_keyinit.so force revoke
session required /lib/security/pam-limits.so
session required pam_limits.so
Now the operating system is installed and users and groups are created and
configured with the needed permissions. You still need to verify (and possibly
update) some configurations needed by the kernel parameters so that they match at
least the minimum requirements for Oracle. The Oracle Quick Installation Guide is
the reference for these requirements. We hope that you are starting to see the
importance of these documents and why these pieces of information should be
gathered before you start the installs.
Project 2-1 Configure Kernel Parameters
The default install of the Linux operating system has values set for the kernel parameters,
but the Oracle requirements may need you to adjust these settings. This project will
walk you through step by step to change the kernel parameters and show the values
of these settings.
Step by Step
1. Get the minimum values from the installation guide. Here is a quick list:
Semmsl 250
Semmns 32000
Semopm 100
Semmni 128
Shmall 2097152
Shmmax The lesser of the two: either half the size of the
physical memory or 4GB
Shmmni 4096
file-max 512*PROCESSES
ip_local_port_range Min: 1024, max: 650000
rmem_default 4194304
rmem_max 4194304
wmem_default 262144
wmem_max 262144
2. Verify the parameters that are currently set. There may be values already set
above the minimum value, so these would not need to be changed. At the
command line type the following:
# /sbin/sysctl –a | grep <param-name>
3. Substitute the parameter name for param-name. For example, shm will
show the values for the semaphore parameters:
# /sbin/sysctl –a | grep shm
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmax = 2147483648
4. Edit the /etc/sysctl.conf to adjust the kernel parameter values. Use vi or
another text editor to add the line as listed under #ORACLE 11gR1 kernel
parameters to the end of the file. The other parameter that was changed was
kernel.shmmax. Here is the example /etc/sysctl.conf file; the areas that need
to change or be added are in bold:
sysctl.conf edits:
# Controls the maximum size of a message, in bytes
kernel.msgmnb = 65536
# Controls the default maximum size of a message queue
kernel.msgmax = 65536
# Controls for maximum shared segment size, in bytes
# CHANGED FOR ORACLE 11g # kernel.shmmax = 1073740324
kernel.shmmax = 2147483648
# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 2097152
#ORACLE 11gR1 Kernel Parameters – add the following lines
fs.file-max = 6553600
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144
Chapter 2: Installing Oracle 45
Configure Kernel Parameters
Project 2-1
(continued)
5. Reload the kernel parameters for the new values to take effect. This can be
done by restarting the server or by using the following command to reload:
# /sbin/syctl –p
The execution of this command and its output are shown in Figure 2-5.
Project Summary
In walking through this project, you now know how to see the kernel parameters,
make changes to the parameters, and verify those changes. In changing the kernel
parameters, you are making the necessary optimizations for the Oracle software to
run on the Linux platform.
46 Oracle Database 11g: A Beginner’s Guide
FIGURE 2-5. Verify the kernel parameters using sysctl –p
CRITICAL SKILL 2.3
Get Familiar with Linux
Having the user interface and the terminal window for access to the command line
means that there are a couple of ways to navigate through Linux and do what needs to
be done. With previous examples of using the command line, files were edited, users
were added, and parameters were set up. In managing some of the Oracle files and
directories, it is useful to know some of the basic commands or how to look up the
option for the commands. Changing directories, copying and moving files, editing, and
being able to look at the content of the file are all basic commands in Linux (and almost
the same as what is used in Unix, with a couple of possible differences in the parameter
options). The following are some useful Linux commands, with a brief definition:
■pwd This shows the current directory (print working directory).
■more filename This lists the file.
■ls This lists the files in the directory.
■echo $VAR This shows value of variables or echoes back the text.
■mv filename newfilename This renames a file.
■cp filename /newdirectory This copies a file.
■rm filename This removes (deletes) a file; wildcards can be used but are
not recommended for a root directory.
Manual pages are available to provide details for commands as well as available
options. There are also examples for how to use the commands in the details. This
information can be accessed by typing man and then the command. Here is an
example of the command, and Figure 2-6 show the results of this command and
what can be found in the man pages.
$man grep
Progress Check
1. How are kernel parameters verified on Linux?
2. What is the Linux command to view the manual pages for grep?
3. What is the command to create a Unix group?
Chapter 2: Installing Oracle 47
Progress Check Answers
1. sysctl –p
2. man grep
3. group add oinstall
48 Oracle Database 11g: A Beginner’s Guide
CRITICAL SKILL 2.4
Choose Components to Install
Various components of the Oracle software are automatically installed; some are
available on the companion CDs or as additional downloads. With Oracle 11g, the
following products are now installed by default with the database:
■Oracle Database Vault
■Oracle SQL Developer
■Oracle Configuration Manager
■Oracle Application Express
■Oracle Warehouse Builder
This section is not going to turn into a discussion of licensing for standard or
enterprise editions and of the different products that are available; however, there
FIGURE 2-6. OUTPUT of man commands
are products that provide benefits for different needs. Reviewing the products and
versions to match them with business needs before installing is helpful for performing
the install of these products at the same time as the initial install. It is possible to add
products by running through the install again and choosing the options that have
not yet been installed.
The Oracle Universal Installer allows for the Basic install, which is just going to
install the default options for Oracle. There is an Advanced option available for
deselecting or selecting other available options.
CRITICAL SKILL 2.5
Install the Oracle Software
To begin, run the Oracle Universal Installer as oracle, either from the DVDs or from
the downloaded software that has been unzipped on the file system:
[oracle@mmrac1 database]$ ./runInstaller
If the ORACLE_HOME environment variable is set before running the Installer,
information will already be populated with these details. Starting with Figure 2-7,
let’s walk through some of the screens of the Oracle Universal Installer. Each of the
illustrations following provides some detail about what options to select and
information to provide while installing the Oracle software.
Chapter 2: Installing Oracle 49
FIGURE 2-7. Basic vs. advanced install
50 Oracle Database 11g: A Beginner’s Guide
The Basic install will start up the database configuration assistant in order to
create a database instance after installing the software. Certain configurations are
not available, such as Automatic Storage Management (ASM), but the Basic install
will install the default components without walking through the options. The
Advanced install will allow you to pick and choose from the available options.
Advanced install was chosen in Figure 2-7, and the following illustrations for this
install will show the options available with the advanced option.
It’s a good idea to start to document the options that are chosen for the checklist
for the install, so that future installs of the Oracle software will have the same
components installed as needed. A checklist will be useful for creating consistent
environments and ensuring that the same components are being installed across test
to production systems.
The group that was defined on the operating system for the Oracle install is
needed for the permissions to the OraInventory directory. This is normally a group
such as oinstall, as seen in the following illustration. The OraInventory will contain
the information about what was installed and the versions used. OraInventory is
used for when the binaries need to be patched and when needing to add or remove
components.
Chapter 2: Installing Oracle 51
As seen in the next illustration, there are three types of installs: Enterprise,
Standard, or Custom. Choosing Enterprise or Standard will install the default
products for those versions. Obviously, the Enterprise and Standard versions
have different licensing issues, and limitations are set on the Standard edition
for CPUs and the options that can be added. There are several product options
that come with installing a default type of Standard or Enterprise. Some may
argue that you should install only what is needed, while others may suggest
that for a development environment, you should install everything to allow
developers to test and try out different options. For most systems, we recommend
that you use the Custom option to select only options that are needed. Standardize
the install so that it can repeated when you are ready for a production
environment.
On a server, there can be several home directories with different versions,
options, and patch sets installed. The following illustration shows an example of
what the base directory and home directory should be set to. If doing a new install
and for additional components or patching existing ones, Oracle home directory
should be used.
The following illustration shows the install screen running through checks for
kernel parameters, memory, and patch sets for the operating system, as well as
other requirements that are needed to install and run the Oracle software. If
the verification of the operating system was done as part of the planning, these
checks should all pass. If there are failures here, they need to be corrected before
continuing with the install.
52 Oracle Database 11g: A Beginner’s Guide
Chapter 2: Installing Oracle 53
As discussed when using the custom install, there are options for what to install,
as shown in the following illustration. Look through the available options and check
the components that are desired or uncheck those components not needed.
After the software is installed, the installer will open up the database configuration
assistant to create a database or to configure ASM. See the following illustration,
which shows these choices; in this case, only software has been selected. If you are
just installing the software, the database creation assistant can be used at another time
for creating databases and ASM instances. The steps for the database configuration
assistant are listed in the next section, “Database Configuration Assistant.”
During the last step before the actual install, you still have the opportunity to go
back and modify any directories or options for the install. The following illustration
shows the screen with this summary information, which has the directories where it
is to be installed, installation type, and its space requirements. At this step, take time
to review to make sure the information is correct.
54 Oracle Database 11g: A Beginner’s Guide
Chapter 2: Installing Oracle 55
The following illustration shows the files are being copied and installed. Note
the location of the log file. If any issues or errors come up, this would be the first
place to look for more information.
Your last steps are demonstrated in the following illustration. To complete the
installation, you will need to make changes regarding permissions and directories
that are owned by root. There are two scripts that need to be executed by the root
owner to make these changes. Have the operating system administrator log in and
run orainstRoot.sh and root.sh (as shown in the illustration).
This final illustration is always a good screen to see because it means that the
installation of the Oracle software has completed successfully. The installation went
through the listener configuration and the instance configuration, so the database is
now up and available for use. The listener configuration will be covered in more
detail in the next chapter, but for reference here, it is important to have the listener
service started to be able to connect to the database instance from another server.
This configuration is completed with a default listener name and port as well as
starting of the service which will allow the connections to the database.
56 Oracle Database 11g: A Beginner’s Guide
Database Configuration Assistant
As part of the installation, the database configuration assistant (dbca) can be configured
to start up after the initial software install. Otherwise, the assistant can be started any
time to create a new database instance. The dbca has standard templates to be used for
different types of databases; the templates have some parameter settings for memory and
others based on the type of instance. The passwords and directories for the tablespaces
can use the default values or be customized. As part of the planning process, these
configurations should be decided on depending on the directory structures and
templates to be used. A checklist needs to be kept to determine which templates,
parameter settings, and other choices are to be made within the assistant. The advantage
is that even after the database instance is created, parameters and file locations can
be adjusted. However, some adjustments are easier than others, and with Oracle 11g,
there are even more dynamic parameters that can be changed while the database is up
and available, instead of having to restart the database instance to make the change in
value for the parameter.
As shown in Figure 2-8, the database can be created or an existing database can
be dropped. There is also the option to create an Automatic Storage Management
(ASM) instance, which will be described in more detail in Chapter 8. Choosing this
option for ASM will only install an ASM instance, while running through dbca will
be required in order to install a database instance.
Chapter 2: Installing Oracle 57
58 Oracle Database 11g: A Beginner’s Guide
The next dbac screen has three default options to choose for the databases:
General Purpose, Custom Database, and Data Warehouse. For following along
with examples that will be used in the rest of the book, it would be useful to install
a General Purpose database with the sample schemas. The SH schema that is used
for examples is included with other sample schemas.
In stepping through the screens, the next step would be to name the database
instance. Passwords should be set for SYS and SYSTEM. All of the system passwords
can be the same or they can all be different. The main thing is that these passwords
should not be set with a default value as they have in the past. Anyone who knows
about installing Oracle and default passwords would be able to log into the
database if the default was not changed. For now, the choices to be made with the
database install can be kept to defaults or basic choices. As you learn more about
FIGURE 2-8. Option for install of database
Chapter 2: Installing Oracle 59
what the system is to be used for, you can make more adjustments with parameters
and configurations.
For a test database instance with schemas and to work through the examples,
check Sample Schemas, as shown here.
The initialization parameters can be configured as you create the database. The
following illustration shows step 10 of the database configuration assistant, where
these parameters can be configured. The memory can be customized or default
values can be taken, based on a percentage of overall available memory. The
character set should also be set up; it is more difficult to change, unlike the memory
settings, so verifying the character set for the database is important. The character
sets are important for databases with international characters and globalization of
the characters. When dealing with international characters, it is suggested to use a
60 Oracle Database 11g: A Beginner’s Guide
character set that allows for these values. Unicode character sets support characters
with different sizes and in multiple languages.
Creating the database, creating the scripts, and developing a template are the
final steps before the database is actually created. The following illustration shows
the option to save the database as a template and to generate database creation
scripts. Having scripts is a valuable tool for creating another similar database, or
when needing to create the database again without the assistant. The templates will
be added to the creation process of other databases in this Oracle home, again,
making it easier to create another database that is similar in nature.
Chapter 2: Installing Oracle 61
Verify the Installation
The operating system is now configured, the Oracle software is installed, and an
initial database has been created. Now the system should be reviewed to confirm
that everything is in the right place and that everything is working as expected.
Looking through the directories and log directories of the database to verify the
install is very useful. Check the install log file, which was the log file that was listed
during the install of the software. See Figure 2-9 for example output of the log file.
Review the alert logs of the database and check the logs for any issues with
the startup or parameters of the database. The default location for the log files are
$ORACLE_BASE\admin\SID. This information can also be retrieved by queries
to the database and selecting the value from v$parameter where the name =
'background_dump_dest'. Look at the file systems to make sure that the datafiles
62 Oracle Database 11g: A Beginner’s Guide
are going to the directory that is expected. Again, a quick query against the database,
such as select file_name from dba_data_files, will show all of the current data files.
Log into Oracle Enterprise Manager (OEM) and make sure the database can be
accessed. Several of these checks are listed in greater detail in later chapters of the
book and will show more details about Oracle Enterprise Manager as well as
information about tablespaces and data files. Check the memory usage on the
operating system level to ensure that the right amount of memory was configured
for Oracle and that there is space available for user processes too. Use commands
like top to see what the top processes are and to view memory usage for the processes.
The file systems should also have enough space; you should verify this after
installing Oracle and creating the database.
After these initial checks the system should be ready to use, allowing you to install
the front-end application, add users, and set up monitoring and backups. Setting up
backups and monitoring help also verify that the system is ready to go and might be
a good step to complete before allowing other users to access the database.
FIGURE 2-9. Log file details
Tie It All Together
When doing an Oracle install, the upfront planning and research is important.
Gathering business requirements in order to match up database options and
versions is also a critical first step to doing the installations. A basic understanding
of an operating system is needed to work through the install and to ensure that the
user permissions, required system, and hardware components are available. Oracle
Enterprise Linux has a standard install, with packages that match up to the database
requirements. Gathering documents that provide the latest information about issues
and needed prerequisites will make the overall installation go smoothly plus give
the added bonus of starting an install process that would be repeatable in the
production environment.
þChapter 2 Mastery Check
1. How much disk space is needed for installing Oracle software on Linux?
2. What users and groups are used for installing the Oracle software?
3. True or false: Installing Oracle software will automatically install an Oracle
database.
4. What are the prerequisites for installing Oracle software?
5. What are the types of installation options for the Oracle software?
6. What is the Oracle home directory? Can there be more than one?
7. Besides the database, what are some of the other products that are installed
by default?
8. What is the tool for creating a database, after the Oracle software install?
9. What is the default password for SYS and SYSTEM users in the database?
10. Which scripts need to be run by root (system administrator) after the install
of the software?
Chapter 2: Installing Oracle 63
This page intentionally left blank
Chapter
3
Connecting to Oracle
CRITICAL SKILLS
3.1 Use Oracle Net Services
3.2 Learn the Difference Between
Dedicated and Shared Server
Architectures
3.3 Define Connections
3.4 Use the Oracle Net Listener
3.5 Learn Naming Methods
3.6 Use Oracle Configuration Files
3.7 Use Administration Tools
3.8 Use Profiles
3.9 Network in a Multi-tiered
Environment
3.10 Install the Oracle 11gClient
Software
66 Oracle Database 11g: A Beginner’s Guide
his chapter introduces Oracle Net Services, which allows database
applications running on remote systems to access an Oracle
database. It creates and maintains the network connection, and
also exchanges data between the application and the database.
Oracle networking plays a critical role in performance and
availability. Each new version of Oracle is designed to support more data and users
than the previous release. This increased amount of database activity and network
traffic needs to be addressed from an availability and performance perspective
and should be managed by the DBA. A DBA also has to be able to determine if a
performance issue is due to networking, and if so, then they must be able to resolve
any network performance issues from a database configuration perspective.
Throughout this chapter we will refer to DBAs, which in this context means
anyone that is performing networking administration operations to make the
database connectivity work. These days, more developers are managing their own
development databases and performing operations traditionally reserved for DBAs.
NOTE
Oracle Net Services is a large topic. The emphasis
in this chapter is to introduce DBAs to Oracle
Net Services terminology and concepts,
feature/functionality, and key components and
tools. Once a beginning DBA reads this section,
they should be able to understand the Oracle
networking references and be capable of performing
simple operations using the Oracle GUI tools and
wizards for Oracle Net Services.
CRITICAL SKILL 3.1
Use Oracle Net Services
Oracle Net Services is the software component that allows enterprise connectivity
across heterogeneous environments. Oracle Net is the part of Oracle Net Services
that manages data communication between a remote application and the Oracle
database; it runs on top of a network protocol like TCP/IP. The software used by
Oracle Net software resides on the remote system and the Oracle database platform.
A listener process must be running on the database server to receive the network
request. (A listener is a program that listens on a port for incoming network requests
and then hands the request to another program for processing.) The listener then
determines the appropriate type of process to handle the request.
The network protocol sends a request to the Oracle Protocol layer, which sends
the information to the Oracle Net Foundation layer, which in turn communicates
with the database server. The Oracle network communication stack, shown in
Figure 3-1, is similar on both the client and server sides.
T
Chapter 3: Connecting to Oracle 67
Oracle Net (Oracle Net Foundation Layer and Oracle Protocol Support) fits into
the session layer of the Open Systems Interconnect (OSI) model (visit www.ietf.org
for more information about the OSI model).
Network Protocols
Oracle supports a number of industry standard protocols. These protocols transport
the data between the remote platform and the database server platform. The protocols
also display how users need to work with data differently than they did a few years
ago. Oracle-supporting protocols like SDP, HTTP, FTP, and WebDAV show that
Oracle Database 11genhances network performance and offers increased flexibility
for users working with data. In this section, the term application server will be used
to address both web and application server services available in the middle tier.
Table 3-1 lists the supported industry-standard protocols.
Optimize Network Bandwidth
Multi-tiered architectures need to maximize the bandwidth between the application
server and the database server platforms. Oracle Net Services supports the high-speed
networks of InfiniBand, a channel-based, high-speed interconnect technology
designed to optimize performance between different platforms. It’s used for server
clustering and for network interfaces to storage area networks (SANs) and local area
networks (LANs). Vendors such as Hewlett-Packard, IBM, Sun Microsystems, Dell,
and Microsoft support InfiniBand technology; the SDP protocol, an industry-standard
wire protocol, is also used with the InfiniBand network. Highly active multi-tiered
environments should consider using high-speed interconnects between the application
server and the database server.
Server-based
ServerClient
FIGURE 3-1. The Oracle network communication stack
68 Oracle Database 11g: A Beginner’s Guide
Connections
Aconnection is an Oracle communication path between a user process and the
Oracle database server. If this communication path is dropped, a user must establish
a new session. The current transaction is rolled back if the connection for its session
is lost. A session is a specific connection for a user between the user process and the
Oracle database server.
Protocol Description
TCP/IP The Transmission Control Protocol/Internet Protocol (TCP/IP) is
the standard protocol used in client server environments.
TCP/IP
with SSL
TCP/IP with Secure Sockets Layer (SSL) provides authentication
(certificates and private keys) encryption. The Oracle Advanced
Security option is required for this protocol.
SDP The Sockets Directory Protocol (SDP) is an industry-standard
high-speed protocol. SDP is used with an InfiniBand network. The
InfiniBand network takes the messaging burden off the CPU and
onto the network hardware. This network reduces the overhead
of TCP/IP, providing increased bandwidth.
Named pipes This supports inter-process communication between remote
platforms and the database server platform using pipes. A pipe
is opened on one end and information is sent down the pipe to
allow I/O between the platforms.
HTTP The Hypertext Transport Protocol (HTTP) is an industry- standard
protocol that is primarily used between clients and application
servers. Oracle can also start up an HTTP listener to handle a
request over HTTP directly.
FTP File Transfer Protocol (FTP) is a standard method for transferring files
across the Internet. It makes it easy to transfer files back and forth
between different platforms. A server that can receive an FTP
connection is referred to as an FTP server or FTP site. FTP addresses
looks similar to HTTP; ftp://ftp.beginner.com is an example of an FTP
server address.
WebDAV The Web-based Distributed Authoring and Versioning (WebDAV)
protocol supports collaborative authoring over the Internet. The
benefits of WebDAV include locking mechanisms, interoperable
publishing with HTTP and XML support, writing over the Web
with embedded devices, versioning, and Access Control Lists.
TABLE 3-1. Standard Industry Network Protocols
If a connection cannot be made, it is important to be able to troubleshoot these
issues and problems. In the Automatic Diagnostic Repository (ADR) for Oracle 11g,
the network information is also captured. The repository holds trace files and other
errors collected into a standard place. This troubleshooting facility for diagnosing
network problems is the same as the one you will use to analyze and diagnose
database problems. With tools like this it will be easier to find connection issues
or avoid problems.
Maintain Connections
The Oracle Net Foundation Layer establishes and maintains connections with the
database server. Transparent Network Substrate (TNS) is the common interface
between all the industry-standard protocols. Oracle Protocol Support maps the
industry-standard protocols (TCP/IP, TCP/IP with SSL, SDP and Named Pipes) used
in the connection.
Figure 3-2 shows us how Oracle Net works. Oracle Net software will reside on
the database server platform and the platform that is running the Oracle applications.
With an application server, HTTP runs on top of a network protocol between the
browser platform and the application server platform. Oracle Net then runs on top
of a network protocol between the application server and the database server. For
a client/server configuration, Oracle Net will reside on the client platform and the
database server platform, and will run on top of a network protocol between the
client and the database server platforms.
Chapter 3: Connecting to Oracle 69
FIGURE 3-2. An Oracle network overview
If Java programs are running, a Java Database Connectivity (JDBC) OCI, or Thin
driver, will communicate with Oracle Net to process the database request. A JDBC
OCI driver requires Oracle Net on the remote platform and the database server. A
Thin driver doesn’t require a full Oracle Client to be installed and uses network
calls to connect to a database. So, a JDBC Thin driver written entirely in Java uses
JavaNet to communicate, and requires Oracle Net only on the server platform.
Define a Location
Locations need to be defined so a remote application can find the correct Oracle
database server on the network. A service name, such as customer.us.beginner.com,
is used to define the unique location of each database server. In the preceding
example, customer is the database name and us.beginner.com is the domain name.
On the plus side, if the physical location of the database is changed, the service
name can stay the same while the definition or settings of the name can change
underneath.
A database can support multiple services. The service name, defined with the
initialization parameter SERVICE_NAMES, makes the physical location of the database
transparent and will default to the global database name (the name of your database),
which uses the format database_name.database_domain, as in customer.us.beginner.com.
The database domain name is the domain where the database is located, and is
made up of the initialization parameters DB_NAME and DB_DOMAIN. The combination
of the DB_NAME and DB_DOMAIN (customer.us.beginner.com) name distinguishes
one database from another, as shown in the following examples:
DB_NAME=customer
DB_DOMAIN=us.beginner.com
70 Oracle Database 11g: A Beginner’s Guide
Ask the Expert
Q: Why is it important for DBAs to understand the networking setup and
configuration for Oracle Database 11g?
A: Often as systems interact with the database, networking bottlenecks
surface that require attention from DBA personnel in addition to those who
manage the applications. Familiarity if not fluency with setting up Oracle Net
services and its configuration files arms the DBA with the skills required to
intervene.
Chapter 3: Connecting to Oracle 71
CRITICAL SKILL 3.2
Learn the Difference Between Dedicated and
Shared Server Architectures
An Oracle database server can be configured to run either a dedicated or shared
server architecture. This decision determines how the listener processes requests
and how server processes work for an Oracle instance. Server processes are the
interface between the Oracle database server and user processes, the latter of which
must go through a server process that handles the database communication between
the user process and the database. Server processes can
■Process database requests, access data, and return the results.
■Perform data translations and conversions between the application and
database server environments.
■Protect the database server from illegal operations by the user processes. A
server process accesses Oracle database and memory structures on behalf
of the user process. This separates user process activity from direct access to
Oracle’s internal memory.
Dedicated Server
A dedicated server environment uses a “dedicated” server process for each user
process. The benefit of this is that each user process has a dedicated server process
to handle all of its database requests. If there are a hundred separate sessions, there
will be a hundred dedicated server processes running on the same platform as the
database server.
The problem is that each dedicated server process is often idle a large
percentage of the time. This takes up a lot of operating system resources for server
processes that are sitting idle and creates issues when large numbers of users are
accessing a system. Oracle databases that allow access from the Internet can have
tremendous spikes of activity that generate a large number of dedicated server
processes. The dedicated server architecture also does not support FTP, HTTP, or
WebDAV clients.
Figure 3-3 illustrates the way that dedicated server processes run on the database
server platform. A dedicated server process will be run for each user session.
72 Oracle Database 11g: A Beginner’s Guide
Shared Server
A shared server architecture offers increased scalability for a large number of users.
This is possible because a single server process can be “shared” among a number of
user processes, allowing a single server process to be able to support a large number
of user processes. If there are 100 separate sessions, there may in turn be 20 shared
server processes supporting them. Having a small pool of server processes that can
support a large number of sessions increases scalability. This shared server architecture
is much more scalable than a dedicated architecture as the number of users for a
system increase. The shared server process can also handle large spikes of user
activity much better than a dedicated server process configuration.
When a user request arrives, the listener will route the request to a dispatcher,
which then processes and routes the request to a common queue. From a pool of
shared server processes, an idle shared server will see if there is work in the common
queue. Requests are processed on a first-in first-out basis. The shared server then
processes the request and puts the results in a response queue (each dispatcher has
one) that a dispatcher can return to the user process. Afterward, the dispatcher
returns the results from its response queue to the appropriate user process.
A dispatcher supports multiple connections with virtual circuits, which are
sections of shared memory that contain the information necessary for client
communication. The dispatcher puts the virtual circuit on the common (request)
queue accessed by the server process.
There are some administration operations that cannot be performed through a
dispatcher, however. To perform these restricted administration operations in a shared
FIGURE 3-3. The dedicated server architecture
server environment, the DBA needs to connect with a dedicated server process instead
of a dispatcher process. The restricted operation needs a connect descriptor with a
setting of SERVER=DEDICATED, defined in the CONNECT_DATA section of the
tnsnames.ora file: (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME =
MMDEV1)). Restricted operations include the following:
■Starting up an instance
■Shutting down an instance
■Media recovery
As Figure 3-4 shows, a shared server process can support multiple user sessions.
Table 3-2 illustrates the initialization parameters that are used to configure the
shared server architecture. Possible values for these parameters are dependent on
the level of user activity and the types of operations the server processes are
executing.
Oracle recommends starting with one shared server process for every ten
connections. It then automatically increases the number of shared servers based
upon the workload up to the MAX_SHARED_SERVERS that are defined. The PMON
process is responsible for adding and removing shared servers; the number of shared
servers will never drop below the value contained in the SHARED_SERVERS parameter.
You should also note that the parameters that control the minimum and maximum
number of these shared servers can be set dynamically; therefore you can always
ensure that you can react quickly to shared server issues.
Chapter 3: Connecting to Oracle 73
FIGURE 3-4. The shared server architecture
74 Oracle Database 11g: A Beginner’s Guide
Set Dispatchers
To set the number of dispatchers, determine the maximum number of concurrent
sessions and divide this by the number of connections per dispatcher. Then,
dependent upon the level of activity, the number of dispatchers may need to be
Oracle Initialization Parameter Definition
DISPATCHERS This defines the number of dispatcher
processes to start in a shared server
architecture. The number of dispatchers can
be dynamically added or reduced. There
must be at least one dispatcher for each
network protocol. Additional dispatchers
can be defined based upon the workload.
MAX_DISPATCHERS This defines the maximum number of
dispatchers. This is not a fixed limit. In this
release, this value can be dynamically
exceeded at runtime.
SHARED_SERVERS This defines the number of shared servers to
invoke on database startup in a shared server
architecture.
MAX_SHARED_SERVERS This defines the maximum number of shared
server processes.
SESSIONS This defines the maximum number of
sessions that can be active in a system.
SHARED_SERVER__SESSIONS This defines the maximum number of shared
server sessions that can be started in a
system. It also allows dedicated sessions to
be reserved in a shared server environment.
Sessions started above this limit will use
dedicated server processes.
CIRCUITS This defines the maximum number of virtual
circuits.
LARGE_POOL_SIZE This defines the size of the large pool area in
the SGA. If a large pool exists, the session
information will be stored in the large pool,
not the shared pool area.
TABLE 3-2. Initialization Parameters Used by Shared Servers
Chapter 3: Connecting to Oracle 75
increased or decreased. A single dispatcher can handle a large number of shared
server processes, but the number of dispatchers per shared server is dependent upon
the activity of the shared server processes.
One of the following attributes—PROTOCOL, ADDRESS, or DESCRIPTION—can
be set with dispatchers. PROTOCOL defines the network protocol to use, ADDRESS
defines the network protocol address on which the dispatchers listen, and
DESCRIPTION is the network description. Default values are used if the attributes
are not defined, and additional network options can be defined if the ADDRESS or
DESCRIPTION attribute is set.
Additional attributes that can be set with ADDRESS or DESCRIPTION include
the following:
■SESSIONS This defines the maximum number of network sessions per
dispatcher.
■CONNECTIONS This defines the maximum number of network
connections per dispatcher.
■TICKS This defines the length of a network tick (seconds). A tick defines
the length of time for a message to get from the client to the database server
or from the database server to the client.
■POOL This defines the timeout in ticks for incoming (IN=15) and outgoing
(OUT=20) connections, and whether connection pooling is enabled. The
number of ticks multiplied by the POOL value determines the total
connection pool timeout.
■MULTIPLEX This defines if multiplexing with the Connection Manager is
set for incoming and outgoing connections. Multiplexing allows multiple
sessions to transport over a single network connection. This is used to
increase the network capacity for a large number of sessions.
■LISTENER This defines the network name of an address for the listener.
■SERVICE This defines the server names that dispatchers determine with
the listeners.
■INDEX This defines which dispatcher should be modified.
In your init.ora file, you will define the dispatchers. The following are examples
of different types of entries that will typically be created to support shared servers.
Define the number of dispatchers to start:
DISPATCHERS='(PROTOCOL=TCP)(DISPATCHERS=5)'
Define a dispatcher to start on a specific port:
DISPATCHERS='(ADDRESS=(PROTOCOL=TCP)(DISPATCHERS=5))'
76 Oracle Database 11g: A Beginner’s Guide
Define a dispatcher with more options:
DISPATCHERS="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=eclipse)(PORT=1521)(QUEUESIZE=20)))
(DISPATCHERS=2)
(SERVICE = customer.us.beginner.com)
(SESSIONS=2000)
(CONNECTIONS = 2000)
(MULTIPLEX = ON)
(POOL = ON)
(TICK = 5)"
As you can see, there are numerous options that may be used, depending on the
configuration methods that you select when configuring the dispatcher.
Views to Monitor the Shared Server
The following views can be used to monitor the load on the dispatchers:
■V$DISPATCHER
■V$DISPATCHER_RATE
■V$QUEUE
■V$DISPATCHER_CONFIG
The following views can be used to monitor the load on the shared servers:
■V$SHARED_SERVER
■V$SHARED_SERVER_MONITOR
■V$QUEUE
The V$CIRCUIT view can be used to monitor virtual circuits.
The following views can be used to monitor the SGA memory associated with
the shared server environment:
■V$SGA
■V$SGASTAT
■V$SHARED_POOL_RESERVED
These views provide you with the ability to monitor your database and the
activity related to your shared servers and database. We encourage you to take a
look at the data in these tables before and after you implement shared servers, to
see how they change your database and how it functions.
Chapter 3: Connecting to Oracle 77
CRITICAL SKILL 3.3
Define Connections
This section will discuss the core components required to handle Oracle connections.
A Connect Descriptor
Aconnect descriptor is used to define the service name and the location of the
database. The address component of a connect descriptor defines the protocol, host
name, and port number. Though port numbers can be between 1 to 65535, those
from 1 to 1024 are usually reserved for special processes. The default port for
Oracle Listener is 1521. The connect data component of the description describes
the service to which you want to connect. If you do not include the instance_name
in your descriptor, it will default to the Oracle SID.
A sample connect descriptor for customer.us.beginner.com looks like the
following:
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=customer.us.beginner.com)))
A specific connect descriptor can be defined for a specific service handler. For
example, in a shared server architecture, a dedicated service handler can be chosen,
which can be set to dedicated (SERVER=dedicated) or shared (SERVER=shared). If
no dispatchers are available, a dedicated server will be used and the default service
handler is shared:
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=customer.us.beginner.com)
(SERVER=dedicated)))
Define a Connect Descriptor
When establishing a connection, you have two choices: a detailed connect descriptor
can be defined or a manual name that maps to a connect descriptor can be used.
The following example shows you how to define a manual connect descriptor or
name a connection descriptor name:
-- Manual definition of a connection descriptor
CONNECT
username/password@(DESCRIPTION = (ADDRESS=(PROTOCOL=tcp) (HOST=eclipse)
(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=customer.us.beginner.com)))
-- Connect using a pre-defined descriptor
CONNECT username/password@cust
The Oracle Connection Manager
The Oracle Connection Manager processes and filters requests to the database
server. It can also optimize network performance for a large number of sessions.
Figure 3-5 illustrates the various layers between the users and the database that
need to be controlled by the manager.
The Oracle Connection Manager Control utility allows administration of the
Oracle Connection Manager. The syntax is
cmctl {command} [parameter1 ... parameterN] {-c instance_name}
{-p password}
Connection Manager commands can be executed from within the utility, as
shown here:
cmctl
CMCTL> startup –c cman0
The Oracle Connection Manager can offload network I/O from the application
servers.
78 Oracle Database 11g: A Beginner’s Guide
FIGURE 3-5. The Oracle Connection Manager architecture
We will now move on and look at the Oracle Connection Manager options to
include session multiplexing and firewall access control.
Session Multiplexing
The Oracle Connection Manager allows a number of different client network sessions
to be shared (multiplexed) through a single network connection to the database
server. Multiplexing sessions increases the number of network sessions that can be
supported. Similarly, multiple Connection Managers can be used to handle hundreds
or thousands of concurrent users; they run on the application server platform in
order to multiplex sessions to the Oracle database server.
Firewall Access Control
The Oracle Connection Manager can define filtering rules to grant or deny access to
the database server; this is done via the Oracle Net Firewall Proxy. The Oracle Net
Firewall Proxy is software that provides Oracle Connection Manager features through
different firewall vendors.
Progress Check
1. The protocol ____________________ supports collaborative authoring over
the Internet.
2. True or False: The SDP protocol adds advanced network security features.
3. True or False: A virtual circuit is a section of shared memory that contains
information for client communication.
4. True or False: Port numbers from 1 to 1024 are usually reserved for SSL.
5. The ___________ server architecture does not support FTP, HTTP, or
WebDAV clients.
6. True or False: The Oracle Connection Manager supports multiplexing sessions.
Chapter 3: Connecting to Oracle 79
Progress Check Answers
1. The protocol WebDAV supports collaborative authoring over the internet.
2. False. The SDP protocol is used with high-speed networks.
3. True. A virtual circuit is a section of shared memory that contains information for client
communication.
4. False. Ports 1 to 1024 are used for special processes. They are not reserved for SSL.
5. The dedicated server architecture does not support FTP, HTTP, or WebDAV clients.
6. True. Yes, this is one of the advantages of using the Oracle Connection Manager.
80 Oracle Database 11g: A Beginner’s Guide
CRITICAL SKILL 3.4
Use the Oracle Net Listener
The Oracle Net Listener (listener) listens on a network port (listening endpoints) for
incoming database requests. A listening endpoint defines the protocol addresses the
listener is defined to listen on. Listening endpoints include HTTP, FTP, WebDAV,
and Oracle XML. Look at the ORACLE XML DB Developer’s Guide for more detail
on registering FTP, HTTP, and WebDAV listening points.
The process is fairly simple. The listener receives a request and hands the
request to a service handler, which is a server process that runs on the same
platform as the Oracle database server. The service handler can be a dedicated
server or a dispatcher, the latter of which works with shared servers.
The PMON background process registers the service information to the listener.
During registration, PMON gives the listener information on the database services and
instance information. PMON then tries to register with the listener once the listener has
been started. Dynamic registration is supported with the alter system register command.
If PMON has not registered with the listener, a TNS listener error will occur. View the
Oracle Database 11gError Messages reference manual for more details.
The listener will receive the database request and spawn a dedicated server
process if the environment is configured for the dedicated server architecture. The
listener will hand the request over to a dispatcher if running a shared server architecture.
A client application can bypass the listener if it is running on the same platform as
the database server. Once the listener hands off the request it will resume listening
for additional network requests.
A default listener (named listener) is configured at installation with the Oracle
Net Configuration Assistant making it easy to start up the default listener when a
system is first built. An additional ICP protocol address is defined for external routes
(EXTPROC) during installation.
The following is a sample listener.ora file:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = eclipse)(PORT = 1521))
)
)
)
Chapter 3: Connecting to Oracle 81
Table 3-3 illustrates the contents of the listener.ora file.
In the following, host defines the server name, PORT defines the port number,
SERVER defines the host server name, PIPE defines the pipe name, and KEY defines
a unique name for the service. It is recommended that you use the Oracle SID value
for the key.
Table 3-4 defines the components of the protocol definition.
Parameter Description
DESCRIPTION This defines a connect descriptor for a net service name.
DESCRIPTION_LIST This defines a list of connect descriptors.
LISTENER This defines the listener alias.
ADDRESS This defines the listener protocol address.
ADDRESS_LIST This defines a list of protocol addresses that contain
common behavior.
TABLE 3-3. Listener.ora File Formats
Protocol Example
TCP (PROTOCOL=tcp)(host=eclipse)(PORT=1521)
TCP/IP with SSL (PROTOCOL=tcps)(host=eclipse)(PORT=2484)
IPC (PROTOCOL=ipc)(KEY=cust)
Named pipes (PROTOCOL=nmp)(SERVER=eclipse)(PIPE=pipe01)
SDP (PROTOCOL=sdp)(host=eclipse)(PORT=1521)
TABLE 3-4. Protocol Examples in the listener.ora File
After installation, the Oracle Net Manager can be used to modify the listener
configuration. Some of the values that can be configured for the listener include the
following:
■If the default port of 1521 is not specified, the LOCAL_LISTENER initialization
parameter needs to be defined through a naming method. The LOCAL_LISTENER
parameter is dynamic and can be set with the alter system command.
■Be careful, because the LISTENER parameter overrides the
LOCAL_LISTENER parameter. A host system can have multiple IP
addresses, and a listener can be configured to listen on them.
■The I/O buffer size for send and receive operations can be defined.
■Heterogeneous services can be set to support additional services such as
external routines.
■The QUEUESIZE parameter can be defined for environments that may
have a large number of concurrent connection requests for a listener on
a listening endpoint.
Password Authentication
In Oracle 11g, the listener administration is secure through the operating system
authentication. So the administration is then restricted to the account that started
the listener. Another option is to set a password for the listener. Also for remote
administration of the listener a password is required. The change_password command
can be used to change a password or set a new password. If a password is not set,
someone can accidentally impact the availability of the database—for example,
accidentally shutting down the listener. If you don’t have a listener, new sessions cannot
be established. It is important that a DBA protect access to listener management.
Using the listener utility, lsnrctl, listener configurations can be managed. The
following example sets the listener password:
> lsnrctl
lsnrctl> change_password
Old password: <enter>
New password: newpassword
Reenter new password: newpassword
lsnrctl> save_config
Multiple Listeners
Multiple listeners can be defined for a service and can offer a number of advantages
for more complex environments. These advantages include the following:
82 Oracle Database 11g: A Beginner’s Guide
■Failover
■Transparent application failover
■Load balancing
The following is a sample connect descriptor for a listener:
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=customer.us.beginner.com)))
Connection Pooling
A shared server architecture is used to improve user scalability. So, it is assumed
that if this architecture is being used, there is a potential for a large number of users.
As mentioned previously, at any point in time there can be a large percentage of
idle processes. Connection pooling allows the database server to time out sessions
that are idle and then use the connection to support an active session. These
sessions remain open but in an idle state. When they become active again, a
connection is reestablished.
CRITICAL SKILL 3.5
Learn Naming Methods
Anaming method defines the type of repository used to configure Oracle network
information. This repository is accessed to define where the Oracle database server
is located.
Oracle supports various types of naming methods, such as:
■Directory naming (centralized configuration)
■Local naming (client configuration)
■External naming (external configuration)
■Easy naming (manual configuration)
Directory Naming Method
For centralized network management, Oracle Net Services uses a Lightweight Directory
Access Protocol (LDAP) directory server as the repository. LDAP uses hierarchical
structures (directories) that contain different components of a communication path.
The LDAP directory stores all database network information, policies, security, and
Chapter 3: Connecting to Oracle 83
authentication information in this centralized repository. Remote applications will go to
the centralized repository to find network configuration information. The results are then
returned containing the communication path to the Oracle database server.
Different vendors provide their own LDAP directory server. The Oracle LDAP
directory, for instance, is named the Oracle Internet Directory (OID). (The Microsoft
version of this is named Microsoft Active Directory.)
You should note that there are some restrictions when using the Microsoft Active
Directory. The Oracle Net Configuration Assistant may be used with the Microsoft
Active Directory; however, the Oracle Internet Directory Configuration tool cannot
be used with the Microsoft Active Directory.
Security has been a focus of Oracle 11g, and with directories such as the LDAP
directory, there are areas that need to be more secure than others. The method of
authentication of lookups through parameters in the directory users can be validated
and authorized first, which would protect sensitive Net Services information. A
simple authenticated user is defined by just a login and a password; in comparison,
strong authentication includes encryption. Modification of the directory via Oracle
Net Manager or lookups in the directory can be configured securely through simple
or strong authentication.
Storing network information in a centralized location is much more efficient
from an administration perspective. Make a change in one place, and it is reflected
everywhere. It’s also better from a security perspective because the database
location is stored in a centralized repository instead of a file on a local machine.
Directory Information Trees
LDAP directory servers store information in a hierarchical tree structure called a
Directory Information Tree (DIT). DITs are typically organized in a Domain Name
Space (DNS) structure (usually along corporate or geographical lines), and are
defined by the Oracle Internet Directory Configuration Assistant. Every node in
the tree is referred to as an entry, each of which can be modified with the Oracle
Enterprise Manager or the Oracle Net Manager. The following example shows how
a connect descriptor maps to a DIT:
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=customer.us.beginner.com)))
Figure 3-6 illustrates how the directories are organized and may be navigated
when using the Oracle Internet Directory Configuration Assistant. It is important to
know your directory trees to ensure that you correctly move through your hierarchy.
84 Oracle Database 11g: A Beginner’s Guide
Distinguished Names
A distinguished name (DN) defines where an entry resides in the directory path,
and begins at the lowest entry. The DN for the customer distinguished name is
dn:cn=customer, cn=OracleContext, dc=us, dc=beginner, and dc=com. Relative
distinguished names (RDNs), on the other hand, define the sequences within the
path. An RDN contains an attribute that defines the RDN. An important RDN is
the Oracle Context, which defines the default location for connect identifiers. An
identity management realm, meanwhile, defines a set of identities that share the
same administration policies.
How to Find the Directory Naming Information
With this naming method, a client needs to find the centralized information that is
stored in the LDAP repository to be able to connect to the database server. There are
two ways to find the centralized directory naming information stored on a separate
system:
■The static method, which works via a local ldap.ora file.
■The dynamic method, which works via a domain name server (DNS).
An ldap.ora file is a statically configured file containing the location of the LDAP
server. DNS uses name servers to map names and IP addresses for systems. If the
latter changes, the next time the name is looked for on the domain name server, it
will map to the new IP address.
Chapter 3: Connecting to Oracle 85
FIGURE 3-6. A directory information tree
86 Oracle Database 11g: A Beginner’s Guide
Net Service Alias Entries
Anet service alias entry is another name for a net service name. A net service alias
references the directory location and the name cust in the directory information tree
is a net service alias. Aliases simplify management by using a short alias instead of
having to specify the full path.
The Local Naming Method
The local naming method uses a local configuration file called tnsnames.ora. The
tnsnames.ora file stores net service names and connect descriptors and it resides
on the platform running the database application. It also contains the information
required to find and connect to the Oracle database server. The following definition
defines the address (protocol, host, port number) along with the dedicated server
environment and which service to connect to:
CUST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = eclipse)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CUST)
)
)
This is a simple file to configure. The problem is that if you have 1000 users,
you need to make sure the tnsnames.ora file has been updated for all of the client
machines. From a security perspective, it is not ideal to allow clients access to a
server location and the connection information.
Ask the Expert:
Q: Should I be prepared to implement non-TCP/IP-based network
transports with my Database 11ginstallations?
A: Most sites leverage the TCP/IP protocol for network services from Oracle
and other vendors that play a role in getting information from the client to the
database back end. In most of your travels around the Oracle technology, you
too will use the TCP/IP standard almost all the time. Familiarity with other
protocols is wise if and when called upon to administer non-TCP/IP networks.
Chapter 3: Connecting to Oracle 87
The Easy Naming Method
The easy naming method explicitly defines the connect information. The connect
information contains the host, port, service name, and instance name. This allows
someone to connect in a specific way without going through the configuration
effort. The format is
CONNECT username/password@eclipse:1521/customer.us.beginner.com/cust
An advantage of the easy naming method is that it is easy to configure. The user
need only provide minimal information to get a connection. As a result, no other
naming methods need to be configured. This method cannot be used if more
advanced features are required.
The External Naming Method
The external naming method uses net service names that are defined in a non-Oracle
environment. This naming method works well for administrators that want to use
their native naming service and allows them to use native tools and utilities with
which they have experience. The disadvantage of this approach is that Oracle Net
tools cannot be used for these native naming methods. Supported non-Oracle
services include the Network Information Service (NIS) or Cell Directory Services
(CDS). CDS is part of a Distributed Computing Environment (DCE). DCE is an
integrated distributed environment designed to resolve interoperability issues with
heterogeneous environments. DCE is maintained by the Open Systems Foundation
(OSF).
Which Naming Method to Use
The local naming method (tnsnames.ora) has traditionally been the most popular
method. However, there are a number of administration and security issues in
stored local configuration with a tnsnames.ora file. The directory (centralized)
naming method is more scalable and has less administration than the local naming
method. For large systems, the directory method is becoming more popular.
CRITICAL SKILL 3.6
Use Oracle Configuration Files
Remote applications will look for Oracle Net configuration files to determine
how to access the Oracle database server. Configuration files can be found in the
ORACLE_HOME/network/admin directory location. Table 3-5 defines the primary
configuration files.
88 Oracle Database 11g: A Beginner’s Guide
DBAs can use the management tools to modify Oracle Net Services configurations.
However, since the configuration files have a simple syntax, it is easy to modify the
configuration files directly. The following is an example of the listener.ora file:
# LISTENER.ORA Date: 04/25/2009
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = eclipse)(PORT = 1521))
)
)
)
Should a DBA want to modify the files directly, the following syntax rules must
be followed:
Network Configuration
Filename Description
listener.ora The listener.ora file defines how the listeners are
configured on the database server.
sqlnet.ora The sqlnet.ora file resides on the database server and
the local platform. Profile information is stored in the
sqlnet.ora file. This file defines information on service
names, naming methods, external naming information,
Advanced Security parameters, and database access
information. The TNS_ADMIN environmental variable
can override the default location of these files.
tnsnames.ora Resides on the local system and is used with the local
naming method. This defines net service names and
connect descriptor information.
cman.ora The configuration file for the Oracle Connection
Manager. This file resides on the same platform where
the Oracle Connection Manager runs.
ldap.ora The directory usage file is created by the Oracle
Internet Directory Configuration Assistant.
TABLE 3-5. Primary Configuration Files for Oracle Net Services
Chapter 3: Connecting to Oracle 89
■Comments must begin with a pound sign (#). Anything following the pound
sign is treated as a comment.
■Keywords are not case sensitive and cannot contain spaces.
■Spaces are optional around equal (=) signs.
■Values can only contain spaces if they are surrounded by quotes. The
values may be case sensitive depending on the operating system and
protocol.
■A connect descriptor can be no more than 4KB in length.
■All characters must be part of the network set.
■Care must be taken with parentheses when editing. Troubleshooting a
connection problem might be necessary because a simple edit was made
to the file and close or open parenthesis was dropped.
CRITICAL SKILL 3.7
Use Administration Tools
Oracle Net Services contains a number of user interfaces and tools that simplify the
management of the Oracle network, including the following:
■Oracle Enterprise Manager (OEM) / Grid Control
■The OEM console
■Oracle Net Manager
■Oracle Net Configuration Assistant
■Oracle Connection Manager
■Oracle Internet Directory Configuration Assistant
■Command-line utilities
■Oracle Advanced Security
The Oracle Enterprise Manager/Grid Control
Along with database administration, OEM allows configuration of Oracle Net
Services. OEM can be used to perform the following administration features:
■The configuration of listeners
■The configuration of naming definitions such as connect descriptors
The Oracle Net Manager
The Oracle Net Manager allows the configuration of Oracle Net Services and can
be started from the OEM console, by choosing Tools | Service Management | Oracle
Net Manager.
The Oracle Net Manager provides the following administration support:
■Listeners This supports creating and configuring listeners.
■Naming This supports defining simple names. Simple names specify
information for connect descriptors and service location information.
■Naming methods This supports the definition of naming methods.
Some of the functionality in OEM is also available in the Oracle Net Manager.
Table 3-6 shows the overlapping functionality and the differences between the two
tools.
The following can be used to start Oracle Net Manager manually through UNIX:
$ $ORACLE_HOME/bin/netmgr
Oracle Net Manager can also be started manually through Windows by
selecting Start | Programs | Oracle—OraHome11 | Configuration and Migration
Tools | Net Manager.
90 Oracle Database 11g: A Beginner’s Guide
Oracle Enterprise Manager Oracle Net Manager
Local naming (tnsnames.ora) Local naming (tnsnames.ora)
Directory naming Directory naming
Listeners Listeners
Oracle home support for multiple hosts Oracle home support for a single host
Search capability on local and directory
names
Profiles
Export directory entries to tnsnames.ora
file
Changing tracing and logging settings
TABLE 3-6. Common Features and Differences Between OEM and Oracle Net
Manager
Chapter 3: Connecting to Oracle 91
The OEM Console
The Oracle Enterprise Manager Central Console is a web-based interface for
managing the entire enterprise from the console. It offers a lot more functionality
than the standard Oracle Enterprise Manager that comes with a typical database
install. The default ports for running in a nonsecure mode are 7777-7877; default
ports for running in a secure mode are 4443-4533.
You can access the OEM Central Console from the following URLs: http://<oms
hostname>.<domain>.<port>/em and https://<oms hostname>.<domain>.<port>/
em. The OEM Central Console requires the Oracle Management Service unless the
Oracle Management Agent is installed separately.
The OEM Components
The OEM console uses the following components installed with the Oracle
application server:
■The Oracle Management Service This is a web-based application that
runs on the Oracle application server. It provides the user interface for the
OEM console, and interfaces with the management agents to process and
monitor information.
■The Oracle Management Agent This monitors information from sites that
need to be managed and that are loaded into the management service.
■The Oracle Management Repository This contains all the information
managed by the Oracle Enterprise Manager.
Before installing the Complete Enterprise Manager, make sure to read the
requirements for the complete installation that includes the Oracle Application
Server 11g, Web Cache, and the Management Service application as well as
verifying additional licensing requirements.
The Oracle Net Configuration Assistant
The Oracle Net Configuration Assistant is used during installation to configure the
basic network components. The Oracle Net Configuration Assistant can also be run
standalone to modify the same values configured during installation. Configurable
components include the following:
■Naming methods
■Net service names (tnsnames.ora)
■Listener names and protocol addresses
■Directory server usage
92 Oracle Database 11g: A Beginner’s Guide
The following can be used to start the Oracle Net Configuration Assistant
manually through UNIX:
$ $ORACLE_HOME/bin/netca
The Oracle Net Configuration Assistant can also be started manually through
Windows by selecting Start | Programs | Oracle—OraHome11 | Configuration and
Migration Tools | Net Configuration Assistant.
The Oracle Internet Directory
Configuration Assistant
The Oracle Internet Directory Configuration Assistant can be used to configure
the Oracle Internet Directory. The directory configuration file ldap.ora can be
configured with the Oracle Internet Directory Configuration Assistant or the Oracle
Net Configuration Assistant. The ldap.ora file can reside in different locations
depending on which tool created the ldap.ora file:
■If created by the OID Configuration Assistant, the ldap.ora file is stored in
the ORACLE_HOME/ldap/admin directory.
■If created by the Oracle Net Configuration Assistant, the ldap.ora file is
stored in the ORACLE_HOME/network/admin directory.
■The ldap.ora file location can be manually specified with the
LDAP_ADMIN or TNS_ADMIN environmental variables.
Command-Line Utilities
The Listener Control utility can be used to start and stop listeners, check their status,
and perform tracing and other management operations. The syntax is
lsnrctl command [listener_name]
Listener commands can also be executed from within the Listener Control utility.
The listener name is the one defined in the listener.ora file, but a default listener
named LISTENER can be used instead. If LISTENER is used, a listener name does not
need to be specified.
The following shows how to stop the listener. Here, executing the lsnrctl
command generates an LSNRCTL prompt:
$ lsnrctl
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
The command completed successfully
Chapter 3: Connecting to Oracle 93
The next example shows a sample of the type of information displayed when
starting the listener:
LSNRCTL> start
starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 -
System parameter file is C:\oracle\ora11\network\admin\listener.ora
Log messages written to C:\oracle\ora11\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)
(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows:Version 11.1.0.6.0
Start Date 03-FEB-2009 21:26:56
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File C:\oracle\ora11\network\admin\listener.ora
Listener Log File C:\oracle\ora11\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)(PORT=1521)))
Services Summary...
Service "cust" has 1 instance(s).
Instance "cust", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
The status command displays detailed information on the status of the listener.
Information includes the start time of the listener, the location of log and
configuration files, and so on.
LSNRCTL> status
The services command lists dispatchers in a shared server environment and
dedicated servers in a dedicated server environment:
LSNRCTL> services
Here is a list of listener commands:
■change_password
■exit
■help
94 Oracle Database 11g: A Beginner’s Guide
■quit
■reload
■save_config
■services
■set
■show
■spawn
■start
■status
■stop
■trace
■version
The set command can be used to modify different parameter values for a
listener. The set command, by itself, will display the parameter values that can be
modified:
LSNRCTL> set
password rawmode
displaymode trc_file
trc_directory trc_level
log_file log_directory
log_status current_listener
inbound_connect_timeout startup_waittime
save_config_on_stop
The Oracle Advanced Security Option
The Oracle Advanced Security option supports data encryption, enhanced
authentication, integrity checking, single sign-on, and the Distributed Computing
Environment (DCE). The Oracle Net Manager is used to configure Oracle Advanced
Security options.
Dispatchers
The DISPATCHERS parameter can be set to define how dispatchers will work with
the shared server architecture. Dispatchers must be defined to work with different
protocols, as shown in the following:
DISPATCHERS="(PROTOCOL=tcp)(DISPATCHERS=6)(CONNECTIONS=1000)"
DISPATCHERS="(PROTOCOL=tcps)(DISPATCHERS=6)(CONNECTIONS=1000)"
Connection pooling can also be defined as shown next:
DISPATCHERS="(PROTOCOL=tcp)(DISPATCHERS=6)(POOL=on) (TICK=1)
(CONNECTIONS=1000)(SESSIONS=5000)"
DISPATCHERS="(PROTOCOL=tcps)(DISPATCHERS=6)(POOL=on) (TICK=1)
(CONNECTIONS=1000)(SESSIONS=5000)"
NOTE
TICK is the amount of time for a message to be sent
from the server to the client; for fast networks,
recommended value is 1, but default is 15.
Project 3-1 Test a Connection
The following project will walk you through the steps of testing a connection to an
Oracle database server.
Step by Step
The first step is to test the network connectivity between the remote system and the
Oracle database server. The ping command will verify network access. If ping is
successful, the remote system can resolve the name of the host server name. The
host server name should be defined in the hosts file for the operating system.
The hosts file in UNIX is in the /etc directory; the hosts file in Windows is in
the\winnt directory. The following is an example hosts file entry:
eclipse customer.us.beginner.com
1. Ping the host server name:
ping eclipse
2. If the ping is not successful using the host server name then use the IP
address to verify that the remote system can access the host server through
the network:
ping 122.23.20.24
3. Start the listener. If the listener does not start, check the listener.ora file for
the proper entries. The listener.ora file can be found in the ORACLE_
HOME/ network/ admin directory:
lsnrctl start listener_name
Chapter 3: Connecting to Oracle 95
Test a Connection
Project 3-1
(continued)
96 Oracle Database 11g: A Beginner’s Guide
4. Verify that the service registration has been completed and that the listener
is ready to handle requests:
lsnrctl services listener_name
5. Service registration is impacted by a number of initialization parameters.
They include SERVICE_NAMES (cust.us.acme.com) and INSTANCE_NAME
(cust). The SERVICE_NAMES parameter defaults to the global database
name. The global database name is made up of the DB_NAME and
DB_DOMAIN parameters.
6. The remote system now needs to be configured. The Oracle Net Configuration
Assistant can be used for configuration. Start the Oracle Net Configuration
Assistant.
7. Of the four configuration options on the Welcome page, select the Local
Net Service Name configuration.
8. Select Add and then click Next.
9. Enter the service name (cust) and click Next.
10. Select the protocol (TCP/IP) and click Next.
11. Select the host name and port number and then click Next.
12. Select Yes, perform a test, and then click Next. If the test fails, check
whether the instance and listener are running. If they are, check the
protocol information, and if it still fails, double-check the username and
password used for the test.
13. Enter the net service name and click Next.
14. Select No when asked if you would like to configure another net service
name, and then click Next.
15. At the Congratulations screen, select Next, and then click Finish.
16. The local naming method will create a connect descriptor in the
tnsnames.ora file similar to this one:
cust=
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=customer.us.beginner.com)))
17. For the final test, log into Oracle and see if you can connect using the new
net service name:
SQL> CONNECT username/password@cust
18. The tnsping utility can also be used to test a service. If unsuccessful, it will
return the error that occurred. tnsping requires the net service name found
in the tnsnames.ora file. The count parameter defines how many attempts
are made to reach the server.
tnsping net_service_name [count]
19. If unable to connect, the trcroute utility can be used to get more detailed
error information. The trcroute utility tracks the TNS address of every node
it accesses in the path.
trcroute net_service_name
Project Summary
This project walked you through the steps a DBA will go through to test a simple
connection for an Oracle database server using the local naming method.
CRITICAL SKILL 3.8
Use Profiles
A profile contains a set of parameters that define Oracle Net options on the remote
or database server. Profiles are stored in the sqlnet.ora file and can be used to
■Route connections to specific processes
■Control access through protocol-specific parameters
■Prioritize naming methods
■Control logging and tracing features
■Configure for external naming
■Define how the client domain should append to unqualified names
During installation, the priority order for the naming methods will be defined.
If the first naming method cannot resolve the connect identifier, the next naming
method will be checked. The results will then be stored in the sqlnet.ora file, as
shown in the following example:
NAMES.DIRECTORY_PATH=(ezconnect, tnsnames)
After installation, Oracle Net Manager can be used to modify the sqlnet.ora
configuration file.
The sqlnet.ora file can be used to grant or deny access to the Oracle database
server. Table 3-7 displays sqlnet.ora parameters that control access.
Chapter 3: Connecting to Oracle 97
Test a Connection
Project 3-1
The Oracle Net Manager is used to define database access control. To define
database access control, perform the following steps using Oracle Net Manager:
1. After starting Net Manager, select Local | Profile.
2. Choose General.
3. Select Access Rights.
4. Choose Check TCP/IP Client Access Rights.
5. In the Clients Excluded From Access and the Clients Allowed To Access
fields, access control can now be defined.
CRITICAL SKILL 3.9
Network in a Multi-tiered Environment
Although the Oracle Database 11ghas additional features that simplify database
administration, the environment the database server runs in is becoming more
complex. The following areas continue to increase the complexity of Oracle
networking environments:
■Oracle Database 11g–supporting HTTP, FTP, and WebDAV protocols are
changing how data is used and accessed.
■The OEM Central Console is changing how Oracle DBAs perform
administration across multiple databases.
98 Oracle Database 11g: A Beginner’s Guide
sqlnet.ora Parameter Name Description
TCP.VALIDNODE_CHECKING Determines where to control access to
the database. If this parameter is set, the
following parameters will be used to
define the access.
TCP.EXCLUDED_NODES Defines which systems are denied access
to the database.
TCP.INVITED_NODES Defines which systems are granted access
to the database.
TABLE 3-7. sqlnet.ora Parameters
■Multi-tiered architectures are placing increasing demands on network
performance and security.
Traditionally, most Oracle networks have been set with the local naming
method. In the future, more Oracle networking environments will work with
multi-tiered architectures, the Oracle OEM Central Console, encryption, and the
directory naming method. Companies are going to need people with skills to
manage these complex environments. This chapter introduced you to the main
components of Oracle Net Services. To begin working with Oracle Net Services,
you may want to look at the following areas in the following order in terms of
developing your skills:
■Strengthen your understanding of the Oracle Net Services architecture.
■Obtain a solid understanding of configuring dedicated and shared server
environments.
■Become comfortable working with listeners and the local naming method.
■Get comfortable working with the directory naming method.
■Be able to work with the OEM Central Console and the environment
required to support it.
This list should be able to keep you busy for a few days. After that, developing
skill in tuning and troubleshooting the Oracle Net Services environment will be
important. Not included in these discussions, but also very important, is the ability
to troubleshoot and tune the network from an operating system perspective.
CRITICAL SKILL 3.10
Install the Oracle 11gClient Software
Before moving on from Oracle’s networking offering, you are going to get a quick
look at the Oracle 11gclient software. Classically, this piece of the puzzle goes on
a desktop computer, but in a 3-tier client-server model, it is often put on the middle
tier. This architecture makes it accessible to multiple client computers and often
eases management and maintenance tasks for the administrator. The journey begins
at technet.oracle.com in the download area of the site, as shown in the following
illustration.
Chapter 3: Connecting to Oracle 99
100 Oracle Database 11g: A Beginner’s Guide
Click on the name of the client software and choose the Save to Disk option as
your web browser prepares to download the file for you, as shown next.
Chapter 3: Connecting to Oracle 101
NOTE
You will need a valid username and password to
download the Oracle software discussed in the next
few sections. The Oracle software contains several
components that can be installed, and along with
these components, several more options are
available as part of the downloaded software. The
material we discuss here is an overview of a simple
client install; a mere tip-of-the-iceberg so to speak.
With the file downloaded, using WinZIP or the Windows compressed folders
feature, uncompress the file in preparation for the client install. Proceed to the
location where the media was unzipped and double-click the setup.exe program.
A DOS window appears as soon as the setup is invoked, as shown in the following
illustration.
As the first screen appears, click the Advanced Installation radio button as
shown in the following illustration, and then click Next. When asked for an
Installation type on the next screen, choose Custom, and then click Next. On the
Installation location screen, you can overwrite the suggested Oracle Base or leave
102 Oracle Database 11g: A Beginner’s Guide
the default as suggested by the installer. Once you’re happy with the Oracle Base
location, click Next.
The next screen to appear, as shown in the following illustration, does a quick
series of Product-Specific Prerequisite Checks. This ensures that the environment
where the 11gclient is to be installed can support the software. Once the installer
completes its checks, click Next to continue.
Chapter 3: Connecting to Oracle 103
You will then be positioned on the Available Product Components screen. This
tree lists the products that can be installed from the download. All of the products in
the tree should be de-selected, except for Oracle Net Services (including the Oracle
Net Listener and Oracle Connection Manager). Click Next to display the installation
summary screen before the process commences. A progress screen appears with
messages about what is being installed and a progress indicator as shown here.
Once the installer completes its work, it will bring up a Configuration Assistant
notification screen followed by starting the Oracle Net Configuration Assistant, as
shown in the following illustration.
Proceed as follows with the next few screens and the questions they ask,
clicking Next to move from one screen to the next:
■When a listener name is suggested, accept the default LISTENER.
■In the Available Protocols/Selected Protocols, ensure TCP-IP is in the
Selected Protocols list.
■Allow it to use the standard port 1521 for the listener.
■When asked about configuring another listener, say No.
■When asked if you want to configure additional naming methods, answer No.
You are then done with the network configuration. You will be returned to the
setup completion screen, at which point it is safe to shut down the installer.
þChapter 3 Mastery Check
1. The __________ background process registers the service information to the
listener.
2. True or false: The LOCAL_LISTENER parameter should be set to work with
port 1521.
3. The _______________________ is used during installation to configure
Oracle Net Services.
4. The _____________ file can be used to define, grant, or deny access to the
Oracle database server.
5. The ________________ utility can also be used to test a service.
6. A __________________ contains a set of parameters that define Oracle Net
options on the remote or database server.
7. The ldap.ora file location can be manually specified with the ____________
or TNS_ADMIN environmental variables.
8. True or false: The easy naming method is a valid naming method.
9. The Oracle LDAP directory is called the _______________.
10. True or false: The Oracle Management Service is a repository of information
generated by the Management Agent.
104 Oracle Database 11g: A Beginner’s Guide
Chapter
4
SQL: Structured Query
Language
CRITICAL SKILLS
4.1 Learn the SQL Statement Components
4.2 Use Basic Insert and Select Statements
4.3 Use Simple Where Clauses
4.4 Use Basic Update and Delete
Statements
4.5 Order Data
4.6 Employ Functions: String, Numeric,
Aggregate (No Grouping)
4.7 Use Dates and Data Functions
(Formatting and Chronological)
4.8 Employ Joins (ANSI vs. Oracle): Inner,
Outer, Self
4.9 Learn the Group By and Having
Clauses
4.10 Learn Subqueries: Simple and
Correlated Comparison with Joins
4.11 Use Set Operators: Union, Intersect,
Minus
4.12 Use Views
4.13 Learn Sequences: Just Simple Stuff
4.14 Employ Constraints: Linkage to Entity
Models, Types, Deferred, Enforced,
Gathering Exceptions
4.15 Format Your Output with SQL*Plus
106 Oracle Database 11g: A Beginner’s Guide
QL is the fundamental access tool of Oracle Database; in fact, it is
the fundamental access tool of all relational databases. SQL is used
to build database objects and it is also used to query and manipulate
both these objects and the data they may contain. You cannot insert
a row of data into an Oracle database unless you have first issued
some basic SQL statements to create the underlying tables. While Oracle provides
SQL*Plus, a SQL tool that enables you to interact with the database, there are also
many GUI tools that can be used, which can then issue SQL statements on your
behalf behind the scenes. However you decide to access the database, you will
need to understand the fundamentals of SQL. SQL will become your connection to
your data and it is an important starting point for all Oracle professionals to master.
CRITICAL SKILL 4.1
Learn the SQL Statement Components
Before you learn the many SQL commands that you will use frequently, you should
take a look at the two different categories that SQL statements are classified into.
They are DDL, or data definition language, and DML, or data manipulation
language. The majority of this chapter will deal with the latter.
DDL
DDL is the set of SQL statements that define or delete database objects such as
tables or views. For the purposes of this chapter, you will concentrate on dealing
with tables. Examples of DDL are any SQL statements that begin with create, alter,
and drop: all of these commands would act upon a database object like a table,
view, or trigger, among many others. Table 4-1 is a sample list of some DDL statements.
It does not completely represent the many varied statements that all have a unique
purpose and value.
The following SQL statements are examples of DDL create and drop statements
in action:
SQL> create table b
2 (colb char(1));
SQL> drop table b;
Table dropped.
SQL> create table state
2 (state_cd char(2) not null,
3 state_name varchar2(30));
Table created.
After you have created your table, you should confirm that it was created as you
expected. To display a description of a table, the describe command is used. Our
S
Chapter 4: SQL: Structured Query Language 107
experience suggests that you will find it very useful to be able to describe tables
within the database after you create them or any time you need to know the exact
nature of the table. You should take a closer look at the state table that you created
in the previous example:
SQL> desc state;
Name Null? Type
----------------------------------------- -------- -------------------
STATE_CD NOT NULL CHAR(2)
STATE_NAME VARCHAR2(30)
DML
DML is any SQL statement that begins with select, insert, update, or delete. The
remainder of this chapter will deal primarily with DML. Every DML SQL statement
consists of a few basic components. The following three items form the basic
foundation of most DML statements:
■Each DML statement begins with either a select, insert, update, or delete
command:
■select is used when you want to retrieve data from an Oracle database.
It is the most common SQL statement you will see.
SQL Command Purpose
create table Creates a table
create index Creates an index
alter table Adds a column, redefines an existing column, changes
storage allocation
drop table Drops a table
grant Grants privileges or roles to a user or another role
truncate Removes all rows from a table
revoke Removes privileges from a user or a role
analyze Gathers performance statistics on database objects for use by
the cost-based optimizer (although we recommend that you
use the Oracle built-in package named DBMS_STATS for the
most robust statistic collection)
TABLE 4-1. Common Formats for Date Type Data
■insert is used when you want to add rows into an Oracle table.
■update commands are used to change one or more records in a table.
■delete commands are issued when you want to remove one or more
records from a table.
■All DML commands require reference to an object that will be
manipulated. More often than not, the object being referenced is a table.
■A conditional statement can be added to any select, update, or delete
command. Absence of a conditional statement means that the command
will be performed against every record in the object. A conditional
statement is used when the DML command is intended to only act upon a
group of records that meet a specific condition. The where clause will be
discussed a little later in this chapter.
More optional DML statements will be described later in this chapter. For now,
you should concentrate on understanding the fundamental structure of each DML
statement starting with the insert and select statements.
CRITICAL SKILL 4.2
Use Basic Insert and Select Statements
Getting data into and out of a database are two of the most important features of a
database. Oracle provides two basic features that help you do just that. To get data
into the database, use the insert command; to get it back out, use the select command.
You must master these commands because they form the basics for most data access
to your Oracle database. In this section you’ll first learn how to get data into your
database and then how to get data out.
Insert
Using the state table created in the DDL example, the following is an illustration of
using the insert statement in its simplest form:
SQL> insert into state values ('AZ','Arizona');
1 row created.
Each time you execute an insert command, you receive the message “1 row
created.” Thus, you get immediate feedback that you are populating the given table
with data. When you load data into a table, you may also specify the column to
load it into. This ensures that there is no mistaking where you want the data to be
placed. In the next example, the columns are specified after the insert command:
108 Oracle Database 11g: A Beginner’s Guide
SQL> insert into state (state_cd, state_name)
2> values ('NJ','New Jersey');
1 row created.
SQL> insert into state (state_cd, state_name)
2> values ('CA','California');
1 row created.
SQL> insert into state (state_cd, state_name)
2> values ('TX','Texas');
1 row created.
SQL> insert into state (state_cd, state_name)
2> values ('FL','Florida');
1 row created.
SQL> insert into state (state_cd, state_name)
2> values ('ME','Maine');
1 row created.
Select
As mentioned earlier, the select statement is used to retrieve data from the database.
This is the commonest SQL statement you will use. The five basic parts of the SQL
statement are as follows:
■First is the keyword select, followed by what you want to retrieve from the
database. The names of the columns to be retrieved are listed here. The
select clause is mandatory.
■The word from is the next part of the SQL statement. Reference to the
object that the data is being retrieved from is made here. This is usually
a table name. The from clause is mandatory.
■As mentioned before, a conditional statement is optional for select statements.
The word where, followed by the conditions, would be the next part of the
SQL statement. (See Critical Skill 4.3 for more details on the where clause.)
■A group by clause is another optional component of the select statement.
This topic will be covered in more detail in Critical Skill 4.9, once we have
had the opportunity to discuss functions.
■The final component of a select statement is the order by clause. This will
also be discussed in more detail later on in this chapter. This is an optional
component, which will sort the results of the query before they are presented
back to you.
Chapter 4: SQL: Structured Query Language 109
110 Oracle Database 11g: A Beginner’s Guide
You should now issue your first select statement against the state table you just
populated in the insert statement examples:
SQL> select *
2 from state;
ST STATE_NAME
-- ------------------------------
AZ Arizona
NJ New Jersey
CA California
TX Texas
FL Florida
MN Maine
6 rows selected.
Notice the use of the asterisk in the select statement. The asterisk means “retrieve
data from all the columns” of the state table. (select * from emp,dept retrieves all
columns from the table.)
Rather than using the asterisk as you did in the previous example, you can specify
one or more columns after the select command in a comma-separated list. You
should rewrite the previous query and only select the state_name column this time:
SQL> select state_name
2 from state;
STATE_NAME
------------------------------
Arizona
New Jersey
California
Texas
Florida
Maine
6 rows selected.
The semicolons in the two SQL examples force the immediate execution of the
SQL statement within SQL*Plus. There are two ways to signify you have finished
and that the SQL statement can be executed in SQL*Plus:
■The semicolon at the end of a line
■The slash on a separate line
Until SQL*Plus encounters either of these characters, it assumes you need an
additional line. The following example highlights this point. Notice the use of the
slash and semicolon.
Chapter 4: SQL: Structured Query Language 111
SQL> select *
2 from a;
SQL> select *
2 from a
3 /
The absence of the semicolon in the second example resulted in a new line. In
these cases, the semicolon and slash on a separate line would force the execution of
the statement.
CRITICAL SKILL 4.3
Use Simple Where Clauses
Up to now, you have seen how the select command can be used to retrieve records
from a table. However, our basic examples have all retrieved every record from the
table. If you want to see only certain rows, you must add a where clause.
Since the previous examples returned every record in the table, you created a
simple table with a few rows in it for illustration purposes. Had you chosen to
illustrate the select command against the large sample tables provided by Oracle,
you would have returned thousands of rows—far too many for listing in this chapter.
Now that you are introducing the where clause, you will be able to control the
output. As a result, the remaining examples in this chapter will now use the
customers, products, sales, and costs tables that are part of the Oracle sample
database; they can be found in the SH schema that is included when you install the
sample databases with a default installation. Let’s describe each of these tables in
the SH schema. This may be done in SQL*Plus or in a GUI that provides this type
of table interface:
SQL> desc customers;
Name Null? Type
----------------------------------------- -------- -------------------
CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER NOT NULL CHAR(1)
CUST_YEAR_OF_BIRTH NOT NULL NUMBER(4)
CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_CITY_ID NOT NULL NUMBER
CUST_STATE_PROVINCE NOT NULL VARCHAR2(40)
CUST_STATE_PROVINCE_ID NOT NULL NUMBER
COUNTRY_ID NOT NULL NUMBER
CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25)
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
CUST_EMAIL VARCHAR2(30)
CUST_TOTAL NOT NULL VARCHAR2(14)
CUST_TOTAL_ID NOT NULL NUMBER
CUST_SRC_ID NUMBER
CUST_EFF_FROM DATE
CUST_EFF_TO DATE
CUST_VALID VARCHAR2(1)
SQL> desc products;
Name Null? Type
----------------------------------------- -------- -------------------
PROD_ID NOT NULL NUMBER(6)
PROD_NAME NOT NULL VARCHAR2(50)
PROD_DESC NOT NULL VARCHAR2(4000)
PROD_SUBCATEGORY NOT NULL VARCHAR2(50)
PROD_SUBCATEGORY_ID NOT NULL NUMBER
PROD_SUBCATEGORY_DESC NOT NULL VARCHAR2(2000)
PROD_CATEGORY NOT NULL VARCHAR2(50)
PROD_CATEGORY_ID NOT NULL NUMBER
PROD_CATEGORY_DESC NOT NULL VARCHAR2(2000)
PROD_WEIGHT_CLASS NOT NULL NUMBER(3)
PROD_UNIT_OF_MEASURE VARCHAR2(20)
PROD_PACK_SIZE NOT NULL VARCHAR2(30)
SUPPLIER_ID NOT NULL NUMBER(6)
PROD_STATUS NOT NULL VARCHAR2(20)
PROD_LIST_PRICE NOT NULL NUMBER(8,2)
PROD_MIN_PRICE NOT NULL NUMBER(8,2)
PROD_TOTAL NOT NULL VARCHAR2(13)
PROD_TOTAL_ID NOT NULL NUMBER
PROD_SRC_ID NUMBER
PROD_EFF_FROM DATE
PROD_EFF_TO DATE
PROD_VALID VARCHAR2(1)
SQL> desc sales;
Name Null? Type
----------------------------------------- -------- -------------------
PROD_ID NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
TIME_ID NOT NULL DATE
CHANNEL_ID NOT NULL NUMBER
PROMO_ID NOT NULL NUMBER
QUANTITY_SOLD NOT NULL NUMBER(10,2)
AMOUNT_SOLD NOT NULL NUMBER(10,2)
112 Oracle Database 11g: A Beginner’s Guide
SQL> desc costs;
Name Null? Type
----------------------------------------- -------- -------------------
PROD_ID NOT NULL NUMBER
TIME_ID NOT NULL DATE
PROMO_ID NOT NULL NUMBER
CHANNEL_ID NOT NULL NUMBER
UNIT_COST NOT NULL NUMBER(10,2)
UNIT_PRICE NOT NULL NUMBER(10,2)
SQL> desc promotions
Name Null? Type
------------------------------------- -------- -----------------------
PROMO_ID NOT NULL NUMBER(6)
PROMO_NAME NOT NULL VARCHAR2(30)
PROMO_SUBCATEGORY NOT NULL VARCHAR2(30)
PROMO_SUBCATEGORY_ID NOT NULL NUMBER
PROMO_CATEGORY NOT NULL VARCHAR2(30)
PROMO_CATEGORY_ID NOT NULL NUMBER
PROMO_COST NOT NULL NUMBER(10,2)
PROMO_BEGIN_DATE NOT NULL DATE
PROMO_END_DATE NOT NULL DATE
PROMO_TOTAL NOT NULL VARCHAR2(15)
PROMO_TOTAL_ID NOT NULL NUMBER
The PRODUCTS table contains more than 70 products for sale. The following
select statement will retrieve only one record for product ID (prod_id) 117, which
is the use of the simplest of where clauses. In this case, you will perform an exact
query to find product ID number 117, which is the product ID for a pack of CD-Rs:
SQL> select prod_id, prod_name, prod_category, prod_list_price PRC
2 from products
3 where prod_id = 117;
PROD_ID PROD_NAME PROD_CATEGORY PRC
---------- ---------------------------------- ---------------- ------
117 CD-R, Profess. Grade, Pack of 10 Software/Other 8.99
A Where Clause with and/or
A where clause instructs Oracle to search the data in a table and then return only
those rows that meet the criteria that you have defined. In the preceding example,
you searched the products table for one specific record with a product ID equal to
117. This was accomplished with where prod_id = 117;.
You will often be interested in retrieving rows that meet multiple criteria—for
example, if you want to retrieve a list of customers from Utah who also have a
Chapter 4: SQL: Structured Query Language 113
credit limit greater than $10,000. The SQL statement would produce the following
output:
SQL> select cust_id, cust_state_province, cust_credit_limit
2 from customers
3 where cust_state_province = 'UT'
4 and cust_credit_limit > 10000;
CUST_ID CUST_STATE_PROVINCE CUST_CREDIT_LIMIT
---------- ---------------------------------------- -----------------
50601 UT 11000
24830 UT 15000
28983 UT 15000
100267 UT 11000
100207 UT 11000
103007 UT 15000
6 rows selected.
In the previous example, you retrieved records that met all the criteria. You may
be interested in retrieving records that meet one criterion or another. For example,
if you wanted to find all the product IDs in the products table that are either in the
Hardware product category or have a weight class of 4, you would generate the
following SQL statement and output:
SQL> select prod_id, prod_category, prod_weight_class WGT
2 from products
3 where prod_category = 'Hardware'
4 or prod_weight_class = 4;
PROD_ID PROD_CATEGORY WGT
---------- -------------------------------------------------- ---------
15 Hardware 1
18 Hardware 1
139 Electronics 4
114 Oracle Database 11g: A Beginner’s Guide
Ask the Expert
Q: Why is hardware in quotes in the sample statement?
A: When a character column is used in a where clause, it is necessary to use
the single quotes around the value to be compared.
The and condition and the or condition are known as logical operators. They are
used to tell the query how the multiple criteria affect each other. Compound conditions
connected by the and keyword all have to evaluate to true for records to be retrieved.
Records are returned by compound conditions connected by the or keyword when
either one of the conditions is true. If you mix your and and or conditions, you must
carefully evaluate how the these two types will interact.
The Where Clause with NOT
The ability also exists within Oracle to retrieve records with negative criteria. The
“not equals” operator is != or may also use <>. For example, you might want to see
all the products that are not in weight class 1. The following query and its output
illustrate this example:
SQL> select prod_id, prod_category, prod_weight_class WGT
2 from products
3 where prod_weight_class != 1;
PROD_ID PROD_CATEGORY WGT
---------- -------------------------------------------------- ---------
139 Electronics 4
The Where Clause with a Range Search
Oracle also supports range searches so you can query for records that are between
two values. If you want to find all male customers in Connecticut who were born
between 1936 and 1939, you would write a query with three conditions joined by
the and keyword (all three need to evaluate to true), and one of the conditions
would use the range search between keyword. The following example illustrates
the query and resulting output:
SQL> select cust_id, cust_gender, cust_year_of_birth
2 from customers
3 where cust_state_province = 'CT'
4 and cust_gender = 'M'
5 and cust_year_of_birth between 1936 and 1939;
CUST_ID C CUST_YEAR_OF_BIRTH
---------- - ------------------
20058 M 1937
17139 M 1936
1218 M 1938
3985 M 1939
Chapter 4: SQL: Structured Query Language 115
116 Oracle Database 11g: A Beginner’s Guide
The Where Clause with a Search List
Oracle also supports searching for records that meet criteria within a list. If you
wanted to find all customers in Utah and Connecticut with a credit limit of $15,000,
this can be done with a search list. The following query represents a search list
condition:
SQL> select cust_id, cust_state_province, cust_credit_limit
2 from customers
3 where cust_credit_limit = 15000
4 and cust_state_province in ('UT','CT');
CUST_ID CUST_STATE_PROVINCE CUST_CREDIT_LIMIT
---------- ---------------------------------------- -----------------
24830 UT 15000
28983 UT 15000
101798 CT 15000
103171 CT 15000
102579 CT 15000
102238 CT 15000
101515 CT 15000
103007 UT 15000
104381 CT 15000
9 rows selected.
The Where Clause with a Pattern Search
The like command exists within Oracle to search for records that match a pattern.
The wildcard operator for pattern searches is the % sign. To search for all customers
whose last name begins with the letter Q, the following query would produce these
results:
SQL> select cust_last_name, cust_credit_limit
2 from customers
3 where cust_last_name like 'Q%';
CUST_LAST_NAME CUST_CREDIT_LIMIT
---------------------------------------- -----------------
Quinlan 9000
Quinn 11000
Chapter 4: SQL: Structured Query Language 117
You could also ask Oracle to retrieve customers whose last names contain “inl”
by using the wildcard at the beginning and end of the pattern search. The query and
output would resemble the following:
SQL> select cust_last_name
2 from customers
3 where cust_last_name like '%inl%';
CUST_LAST_NAME
----------------------------------------
Quinlan
The Where Clause: Common Operators
As you can see from the preceding examples, Oracle has a very powerful set of
operators when it comes to restricting the rows retrieved. Table 4-2 is a partial list
of operators you can use in the where clause.
Ask the Expert
Q: Are character searches case-sensitive?
A: Yes. Character columns can contain upper- or lowercase alphas. If
you searched the CUSTOMERS table for all instances of “INL” in the last
names, you would not have retrieved any records.
Q: The percent (%) sign appears to be a multicharacter wildcard.
Is there a single character wildcard available for pattern searches?
A: Yes. The underscore (_) symbol serves as the single character
wildcard.
118 Oracle Database 11g: A Beginner’s Guide
CRITICAL SKILL 4.4
Use Basic Update and Delete Statements
While select will likely be the command you use the most; you’ll use the update
and delete commands regularly, too. As you will see in Chapter 5, your programs
Operator Purpose Example
= Tests for equality. select * from customers where
cust_state_province = 'UT';
!= Tests for inequality. select * from customers where
cust_state_province != 'UT';
^= Same as !=. select * from customers where
cust_state_province ^= 'UT';
<> Same as !=. select * from customers where
cust_state_province <> 'UT';
< Less than. select * from sales where
amount_sold < 100;
> Greater than. select * from sales where
amount_sold > 100;
<= Less than or equal to. select * from sales where
amount_sold <= 500;
>= Greater than or equal to. select * from sales where
amount_sold >= 600;
In Equal to any member in
parentheses.
select * from customers where
cust_state_ province is in
('UT','CA','TX');
not in Not equal to any member in
parentheses.
select * from customers where
cust_state_ province is not in
('UT','CA','TX');
between A and B Greater than or equal to A and
less than or equal to B.
select * from sales where
amount_sold is between 100
and 500;
not between A and B Not greater than or equal to A,
and not less than or equal to B.
select * from sales where
amount_sold is not between
100 and 500;
like '%tin%' Contains given text (for
example, 'tin').
select * from customer where
cust_last_name is like '%tin%';
TABLE 4-2. Common Comparison Operators
will have a mixture of DML statements. In this section, you’ll take a closer look at
the update and delete commands.
Update
It is often necessary to change data already stored within a table. This is done using
the update command. There are three parts to this command:
1. The word update followed by the table to which you want to apply the
change. This part is mandatory.
2. The word set followed by one or more columns in which you want to
change the values. This part is also mandatory.
3. A where clause followed by selection criteria. This is optional.
Imagine that one of our customers has requested an increase in their credit limit
and our accounting department has approved it. An update statement will have to
be executed to alter the credit limit. For illustration purposes, a customer record will
be displayed before and after the update. The following example illustrates a simple
update for one customer:
SQL> select cust_id, cust_credit_limit
2 from customers
3 where cust_id = 28983;
CUST_ID CUST_CREDIT_LIMIT
---------- -----------------
28983 15000
SQL> update customers
2 set cust_credit_limit = 20000
3 where cust_id = 28983;
1 row updated.
SQL> select cust_id, cust_credit_limit
2 from customers
3 where cust_id = 28983;
CUST_ID CUST_CREDIT_LIMIT
---------- -----------------
28983 20000
This example reveals that customer 28983 had a $15,000 credit limit before
the update statement was executed. The update statement is written against the
CUSTOMERS table with a set clause issued against the column to be changed,
Chapter 4: SQL: Structured Query Language 119
cust_credit_limit, and a where clause to make the change only for customer 28983.
After the command is executed, a select statement reveals that the customer now
has a credit limit of $20,000. The update statement is a very powerful tool. It can be
used against one record, multiple records meeting simple or complex criteria, or all
records in a table.
Delete
Use the delete statement when you want to remove one or more rows of data from a
table. The command has two parts:
1. The keywords delete from followed by the table name you want to remove
records from. This is mandatory.
2. A where clause followed by the record selection criteria. This is optional.
As with the update, absence of a where clause will remove every record
from the table.
If you want to remove all the customers from the CUSTOMERS table, you would
issue the SQL statement delete from customer;. As you become more familiar with
Oracle, you will learn that the truncate customer; command will also remove every
record, but this doesn’t allow you to roll back the changes if you make a mistake.
It’s very easy to accidentally drop all the records in a table. As with the update
statement, be very careful when issuing the delete or truncate commands.
120 Oracle Database 11g: A Beginner’s Guide
Ask the Expert
Q: Can you use a where clause with every type of DML statement?
A: The where clause can be used only with select, update, and delete
statements. The insert statement can never have a where clause.
Q: You mentioned that a where clause is optional for update statements.
What would happen if one isn’t used during an update?
A: If a where clause isn’t used with an update statement, every record in the
table will be updated.
Let us now illustrate a deletion of all customers in the province of Delhi. The
code listing will first show a count of customers in Delhi, introducing count(*) for
the first time. This is being used to illustrate the number of records you expect to
delete when you issue the command. The second SQL statement issues the delete
from command, which confirms the number of records deleted. The final SQL
statement is a repeat of the first one to illustrate that there are no records remaining
for the province of Delhi. In order to continue to use these records for later examples,
you will rollback the changes so that they never get permanently committed to the
database and re-run the first SQL statement one more time to confirm that the records
have been restored:
SQL> select count(*)
2 from customers
3 where cust_state_province = 'Delhi';
COUNT(*)
----------
34
SQL> delete from customers
2 where cust_state_province = 'Delhi';
34 rows deleted.
SQL> select count(*)
2 from customers
3 where cust_state_province = 'Delhi';
COUNT(*)
----------
0
SQL> rollback;
Rollback complete.
SQL> select count(*)
2 from customers
3 where cust_state_province = 'Delhi';
COUNT(*)
----------
34
Chapter 4: SQL: Structured Query Language 121
Progress Check
1. Of the following four items, which one is not a DML keyword?
A. select
B. insert
C. create
D. update
2. How can the current column definitions of the CUSTOMERS table be
displayed?
3. In order to retrieve data from the database, there are two keywords that are
mandatory. Name them.
4. Write a SQL statement to select the customer last name and city for all
customers in Florida (FL) with a credit limit less than $5000.
CRITICAL SKILL 4.5
Order Data
So far, all of your select queries have returned records in random order. Earlier, you
selected records from the customer table, where the customer was located in either
Connecticut or Utah and had a credit limit of $15,000. The results came back in no
apparent order. It is often desirable to order the result set on one or more of the
selected columns. This ordering of data is known as sorting. Sorting will provide
guidance to the reader on how they can direct and manage their data review and
analysis. In this case, it probably would have been easier to interpret the results if
122 Oracle Database 11g: A Beginner’s Guide
Progress Check Answers
1. C. The four DML keywords are select, insert, update, and delete.
2. The following code listing displays the defined columns for the CUSTOMERS table:
desc customers;
3. Every SQL statement that retrieves data from the database will have both the select and from keywords.
4. The following SQL statement is a correct answer:
SQL> select cust_last_name, cust_city
2 from customers
3 where cust_state_province = 'UT'
4 and cust_credit_limit < 5000;
they were sorted by state, and within that state were then sorted by customer ID.
You should take a look at the query syntax and resulting output:
SQL> select cust_id, cust_state_province, cust_credit_limit
2 from customers
3 where cust_credit_limit = 15000
4 and cust_state_province in ('UT','CT')
5 order by cust_state_province, cust_id;
CUST_ID CUST_STATE_PROVINCE CUST_CREDIT_LIMIT
---------- ---------------------------------------- -----------------
101515 CT 15000
101798 CT 15000
102238 CT 15000
102579 CT 15000
103171 CT 15000
104381 CT 15000
24830 UT 15000
103007 UT 15000
8 rows selected.
Any column specified in the order by statement could be sorted either in ascending
or descending order. By default, Oracle will sort each column in ascending order. In
order to sort a column in descending order, the use of desc following the order by
column will accomplish this. You should look at the previous example one more
time with the customer IDs sorted in descending order:
SQL> select cust_id, cust_state_province, cust_credit_limit
2 from customers
3 where cust_credit_limit = 15000
4 and cust_state_province in ('UT','CT')
5 order by cust_state_province, cust_id desc;
CUST_ID CUST_STATE_PROVINCE CUST_CREDIT_LIMIT
---------- ---------------------------------------- -----------------
104381 CT 15000
103171 CT 15000
102579 CT 15000
102238 CT 15000
101798 CT 15000
101515 CT 15000
103007 UT 15000
24830 UT 15000
8 rows selected.
Chapter 4: SQL: Structured Query Language 123
CRITICAL SKILL 4.6
Employ Functions: String, Numeric, Aggregate
(No Grouping)
Up to now, you have illustrated a number of fairly simplistic DML statements.
You’ve selected some records from different tables using criteria, you’ve updated
existing rows, and you’ve even inserted and deleted some records.
Oracle provides us with many functions that allow us to analyze and aggregate
the data, returning results that differ greatly from the result sets you’ve seen so far. A
function manipulates the contents of a column in a SQL statement. You can find
what the largest credit limit is in the CUSTOMERS table and you can round numbers
or pad results with characters. In fact, when you ran a count of customers that were
in the province of Delhi before and after deleting these records, you took a sneak
peek ahead at functions.
This section will introduce you to three different types of functions: string (or
character), numeric, and aggregate.
String Functions
String functions, also known as character functions, can be categorized in two
types: those that return character values and those that return numeric values.
Table 4-3 represents the most common functions you will perform with the
character data type; it’s only a partial list. The examples that follow all use the dual
table. The dual table is an internal Oracle table and is useful in SQL and PL/SQL for
performing functions that return a single row. It can be used to return the current
system date and time, to perform arithmetic functions, or to obtain a generated
sequential number (more on this later in the chapter).
Numeric Functions
Table 4-4 illustrates some common numeric functions, their syntax, and the results
they produce. These are only a few of the many functions available.
Aggregate Functions
Unlike the character or numeric functions, which act on a single row, aggregate
functions act on an entire column of data. Aggregate functions save the developer
from having to write a lot of code to determine the maximum column value in a set
of records or an average, for example. A single result row is returned by aggregate
functions based on the group of rows. Table 4-5 illustrates the more commonly used
aggregate functions but is only a partial list. As simple as these are, we’re sure you’ll
agree that they are indeed quite powerful.
124 Oracle Database 11g: A Beginner’s Guide
Chapter 4: SQL: Structured Query Language 125
Function Action Example Displays
lower(char) Converts the entire string
to lowercase.
select lower('DAliA') from
dual;
dalia
replace(char,str1,str2) Replaces every occurrence
of str1 in char with str2.
select replace('Scott', 'S',
'Boy') from dual;
Boycott
substr(char,m,n) Extracts the characters
from char starting in
position mfor n
characters.
select substr('ABCDEF',4,2)
from dual;
DE
length(char) Returns the length of char. select length('Marissa')
from dual;
7
rpad(expr1,n,expr2) Pads expr1 with expr2 to
the right for ncharacters.
Often used for space
padding in the creation
of a fixed-length record.
select rpad('Amanda', 10, '1')
from dual;
Amanda1111
initcap(char) Changes the first character
of each element in char to
uppercase.
select initcap('shane k.')
from dual;
Shane K.
TABLE 4-3. Common String Functions
Function Action Example Displays
ceil(n) Returns nearest whole number
greater than or equal to n.
select ceil(12.3) from dual; 13
floor(n) Returns nearest whole number
less than or equal to n.
select floor(127.6) from dual; 127
round(n,m) Rounds nto mplaces to the
right of the decimal point.
select round(579.34886,3)
from dual;
579.349
power(m,n) Multiplies mto the power n. select power(5,3) from dual; 125
mod(m,n) Returns the remainder of the
division of mby n. If n=0, then
0 is returned. If n>m, then mis
returned.
select mod(9,5) from dual;
select mod(10,5) from dual;
select mod(6,7) from dual;
4
0
6
sqrt(n) Returns the square root of n. select sqrt(9) from dual; 3
TABLE 4-4. Common Numeric Functions
126 Oracle Database 11g: A Beginner’s Guide
CRITICAL SKILL 4.7
Use Dates and Data Functions (Formatting
and Chronological)
Date is the next commonest type of data you’ll find in an Oracle database after
character and numeric data. The date data type consists of two principal elements:
date and time. It’s important to keep in mind that the date data type includes time
when comparing two dates with each other for equality.
The default date format in many Oracle databases is DD-MON-YY, where DD
represents the day, MON is the month, and YY is the two-digit year. A date can be
inserted into a table without specifying either the four-digit year or a value for the
time element. Oracle will default the century to 20 for years 00–49 and 19 for years
50–99. Without a specific time being specified during an insert, the time will default
to midnight, which is represented as 00:00:00.
Date Functions
As with the numeric and character data types, Oracle has provided many date
functions to help with the manipulation of date data. If you were to routinely print
customized letters to your best customers offering them a special deal that expires
Function Action Example Displays
count(expr) Returns a count of
non-null column
values for each row
retrieved.
select count(cust_id)
from customers
where cust_state_ province = 'NY';
694
avg(expr) Returns the average
for the column values
and rows selected.
select avg(amount_sold) from sales
where prod_id = 117;
9.92712978
sum(expr) Returns the sum of the
column values for all
the retrieved rows.
select sum(amount_sold) from sales
where prod_id = 117;
170270.13
min(expr) Returns the minimum
value for the column
and rows retrieved.
select min(prod_list_price) from
products;
6.99
max(expr) Returns the maximum
value for the column
and rows retrieved.
select max(prod_list_price) from
products;
1299.99
TABLE 4-5. Common Aggregate Functions
on the last day of the month, the last_day function could be used to automatically
generate the expiration date for the offer. Table 4-6 shows the commonest date
functions.
Special Formats with the Date Data Type
Date formats are used to change the display format of a date. This is done using the
to_char conversion function along with the date and format mask. Table 4-7 shows
a sample of the commoner date formats and their output.
Chapter 4: SQL: Structured Query Language 127
Function Action Example Displays
Sysdate Returns current
system date. Time
could also be
retrieved using the
to_char function,
which is discussed
in the next section.
select sysdate from dual; 17-MAR-08 on
March 17, 2008
last_day(date) Returns last day of
the month for date.
select
last_day('17-MAR-08')
from dual;
31-MAR-08
add_months(d,n) Adds nor subtracts
-nmonths from
date d.
select add_
months('21-APR-08', 2)
from dual;
21-JUN-08
months_between(d1,d2) Returns difference
in months between
date d1 and
date d2.
select months_
between('17-MAR-61',
'21-APR-62') from dual;
-13.129032
next_day(d,day) Returns the date
that corresponds
with the day of the
week after date d.
select
next_day('01-FEB-08',
'Saturday') from dual;
07-FEB-08
current_timestamp Returns the current
timestamp along
with the time
zone offset.
select sessiontimezone,
current_timestamp
from dual;
01-NOV-08
01.17.56.917550
PM -05:00
TABLE 4-6. Common Date Functions
128 Oracle Database 11g: A Beginner’s Guide
Nested Functions
It is also common to nest functions within functions. Using the months_between
example from Table 4-7, it would be possible to round the number of months
between the two dates. The following statement and output illustrates this example.
SQL> select round(months_between('17-MAR-61','21-APR-62'))
2 from dual;
ROUND(MONTHS_BETWEEN('17-MAR-61','21-APR-62'))
----------------------------------------------
-13
The inner function is evaluated first, and then the outer function is evaluated
second. This is true for all nested functions and as this example illustrates, different
function types can be combined. Pay special notice to the parentheses for the outer
Format Mask Returns Example Displays
Y or YY or YYY Last one, two, or three
digits of year
select to_char(sysdate,'YYY')
from dual;
004 for all dates in 2004
YEAR Year spelled out select
to_char(sysdate,'YEAR')
from dual;
TWO THOUSAND FOUR
in 2004
Q Quarter of year select to_char(sysdate,'Q')
from dual;
3 for all dates in August
MM Month select to_char(sysdate,'MM')
from dual;
12 for all dates in December
Month Name of month as a
nine-character name
select
to_char(sysdate,'Month')
from dual;
March followed by 4 spaces
for all dates in March
WW Week of year select to_char(sysdate,'WW')
from dual;
29 on July 15, 2004
W Week of the month select to_char(sysdate,'W')
from dual;
3 on May 15, 2004
DDD Day of the year select
to_char(sysdate,'DDD')
from dual;
359 on December 25 in
non-leap years
DD Day of the month select to_char(sysdate,'DD')
from dual;
09 on September 9 in
any year
D Day of the week
(1 through 7)
select to_char(sysdate,'D')
from dual;
5 on January 29, 2004
TABLE 4-7. Common Formats of Date Type Data
and inner functions. For illustration purposes, this example nests only one function
within another. However, it is possible to nest many functions within each other.
Just be careful; the order of the functions is important, and the complexity of
debugging nested functions increases with each additional nested function.
CRITICAL SKILL 4.8
Employ Joins (ANSI vs. Oracle):
Inner, Outer, Self
Up until now, all of the examples in this chapter have selected data from only one
table. In actual fact, much of the data that you need is in two or more tables. The
true power of a relational database (and the source of its name) comes from the
ability to relate different tables and their data together. Understanding this concept
is critical to harvesting the information held within the database. This is more
commonly known as joining two or more tables.
With Oracle Database 11g, queries can be written using either Oracle’s SQL
syntax or ANSI syntax. While Oracle hasn’t made ANSI syntax available until
recently, it has been used in non-Oracle environments for some time. Many
third-party tools accept ANSI SQL and, as you’ll see shortly, the joins are quite
different.
Inner Joins
An inner join, also known simply as join, occurs when records are selected from
two tables and the values in one column from the first table are also found in a
similar column in the second table. In effect, two or more tables are joined together
based on common fields. These common fields are known as keys. There are two
types of keys:
■Aprimary key is what makes a row of data unique within a table. In the
CUSTOMERS table, CUST_ID is the primary key.
■Aforeign key is the primary key of one table that is stored inside another
table. The foreign key connects the two tables together. The SALES table
also contains CUST_ID, which in the case of the SALES table, is a foreign
key back to the CUSTOMERS table.
Oracle Inner Joins
The tables to be joined are listed in the from clause and then related together in the
where clause. Whenever two or more tables are found in the from clause, a join
happens. Additional conditions can still be specified in the where clause to limit
which rows will be returned by the join. For example, when you queried the SALES
Chapter 4: SQL: Structured Query Language 129
130 Oracle Database 11g: A Beginner’s Guide
table on its own, the only customer information available to us was the CUST_ID.
However, if you join each record, you retrieve from the SALES table by the
CUST_ID to the same column in the CUSTOMERS table, and all the customer
information becomes available to you instantly.
This first join example displays both the city and state details for each customer
who has purchased a particular product under a specific promotion. The product ID
and quantity sold are also displayed:
SQL> select prod_id, quantity_sold, cust_city, cust_state_province
2 from sales, customers
3 where sales.cust_id = customers.cust_id
4 and prod_id = 117;
PROD_ID QUANTITY_SOLD CUST_CITY CUST_STATE_PROVINCE
---------- ------------- --------------- -------------------
117 1 Fort Klamath OR
117 1 San Mateo CA
117 1 Frederick CO
. . .
The from clause identified two tables and the where clause joins them with
table_name.column_name syntax. Later on in this chapter, you’ll take a brief look at
the report formatting capabilities of SQL*Plus, which will allow you to control the
look of the output.
NOTE
The reason you must adopt the table_name.column_
name construct is to tell Oracle exactly which tables
and columns to join. This is to avoid any ambiguity
when different tables have columns that are named
the same.
SQL statements can become quite confusing once you start joining tables,
especially when you’re joining more than two. Oracle also allows you to give the
tables an alternate name known as a table alias. You should present this query
again using “s” as the table alias for the SALES table and “c” as the table alias for
the CUSTOMERS table:
select prod_id, quantity_sold, cust_city, cust_state_province
from sales s, customers c
where s.cust_id = c.cust_id
and prod_id = 117
You should take this join example one step further. cust_id is the column you
are using to join the two tables, and therefore it is found in both the SALES and
CUSTOMERS tables. If you want to display cust_id as part of the select list, you
would need to prefix it with the table alias:
select s.prod_id, s.quantity_sold, c.cust_id, c.cust_city,
c.cust_state_province
from sales s, customers c
where s.cust_id = c.cust_id
and s.prod_id = 117
All the column names in this example were prefixed with the table alias qualifier.
While it’s only necessary for columns that appear in more than one table, it enhances
the readability of the SQL statement as the statements become more complex and
include more than one table join.
This leads you into the final example, which presents the concept of joining more
than two tables. In addition to joining the CUSTOMERS table to the SALES table as
you have in all of the preceding examples, you are also joining the CUSTOMERS
table to the PRODUCTS and PROMOTIONS tables so you can pull in columns from
those tables, as well:
select c.country_id, p1.promo_name, p2.prod_category, s.quantity_sold,
from sales s,
customers c,
promotions p1,
products p2
where s.cust_id = c.cust_id
and s.promo_id = p1.promo_id
and s.prod_id = p2.prod_id
and s.prod_id = 117
It’s that simple to join a bunch of tables together. Name each of the tables in the
from clause, alias them, and then join them to each other in your where clause using
the foreign key relationships.
Chapter 4: SQL: Structured Query Language 131
ANSI Inner Joins
With ANSI joins, the join criteria is found in the from portion of the SQL statement.
The where clause only lists the selection criteria for the rows. There are a couple of
different ways to join the tables together with ANSI syntax.
ANSI on/using A simple join can be specified with an on or using statement. The
columns to be joined on will be listed, while the where clause can list additional
selection criteria. The following two examples illustrate the on syntax followed by
the using syntax:
select c.cust_id, c.cust_state_province,
s.quantity_sold, s.prod_category
from sales s join customers c
on s.cust_id = c.cust_id
where prod_id = 117;
select cust_id, c.cust_state_province,
s.quantity_sold, s.prod_category
from sales s join customers c
using (cust_id)
where prod_id = 117;
The ANSI syntax also allows for two or more tables to be joined. This can be
accomplished with multiple join on or multiple join using statements in the from
section of the SQL statement. The following are two examples:
select c.cust_id, c.cust_state_province,
s.quantity_sold, p.prod_name
from sales s
join customers c
on s.cust_id = c.cust_id
join products
on s.prod_id = p.prod_id
where p.prod_id = 117
and c.country_id = 52790;
select cust_id, c.cust_state_province,
s.quantity_sold, p.prod_name
from sales s
join customers c using (cust_id)
join products p using (prod_id)
where p.prod_id = 117
and c.country_id = 52790;
132 Oracle Database 11g: A Beginner’s Guide
ANSI Natural Join ANSI SQL also gives us a third join alternative: the natural join.
In this case, the columns to be joined are not specified but rather are resolved by
Oracle. They must be similarly named in the tables to be joined. As always,
additional selection criteria can be specified in the where clause:
select cust_id, c.cust_state_province,
s.quantity_sold, p.prod_name
from sales s
natural join customers c
natural join products p
where prod_id = 117;
As you found out with the using syntax, you couldn’t use the table alias qualifier
on the cust_id column. If you did, you would get an “ORA-25155: column used in
NATURAL join cannot have qualifier” error message.
Although it would be very poor database design, it’s entirely possible that a
similarly named column could exist in different tables but have no relationship to
each other. Be careful while naturally joining tables to make sure that it makes
sense to join them. While this could just as easily happen with a regular Oracle
join, the simple act of having to specify which columns to join could force you to
go through this thought process. It’s an important fact to know your tables and what
you want to accomplish with the joins.
Chapter 4: SQL: Structured Query Language 133
Ask the Expert
Q: Why does the cust_id column in the ANSI on join have a table prefix
qualifier while the cust_id column in the ANSI using join does not?
A: The on join syntax tells Oracle which columns to use in the table join. Like
the Oracle inner join examples, the table prefix is required for cust_id within
both the select list of columns and the table join. The using syntax declares
only the column name and allows Oracle to resolve the join. The table
qualifiers for the cust_id column are absent from the join portion of the SQL
statement and need to be kept out of the select list as well. If you forget, don’t
worry, Oracle will return an “ORA-25154: column part of USING clause
cannot have a qualifier” error message.
134 Oracle Database 11g: A Beginner’s Guide
Outer Joins
Unlike an inner join, which only returned records that had matching values for a
specific column in both tables, an outer join can return results from one table where
the corresponding table did not have a matching value.
In our sample set of data, there are a number of customers that haven’t recorded
any sales. There are also a number of products that haven’t been sold either. These
examples will be used in the following explanation of Oracle and ANSI outer joins.
Oracle Outer Joins
In order to find rows from one table that don’t match rows in another, known as an
outer join, Oracle presents you with the “(+)” notation. The “(+)” is used in the where
clause on either of the tables where nonmatching rows are to be returned.
You have found that cust_id = 1 does not have any sales, while cust_id = 80 has
exactly two. Now take a look at what happens when you select these two customers
from the CUSTOMERS table and request some SALES table details if they exist:
SQL> select c.cust_id, c.cust_last_name, s.prod_id, s.quantity_sold
2 from customers c, sales s
3 where c.cust_id = s.cust_id(+)
4 and c.cust_id in (1,80);
CUST_ID CUST_LAST_NAME PROD_ID QUANTITY_SOLD
---------- ---------------------------------- ---------- -------------
1 Kessel
80 Carpenter 127 1
80 Carpenter 36 1
The outer join allows you to display the CUSTOMERS columns alongside the
nulls for the nonmatched rows’ SALES records. A simple join would have only
returned the two records for cust_id 80.
Project 4-1 Join Data Using Inner and Outer Joins
With the sample tables Oracle has provided, there are no outer join examples. When
you learn about referential integrity and constraints later in this chapter, this will
become a little clearer. Suffice it to say that the customers, products, and promotions
in the sales table all exist in their respective tables. In this project, you’re going to
create your own simple tables where you can better demonstrate outer joins. Once
you discuss the ANSI version of joins, you’ll revisit this project and introduce a new
concept available only with the ANSI syntax.
Step by Step
You should start by creating and populating two very simple tables that will join on
a common column. Open up a SQL*Plus session and issue the following commands:
1. create table temp1 (id number(3), desc1 char(5));
Projec
2. create table temp2 (id number(3), desc2 char(5));
3. insert into temp1 values (123, 'ABCDE');
4. insert into temp1 values (456, 'FGHIJ');
5. insert into temp2 values (456, 'ZZZZZ');
6. insert into temp2 values (789, 'MMMMM');
Table temp1 and temp2 each have two records. The two tables join with each
other on the “ID” column, and they have one ID in common: 456. You should
continue now by displaying all the records from temp1 and temp2 followed by
writing an inner, right outer, and left outer join. In SQL*Plus, enter the code from
the following code listings and check that you get the same output.
1. Display the records from temp1 (remember to use select * when doing so):
ID DESC1
---------- -----
123 ABCDE
456 FGHIJ
2. Next, display the records from temp2:
ID DESC2
---------- -----
456 ZZZZZ
789 MMMMM
3. Use an inner join to join the two:
SQL> select a.id, a.desc1, b.desc2
2 from temp1 a, temp2 b
3 where a.id = b.id;
ID DESC1 DESC2
---------- ----- -----
456 FGHIJ ZZZZZ
4. Create an outer join table called temp2, as in the following:
SQL> select a.id, a.desc1, b.id, b.desc2
2 from temp1 a, temp2 b
3 where a.id = b.id(+);
ID DESC1 ID DESC2
---------- ----- ---------- -----
123 ABCDE
456 FGHIJ 456 ZZZZZ
Chapter 4: SQL: Structured Query Language 135
Join Data Using Inner and Outer Joins
Project 4-1
(continued)
5. Generate outer join table temp1:
SQL> select a.id, a.desc1, b.id, b.desc2
2 from temp1 a, temp2 b
3 where a.id(+) = b.id;
ID DESC1 ID DESC2
---------- ----- ---------- -----
456 FGHIJ 456 ZZZZZ
789 MMMMM
6. Now, outer join both sides, as in the following:
SQL> select a.id, a.desc1, b.id, b.desc2
2 from temp1 a, temp2 b
3 where a.id(+) = b.id(+);
where a.id(+) = b.id(+)
*
ERROR at line 3:
ORA-01468: a predicate may reference only one outer-joined table
Project Summary
The outer join of table temp2 returned all records from temp1 even if they had
nonmatching rows in temp2. The outer join of table temp1 returned all records from
temp2 whether or not they had matching rows in temp1. Lastly, you tried an outer
join on both sides to see what would happen. This syntax would not work, and
Oracle gave us a helpful error message (that’s not always the case!). When you
learn the union critical point later on in this chapter, you’ll see that there’s a way to
do this with Oracle’s syntax. However, you should move on to the ANSI outer join
examples now, where you’ll see that it is possible without writing a lot of code
(that’s a good thing!).
ANSI Outer Joins
With Oracle9i, Oracle began down the journey to fully support ANSI SQL standards.
To meet this goal, Oracle started the support of ANSI joins as discussed previously.
You are now presented with ANSI outer joins. As was just alluded to in Project 4-1,
the ANSI outer join syntax allows you to perform right outer joins, left outer joins,
and full outer joins.
ANSI Right Outer Joins As with the ANSI inner joins, the ANSI outer joins have
moved the join to the from clause. A right outer join can be written with keywords
right outer join or right join since outer is redundant. Rewriting our SALES and
CUSTOMERS example from before with the ANSI syntax would produce the
following:
136 Oracle Database 11g: A Beginner’s Guide
Projec
SQL> select c.cust_id, c.cust_last_name, s.prod_id, s.quantity_sold
2 from sales s right join customers c
3 on c.cust_id = s.cust_id
4 where c.cust_id in (1,80);
CUST_ID CUST_LAST_NAME PROD_ID QUANTITY_SOLD
---------- ----------------------------------- ---------- -------------
1 Kessel
80 Carpenter 127 1
80 Carpenter 36 1
As with the Oracle example, the SALES table nonmatched rows are returned.
The main difference was that s.cust_id had the (+) notation before; now you state
that SALES will be right joined to CUSTOMERS. The join syntax is in the from
clause, while the where clause contains only the selection criteria (in this case, only
customer 1’s and 80’s records). This query can also be written with using or natural
right join ANSI syntax. Go ahead and try that on your own. Make sure you get the
exact same results as you did with the on example from the preceding example.
ANSI Left Outer Joins The ANSI left outer join works exactly the same as the right
outer join and can be written using either left outer join or left join. As with the right
outer join, the join on, join using, or natural left join styles are all available. Any of
the combinations will produce exactly the same results. Hold off on the left outer
join example until you revisit the outer join idea later in Project 4-4.
ANSI Full Outer Joins A full outer join is possible when using the ANSI syntax
without having to write too much code. With a full outer join, you will be able to
return both the right outer join and left outer join results from the same query.
The full outer join queries can be written as full outer join or full join and once
again, the on, using, or natural joins are all possible. You should revisit the Outer
Joins Project and try the ANSI syntax out.
Project 4-2 Join Data Using ANSI SQL Joins
Using the temp1 and temp2 tables you created and populated, try out the ANSI
right, left, and full outer joins.
Step by Step
You’ve just learned that you can write the ANSI outer joins with or without the outer
keyword in each of the ANSI right, left, and full outer joins. You also learned that
the ANSI on, using, and natural join syntax is available as well. The following
step-by-step instructions use a combination of these for illustration purposes. Feel
Chapter 4: SQL: Structured Query Language 137
Join Data Using ANSI SQL Joins
Project 4-2
(continued)
free to try alternate syntax, but we encourage you to adopt a consistent style to
allow your code to be self-documenting and traceable by other developers.
1. Use the ANSI right outer join:
SQL> select id, desc1, desc2
2 from temp2 right outer join temp1
3 using (id);
ID DESC1 DESC2
---------- ----- -----
456 EFGH ZZZZ
123 ABCD
2. Use the ANSI left outer join, shown in the following:
SQL> select id, desc1, desc2
2 from temp2 b natural left join temp1 a;
ID DESC1 DESC2
---------- ----- -----
456 EFGH ZZZZ
789 MMMM
3. Use the ANSI full outer join to complete the syntax:
SQL> select a.id, a.desc1, b.id, b.desc2
2 from temp1 a full join temp2 b
3 on a.id = b.id;
ID DESC1 ID DESC2
---------- ----- ---------- -----
456 EFGH 456 ZZZZ
123 ABCD 789 MMMM
Project Summary
The three examples in this project show an alternate way of performing outer joins
using ANSI SQL. Our first join, the right outer join, returns all of the rows from the
table listed on the right side of the from clause, temp1, regardless of whether or not
they match to a row from the other table, temp2.
The second example switches the logic. The table on the left, temp2, returns all
rows with a left outer join specified as natural left join.
The final example introduces the full outer join concept available with ANSI
SQL. In this case, all rows are returned from each table regardless of whether or
not a match was made.
138 Oracle Database 11g: A Beginner’s Guide
Self-Joins
A self-join is used for a relationship within a single table. Rows are joined back to
the same table instead of joining them to a related second table as you have seen
with the many CUSTOMERS and SALES tables examples throughout this chapter.
A common example involves hierarchical relationships where all of the records
and related records are stored within the same table. A family tree is one such
hierarchy that best illustrates the self-join. You should take a look at the FAMILY
table that you have defined for this concept:
SQL> desc family
Name Null? Type
----------------------------------------- -------- --------------
NAME NOT NULL CHAR(10)
BIRTH_YEAR NOT NULL NUMBER(4)
FATHER CHAR(10)
The table contains columns for a person’s name and birth year as well as their
father’s name. The fathers each have their own row in the table with their respective
birth years and names. This table could be filled out with every known relationship
in the family tree. For this example, Moishe, born in 1894, has a son, Joseph, who was
born in 1930. Joseph has three children: Michael, born in 1957; David, born in 1959;
and Ian, born in 1963. You can see that Ian then had two children. The example first
takes a look at all of the records in the table followed by your hierarchical self-join
example. In the first record, we show you all of the data we created. You can
simulate this example by creating your own insert statements into the family table:
SQL> select * from family;
NAME BIRTH_YEAR FATHER
---------- ---------- ----------
Moishe 1894
Joseph 1930 Moishe
Michael 1957 Joseph
Davi 1959 Joseph
Ian 1963 Joseph
Baila 1989 Ian
Jillian 1991 Ian
SQL> select a.name, a.birth_year,
2 a.father, b.birth_year
3 from family a, family b, family c
4 where a.father = b.name;
NAME BIRTH_YEAR FATHER BIRTH_YEAR
---------- ---------- ---------- ----------
Joseph 1930 Moishe 1894
Chapter 4: SQL: Structured Query Language 139
140 Oracle Database 11g: A Beginner’s Guide
Michael 1957 Joseph 1930
David 1959 Joseph 1930
Ian 1963 Joseph 1930
Baila 1989 Ian 1963
Jillian 1991 Ian 1963
The FAMILY table is found in the from clause twice with table aliases of a and b.
The table is joined back to itself to retrieve the father’s details. In order to accomplish
this, the value found in the father column for each retrieved record (a.father) is
joined back to the table to obtain a match on the name column (b.name), which
will return the father’s details—in this case, his year of birth (b.birth_year). Although
this appears complex on the surface, you will find that connecting information
together whether from multiple tables or one table back to itself is a regular way
for you to view your data in a more meaningful manner.
CRITICAL SKILL 4.9
Learn the Group By and Having Clauses
Earlier, you learned about functions that can work on sets of rows. You can also
group sets of rows to lump similar types of information together and return summary
information, also referred to as aggregated information. A large number of queries
you write will perform group functions as the data is retrieved from the database.
Mastering the use of functions and grouping is fundamental to understanding the
full power of SQL.
Group By
You can use many of the functions you were presented with earlier with or without
the group by clause; but, when you use them without it, Oracle treats all of the
selected rows as one group. For example, the following query, when written without
a group by clause, returns the average amount sold for products within the Electronics
category:
SQL> select avg(amount_sold)
2 from sales s, products p
3 where s.prod_id = p.prod_id
4 and prod_category = 'Electronics';
AVG(AMOUNT_SOLD)
----------------
125.551667
The entire Electronics category was treated as one group. If you wanted to see
the average amount sold for each subcategory within the Electronics category, you
will need to use a group by clause in your query, lumping each of the Electronics
subcategories together before calculating the average. Each group by clause is
Chapter 4: SQL: Structured Query Language 141
Projec
accomplished by putting the column or columns to group by in the select list
followed by one or more functions. A group by statement follows the where clause,
and it must include each of the select list columns that are not acted upon by a
group function. You should take a look at an example:
SQL> select prod_subcategory, avg(amount_sold)
2 from sales s, products p
3 where s.prod_id = p.prod_id
4 and prod_category = 'Electronics'
5 group by prod_subcategory;
PROD_SUBCATEGORY AVG(AMOUNT_SOLD)
-------------------------------------------------- ----------------
Game Consoles 300.523928
Y Box Accessories 18.7803303
Home Audio 582.175922
Y Box Games 22.640670
This group by example illustrates a column and function in the select list and the
repetition of the column again in the group by clause.
Having
Just as you have used selection criteria to reduce the result set, you can apply the
having clause to summarized data from a group by operation to restrict the groups
returned. Using the previous example, suppose you only wanted to see the Product
Subcategory groups that had an average amount sold greater than 300. The following
is a having clause executed against the avg(amount_sold) aggregation example:
SQL> select prod_subcategory, avg(amount_sold)
2 from sales s, products p
3 where s.prod_id = p.prod_id
4 and prod_category = 'Electronics'
5 group by prod_subcategory
6 having avg(amount_sold) > 300;
PROD_SUBCATEGORY AVG(AMOUNT_SOLD)
-------------------------------------------------- ----------------
Game Consoles 300.523928
Home Audio 582.175922
Project 4-3 Group Data in Your Select Statements
One final example will demonstrate the grouping of multiple columns and more
than one function being performed for each group. As you build on this example,
you will be introduced to column aliases, the round function combined with an avg
Group Data in Your Select Statements
Project 4-3
(continued)
142 Oracle Database 11g: A Beginner’s Guide
function, and the use of a substr function, which will serve to select only a specified
number of characters for the product subcategories and names results.
Step by Step
Start with the preceding group by example and build on it as you introduce some
formatting and intermediate concepts. Look at the output each time and see how
you are transforming it along the way. A final output listing has been provided at the
end for you to compare against:
1. Start SQL*Plus and re-execute the preceding group by example:
select prod_subcategory, avg(amount_sold)
from sales s, products p
where s.prod_id = p.prod_id
and prod_category = 'Electronics'
group by prod_subcategory;
2. Add the product name to the select list. Don’t forget to add it to the group
by also:
select prod_subcategory, prod_name, avg(amount_sold)
from sales s, products p
where s.prod_id = p.prod_id
and prod_category = 'Electronics'
group by prod_subcategory, prod_name;
3. Rewrite the query to use a natural join, remove the table aliases, and
exclude the 'Home Audio' subcategory from the selection:
select prod_subcategory, prod_name, avg(amount_sold)
from sales natural join products
where prod_category = 'Electronics'
and prod_subcategory != 'Home Audio'
group by prod_subcategory, prod_name;
4. Add a max function calculation on the amount_sold to the query:
select prod_subcategory, prod_name, max(amount_sold), avg(amount_sold)
from sales natural join products
where prod_category = 'Electronics'
and prod_subcategory != 'Home Audio'
group by prod_subcategory, prod_name;
5. Add a substr function to both the prod_subcategory and prod_name,
selecting the first 18 and 25 characters, respectively, to shorten the
displayed results. Don’t forget to change the group by at the same time:
select substr(prod_subcategory,1,18),
substr(prod_name,1,25),
max(amount_sold),
avg(amount_sold)
from sales natural join products
where prod_category = 'Electronics'
and prod_subcategory != 'Home Audio'
group by substr(prod_subcategory,1,18),
substr(prod_name,1,25);
6. Add a round function to the avg(amount_sold) function. In this step, you
should also give the column names aliases to make the results more readable:
select substr(prod_subcategory,1,18) Subcategory,
substr(prod_name,1,25) Product_Name,
max(amount_sold) Max_Amt_Sold,
round(avg(amount_sold),2) AvgAmt
from sales natural join products
where prod_category = 'Electronics'
and prod_subcategory != 'Home Audio'
group by substr(prod_subcategory,1,18),
substr(prod_name,1,25);
7. Add a having clause to return aggregated rows that have both a maximum
amount sold and an average amount sold greater than 10. As one final
measure, you should also add an order by:
select substr(prod_subcategory,1,18) Subcategory,
substr(prod_name,1,25) Product_Name,
max(amount_sold) Max_Amt_Sold,
round(avg(amount_sold),2) AvgAmt
from sales natural join products
where prod_category = 'Electronics'
and prod_subcategory != 'Home Audio'
group by substr(prod_subcategory,1,18),
substr(prod_name,1,25)
having max(amount_sold) > 10
and avg(amount_sold) > 10
order by substr(prod_subcategory,1,18),
substr(prod_name,1,25);
8. Your final output should look like this:
SUBCATEGORY PRODUCT_NAME MAX_AMT_SOLD AVGAMT
------------------ ------------------------ ------------ ------
Game Consoles Y Box 326.39 300.52
Y Box Accessories Xtend Memory 29.8 24.15
Y Box Games Adventures with Numbers 17.03 13.78
Y Box Games Bounce 25.55 21.13
Y Box Games Comic Book Heroes 25.76 22.14
Y Box Games Endurance Racing 42.58 34.29
Y Box Games Finding Fido 16.6 12.79
Chapter 4: SQL: Structured Query Language 143
Group Data in Your Select Statements
Project 4-3
Group Data in Your Select Statements
Project 4-3
(continued)
Y Box Games Martial Arts Champions 25.76 22.14
Y Box Games Smash up Boxing 38.64 33.2
9 rows selected.
Project Summary
While the final example, and a few transformations along the way, could be considered
more along the lines of intermediate SQL, take some time to study each of the steps
and the resulting changes to the output. Once you understand the different components
of the SQL statement that evolved in this project, you’ll be well on your way to
unleashing the power of SQL.
Progress Check
1. Retrieve a list of all product categories, subcategories, names, and list prices
where the list price is greater than $100. Order this query by product category,
subcategory, and name.
2. List the aggregate total sales for every product category and subcategory
group using the ANSI natural join syntax.
3. Retrieve a list of all customers IDs and last names where the customer only
has one entry in the SALES table.
144 Oracle Database 11g: A Beginner’s Guide
Progress Check Answers
1. An ordered list of all product categories, subcategories, names, and list prices greater than $100 is
returned by the following query:
SQL> select prod_category, prod_subcategory, prod_name, prod_list_price
2 from products
3 where prod_list_price > 100
4 order by prod_category, prod_subcategory, prod_name;
2. The SQL statement that will return the aggregate amount sold for every product category and
subcategory using the ANSI SQL natural join is shown here:
SQL> select prod_category, prod_subcategory, sum(amount_sold)
2 from products natural join sales
3 group by prod_category, prod_subcategory;
3. The list of all customer IDs and last names for customers that only had one sale is returned by the
following SQL statement:
SQL> select c.cust_id, cust_last_name, count(*)
2 from customers c, sales s
3 where c.cust_id = s.cust_id
4 group by c.cust_id, cust_last_name
5 having count(*) = 1;
CRITICAL SKILL 4.10
Learn Subqueries: Simple and Correlated
Comparison with Joins
Within SQL, functionality exists to create subqueries, which are essentially queries
within queries. This power capability makes it possible to produce results based on
another result or set of results. You should explore this concept a little further.
Simple Subquery
Without the functionality of subqueries, it would take a couple SQL queries to
retrieve product information for the product with the maximum list price. The first
query would have to find the value of max(prod_list_price). A subsequent query
would have to use the value resolved for max(prod_list_price) to find the product
details. You should take a look at how you can resolve this with a subquery
embedded in the where clause of the main query:
select prod_id, prod_name, prod_category
from products
where prod_list_price = (select max(prod_list_price)
from products);
The subquery is enclosed in parentheses and is part of the where clause. The main
query is resolved based on the results of the subquery; in this case, the maximum
product list price. As you can see, the ability to have a query within a query is very
powerful.
Running SQL queries with embedded subqueries can affect performance. As
your experience with subqueries increases, you will find that you will need to work
closely with your database administrator, more commonly referred to as a DBA, to
optimize statements with subquery processing.
Chapter 4: SQL: Structured Query Language 145
Ask the Expert
Q: What would happen if the subquery returned multiple values?
A: Since the subquery in the example could return only a single value, it was
acceptable for it to be written with the equals (=) operand. If multiple values
are expected from the subquery, the in list operand should be used.
146 Oracle Database 11g: A Beginner’s Guide
Correlated Subqueries with Joins
A correlated subquery is a query that references a column from the main query. In
the example that follows, you are able to first retrieve the average list price for each
product category and then join it back (correlate it) to the product category in the
outer query. You should take a look at the example and its output:
SQL> select substr(prod_category,1,22) Category,
2 substr(prod_name,1,39) Product,
3 prod_list_price List
4 from products p
5 where prod_list_price > (select avg(prod_list_price)
6 from products
7 where p.prod_category = prod_category)
8 order by substr(prod_category,1,22), prod_list_price desc;
CATEGORY PRODUCT LIST
---------------------- --------------------------------------- --------
Electronics Home Theatre Package with DVD-Audio/Vid 599.99
Electronics 8.3 Minitower Speaker 499.99
Electronics Y Box 299.99
Hardware Envoy Ambassador 1299.99
Peripherals and Access 17" LCD w/built-in HDTV Tuner 999.99
Peripherals and Access 18" Flat Panel Graphics Monitor 899.99
Peripherals and Access Model NM500X High Yield Toner Cartridge 192.99
Peripherals and Access SIMM- 16MB PCMCIAII card 149.99
Photo Mini DV Camcorder with 3.5" Swivel LCD 1099.99
Photo 5MP Telephoto Digital Camera 899.99
Software/Other Unix/Windows 1-user pack 199.99
Software/Other Laptop carrying case 55.99
Software/Other DVD-R Discs, 4.7GB, Pack of 5 49.99
Software/Other O/S Documentation Set - English 44.99
Software/Other O/S Documentation Set - German 44.99
Software/Other O/S Documentation Set - French 44.99
Software/Other O/S Documentation Set - Spanish 44.99
Software/Other O/S Documentation Set - Italian 44.99
Software/Other O/S Documentation Set - Kanji 44.99
19 rows selected.
The main query retrieves the Category, Product, and List Price details for each
product that is greater than the average list price of all products within its category.
This wouldn’t be possible without the subquery. Data from the subquery’s product
category is joined with the main query’s product category and referenced by the
main query’s table alias.
Notice as well that the order by exists on the outer query. If it were placed in the
subquery, it wouldn’t work. The displayed results are what you want to order, not
the subquery results.
CRITICAL SKILL 4.11
Use Set Operators: Union, Intersect, Minus
One of the nice things about a relational database is that SQL queries act upon sets
of data versus a single row of data. Oracle provides us with a series of set functions
that can be used to bring data sets together. The set functions will be discussed in
the next few sections using two single column tables: table x and table y. Before
proceeding to the discussion on the set functions, you should first take a look at the
contents of these tables.
Table x:
SQL> select * from x;
COL
---
1
2
3
4
5
6
6 rows selected.
Table y:
SQL> select * from y;
COL
---
5
6
7
3 rows selected.
Union
When you use this operator in SQL*Plus, it returns all the rows in both tables without
any duplicates. This is done by Oracle with a sort operation. In the preceding table
listings, both tables have columns with values of 5 and 6. A closer look at the union
query and resulting output is shown here:
SQL> select * from x
2 union
3 select * from y;
Chapter 4: SQL: Structured Query Language 147
148 Oracle Database 11g: A Beginner’s Guide
COL
---
1
2
3
4
5
6
7
7 rows selected.
Union All
The union all set function is similar to the union query with the exception that it
returns all rows from both tables with duplicates. The following example is a rewrite
of the preceding union example using union all:
SQL> select * from x
2 union all
3 select * from y;
COL
---
1
2
3
4
5
6
5
6
7
9 rows selected.
Intersect
The intersect operator will return all the rows in one table that also reside in the
other. Column values 5 and 6 exist in both the tables. The following example
demonstrates the intersect set function:
SQL> select * from x
2 intersect
3 select * from y;
COL
---
5
6
Projec
NOTE
Please be aware that the intersect set operator can
introduce major performance problems. If you are
venturing down this path, weigh the alternatives
first.
Minus
The minus set function returns all the rows in the first table minus the rows in the
first table that are also in the second table. The order of the tables is important. Pay
close attention to the order of the tables and the different results in these two query
examples:
SQL> select * from x
2 minus
3 select * from y;
COL
---
1
2
3
4
SQL> select * from y
2 minus
3 select * from x;
COL
---
7
Project 4-4 Use the Union Function in Your SQL
During the discussion of Oracle outer joins and the associated Project 4-1, you
learned that a full outer join wasn’t readily available using the Oracle syntax. You
also learned about the union set function, and you took a moment to revisit creating
an outer join without using ANSI SQL syntax.
Step by Step
You should first recall the Oracle right outer join and left outer join examples you
were working on in Project 4-1.
1. Start by using the right outer join example from Project 4-1:
SQL> select a.id, a.desc1, b.id, b.desc2
2 from temp1 a, temp2 b
Chapter 4: SQL: Structured Query Language 149
Use the Union Function in Your SQL
Project 4-4
(continued)
150 Oracle Database 11g: A Beginner’s Guide
3 where a.id = b.id(+);
ID DESC1 ID DESC2
---------- ----- ---------- -----
123 ABCDE
456 FGHIJ 456 ZZZZZ
2. Now, use the left outer join example from Project 4-1:
SQL> select a.id, a.desc1, b.id, b.desc2
2 from temp1 a, temp2 b
3 where a.id(+) = b.id;
ID DESC1 ID DESC2
---------- ----- ---------- -----
456 FGHIJ 456 ZZZZZ
789 MMMMM
3. Now, put the two together with a full outer join using union. ANSI SQL
outer join syntax provided you with a full outer join option that wasn’t
available with Oracle’s standard SQL. With the union set function in your
Oracle tool belt, you have another way to solve this problem. So now, take
the two queries from the recalled examples and union them together:
SQL> select a.id, a.desc1, b.id, b.desc2
2 from temp1 a, temp2 b
3 where a.id = b.id(+)
4 union
5 select a.id, a.desc1, b.id, b.desc2
6 from temp1 a, temp2 b
7 where a.id(+) = b.id;
ID DESC1 ID DESC2
---------- ----- ---------- -----
123 ABCDE
456 FGHIJ 456 ZZZZZ
789 MMMMM
Project Summary
In this project, by combining the right and left outer join Oracle statements together
with a union set operator, you were able to mimic the ANSI SQL full outer join
functionality.
CRITICAL SKILL 4.12
Use Views
Views are database objects that are based on one or more tables. They allow the
user to create a pseudo-table that has no data. The view consists solely of a SQL
query that retrieves specific columns and rows. The data that is retrieved by a view
is presented like a table.
Views can provide a level of security, making only certain rows and columns from
one or more tables available to the end user. You could hide the underlying tables,
CUSTOMERS and SALES, from all the users in our organization and only make
available the data for states they are entitled to see. In the following example, you are
creating a view to show only specific details about Utah-based customer sales:
SQL> create view utah_sales
2 as
3 select c.cust_id ID,
4 substr(cust_last_name,1,20) Name,
5 substr(cust_city,1,20) City,
6 substr(cust_state_province,1,5) State,
7 sum(amount_sold) Total
8 from customers c, sales s
9 where c.cust_id = s.cust_id
10 and cust_state_province = 'UT'
11 group by c.cust_id,
12 substr(cust_last_name,1,20),
13 substr(cust_city,1,20),
14 substr(cust_state_province,1,5);
View created.
The create view statement names the view and then uses keywords as select to
define the select list, tables, and selection criteria that the view will be based upon.
The following code listing issues a desc statement to demonstrate that the view
looks just like a table. Notice that the column names have been changed from their
original ones and were instead created using the column aliases from the select
statement in the preceding view creation DDL:
SQL> desc utah_sales
Name Null? Type
----------------------------------------- -------- -------------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
CITY VARCHAR2(20)
STATE VARCHAR2(5)
TOTAL NUMBER
The view looks like a table as demonstrated by the preceding code listing, so
you should now issue a couple of queries against it. The first one that follows selects
all rows and columns from this view. The second example selects only the name
and total columns for customers whose sales are greater than 20,000. Keep in mind,
this is still only for Utah customers:
SQL> select *
2 from utah_sales;
Chapter 4: SQL: Structured Query Language 151
152 Oracle Database 11g: A Beginner’s Guide
ID NAME CITY STATE TOTAL
---------- -------------------- -------------------- ----- ----------
118 Kuehler Farmington UT 23258.4
392 Eubank Farmington UT 21297.49
411 Vankirk Farmington UT 19279.94
462 Nielley Farmington UT 64509.91
599 Robbinette Farmington UT 11167.65
7003 Bane Farmington UT 62605.42
100207 Campbell Farmington UT 11.99
100267 Desai Farmington UT 240.95
100308 Wilbur Farmington UT 190.96
9 rows selected.
SQL> select name, total
2 from utah_sales
3 where total > 20000;
NAME TOTAL
-------------------- ----------
Kuehler 23258.4
Eubank 21297.49
Nielley 64509.91
Bane 62605.42
It’s easy to see how you could keep certain users in our company from accessing
sales information from more than the states they are granted access to. If this sample
database had sale representatives with assigned territories, one could imagine how
the use of territory-based views could keep one salesperson from viewing the sales
and commissions of another territory representative.
You have demonstrated here that views contain no data. All the data for the
view example in this section resides in the underlying tables. In Chapter 9, we will
introduce you to materialized views: a physical implementation of a view that is
used to improve performance when you have a significant amount of data.
CRITICAL SKILL 4.13
Learn Sequences: Just Simple Stuff
Quite often, primary keys in tables are simply generated numeric values that are
sequential. In the sample database that you’ve used throughout this chapter, cust_id
and prod_id in the CUSTOMERS and PRODUCTS tables are likely candidates for
creation using a sequence.
Sequences are objects in the database that can be used to provide sequentially
generated integers. Without these valuable objects available to users, generating
values sequentially would only be possible through the use of programs.
Sequences are generally created and named by a DBA. Among the attributes
that can be defined when creating a sequence are a minimum value, a maximum
value, a number to increment by, and a number to start with. They are then made
available to the systems applications and users that would need to generate them.
For the following example, you will have established a cust_id_seq sequence,
which increments by one each time it’s called. When you created the sequence,
you specified that 104501 should be the number to start with. For demonstration
purposes, you’ll use the DUAL table to select the next two sequence numbers. More
often than not, an application will retrieve and assign the sequence numbers as
records are inserted into the associated table:
SQL> create sequence cust_id_seq
2 start with 104501;
Sequence created
SQL> select cust_id_seq.nextval
2 from dual;
NEXTVAL
----------
104501
SQL> select cust_id_seq.nextval
2 from dual;
NEXTVAL
----------
104502
CRITICAL SKILL 4.14
Employ Constraints: Linkage to Entity Models,
Types, Deferred, Enforced, Gathering
Exceptions
In the section on joins in this chapter, you were introduced to the concept of primary
and foreign keys. These were, in fact, constraints on the tables. Constraints preserve
the integrity of the database by enforcing business rules.
The primary key for the PROMOTIONS table in the sample schema is an integrity
constraint. It requires that each value in promo_id be unique. You should see what
would happen if you tried to insert a row in this table with a promo_id value that
already exists:
SQL> insert into promotions
2 (promo_id,
3 promo_name,
4 promo_subcategory,
5 promo_subcategory_id,
Chapter 4: SQL: Structured Query Language 153
154 Oracle Database 11g: A Beginner’s Guide
6 promo_category,
7 promo_category_id,
8 promo_cost,
9 promo_begin_date,
10 promo_end_date,
11 promo_total,
12 promo_total_id)
13 values
14 (36,
15 'Thanksgiving Sale',
16 'Newspaper',
17 28,
18 'ad news',
19 4,
20 250,
21 '23-NOV-03',
22 '27-NOV-03',
23 'Promotion Total',
24 5);
insert into promotions
*
ERROR at line 1:
ORA-00001: unique constraint (SH.PROMO_PK) violated
Since the value 36 already existed for promo_id, the unique constraint was
violated when you tried to insert another row in the table with the same value. This
constraint preserved the integrity of the data by enforcing the business rule that
every promotion must be identified uniquely.
Linkage to Entity Models
Many organizations have complex databases and, as a result, they use entity models
to document each system’s database objects and constraints. These models of the
organizations’ database schemas graphically represent the relationships between objects.
The function of database design could be the responsibility of the developer,
DBA, or a database designer. Among other uses, entity-modeling software allows
the database designer to graphically define and link tables to each other. The result
is a data model with tables, columns, primary keys, and foreign keys. Throughout
this chapter, you have issued DDL to create tables. Typically, entity-modeling
software will generate the DDL in a script that can be executed against the
database. This makes the job of defining and maintaining database objects (and
their associated constraints and relationships with each other) a lot easier.
Types
There are a number of different types of integrity constraints. The following is a list
of the integrity constraints that are available in Oracle Database:
■NULL constraints are defined on a single column and dictate whether or
not the column must contain a value. If a column is defined as NOT NULL,
it must contain values in each and every record.
■UNIQUE constraints allow a value in a column to be inserted or updated
providing it contains a unique value.
■PRIMARY KEY constraints require that the key uniquely identifies each
row in the table. The key may consist of one column or a combination
of columns.
■FOREIGN KEY constraints define the relationships between tables. This is
commonly referred to as referential integrity. These are rules that are based
on a key in one table that assure that the values exist in the key of the
referenced table.
■CHECK constraints enable users to define and enforce rules on columns.
Acceptable values are defined for a column and insert, update, and delete
commands are interrogated and are then accepted or rejected based on
whether or not the values are specifically allowed. A separate check
constraint definition is required if the requirement exists to perform either
similar or different checks on more than one column. The following
example illustrates the creation of a table with a single check constraint,
followed by an insert with an acceptable value and an attempted insert
with a disallowed value:
SQL> create table check_constraint_example
2 (col1 char(1)
3 constraint check_col1
4 check (col1 in ('B','G','N')));
Table created.
SQL> insert into check_constraint_example values ('B');
1 row created.
SQL> insert into check_constraint_example values ('C');
insert into check_constraint_example values ('C')
*
ERROR at line 1:
ORA-02290: check constraint (SH.CHECK_COL1) violated
Chapter 4: SQL: Structured Query Language 155
Deferred
When constraints are created, they can be created either as deferrable or not
deferrable. A constraint that is not deferred is checked immediately upon execution
of each statement and, if the constraint is violated, it is immediately rolled back. A
constraint that is deferred will not be checked until a commit statement is issued. This
is useful when inserting rows or updating values that reference other values that do
not exist but are part of the overall batch of statements. By deferring the constraint
checking until the commit is issued, you can complete the entire batch of entries
before determining if there are any constraint violations.
CRITICAL SKILL 4.15
Format Your Output with SQL*Plus
Throughout this chapter, you’ve seen the results of many SQL queries. In some, you
added functions like substr to reduce the size of the columns and keep the results
confined within one line. In SQL*Plus, there are many parameters that can be set
to control how the output is displayed. A list of all of the available settings is easily
obtained by issuing the show all command within SQL*Plus. Alternatively, if
156 Oracle Database 11g: A Beginner’s Guide
Ask the Expert
Q: Is a single space an acceptable entry into a NOT NULL constrained column?
A: Yes. Oracle will allow you to enter a space as the sole character in a NOT
NULL constrained column. Be careful though. The single space will look like
a NULL value when a select statement retrieves and displays this row. The
space is very different than a NULL.
Ask the Expert
Q: Once I set parameters, do I ever have to set them again?
A: Yes. Parameters are good only for the current setting. The parameters
always reset to their default settings when you start up a new SQL*Plus
session. However, the parameter defaults can be overwritten at the start of
each SQL*Plus session by entering and saving them in the login.sql file.
you know the parameter and want to see its current value, the command show
parameter_name will give you the answer. Before we close out this chapter, you
should visit a number of the more useful SQL*Plus parameters.
Page and Line Size
The set linesize command tells Oracle how wide the line output is before wrapping
the results to the next line. To set the line size to 100, enter the command set linesize
100. There is no semicolon required to end set commands.
The set pagesize command determines the length of the page. The default page
size is 14 lines. If you don’t want to repeat the result headings every 14 lines, use
this command. If you want your page to be 50 lines long, issue the command set
pagesize 50.
Page Titles
The ttitle (for top title) command includes a number of options. The default settings
return the date and page number on every page followed by the title text centered
on the next line. Multiple headings can also be produced by separating the text with
the vertical bar character. The command ttitle 'Customer List | Utah' centers the text
“Customer List” on the first line followed by “Utah” on the second line.
Page Footers
The btitle command will center text at the bottom of the page. The command btitle
'sample.sql' places the text “sample.sql” at the bottom center of the output listing.
The command btitle left 'sample.sql' results in the footer text “sample.sql” being
placed at the left edge of the footer.
Formatting Columns
Quite often, you’ll need to format the actual column data. The column command
is used to accomplish this. Suppose you are going to select the last name from the
CUSTOMERS table along with a number of other columns. You know that, by
default, the last name data will take up more space than it needs. The command
column cust_last_name format a12 wrap heading 'Last | Name' tells SQL*Plus that
there should be only 12 characters of the last name displayed and that the column
title “Last Name” should be displayed on two separate lines.
Project 4-5 Format Your SQL Output
Now put these SQL*Plus concepts together and format the output of a SQL query.
The following step-by-step instructions will lead you through a few of these basic
formatting commands.
Format Your SQL Output
Project 4-5
Chapter 4: SQL: Structured Query Language 157
Format Your SQL Output
Project 4-5
(continued)
Step by Step
In this project, you’re going to select some customer and sales information for the
customers from Utah. Now first take a look at our sample SQL query and output
before any formatting kicks in:
SQL> select cust_last_name, cust_city, sum(amount_sold)
2 from customers natural join sales
3 where cust_state_province = 'UT'
4 group by cust_last_name, cust_city;
CUST_LAST_NAME CUST_CITY SUM(AMOUNT_SOLD)
----------------- --------------- ----------------
Bane Farmington 62605.42
Desai Farmington 240.95
Eubank Farmington 21297.49
Wilbur Farmington 190.96
Kuehler Farmington 23258.40
Nielley Farmington 64509.91
Vankirk Farmington 19279.94
Campbell Farmington 11.99
Robbinette Farmington 11167.65
9 rows selected.
The following steps correspond to the set commands in the code listing that
follows them. The original SQL query will also be executed a second time with
much nicer formatting results.
1. Set the page size to 15. (You’ll probably never have such a small page size,
but you’re doing this to illustrate multiple pages with this small result set.)
2. Set the line size to 70.
3. Add a title at the top of the page with “Customer Sales Report” and “Utah
Region” in the first and second lines, respectively.
4. Add a footer with “CONFIDENTIAL REPORT” displayed.
5. Format the last name to be exactly 12 characters long and with a title “Last
Name” listed on two separate lines.
6. Format the city with “City” as the title and the data fixed at 15 characters long.
7. Format the summed amount sold with a two-line title “Total Sales.” Format
the data to include a dollar sign, two digits following the decimal point, and
a comma to denote thousands.
158 Oracle Database 11g: A Beginner’s Guide
SQL> set pagesize 15
SQL> set linesize 64
SQL> ttitle 'Customer Sales Report | Utah Region'
SQL> btitle 'CONFIDENTIAL REPORT'
SQL> column cust_last_name format a12 wrap heading 'Last | Name'
SQL> column cust_city format a15 heading 'City'
SQL> column sum(amount_sold) format $999,999.99 wrap
SQL> column sum(amount_sold) heading 'Total | Sales'
SQL> select cust_last_name, cust_city, sum(amount_sold)
2 from customers natural join sales
3 where cust_state_province = 'UT'
4 group by cust_last_name, cust_city;
Mon Jan 12 page 1
Customer Sales Report
Utah Region
Last Total
Name City Sales
------------ --------------- ------------
Bane Farmington $62,605.42
Desai Farmington $240.95
Eubank Farmington $21,297.49
Wilbur Farmington $190.96
Kuehler Farmington $23,258.40
Nielley Farmington $64,509.91
CONFIDENTIAL REPORT
Mon Jan 12 page 2
Customer Sales Report
Utah Region
Last Total
Name City Sales
------------ --------------- ------------
Vankirk Farmington $19,279.94
Campbell Farmington $11.99
Robbinette Farmington $11,167.65
CONFIDENTIAL REPORT
9 rows selected.
Chapter 4: SQL: Structured Query Language 159
Format Your SQL Output
Project 4-5
Format Your SQL Output
Project 4-5
(continued)
Project Summary
With some simple formatting commands available within SQL*Plus, you were
able to transform the unformatted, difficult-to-read output into a simple and
effective report. SQL*Plus has many formatting options available above and
beyond the few you have seen demonstrated here. As you become more familiar
with SQL and SQL*Plus, take the time to research and try more of the available
formatting options. We think you’ll agree that SQL*Plus is an effective query tool
and report formatter.
Writing SQL*Plus Output to a File
The spool command will save the output to a data file. If your database is on a
Windows operating system, the command spool c:\reports\output.dat would
capture the output of the query execution in the “output.dat” file.
þChapter 4 Mastery Check
1. DDL and DML translate to _________ and ________, respectively.
2. Which of the following descriptions is true about insert statements?
A. Insert statements must always have a where clause.
B. Insert statements can never have a where clause.
C. Insert statements can optionally include a where clause.
3. In addition to the two mandatory keywords required to retrieve data from
the database, there are three optional keywords. Name them.
4. Write a SQL statement to select the customer last name, city, state, and
amount sold for the customer represented by customer ID 100895.
5. Retrieve a list of all product categories, subcategories, names, and list prices
where the list price is greater than $100 while displaying the results for the
product category all in uppercase.
6. Rewrite the query from the previous question and round the amount sold so
that there are no cents in the display of the list prices.
7. Retrieve a list of all customer IDs and last names where the customer has
more than 200 entries in the SALES table in SH schema.
8. Display the product name of all products that have the lowest list price.
160 Oracle Database 11g: A Beginner’s Guide
9. Create a view that contains all products in the Electronics category.
10. Sequences provide __________ generated integers.
11. This referential integrity constraint defines the relationship between two
tables. Name it.
12. Check constraints enable users to define and enforce rules for:
A. One or more tables
B. No more than one column
C. One or more columns
D. Only one table
13. Deferred constraints are not checked until the __________ statement is
issued.
Chapter 4: SQL: Structured Query Language 161
This page intentionally left blank
Chapter
5
PL/SQL
CRITICAL SKILLS
5.1 Define PL/SQL and Learn Why We
Use It
5.2 Describe the Basic PL/SQL Program
Structure
5.3 Define PL/SQL Data Types
5.4 Write PL/SQL Programs in SQL*Plus
5.5 Handle Error Conditions in PL/SQL
5.6 Include Conditions in Your
Programs
5.7 Create Stored Procedures—How
and Why
5.8 Create and Use Functions
5.9 Call PL/SQL Programs
he basic way we access data with Oracle is via SQL. It provides us
with the ability to manage both the database and the information.
However, you generally will find that SQL cannot do everything that
the programmer needs to do. SQL has an inherent lack of procedural
control of the output; it has no array handling, looping constructs,
and other programming language features. PL/SQL can be regarded as an extension
to SQL for fine control of database data processing. To address this need, Oracle
developed Procedural Language for Structured Query Language (PL/SQL), Oracle’s
proprietary programming language.
PL/SQL, Oracle’s contribution to the programming world, is a programming
environment that resides directly in the database. You’ll learn about its architecture
later in this chapter. First, some background about this powerful programming
environment.
PL/SQL first appeared in Oracle Version 6 in 1985. It was primarily used within
Oracle’s user interface product SQL*Forms to allow for the inclusion of complex
logic within the forms; it replaced an odd step-method for logical control. It also
provided a reasonably simple block-structured programming language that resembles
ADA and C. You can use PL/SQL to read your data, perform logical tasks, populate
your database, create stored objects, move data within the database, and even to
create and display web pages. PL/SQL has certainly developed into a mature
technology, and Oracle has shown a very strong dedication to the language, as
illustrated by its use of PL/SQL in many of its products (such as Oracle Applications).
Oracle also uses the web extensions of PL/SQL quite extensively in many other
applications and products. Even products like Oracle Warehouse Builder produce
PL/SQL code to move data from one Oracle data source to another. PL/SQL is
indeed a powerful language that serves all Oracle professionals who need to interact
with the database and the data.
In this chapter, we’ll discuss the basic concepts and constructs of PL/SQL so that
you’ll understand how to create your own PL/SQL programs. There is a lot to cover,
but as important as it is to learn SQL, you will need to know PL/SQL as well; if
you’re looking to become a DBA or an Oracle database developer, you must have
knowledge of PL/SQL in your database toolkit.
CRITICAL SKILL 5.1
Define PL/SQL and Learn Why We Use It
Oracle Database 11gis more than just a database management system—it’s also
an engine for many programming languages. Not only does it serve as a Java
engine with the built-in Java Virtual Machine (JVM), it’s a PL/SQL engine as well.
This means that the code you write may be stored in a database and then run as
required.
164 Oracle Database 11g: A Beginner’s Guide
T
The PL/SQL engine is bundled together with the database, and is an integral part
of the Oracle server, providing you with a powerful language to empower your
logic and data. Let’s look at how PL/SQL fits into the Oracle server. Figure 5-1
shows you how PL/SQL works from both within and without the database.
At the center of the Oracle Database 11gserver in Figure 5-1 is the primary
engine for the database, which serves as the coordinator for all calls to and from the
database. This means that when a call is made from a program to the server to run a
PL/SQL program, the Oracle server loads the compiled program into memory and
then the PL/SQL engine and SQL engine execute the program. The PL/SQL engine
will handle the program’s memory structures and logical program flow and then the
SQL engine issues data requests to the database. It is a closed system and one that
allows for very efficient programming.
PL/SQL is used in numerous Oracle products, including the following:
■Oracle Database Server
■Application Express
■Oracle Data Miner
■Oracle Warehouse Builder
■Oracle eBusiness Suite
■Oracle Portal
Chapter 5: PL/SQL 165
FIGURE 5-1. PL/SQL architecture
166 Oracle Database 11g: A Beginner’s Guide
All of these programs use PL/SQL to some extent. If you look at the internals of
Oracle applications, you’ll see that there can be as many as five million lines of
PL/SQL code contained within it. PL/SQL interfaces can be developed and utilized
from these Oracle development environments:
■SQL*Plus
■Oracle Grid Control/Oracle Enterprise Manager
■Oracle Pre-compilers (such as Pro*C, Pro*COBOL, and so on)
■Oracle Call Interface (OCI)
■Server Manager
■Oracle Application Server 11g
■jDeveloper
As you can see, PL/SQL is well established within Oracle’s line of products. The
reasons for using PL/SQL are primarily its tight integration with the database server
and its ease of use. You will find that there are few tasks that PL/SQL cannot handle.
TIP
Use PL/SQL to program complex tasks or for
program elements that may be used over and
over again.
CRITICAL SKILL 5.2
Describe the Basic PL/SQL Program Structure
The structure you use in PL/SQL is the foundation for the language as a whole. Once
you’ve mastered it, you will then be able to move forward; however, if you do not
take the time to get this first step right, your journey will be difficult. Thankfully, it’s
quite simple.
The structure is quite basic. You will have areas for your program parameters
(these are used to pass values from outside a program to the program itself), your
internal variables, the main program code and logic, and various ways to deal with
problem situations. Let’s look at the basic form of a PL/SQL block:
[DECLARE]
-- Put Variables Here
BEGIN
-- Put Program Here
[EXCEPTION]
-- Put exception handlers here
END;
/
That’s it: the basic structure of every PL/SQL program. When we talk about PL/SQL
programs, they are referred to as PL/SQL blocks. PL/SQL blocks are simply programs
that are complete and that are programmed to run successfully. A PL/SQL program
is comprised of one or more of these blocks, which you can think of as routines. So
at the basic level, you only need one block for a valid PL/SQL program, but as you
consider writing a more complex program, you will find it easiest if each block
addresses a particular task or subtask; it is these structures that you will use in all of
your PL/SQL programs to create the most robust code possible. The PL/SQL block
structures form the basis for any program you shall be writing in PL/SQL. This
chapter builds upon that fundamental form, each section helping you move
toward more complex programs.
In its basic form, you will usually need to declare variables in your PL/SQL program.
It is these variables used in the PL/SQL that hold the declarative or working storage
area (including constants, local program variables, select statements, data arrays,
and such) within your program. These variables are then available for use in your
program. So if you need a counter, a data array, data variables, or even Boolean
variables, you will declare them here.
Next is the program body or executable section. It is the only section you really
need to include in your PL/SQL block, since you could write a program without
variables or exception handling. It is in this section that you build your program
logic and database access. That is why you must always remember BEGIN and
END; these are your PL/SQL bookends. It is between these two lines that your
program logic is contained.
The final section is the exception section. It is within this section that you will
find all the error handling needed for your program. This section is an optional
portion of the PL/SQL block. However, it is recommended that all programs include
the use of exception handling to ensure a controlled run of your programs.
Chapter 5: PL/SQL 167
Ask the Expert
Q: What are the only lines of the PL/SQL block that are required to create a
functional program?
A: The only lines of the basic PL/SQL block that are required to create a
functional program are BEGIN and END.
CRITICAL SKILL 5.3
Define PL/SQL Data Types
The use of local variables within a PL/SQL program is an important knowledge point
for everyone using the language. It is a basic component of each program, and as
such, it is invaluable for gaining the knowledge of what is available and how best to
use it. You can now look at how you use and define variables and working storage
within your PL/SQL programs.
As with all programming languages, there are characters that you use to write
your programs. Each language has its own rules and restrictions when it comes to
the valid characters. In the following sections, we will show you
■Valid characters when programming in PL/SQL
■Arithmetic operators
■Relational operators
Valid Characters
When programming in PL/SQL, you may use only characters as defined here:
■Characters can be typed in either upper- or lowercase. PL/SQL is case
insensitive.
■All digits between 0 and 9.
■The following symbols: ( ) + - * / < > = ! ~ ; : . @ % , " ' # ^ & _ | { } ? [ ]
Some of these characters are for program commands; others serve as relational
or arithmetic operators. Together they form a program.
Arithmetic Operators
Table 5-1 shows the common arithmetic operators used in PL/SQL. They are listed
in the order of precedence in which they are executed (that is, by priority). When
the functions appear in the same line, it means that they are executed with the same
168 Oracle Database 11g: A Beginner’s Guide
Operator Meaning
** Exponent
*,/ Multiplication, Division
+,-, || Addition, Subtraction, Concatenation
TABLE 5-1. Arithmetic Operators
level of precedence, so the position of the expression determines which goes first in
the operational execution.
Table 5-2 shows the common relational operators used in PL/SQL. These are the
logical variables that are used to compare data. As with any comparison, they need
to conform to logic hierarchies, especially when using multiple operators in your
conditional clauses.
The use of variables in a PL/SQL program is usually something that is required to
truly leverage the power of the language. It is here that you define how your data is
to be held while you work it through your program. These variables can be the
same types you have already learned about in the SQL language. However, in
addition to these standard data types, there are some special ones that have been
created specifically for the PL/SQL language.
NOTE
All SQL within Oracle is supported directly
with PL/SQL.
One of the important features of Oracle Database 11gis the tight integration
of the SQL and PL/SQL engines into one system. This means that from Oracle9i
forward you can run the same commands in PL/SQL that you use in SQL. This was
not true in earlier versions of PL/SQL, so take care if using these versions (it may be
time to consider an upgrade). That would also qualify you as more than a beginner.
Now you can move on to the most common data types that you will use when
writing PL/SQL programs:
■varchar2
■number
■date
■Boolean
Chapter 5: PL/SQL 169
Operator Meaning
= Equal
<> or != Not equal
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
TABLE 5-2. Relational Operators
You’ll use these variables in PL/SQL the same way that you would use them in
SQL. Assigning values to variables is very important when programming in PL/SQL
or any other programming language. You can assign values to variables in any
section of your program code, and you can also assign values to variables in the
declare section. Defining your variable in the declare section is done to initialize
values in advance of their use in the program or to define values that will be used as
constants in your program. To assign a value to a variable in the declaration section,
you would use the following format:
Variable_name variable_type := value;
NOTE
The important item that you should notice here is
that you use the := to assign a value. This is the
standard used in PL/SQL.
You may also define variable values in the execution and exception sections of
your PL/SQL program. Within the program, you would use the following format to
assign a value to a variable:
Variable_name := value;
To assign values to variables you use, let’s look at a small program that assigns
values in each section of a program:
-- Declaration section of the program
declare
l_counter number := 0; -- initiate value to 0
l_today date := sysdate; -- assign system date to variable
l_name varchar2(50); -- variable is defined but has no value
l_seq_val number := ian_seq.nextval; -- assigns the next sequence
value to the variable
--
-- Execution section of the program
begin l_counter := l_counter + 1; -- add one to current value of counter
l_name := 'LUCY THE CAT'; -- set a value to character variable
-- Error (EXCEPTION) handling section of the program
exception
-- Generic error handling to handle any type of error
when others then
-- print out an error message
raise_application_error (-20100, 'error#' || sqlcode || ' desc: ' sqlerrm)
end;
170 Oracle Database 11g: A Beginner’s Guide
Chapter 5: PL/SQL 171
NOTE
Oracle has some special variables that may be used
in a PL/SQL program. In the example, we used the
sqlcode and sqlerrm variables. These variables
represent the Oracle error number and the Oracle
error message respectively. You may use these
variables to capture Oracle errors in your program.
The varchar2 Data Type
varchar2 is a variable-length alphanumeric data type. In PL/SQL, it may have a
length up to 32,767 bytes. When you define the varchar2 variable in the declare
section, remember to terminate the line with a semicolon (;). The following is the
form of varchar2 variable declarations:
Variable_name varchar2(max_length);
where the max_length is a positive integer, as in
l_name varchar2(30);
You may also set an initial or default value for the variable. This is done on the
same line as the variable declaration in the declare section of your program. You
can do this by using the following syntax:
L_name varchar2(30) := 'ABRAMSON';
The preceding statement will set that value of the variable L_name to the value of
ABRAMSON.
The Number Data Type
The number data type is used to represent all numeric data. The format of the
declaration is
Number_field number(length, decimal_places);
where the length can be from 1 to 38 numerical positions, and decimal_places
represents the positions for numerical precision of the decimal place for the
variable. Keep this in mind when you define your numerical variable, as in
L_average_amount number(12,2);
This describes a variable that may hold up to ten digits (Length(12) - decimal_
places(2)) and up to two decimal places. This means the variable may hold a
number up to a value of 9,999,999,999.99. The number data type has a number of
supported subtypes. These include DECIMAL, FLOAT, REAL, and others. These are
quite familiar to people who use other languages to program for their business needs.
The Date Data Type
The date data type variable is used to store date and datetime values. The following
is the format of the date declaration:
Date_variable date;
By default, Oracle displays values using the format DD-MON-YY. So a value of
14-JAN-08 would be the equivalent of saying January 14, 2008. When programming
in PL/SQL, you should always use this data type when performing date manipulation.
It is possible when combining this data type with some built-in Oracle functions to
extend the flexibility of your date manipulations. For example, let’s say that you
create a variable for a start date and you want to place values into this variable.
Let’s see how this may be done:
Declare
L_start_date date;
Begin L_start_date := '29-SEP-05'; -- Sets variable
-- to September 29, 2009.
L_start_date := to_date('29-SEP-2083 ', 'DD-MON-YYYY');
-- Sets variable to September 29, 2083
L_start_date := to_date('09-JUN-91:13:01 ', 'DD-MON-YY:HH24:MI');
-- Sets variable to June 9, 1991, 1:01 p.m.
End;
So here you have set the date variable in three different forms. The first is the
simplest, while the second is more complex (since it uses the to_date function),
though it does allow for more flexible data declarations, since you can use a
four-digit year definition. The final example shows you how to put a datetime into
the variable. Again you’ll use the to_date function, but you’ll include the time in the
value and then define it with the date mask definition.
NOTE
For more information on other Oracle built-in
functions, see “Oracle Database SQL Reference,”
in the Oracle Database 11gdocumentation.
You should also familiarize yourself with the variations of the date data type.
This includes the timestamp data type, which provides additional date support that
may not be available with the simple date data type.
172 Oracle Database 11g: A Beginner’s Guide
The Boolean Data Type
The final basic data type we will discuss is the Boolean data type. Simply put, this
variable will hold a value of either true or false.When you use this data type, you
must test its status and then do one thing if it is true and another if it is false. You
can use a Boolean expression to compare arithmetic expressions or character
expressions. So, if you have the following arithmetic values, you’ll get
L_record_goals := 91;
L_season_goals := 77;
-- Therefore the following expression will be true
L_record_goals > l_season_goals
-- However the next is false
l_record_goals <= l_season_goals
If you wish to compare character strings, the same may be done. Here’s an example:
l_Cognos_developer := 'Falcon';
l_Oracle_dba := 'Ruxpinnah';
-- The following expression will be true in a true Boolean value
l_Cognos_developer <> l_oracle_dba
It is important to understand that these comparisons provide Boolean results that
may then be used during conditional program control, so you should take the time
to know the difference between true and false.There are numerous other data types,
but by mastering these simple ones you can already build some complex programs.
In C++ and other languages, Booleans can be represented as either true/false or 1/0.
In PL/SQL, the value is assigned only true or false.
Chapter 5: PL/SQL 173
Ask the Expert
Q: How do you let Oracle set the definition of a variable within PL/SQL
programs based on a table’s column definition?
A: The use of dynamic variable definitions based on column definitions is a
very important feature that you should always utilize within PL/SQL. This ties
the variable definition to a table within the database. When defining your
variable, use the name of the table, the column, and the special string of
%TYPE. The following is an example of using the product table’s prod_id
as a variable data definition:
v_product_id products.prod_id%TYPE
By using the %TYPE variable type, we have freed our program of the need
to ever redefine this field. So if the column’s definition changes, so will the
variable within your program.
Progress Check
1. Name four programs or facilities where you can use PL/SQL.
2. Name three sections that may be contained in a PL/SQL block.
3. What is the only required section in a PL/SQL block?
4. What data type would you use to store each of the following?
A. 12344.50
B. True
C. April 11, 1963
D. “PINK FLOYD”
E. 42
CRITICAL SKILL 5.4
Write PL/SQL Programs in SQL*Plus
When you write PL/SQL programs, you have a couple of options for how to run a
program. A program may be run directly in SQL*Plus (or some other SQL environment
such as SQL Developer), or it can be stored in the database and then run from a
174 Oracle Database 11g: A Beginner’s Guide
Progress Check Answers
1. Any four from among the following would be acceptable answers: Oracle Forms, Reports,
Warehouse Builder, Oracle Applications, Oracle Portal, SQL*Plus, Oracle Grid Control, Oracle
Pre-compilers, and Oracle Application Server.
2. The three sections that may be contained in a PL/SQL block are the Declaration, Execution, and
Exception sections.
3. The Execution section is the only required section in a PL/SQL block.
4. The data types used to store each of the variables would be
A. Number or number(8,2). The storage of a number should always be done in a number data
type. You can specify the precision or simply define it as a number with no precision, when you
do not know the exact nature of your data.
B. Boolean. The boolean data type is used to store true and false information.
C. Date. The date data type stores date and time information.
D. Varchar2(10). Character values should be stored in the varchar2 data type. This is more
effective for storing the data, yet it has a limit of 4000 bytes. If you need more than 4000 bytes,
you should then use the LONG data type, which allows you to store up to 2GB of data.
E. Number or number(2). These are the preferred data types for numbers when no decimal places
are required (integers).
Chapter 5: PL/SQL 175
SQL environment or a program. When you store a program in the database, it’s
called a stored program or stored object. (We’ll cover this later in the chapter.) For
now, let’s discuss how to write a program using SQL*Plus. While we illustrate this
example from within the SQL*Plus environment, these programs may also be run
using other SQL interfaces such as SQL Developer, TOAD, or any other product that
you may prefer.
When first writing a program, you can create and modify it using the command
line in SQL*Plus. To do this:
1. Log into SQL*Plus. Depending on your environment you may need to
issue a sqlplus command on the command line. You should note that
with Oracle Database 11g, the Windows version of SQL*Plus has been
deprecated, so if you wish you may also perform this exercise using
SQL Developer.
2. Type in your program via the command line.
3. For each line you write, press ENTER to get to the next line.
4. When you finish typing in your program, remember to terminate it with a “/”
character. This tells Oracle to run the program that you just finished entering.
5. Monitor Oracle to see if your program ran successfully. If your program
does run without errors (syntax errors), you will see the message “PL/SQL
procedure successfully completed.” If you see anything else, this is an
indication that an error occurred.
6. To see the errors created by your program (if you have created a stored
object as we will discuss later in this chapter), type in Show errors.
SQL*Plus will display the errors it encountered during the current run
of your program.
7. Should you receive an error, you will need to edit your program. If your
SQL*Plus and Oracle environment is set up correctly, you should be
able to simply enter edit on the command line and your program will
be loaded into an editor where you can then fix it. Once you exit the
editor, the program will be reloaded into the SQL buffer and may be
run again.
Now let’s move on to an illustration for how to construct a PL/SQL program and
get some output of our results.
176 Oracle Database 11g: A Beginner’s Guide
Project 5-1 Create a PL/SQL Program
This will be the first PL/SQL program that you will create. The concept is straightforward;
you will first declare some variables, place some values into them, and then output
the data to the screen with SQL*Plus.
Step by Step
1. Log into SQL*Plus.
2. At the SQL> prompt, enter the serveroutput command: set serveroutput on;.
3. Enter the following PL/SQL program:
Declare
L_start_date date;
Begin L_start_date := '29-SEP-2005';
dbms_output.putline (l_start_date); --show date
End;
/
4. You should now see the following output on your screen:
SQL> /
29-SEP-05
PL/SQL procedure successfully completed.
5. You have now completed your first PL/SQL program.
Ask the Expert
Q: How can I get feedback/output from my PL/SQL programs?
A: Oracle provides a built-in package named dbms_output for this purpose.
Oracle supplies many of these packages that provide users with additional
functionality, such as outputting data to the screen. By placing the
dbms_output.put_line command into your programs, Oracle PL/SQL can
then provide information to the user as shown in this chapter’s examples.
To actually see this information, you must enable screen output by entering
SET SERVEROUTPUT ON at the SQL> prompt before executing your PL/SQL
routine.
6.