®
Oracle Database 11g :
The Complete Reference
This page intentionally left blank
®
Oracle Database 11g :
The Complete Reference
Kevin Loney
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 system,
without the prior written permission of the publisher.
ISBN: 978-0-07-159876-7
MHID: 0-07-159876-6
The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-159875-0, MHID: 0-07-159875-8.
All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trademarked name,
we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of infringement of the trademark. Where
such designations appear in this book, they have been printed with initial caps.
McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate training
programs. To contact a representative please visit the Contact Us page at www.mhprofessional.com.
Information has been obtained by Publisher from sources believed to be reliable. However, because of the possibility of human or mechanical
error by our sources, Publisher, or others, Publisher does not guarantee to the accuracy, adequacy, or completeness of any information
included in this work and is not responsible for any errors or omissions or the results obtained from the use of such information.
Oracle Corporation does not make any representations or warranties as to the accuracy, adequacy, or completeness of any information
contained in this Work, and is not responsible for any errors or omissions.
TERMS OF USE
This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and to the work. Use
of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve one copy of the
work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon, transmit, distribute,
disseminate, sell, publish or sublicense the work or any part of it without McGraw-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, INCLUDING ANY
INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DIS-
CLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF
MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the
functions contained in the work will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor
its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or for any damages
resulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under no circumstances
shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that result from
the use of or inability to use the work, even if any of them has been advised of the possibility of such damages. This limitation of liability shall
apply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise.
This book is dedicated to my family and friends.
You are a blessing indeed.
About the Author
Kevin Loney is an internationally recognized expert in the design, development,
administration, and tuning of Oracle databases. An enterprise database architect
in the financial industry, he was named Consultant of the Year by Oracle
Magazine in 2002. His best-selling books include Oracle Database 11g DBA
Handbook, Oracle Advanced Tuning and Administration, and Oracle SQL &
PL/SQL Annotated Archives. He is the author of numerous technical articles in
industry magazines and presents at Oracle user conferences in North America
and Europe, where he is regularly among the highest-rated presenters.
About the Contributors
Scott Gossett (contributing author, technical editor) is a technical director in the
Oracle Advanced Technologies Solutions organization with more than 20 years
experience specializing in RAC, performance tuning, and high-availability
databases. Prior to becoming a technical director, Scott was a senior principal
instructor for Oracle Education for over 12 years, primarily teaching Oracle
internals, performance tuning, RAC, and database administration. In addition,
Scott is one of the architects and primary authors of the Oracle Certified Master
exam. Scott has been a technical editor for nine Oracle Press books.
Sreekanth Chintala (technical editor) is an OCP-certified DBA, has been using
Oracle technologies for 10+ years, and has more than 15 years of IT experience.
Sreekanth specializes in Oracle high availability, disaster recovery, and grid
computing. Sreekanth is an author of many technical white papers and a
frequent speaker at Oracle OpenWorld, IOUG, and local user group meetings.
Sreekanth is active in the Oracle community and is the current web seminar
chair for the community-run Oracle Real Application Clusters Special Interest
Group (www.ORACLERACSIG.org).
Contents at a Glance
PART I
Critical Database Concepts
1 Oracle Database 11g Architecture Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2 Installing Oracle Database 11g and Creating a Database ................... 11
3 Upgrading to Oracle Database 11g ................................... 21
4 Planning Oracle Applications—Approaches, Risks, and Standards . . . . . . . . . . . . . . 31
PART II
SQL and SQL*Plus
5 The Basic Parts of Speech in SQL ..................................... 65
6 Basic SQL*Plus Reports and Commands ................................ 91
7 Getting Text Information and Changing It . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
8 Searching for Regular Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
9 Playing the Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
10 Dates: Then, Now, and the Difference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
11 Conversion and Transformation Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
12 Grouping Things Together . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
13 When One Query Depends upon Another . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
14 Some Complex Possibilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
15 Changing Data: insert, update, merge, and delete . . . . . . . . . . . . . . . . . . . . . . . . 257
16 DECODE and CASE: if, then, and else in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
17 Creating and Managing Tables, Views, Indexes, Clusters, and Sequences . . . . . . . 293
18 Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323
19 Basic Oracle Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
vii
PART III
Beyond the Basics
20 Advanced Security—Virtual Private Databases ........................... 355
21 Advanced Security: Transparent Data Encryption ......................... 367
22 Working with Tablespaces .......................................... 375
23 Using SQL*Loader to Load Data ...................................... 385
24 Using Data Pump Export and Import .................................. 399
25 Accessing Remote Data ............................................ 417
26 Using Materialized Views ........................................... 429
27 Using Oracle Text for Text Searches ................................... 449
28 Using External Tables .............................................. 467
29 Using Flashback Queries ........................................... 483
30 Flashback—Tables and Databases ..................................... 493
31 SQL Replay ...................................................... 501
PART IV
PL/SQL
32 An Introduction to PL/SQL .......................................... 513
33 Online Application Upgrades ........................................ 535
34 Triggers ......................................................... 549
35 Procedures, Functions, and Packages .................................. 569
36 Using Native Dynamic SQL and DBMS_SQL ............................ 589
37 PL/SQL Tuning ................................................... 597
PART V
Object-Relational Databases
38 Implementing Object Types, Object Views, and Methods ................... 611
39 Collectors (Nested Tables and Varying Arrays) ........................... 625
40 Using Large Objects ............................................... 639
41 Advanced Object-Oriented Concepts .................................. 665
PART VI
Java in Oracle
42 An Introduction to Java ............................................ 683
43 JDBC Programming ............................................... 699
44 Java Stored Procedures ............................................. 709
Contents at a Glance ix
Hitchhiker’s Guides
45 The Hitchhiker’s Guide to the Oracle Data Dictionary .................... 723
46 The Hitchhiker’s Guide to Tuning Applications and SQL ................... 769
47 SQL Result Cache and Client-Side Query Cache .......................... 811
48 Case Studies in Tuning ............................................. 823
49 Advanced Architecture Options—DB Vault, Content DB, and Records DB ..... 835
50 Oracle Real Application Clusters ..................................... 847
51 The Hitchhiker’s Guide to Database Administration ....................... 857
52 The Hitchhiker’s Guide to XML in Oracle ............................... 883
PART VIII
Alphabetical Reference ............................................. 899
Index .......................................................... 1295
This page intentionally left blank
Contents
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix
PART I
Critical Database Concepts
1 Oracle Database 11g Architecture Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Databases and Instances . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Inside the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Storing the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Guarding the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Programmatic Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Choosing Architectures and Options ...................................... 9
2 Installing Oracle Database 11g and Creating a Database ...................... 11
Overview of Licensing and Installation Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Using OUI to Install the Oracle Software . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
3 Upgrading to Oracle Database 11g . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Choosing an Upgrade Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Before Upgrading . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Running the Pre-Upgrade Information Tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Using the Database Upgrade Assistant . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Performing a Manual Direct Upgrade . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Using Export and Import . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Export and Import Versions to Use . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Performing the Upgrade . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Using the Data-Copying Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
4 Planning Oracle Applications—Approaches, Risks, and Standards . . . . . . . . . . . . . . . . . 31
The Cooperative Approach . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Everyone Has “Data” . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
The Familiar Language of Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Tables of Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Structured Query Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
xi
xii Oracle Database 11g: The Complete Reference
A Simple Oracle Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Why It Is Called “Relational” . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Some Common, Everyday Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
What Are the Risks? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
The Importance of the New Vision . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Changing Environments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Codes, Abbreviations, and Naming Standards ......................... 43
How to Reduce the Confusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
English Names for Tables and Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
English Words for the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Capitalization in Names and Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Normalizing Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Good Design Has a Human Touch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Understanding the Application Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Outline of Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Understanding the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
The Atomic Data Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
The Atomic Business Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
The Business Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Data Entry .................................................... 58
Query and Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
Toward Object Name Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Level-Name Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Singular Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Brevity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Object Name Thesaurus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Intelligent Keys and Column Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
The Commandments .................................................. 62
PART II
SQL and SQL*Plus
5 The Basic Parts of Speech in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Style . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Creating the NEWSPAPER Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Using SQL to Select Data from Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
select, from, where, and order by . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Logic and Value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Single-Value Tests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
LIKE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Simple Tests Against a List of Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Combining Logic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Another Use for where: Subqueries ....................................... 82
Single Values from a Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Lists of Values from a Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Combining Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Creating a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Expanding the View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Contents xiii
6 Basic SQL*Plus Reports and Commands ................................... 91
Building a Simple Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
remark . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
set headsep . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
ttitle and btitle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
break on . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
compute avg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
set linesize . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
set pagesize . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
set newpage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
spool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
/* */ ......................................................... 102
Some Clarification on Column Headings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Other Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Command Line Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
set pause ..................................................... 106
save . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
store . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Editing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Adding SQL*Plus Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
start . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Checking the SQL*Plus Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Building Blocks ...................................................... 111
7 Getting Text Information and Changing It . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
What Is a String? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Notation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Concatenation ( || ) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
How to Cut and Paste Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
RPAD and LPAD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
LTRIM, RTRIM, and TRIM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Combining Two Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Using the TRIM Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Adding One More Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
LOWER, UPPER, and INITCAP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
LENGTH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
SUBSTR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
INSTR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
ASCII and CHR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Using order by and where with String Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
SOUNDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
National Language Support ....................................... 136
Regular Expression Support ....................................... 137
Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
8 Searching for Regular Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Search Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
xiv Oracle Database 11g: The Complete Reference
REGEXP_SUBSTR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
REGEXP_INSTR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
REGEXP_LIKE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
REPLACE and REGEXP_REPLACE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
REGEXP_COUNT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152
9 Playing the Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
The Three Classes of Number Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Notation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Single-Value Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Addition (+), Subtraction (–), Multiplication (*), and Division (/) . . . . . . . . . . . . 157
NULL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
NVL: NULL-Value Substitution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
ABS: Absolute Value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
CEIL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
FLOOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
MOD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
POWER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
SQRT: Square Root . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
EXP, LN, and LOG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
ROUND and TRUNC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
SIGN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
SIN, SINH, COS, COSH, TAN, TANH, ACOS, ATAN, ATAN2, and ASIN ..... 164
Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
NULLs in Group-Value Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
Examples of Single- and Group-Value Functions . . . . . . . . . . . . . . . . . . . . . . . . 165
AVG, COUNT, MAX, MIN, and SUM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Combining Group-Value and Single-Value Functions . . . . . . . . . . . . . . . . . . . . 167
STDDEV and VARIANCE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
DISTINCT in Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
List Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
Finding Rows with MAX or MIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Precedence and Parentheses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
10 Dates: Then, Now, and the Difference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Date Arithmetic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
SYSDATE, CURRENT_DATE, and SYSTIMESTAMP . . . . . . . . . . . . . . . . . . . . . . 176
The Difference Between Two Dates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Adding Months . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Subtracting Months . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
GREATEST and LEAST ........................................... 179
NEXT_DAY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
LAST_DAY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
MONTHS_BETWEEN Two Dates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Combining Date Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
ROUND and TRUNC in Date Calculations ................................. 183
TO_DATE and TO_CHAR Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
The Most Common TO_CHAR Error . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
NEW_TIME: Switching Time Zones ................................. 189
TO_DATE Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
Contents xv
Dates in where Clauses ................................................ 192
Dealing with Multiple Centuries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Using the EXTRACT Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
Using the TIMESTAMP Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
11 Conversion and Transformation Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Elementary Conversion Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Automatic Conversion of Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
A Warning About Automatic Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
Specialized Conversion Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Transformation Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
TRANSLATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
DECODE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
12 Grouping Things Together . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
The Use of group by and having . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Adding an order by . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Order of Execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
Views of Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
Renaming Columns with Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
The Power of Views of Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Using order by in Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Logic in the having Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218
Using order by with Columns and Group Functions . . . . . . . . . . . . . . . . . . . . . 220
Join Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
More Grouping Possibilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
13 When One Query Depends upon Another . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
Advanced Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
Correlated Subqueries ........................................... 224
Coordinating Logical Tests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
Using EXISTS and Its Correlated Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Pre-Oracle9i Syntax for Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Current Syntax for Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Replacing NOT IN with an Outer Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232
Replacing NOT IN with NOT EXISTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Natural and Inner Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
UNION, INTERSECT, and MINUS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
IN Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Restrictions on UNION, INTERSECT, and MINUS ...................... 239
14 Some Complex Possibilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Complex Groupings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Using Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Using ROLLUP, GROUPING, and CUBE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Family Trees and connect by ............................................ 248
Excluding Individuals and Branches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Traveling Toward the Roots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
The Basic Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
xvi Oracle Database 11g: The Complete Reference
15 Changing Data: insert, update, merge, and delete . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
insert . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
Inserting a Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
insert with select . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
Using the APPEND Hint to Improve insert Performance . . . . . . . . . . . . . . . . . . 260
rollback, commit, and autocommit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
Using savepoints ............................................... 262
Implicit commit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
Auto rollback . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
Multitable Inserts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
delete . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
update . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268
update with Embedded select . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
update with NULL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270
Using the merge Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270
Handling Errors ...................................................... 273
16 DECODE and CASE: if, then, and else in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
if, then, else . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
Replacing Values via DECODE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281
DECODE Within DECODE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282
Greater Than and Less Than in DECODE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
Using CASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286
Using PIVOT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
17 Creating and Managing Tables, Views, Indexes, Clusters, and Sequences . . . . . . . . . . 293
Creating a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294
Character Width and NUMBER Precision . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Rounding During Insertion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
Constraints in create table ........................................ 299
Designating Index Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
Naming Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Dropping Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
Altering Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
The Rules for Adding or Modifying a Column . . . . . . . . . . . . . . . . . . . . . . . . . . 305
Creating Read-Only Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
Altering Actively Used Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
Creating Virtual Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
Dropping a Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307
Creating a Table from a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
Creating an Index-Organized Table ....................................... 310
Creating a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311
Stability of a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311
Using order by in Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Creating a Read-Only View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Creating an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314
Enforcing Uniqueness . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
Creating a Unique Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
Creating a Bitmap Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
When to Create an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316
Contents xvii
Creating Invisible Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
Variety in Indexed Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
How Many Indexes to Use on a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Placing an Index in the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Rebuilding an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319
Function-Based Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319
Clusters ............................................................ 320
Sequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321
18 Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323
Creating a Partitioned Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324
List Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326
Creating Subpartitions ................................................. 327
Creating Range and Interval Partitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
Indexing Partitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329
Managing Partitioned Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329
19 Basic Oracle Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
Users, Roles, and Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332
Creating a User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332
Password Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333
Standard Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
Format for the grant Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
Revoking Privileges ............................................. 338
What Users Can Grant . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
Moving to Another User with connect . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340
create synonym . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343
Using Ungranted Privileges ....................................... 343
Passing Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343
Creating a Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345
Granting Privileges to a Role ...................................... 345
Granting a Role to Another Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346
Granting a Role to Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346
Adding a Password to a Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347
Removing a Password from a Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347
Enabling and Disabling Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
Revoking Privileges from a Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
Dropping a Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
Granting UPDATE to Specific Columns .............................. 349
Revoking Object Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
Security by User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
Granting Access to the Public . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351
Granting Limited Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352
PART III
Beyond the Basics
20 Advanced Security—Virtual Private Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
Initial Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356
Create an Application Context . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
Create a Logon Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
xviii Oracle Database 11g: The Complete Reference
Create a Security Policy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360
Apply the Security Policy to Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361
Test VPD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361
How to Implement Column-Level VPD .................................... 363
How to Disable VPD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363
How to Use Policy Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
21 Advanced Security: Transparent Data Encryption . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
Transparent Data Encryption of Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368
Setup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368
Additional Setup for RAC Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369
Opening and Closing the Wallet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370
Encrypting and Decrypting Columns ................................ 370
Encrypting a Tablespace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371
Setup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372
Creating an Encrypted Tablespace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372
22 Working with Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
Tablespaces and the Structure of the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376
Tablespace Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376
RECYCLEBIN Space in Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378
Read-Only Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379
nologging Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
Temporary Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
Tablespaces for System-Managed Undo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
Bigfile Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381
Encrypted Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381
Supporting Flashback Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381
Transporting Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382
Planning Your Tablespace Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382
Separate Active and Static Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382
Separate Indexes and Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382
Separate Large and Small Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
Separate Application Tables from Core Objects . . . . . . . . . . . . . . . . . . . . . . . . 383
23 Using SQL*Loader to Load Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
The Control File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386
Loading Variable-Length Data ..................................... 387
Starting the Load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388
Logical and Physical Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391
Control File Syntax Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392
Managing Data Loads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394
Repeating Data Loads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394
Tuning Data Loads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395
Direct Path Loading . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396
Additional Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398
24 Using Data Pump Export and Import . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399
Creating a Directory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400
Data Pump Export Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400
Starting a Data Pump Export Job . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403
Contents xix
Stopping and Restarting Running Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
Exporting from Another Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405
Using EXCLUDE, INCLUDE, and QUERY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405
Data Pump Import Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
Starting a Data Pump Import Job . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 410
Stopping and Restarting Running Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411
EXCLUDE, INCLUDE, and QUERY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412
Transforming Imported Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412
Generating SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
25 Accessing Remote Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417
Database Links . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418
How a Database Link Works ...................................... 418
Using a Database Link for Remote Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419
Using a Database Link for Synonyms and Views . . . . . . . . . . . . . . . . . . . . . . . . 420
Using a Database Link for Remote Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . 421
Syntax for Database Links . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 421
Using Synonyms for Location Transparency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 424
Using the User Pseudo-Column in Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 425
26 Using Materialized Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429
Functionality ........................................................ 430
Required System Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 430
Required Table Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431
Read-Only vs. Updatable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431
create materialized view Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432
Types of Materialized Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
RowID vs. Primary Key–Based Materialized Views . . . . . . . . . . . . . . . . . . . . . . 436
Using Prebuilt Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436
Indexing Materialized View Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436
Using Materialized Views to Alter Query Execution Paths ...................... 437
Using DBMS_ADVISOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438
Refreshing Materialized Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441
What Kind of Refreshes Can Be Performed? . . . . . . . . . . . . . . . . . . . . . . . . . . . 441
Fast Refresh with CONSIDER FRESH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444
Automatic Refreshes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444
Manual Refreshes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445
create materialized view log Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446
Altering Materialized Views and Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 448
Dropping Materialized Views and Logs .................................... 448
27 Using Oracle Text for Text Searches ...................................... 449
Adding Text to the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450
Text Queries and Text Indexes ........................................... 451
Text Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452
Available Text Query Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452
Searching for an Exact Match of a Word . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453
Searching for an Exact Match of Multiple Words ....................... 454
Searching for an Exact Match of a Phrase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457
Searches for Words That Are Near Each Other . . . . . . . . . . . . . . . . . . . . . . . . . 458
Using Wildcards During Searches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459
Copyright by webtailieu.net