Oracle Database 10g The Complete Reference

Oracle Database 10g The Complete Reference

by Kevin Loney
Released September 2004
Publisher(s): McGraw Hill Computing
ISBN: 9780071770491

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Get a thorough understanding of Oracle Database 10g from the most comprehensive Oracle database reference on the market, published by Oracle Press. From critical architecture concepts to advanced object-oriented concepts, this powerhouse contains nearly 50 chapters designed to enlighten you. Upgrade from earlier versions, use SQL, SQL Plus, and PL/SQL. Get code examples and access popular documentation PDFs--plus a full electronic copy of the book on the included CD-ROM. Go beyond the basics and learn security, text searches, external tables, using Java in Oracle, and a great deal more.

Table of contents

  1. Cover Page
  2. Oracle Database 10g: The Complete Reference
  3. Copyright Page
  4. Dedication
  5. About the Author
  6. Contents
  7. Acknowledgements
  8. Introduction
  9. Part I Critical Database Concepts
    1. 1 Oracle Database 10g Architecture Options
      1. Databases and Instances
      2. Inside the Database
      3. Choosing Architectures and Options
    2. 2 Installing Oracle Database 10g and Creating a Database
      1. Overview of Licensing and Installation Options
    3. 3 Upgrading to Oracle Database 10g
      1. Choosing an Upgrade Method
      2. Before Upgrading
      3. Using the Database Upgrade Assistant
      4. Performing a Manual Direct Upgrade
      5. Using Export and Import
      6. Using the Data-Copying Method
      7. After Upgrading
    4. 4 Planning Oracle Applications—Approaches, Risks, and Standards
      1. The Cooperative Approach
      2. Everyone Has “Data”
      3. The Familiar Language of Oracle
      4. Some Common, Everyday Examples
      5. What Are the Risks?
      6. The Importance of the New Vision
      7. How to Reduce the Confusion
      8. Capitalization in Names and Data
      9. Normalizing Names
      10. Good Design Has a Human Touch
      11. Understanding the Data
      12. Toward Object Name Normalization
      13. Intelligent Keys and Column Values
      14. The Commandments
  10. Part II SQL and SQL*Plus
    1. 5 The Basic Parts of Speech in SQL
      1. Style
      2. Creating the NEWSPAPER Table
      3. Using SQL to Select Data from Tables
      4. select, from, where, and order by
      5. Logic and Value
      6. Another Use for where: Subqueries
      7. Combining Tables
      8. Creating a View
    2. 6 Basic SQL*Plus Reports and Commands
      1. Building a Simple Report
      2. Other Features
      3. Checking the SQL*Plus Environment
      4. Building Blocks
    3. 7 Getting Text Information and Changing It
      1. Datatypes
      2. What Is a String?
      3. Notation
      4. Concatenation ( || )
      5. How to Cut and Paste Strings
      6. Using order by and where with String Functions
      7. Review
    4. 8 Searching for Regular Expressions
      1. Search Strings
      2. REGEXP_SUBSTR
    5. 9 Playing the Numbers
      1. The Three Classes of Number Functions
      2. Notation
      3. Single-Value Functions
      4. Aggregate Functions
      5. List Functions
      6. Finding Rows with MAX or MIN
      7. Precedence and Parentheses
      8. Review
    6. 10 Dates: Then, Now, and the Difference
      1. Date Arithmetic
      2. ROUND and TRUNC in Date Calculations
      3. TO_DATE and TO_CHAR Formatting
      4. Dates in where Clauses
      5. Dealing with Multiple Centuries
      6. Using the EXTRACT Function
      7. Using the TIMESTAMP Datatypes
    7. 11 Conversion and Transformation Functions
      1. Elementary Conversion Functions
      2. Specialized Conversion Functions
      3. Transformation Functions
      4. Review
    8. 12 Grouping Things Together
      1. The Use of group by and having
      2. Views of Groups
      3. The Power of Views of Groups
      4. More Grouping Possibilities
    9. 13 When One Query Depends upon Another
      1. Advanced Subqueries
      2. Outer Joins
      3. Natural and Inner Joins
      4. UNION, INTERSECT, and MINUS
    10. 14 Some Complex Possibilities
      1. Complex Groupings
      2. Using Temporary Tables
      3. Using ROLLUP, GROUPING, and CUBE
      4. Family Trees and connect by
    11. 15 Changing Data: insert, update, merge, and delete
      1. insert
      2. rollback, commit, and autocommit
      3. Multitable Inserts
      4. delete
      5. update
      6. Using the merge Command
    12. 16 DECODE and CASE: if, then, and else in SQL
      1. if, then, else
      2. Replacing Values via DECODE
      3. DECODE Within DECODE
      4. Greater Than and Less Than in DECODE
      5. Using CASE
    13. 17 Creating and Managing Tables, Views, Indexes, Clusters, and Sequences
      1. Creating a Table
      2. Dropping Tables
      3. Altering Tables
      4. Creating a Table from a Table
      5. Creating an Index-Organized Table
      6. Using Partitioned Tables
      7. Creating a View
      8. Indexes
      9. Clusters
      10. Sequences
    14. 18 Basic Oracle Security
      1. Users, Roles, and Privileges
      2. What Users Can Grant
      3. Granting Limited Resources
  11. Part III Beyond the Basics
    1. 19 Advanced Security—Virtual Private Databases
      1. Initial Configuration
      2. Create an Application Context
      3. Create a Logon Trigger
      4. Create a Security Policy
      5. Apply the Security Policy to Tables
      6. Test VPD
      7. How to Implement Column-Level VPD
      8. How to Disable VPD
      9. How to Use Policy Groups
    2. 20 Working with Tablespaces
      1. Tablespaces and the Structure of the Database
      2. Planning Your Tablespace Usage
    3. 21 Using SQL*Loader to Load Data
      1. The Control File
      2. Starting the Load
      3. Control File Syntax Notes
      4. Managing Data Loads
      5. Tuning Data Loads
      6. Additional Features
    4. 22 Using Data Pump Export and Import
      1. Creating a Directory
      2. Data Pump Export Options
      3. Starting a Data Pump Export Job
      4. Data Pump Import Options
      5. Starting a Data Pump Import Job
    5. 23 Accessing Remote Data
      1. Database Links
      2. Using Synonyms for Location Transparency
      3. Using the User Pseudo-Column in Views
      4. Dynamic Links: Using the SQL*Plus copy Command
      5. Connecting to a Remote Database
    6. 24 Using Materialized Views
      1. Functionality
      2. Required System Privileges
      3. Required Table Privileges
      4. Read-Only vs. Updatable
      5. create materialized view Syntax
      6. Using Materialized Views to Alter Query Execution Paths
      7. Using DBMS_ADVISOR
      8. Refreshing Materialized Views
      9. create materialized view log Syntax
      10. Altering Materialized Views and Logs
      11. Dropping Materialized Views and Logs
    7. 25 Using Oracle Text for Text Searches
      1. Adding Text to the Database
      2. Text Queries and Text Indexes
      3. Index Sets
    8. 26 Using External Tables
      1. Accessing the External Data
      2. Creating an External Table
      3. Altering External Tables
      4. Limitations, Benefits, and Potential Uses of External Tables
    9. 27 Using Flashback Queries
      1. Time-Based Flashback Example
      2. Saving the Data
      3. SCN-Based Flashback Example
      4. What If the Flashback Query Fails?
      5. What SCN Is Associated with Each Row?
      6. Flashback Version Queries
      7. Planning for Flashbacks
    10. 28 Flashback—Tables and Databases
      1. The flashback table Command
      2. The flashback database Command
  12. Part IV PL/SQL
    1. 29 An Introduction to PL/SQL
      1. PL/SQL Overview
      2. Declarations Section
      3. Executable Commands Section
      4. Exception Handling Section
    2. 30 Triggers
      1. Required System Privileges
      2. Required Table Privileges
      3. Types of Triggers
      4. Trigger Syntax
      5. Enabling and Disabling Triggers
      6. Replacing Triggers
      7. Dropping Triggers
    3. 31 Procedures, Functions, and Packages
      1. Required System Privileges
      2. Required Table Privileges
      3. Procedures vs. Functions
      4. Procedures vs. Packages
      5. create procedure Syntax
      6. create function Syntax
      7. create package Syntax
      8. Viewing Source Code for Procedural Objects
      9. Compiling Procedures, Functions, and Packages
      10. Replacing Procedures, Functions, and Packages
      11. Dropping Procedures, Functions, and Packages
    4. 32 Using Native Dynamic SQL and DBMS_SQL
      1. Using EXECUTE IMMEDIATE
      2. Using Bind Variables
      3. Using DBMS_SQL
  13. Part V Object-Relational Databases
    1. 33 Implementing Types, Object Views, and Methods
      1. Working with Abstract Datatypes
      2. Implementing Object Views
      3. Methods
    2. 34 Collectors (Nested Tables and Varying Arrays)
      1. Varying Arrays
      2. Nested Tables
      3. Additional Functions for Nested Tables and Varying Arrays
      4. Management Issues for Nested Tables and Varying Arrays
    3. 35 Using Large Objects
      1. Available Datatypes
      2. Specifying Storage for LOB Data
      3. Manipulating and Selecting LOB Values
    4. 36 Advanced Object-Oriented Concepts
      1. Row Objects vs. Column Objects
      2. Object Tables and OIDs
      3. Object Views with REFs
      4. Object PL/SQL
      5. Objects in the Database
  14. Part VI Java in Oracle
    1. 37 An Introduction to Java
      1. Java vs. PL/SQL: An Overview
      2. Getting Started
      3. Declarations
      4. Executable Commands
      5. Classes
    2. 38 JDBC Programming
      1. Getting Started
      2. Using the JDBC Classes
    3. 39 Java Stored Procedures
      1. Loading the Class into the Database
      2. How to Access the Class
  15. Part VII Clustered Oracle—The Grid
    1. 40 Oracle Real Application Clusters
      1. Preinstallation Steps
      2. Installing RAC
      3. Starting and Stopping RAC Instances
      4. Transparent Application Failover
      5. Adding Nodes and Instances to the Cluster
      6. Managing the Cluster Registry and Services
    2. 41 Grid Architecture and Management
      1. Hardware and Operating System Configuration Issues
      2. Adding Servers to the Grid
      3. Sharing Data Across the Grid
      4. Managing the Grid
      5. Launching OEM
  16. Part VIII Hitchhiker’s Guides
    1. 42 The Hitchhiker’s Guide to the Oracle Data Dictionary
      1. A Note About Nomenclature
      2. New Views Introduced in Oracle Database 10g
      3. New Columns Introduced in Oracle Database 10g
      4. The Road Maps: DICTIONARY (DICT) and DICT_COLUMNS
      5. Things You Select From: Tables (and Columns), Views, Synonyms, and Sequences
      6. Recycle Bin—USER_RECYCLEBIN and DBA_RECYCLEBIN
      7. Constraints and Comments
      8. Abstract Datatypes, ORDBMS-Related Structures, and LOBs
      9. Database Links and Materialized Views
      10. Triggers, Procedures, Functions, and Packages
      11. Dimensions
      12. Space Allocation and Usage, Including Partitions and Subpartitions
      13. Users and Privileges
      14. Roles
      15. Auditing
      16. Miscellaneous
      17. Monitoring: The V$ Dynamic Performance Tables
    2. 43 The Hitchhiker’s Guide to Tuning Applications and SQL
      1. New Tuning Features in Oracle Database 10g
      2. Tuning—Best Practices
      3. Generating and Reading Explain Plans
      4. Major Operations Within Explain Plans
      5. Implementing Stored Outlines
      6. Review
    3. 44 Case Studies in Tuning
      1. Case Study 1: Waits, Waits, and More Waits
      2. Case Study 2: Application-Killing Queries
      3. Case Study 3: Long-Running Batch Jobs
    4. 45 The Hitchhiker’s Guide to Oracle Application Server 10g
      1. What Is Oracle Application Server 10g?
      2. Communication Services
      3. Content Management Services
      4. Business Logic Services
      5. Presentation Services
      6. Business Intelligence Services
      7. Portal Services
      8. Web Services
      9. Developer Toolkits
      10. Persistence Layer Services
      11. Caching Services
      12. System Services
      13. Development Tools
    5. 46 The Hitchhiker’s Guide to Database Administration
      1. Creating a Database
      2. Starting and Stopping the Database
      3. Sizing and Managing Memory Areas
      4. Allocating and Managing Space for the Objects
      5. Monitoring an Undo Tablespace
      6. Automating Storage Management
      7. Segment Space Management
      8. Transporting Tablespaces
      9. Performing Backups
      10. Where to Go from Here
    6. 47 The Hitchhiker’s Guide to XML in Oracle
      1. Document Type Definitions, Elements, and Attributes
      2. XML Schema
      3. Using XSU to Select, Insert, Update, and Delete XML Values
      4. Using XMLType
      5. Other Features
  17. Alphabetical Reference
  18. Index
  19. Footnotes
    1. Ch47fn1

Product information

  • Title: Oracle Database 10g The Complete Reference
  • Author(s): Kevin Loney
  • Release date: September 2004
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780071770491