Oracle Database 11g The Complete Reference P1

Chia sẻ: Vong Phat | Ngày: | Loại File: PDF | Số trang:40

Thêm vào BST

Báo xấu

126
lượt xem 17
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

This book is divided into eight major sections. In Part I, “Critical Database Concepts,” you will see an overview of Oracle Database 11g’s options, how to install the Oracle software, how to create or upgrade a database, and advice on planning your application implementation. These chapters establish the common vocabulary that both end users and developers can use to coherently and intelligently share concepts and ensure the success of any development effort.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle Database 11g The Complete Reference P1

® 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