Oracle Database 11g The Complete Reference

Oracle Database 11g The Complete Reference

by Kevin Loney
Released October 2008
Publisher(s): McGraw Hill Computing
ISBN: 9780071598767

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

The Definitive Guide to Oracle Database 11g

Get full details on the powerful features of Oracle Database 11g from this thoroughly updated Oracle Press guide. Oracle Database 11g: The Complete Reference explains how to use all the new features and tools, execute powerful SQL queries, construct PL/SQL and SQL*Plus statements, and work with large objects and object-relational databases. Learn how to implement the latest security measures, tune database performance, and deploy grid computing techniques. An invaluable cross-referenced appendix containing Oracle commands, keywords, features, and functions is also included.

  • Install Oracle Database 11g or upgrade from an earlier version
  • Create database tables, sequences, indexes, views, and user accounts
  • Construct SQL statements, procedures, queries, and subqueries
  • Optimize security using virtual private databases and transparent data encryption
  • Import and export data using SQL*Loader and Oracle Data Pump
  • Use SQL replay, change management, and result caching
  • Avoid human errors using flashback and automatic undo management
  • Build and tune PL/SQL triggers, functions, and packages
  • Develop database applications using Java, JDBC, and XML
  • Optimize availability and scalability with Oracle Real Application Clusters

Table of contents

  1. Cover Page
  2. Oracle Database 11g The Complete Reference
  3. Copyright Page
  4. Contents
  5. Acknowledgments
  6. PART I Critical Database Concepts
    1. 1 Oracle Database 11g Architecture Options
      1. Databases and Instances
      2. Inside the Database
      3. Storing the Data
      4. Guarding the Data
      5. Programmatic Structures
      6. Choosing Architectures and Options
    2. 2 Installing Oracle Database 11g and Creating a Database
      1. Overview of Licensing and Installation Options
      2. Using OUI to Install the Oracle Software
    3. 3 Upgrading to Oracle Database 11g
      1. Choosing an Upgrade Method
      2. Before Upgrading
      3. Running the Pre-Upgrade Information Tool
      4. Using the Database Upgrade Assistant
      5. Performing a Manual Direct Upgrade
      6. Using Export and Import
      7. Export and Import Versions to Use
      8. Performing the Upgrade
      9. Using the Data-Copying Method
    4. 4 Planning Oracle Applications—Approaches, Risks, and Standards
      1. The Cooperative Approach
      2. Everyone Has "Data"
      3. The Familiar Language of Oracle
      4. Tables of Information
      5. Structured Query Language
      6. A Simple Oracle Query
      7. Why It Is Called "Relational"
      8. Some Common, Everyday Examples
      9. What Are the Risks?
      10. The Importance of the New Vision
      11. Changing Environments
      12. Codes, Abbreviations, and Naming Standards
      13. How to Reduce the Confusion
      14. Normalization
      15. English Names for Tables and Columns
      16. English Words for the Data
      17. Capitalization in Names and Data
      18. Normalizing Names
      19. Good Design Has a Human Touch
      20. Understanding the Application Tasks
      21. Outline of Tasks
      22. Understanding the Data
      23. The Atomic Data Models
      24. The Atomic Business Model
      25. The Business Model
      26. Data Entry
      27. Query and Reporting
      28. Toward Object Name Normalization
      29. Level-Name Integrity
      30. Foreign Keys
      31. Singular Names
      32. Brevity
      33. Object Name Thesaurus
      34. Intelligent Keys and Column Values
      35. The Commandments
  7. 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. Single-Value Tests
      7. LIKE
      8. Simple Tests Against a List of Values
      9. Combining Logic
      10. Another Use for where: Subqueries
      11. Single Values from a Subquery
      12. Lists of Values from a Subquery
      13. Combining Tables
      14. Creating a View
      15. Expanding the View
    2. 6 Basic SQL*Plus Reports and Commands
      1. Building a Simple Report
      2. remark
      3. set headsep
      4. ttitle and btitle
      5. column
      6. break on
      7. compute avg
      8. set linesize
      9. set pagesize
      10. set newpage
      11. spool
      12. /* */
      13. Some Clarification on Column Headings
      14. Other Features
      15. Command Line Editor
      16. set pause
      17. save
      18. store
      19. Editing
      20. host
      21. Adding SQL*Plus Commands
      22. start
      23. Checking the SQL*Plus Environment
      24. 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. RPAD and LPAD
      7. LTRIM, RTRIM, and TRIM
      8. Combining Two Functions
      9. Using the TRIM Function
      10. Adding One More Function
      11. LOWER, UPPER, and INITCAP
      12. LENGTH
      13. SUBSTR
      14. INSTR
      15. ASCII and CHR
      16. Using order by and where with String Functions
      17. SOUNDEX
      18. National Language Support
      19. Regular Expression Support
      20. Review
    4. 8 Searching for Regular Expressions
      1. Search Strings
      2. REGEXP_SUBSTR
      3. REGEXP_INSTR
      4. REGEXP_LIKE
      5. REPLACE and REGEXP_REPLACE
      6. REGEXP_COUNT
    5. 9 Playing the Numbers
      1. The Three Classes of Number Functions
      2. Notation
      3. Single-Value Functions
      4. Addition (+), Subtraction (-), Multiplication (*), and Division (/)
      5. NULL
      6. NVL: NULL-Value Substitution
      7. ABS: Absolute Value
      8. CEIL
      9. FLOOR
      10. MOD
      11. POWER
      12. SQRT: Square Root
      13. EXP, LN, and LOG
      14. ROUND and TRUNC
      15. SIGN
      16. SIN, SINH, COS, COSH, TAN, TANH, ACOS, ATAN, ATAN2, and ASIN
      17. Aggregate Functions
      18. NULLs in Group-Value Functions
      19. Examples of Single- and Group-Value Functions
      20. AVG, COUNT, MAX, MIN, and SUM
      21. Combining Group-Value and Single-Value Functions
      22. STDDEV and VARIANCE
      23. DISTINCT in Group Functions
      24. List Functions
      25. Finding Rows with MAX or MIN
      26. Precedence and Parentheses
      27. Review
    6. 10 Dates: Then, Now, and the Difference
      1. Date Arithmetic
      2. SYSDATE, CURRENT_DATE, and SYSTIMESTAMP
      3. The Difference Between Two Dates
      4. Adding Months
      5. Subtracting Months
      6. GREATEST and LEAST
      7. NEXT_DAY
      8. LAST_DAY
      9. MONTHS_BETWEEN Two Dates
      10. Combining Date Functions
      11. ROUND and TRUNC in Date Calculations
      12. TO_DATE and TO_CHAR Formatting
      13. The Most Common TO_CHAR Error
      14. NEW_TIME: Switching Time Zones
      15. TO_DATE Calculations
      16. Dates in where Clauses
      17. Dealing with Multiple Centuries
      18. Using the EXTRACT Function
      19. Using the TIMESTAMP Datatypes
    7. 11 Conversion and Transformation Functions
      1. Elementary Conversion Functions
      2. Automatic Conversion of Datatypes
      3. A Warning About Automatic Conversion
      4. Specialized Conversion Functions
      5. Transformation Functions
      6. TRANSLATE
      7. DECODE
      8. Review
    8. 12 Grouping Things Together
      1. The Use of group by and having
      2. Adding an order by
      3. Order of Execution
      4. Views of Groups
      5. Renaming Columns with Aliases
      6. The Power of Views of Groups
      7. Using order by in Views
      8. Logic in the having Clause
      9. Using order by with Columns and Group Functions
      10. Join Columns
      11. More Grouping Possibilities
    9. 13 When One Query Depends upon Another
      1. Advanced Subqueries
      2. Correlated Subqueries
      3. Coordinating Logical Tests
      4. Using EXISTS and Its Correlated Subquery
      5. Outer Joins
      6. Pre-Oracle9 i Syntax for Outer Joins
      7. Current Syntax for Outer Joins
      8. Replacing NOT IN with an Outer Join
      9. Replacing NOT IN with NOT EXISTS
      10. Natural and Inner Joins
      11. UNION, INTERSECT, and MINUS
      12. IN Subqueries
      13. Restrictions on 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
      5. Excluding Individuals and Branches
      6. Traveling Toward the Roots
      7. The Basic Rules
    11. 15 Changing Data: insert, update, merge, and delete
      1. insert
      2. Inserting a Time
      3. insert with select
      4. Using the APPEND Hint to Improve insert Performance
      5. rollback, commit, and autocommit
      6. Using savepoints
      7. Implicit commit
      8. Auto rollback
      9. Multitable Inserts
      10. delete
      11. update
      12. update with Embedded select
      13. update with NULL
      14. Using the merge Command
      15. Handling Errors
    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
      6. Using PIVOT
    13. 17 Creating and Managing Tables, Views, Indexes, Clusters, and Sequences
      1. Creating a Table
      2. Character Width and NUMBER Precision
      3. Rounding During Insertion
      4. Constraints in create table
      5. Designating Index Tablespaces
      6. Naming Constraints
      7. Dropping Tables
      8. Altering Tables
      9. The Rules for Adding or Modifying a Column
      10. Creating Read-Only Tables
      11. Altering Actively Used Tables
      12. Creating Virtual Columns
      13. Dropping a Column
      14. Creating a Table from a Table
      15. Creating an Index-Organized Table
      16. Creating a View
      17. Stability of a View
      18. Using order by in Views
      19. Creating a Read-Only View
      20. Indexes
      21. Creating an Index
      22. Enforcing Uniqueness
      23. Creating a Unique Index
      24. Creating a Bitmap Index
      25. When to Create an Index
      26. Creating Invisible Indexes
      27. Variety in Indexed Columns
      28. How Many Indexes to Use on a Table
      29. Placing an Index in the Database
      30. Rebuilding an Index
      31. Function-Based Indexes
      32. Clusters
      33. Sequences
    14. 18 Partitioning
      1. Creating a Partitioned Table
      2. List Partitioning
      3. Creating Subpartitions
      4. Creating Range and Interval Partitions
      5. Indexing Partitions
      6. Managing Partitioned Tables
    15. 19 Basic Oracle Security
      1. Users, Roles, and Privileges
      2. Creating a User
      3. Password Management
      4. Standard Roles
      5. Format for the grant Command
      6. Revoking Privileges
      7. What Users Can Grant
      8. Moving to Another User with connect
      9. create synonym
      10. Using Ungranted Privileges
      11. Passing Privileges
      12. Creating a Role
      13. Granting Privileges to a Role
      14. Granting a Role to Another Role
      15. Granting a Role to Users
      16. Adding a Password to a Role
      17. Removing a Password from a Role
      18. Enabling and Disabling Roles
      19. Revoking Privileges from a Role
      20. Dropping a Role
      21. Granting UPDATE to Specific Columns
      22. Revoking Object Privileges
      23. Security by User
      24. Granting Access to the Public
      25. Granting Limited Resources
  8. PART III Beyond the Basics
    1. 20 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. 21 Advanced Security: Transparent Data Encryption
      1. Transparent Data Encryption of Columns
      2. Setup
      3. Additional Setup for RAC Databases
      4. Opening and Closing the Wallet
      5. Encrypting and Decrypting Columns
      6. Encrypting a Tablespace
      7. Setup
      8. Creating an Encrypted Tablespace
    3. 22 Working with Tablespaces
      1. Tablespaces and the Structure of the Database
      2. Tablespace Contents
      3. RECYCLEBIN Space in Tablespaces
      4. Read-Only Tablespaces
      5. nologging Tablespaces
      6. Temporary Tablespaces
      7. Tablespaces for System-Managed Undo
      8. Bigfile Tablespaces
      9. Encrypted Tablespaces
      10. Supporting Flashback Database
      11. Planning Your Tablespace Usage
      12. Separate Active and Static Tables
      13. Separate Indexes and Tables
      14. Separate Large and Small Objects
      15. Separate Application Tables from Core Objects
    4. 23 Using SQL*Loader to Load Data
      1. The Control File
      2. Loading Variable-Length Data
      3. Starting the Load
      4. Logical and Physical Records
      5. Control File Syntax Notes
      6. Managing Data Loads
      7. Repeating Data Loads
      8. Tuning Data Loads
      9. Direct Path Loading
      10. Additional Features
    5. 24 Using Data Pump Export and Import
      1. Creating a Directory
      2. Data Pump Export Options
      3. Starting a Data Pump Export Job
      4. Stopping and Restarting Running Jobs
      5. Exporting from Another Database
      6. Using EXCLUDE, INCLUDE, and QUERY
      7. Data Pump Import Options
      8. Starting a Data Pump Import Job
      9. Stopping and Restarting Running Jobs
      10. EXCLUDE, INCLUDE, and QUERY
      11. Transforming Imported Objects
      12. Generating SQL
    6. 25 Accessing Remote Data
      1. Database Links
      2. How a Database Link Works
      3. Using a Database Link for Remote Queries
      4. Using a Database Link for Synonyms and Views
      5. Using a Database Link for Remote Updates
      6. Syntax for Database Links
      7. Using Synonyms for Location Transparency
      8. Using the User Pseudo-Column in Views
    7. 26 Using Materialized Views
      1. Functionality
      2. Required System Privileges
      3. Required Table Privileges
      4. Read-Only vs. Updatable
      5. create materialized view Syntax
      6. Types of Materialized Views
      7. RowID vs. Primary Key-Based Materialized Views
      8. Using Prebuilt Tables
      9. Indexing Materialized View Tables
      10. Using Materialized Views to Alter Query Execution Paths
      11. Using DBMS_ADVISOR
      12. Refreshing Materialized Views
      13. What Kind of Refreshes Can Be Performed?
      14. Fast Refresh with CONSIDER FRESH
      15. Automatic Refreshes
      16. Manual Refreshes
      17. create materialized view log Syntax
      18. Altering Materialized Views and Logs
      19. Dropping Materialized Views and Logs
    8. 27 Using Oracle Text for Text Searches
      1. Adding Text to the Database
      2. Text Queries and Text Indexes
      3. Text Queries
      4. Available Text Query Expressions
      5. Searching for an Exact Match of a Word
      6. Searching for an Exact Match of Multiple Words
      7. Searching for an Exact Match of a Phrase
      8. Searches for Words That Are Near Each Other
      9. Using Wildcards During Searches
      10. Searching for Words That Share the Same Stem
      11. Searching for Fuzzy Matches
      12. Searches for Words That Sound Like Other Words
      13. Using the ABOUT Operator
      14. Index Synchronization
      15. Index Sets
    9. 28 Using External Tables
      1. Accessing the External Data
      2. Creating an External Table
      3. External Table Creation Options
      4. Loading External Tables on Creation
      5. Altering External Tables
      6. Access Parameters
      7. Add Column
      8. Default Directory
      9. Drop Column
      10. Location
      11. Modify Column
      12. Parallel
      13. Project Column
      14. Reject Limit
      15. Rename To
      16. Limitations, Benefits, and Potential Uses of External Tables
    10. 29 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
    11. 30 Flashback—Tables and Databases
      1. The flashback table Command
      2. Privileges Required
      3. Recovering Dropped Tables
      4. Enabling and Disabling the Recycle Bin
      5. Flashing Back to SCN or Timestamp
      6. Indexes and Statistics
      7. The flashback database Command
    12. 31 SQL Replay
      1. High-level Configuration
      2. Isolation and Links
      3. Creating a Workload Directory
      4. Capturing the Workload
      5. Defining Filters
      6. Starting the Capture
      7. Stopping the Capture
      8. Exporting AWR Data
      9. Processing the Workload
      10. Replaying the Workload
      11. Controlling and Starting the Replay Clients
      12. Initializing and Running the Replay
      13. Exporting AWR Data
  9. PART IV PL/SQL
    1. 32 An Introduction to PL/SQL
      1. PL/SQL Overview
      2. Declarations Section
      3. Executable Commands Section
      4. Conditional Logic
      5. Loops
      6. CASE Statements
      7. Exception Handling Section
    2. 33 Online Application Upgrades
      1. Highly Available Databases
      2. Oracle Data Guard Architecture
      3. Creating the Standby Database Configuration
      4. Managing Roles—Switchovers and Failovers
      5. Making Low-Impact DDL Changes
      6. Creating Virtual Columns
      7. Altering Actively Used Tables
      8. Adding NOT NULL Columns
      9. Online Object Reorganizations
      10. Dropping a Column
    3. 34 Triggers
      1. Required System Privileges
      2. Required Table Privileges
      3. Types of Triggers
      4. Row-Level Triggers
      5. Statement-Level Triggers
      6. BEFORE and AFTER Triggers
      7. INSTEAD OF Triggers
      8. Schema Triggers
      9. Database-Level Triggers
      10. Compound Triggers
      11. Trigger Syntax
      12. Combining DML Trigger Types
      13. Setting Inserted Values
      14. Maintaining Duplicated Data
      15. Customizing Error Conditions
      16. Calling Procedures Within Triggers
      17. Naming Triggers
      18. Creating DDL Event Triggers
      19. Creating Database Event Triggers
      20. Creating Compound Triggers
      21. Enabling and Disabling Triggers
      22. Replacing Triggers
      23. Dropping Triggers
    4. 35 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. Referencing Remote Tables in Procedures
      8. Debugging Procedures
      9. Creating Your Own Functions
      10. Customizing Error Conditions
      11. Naming Procedures and Functions
      12. create package Syntax
      13. Viewing Source Code for Procedural Objects
      14. Compiling Procedures, Functions, and Packages
      15. Replacing Procedures, Functions, and Packages
      16. Dropping Procedures, Functions, and Packages
    5. 36 Using Native Dynamic SQL and DBMS_SQL
      1. Using EXECUTE IMMEDIATE
      2. Using Bind Variables
      3. Using DBMS_SQL
      4. OPEN_CURSOR
      5. PARSE
      6. BIND_VARIABLE and BIND_ARRAY
      7. EXECUTE
      8. DEFINE_COLUMN
      9. FETCH_ROWS, EXECUTE_AND_FETCH, and COLUMN_VALUE
      10. CLOSE_CURSOR
    6. 37 PL/SQL Tuning
      1. Tune the SQL
      2. Steps for Tuning the PL/SQL
      3. Use DBMS_PROFILER to Identify Problems
      4. Use PL/SQL Features for Bulk Operations
      5. forall
      6. bulk collect
  10. PART V Object-Relational Databases
    1. 38 Implementing Object Types, Object Views, and Methods
      1. Working with Object Types
      2. Security for Object Types
      3. Indexing Object Type Attributes
      4. Implementing Object Views
      5. Manipulating Data via Object Views
      6. Using INSTEAD OF Triggers
      7. Methods
      8. Syntax for Creating Methods
      9. Managing Methods
    2. 39 Collectors (Nested Tables and Varying Arrays)
      1. Varying Arrays
      2. Creating a Varying Array
      3. Describing the Varying Array
      4. Inserting Records into the Varying Array
      5. Selecting Data from Varying Arrays
      6. Nested Tables
      7. Specifying Tablespaces for Nested Tables
      8. Inserting Records into a Nested Table
      9. Working with Nested Tables
      10. Additional Functions for Nested Tables and Varying Arrays
      11. Management Issues for Nested Tables and Varying Arrays
      12. Variability in Collectors
      13. Location of the Data
    3. 40 Using Large Objects
      1. Available Datatypes
      2. Specifying Storage for LOB Data
      3. Manipulating and Selecting LOB Values
      4. Initializing Values
      5. Using insert with Subqueries
      6. Updating LOB Values
      7. Using String Functions to Manipulate LOB Values
      8. Using DBMS_LOB to Manipulate LOB Values
      9. Deleting LOBs
    4. 41 Advanced Object-Oriented Concepts
      1. Row Objects vs. Column Objects
      2. Object Tables and OIDs
      3. Inserting Rows into Object Tables
      4. Selecting Values from Object Tables
      5. Updates and Deletes from Object Tables
      6. The REF Function
      7. Using the DEREF Function
      8. The VALUE Function
      9. Invalid References
      10. Object Views with REFs
      11. A Quick Review of Object Views
      12. Object Views Involving References
      13. Object PL/SQL
      14. Objects in the Database
  11. PART VI Java in Oracle
    1. 42 An Introduction to Java
      1. Java vs. PL/SQL: An Overview
      2. Getting Started
      3. Declarations
      4. Executable Commands
      5. Conditional Logic
      6. Loops
      7. Exception Handling
      8. Reserved Words
      9. Classes
    2. 43 JDBC Programming
      1. Using the JDBC Classes
      2. Using JDBC for Data Manipulation
    3. 44 Java Stored Procedures
      1. Loading the Class into the Database
      2. How to Access the Class
      3. Calling Java Stored Procedures Directly
      4. Where to Perform Commands
  12. PART VII Hitchhiker's Guides
    1. 45 The Hitchhiker's Guide to the Oracle Data Dictionary
      1. A Note about Nomenclature
      2. New Views Introduced in Oracle Database 11g
      3. The Road Maps: DICTIONARY (DICT) and DICT_COLUMNS
      4. Things You Select From: Tables (and Columns), Views, Synonyms, and Sequences
      5. Catalog: USER_CATALOG (CAT)
      6. Objects: USER_OBJECTS (OBJ)
      7. Tables: USER_TABLES (TABS)
      8. Columns: USER_TAB_COLUMNS (COLS)
      9. Views: USER_VIEWS
      10. Synonyms: USER_SYNONYMS (SYN)
      11. Sequences: USER_SEQUENCES (SEQ)
      12. Recycle Bin: USER_RECYCLEBIN and DBA_RECYCLEBIN
      13. Constraints and Comments
      14. Constraints: USER_CONSTRAINTS
      15. Constraint Columns: USER_CONS_COLUMNS
      16. Constraint Exceptions: EXCEPTIONS
      17. Table Comments: USER_TAB_COMMENTS
      18. Column Comments: USER_COL_COMMENTS
      19. Indexes and Clusters
      20. Indexes: USER_INDEXES (IND)
      21. Indexed Columns: USER_IND_COLUMNS
      22. Clusters: USER_CLUSTERS (CLU)
      23. Cluster Columns: USER_CLU_COLUMNS
      24. Abstract Datatypes and LOBs
      25. Abstract Datatypes: USER_TYPES
      26. LOBs: USER_LOBS
      27. Database Links and Materialized Views
      28. Database Links: USER_DB_LINKS
      29. Materialized Views
      30. Materialized View Logs: USER_MVIEW_LOGS
      31. Triggers, Procedures, Functions, and Packages
      32. Triggers: USER_TRIGGERS
      33. Procedures, Functions, and Packages: USER_SOURCE
      34. Dimensions
      35. Space Allocation and Usage, Including Partitions and Subpartitions
      36. Tablespaces: USER_TABLESPACES
      37. Space Quotas: USER_TS_QUOTAS
      38. Segments and Extents: USER_SEGMENTS and USER_EXTENTS
      39. Partitions and Subpartitions
      40. Free Space: USER_FREE_SPACE
      41. Users and Privileges
      42. Users: USER_USERS
      43. Resource Limits: USER_RESOURCE_LIMITS
      44. Table Privileges: USER_TAB_PRIVS
      45. Column Privileges: USER_COL_PRIVS
      46. System Privileges: USER_SYS_PRIVS
      47. Roles
      48. Auditing
      49. Miscellaneous
      50. Monitoring: The V$ Dynamic Performance Tables
      51. CHAINED_ROWS
      52. PLAN_TABLE
      53. Interdependencies: USER_DEPENDENCIES and IDEPTREE
      54. DBA-Only Views
      55. Oracle Label Security
      56. SQL*Loader Direct Load Views
      57. Globalization Support Views
      58. Libraries
      59. Heterogeneous Services
      60. Indextypes and Operators
      61. Outlines
      62. Advisors
      63. Schedulers
    2. 46 The Hitchhiker's Guide to Tuning Applications and SQL
      1. New Tuning Features in Oracle Database 11g
      2. New Tuning Features in Oracle 11g
      3. Tuning—Best Practices
      4. Do as Little as Possible
      5. Do It as Simply as Possible
      6. Tell the Database What It Needs to Know
      7. Maximize the Throughput in the Environment
      8. Divide and Conquer Your Data
      9. Test Correctly
      10. Generating and Reading Explain Plans
      11. Using set autotrace on
      12. Using explain plan
      13. Major Operations Within Explain Plans
      14. TABLE ACCESS FULL
      15. TABLE ACCESS BY INDEX ROWID
      16. Related Hints
      17. Operations That Use Indexes
      18. When Indexes Are Used
      19. Operations That Manipulate Data Sets
      20. Operations That Perform Joins
      21. How Oracle Handles Joins of More than Two Tables
      22. Parallelism and Cache Issues
      23. Implementing Stored Outlines
      24. Review
    3. 47 SQL Result Cache and Client-Side Query Cache
      1. Database Parameter Settings for SQL Result Cache
      2. The DBMS_RESULT_CACHE Package
      3. Dictionary Views for the SQL Result Cache
      4. Additional Details for SQL Result Cache
      5. Oracle Call Interface (OCI) Client Query Cache
      6. Oracle Call Interface (OCI) Client Query Cache Restrictions
    4. 48 Case Studies in Tuning
      1. Case Study 1: Waits, Waits, and More Waits
      2. Case Study 2: Application-Killing Queries
      3. Using the 10053 Trace Event
      4. Case Study 3: Long-Running Batch Jobs
    5. 49 Advanced Architecture Options—DB Vault, Content DB, and Records DB
      1. Oracle Database Vault
      2. New Concepts for Oracle Database Vault
      3. Disabling Oracle Database Vault
      4. Enabling Oracle Database Vault
      5. Database Vault Installation Notes
      6. Oracle Content Database Suite
      7. Repository
      8. Document Management
      9. User Security
      10. Oracle Records Database
    6. 50 Oracle Real Application Clusters
      1. Preinstallation Steps
      2. Installing RAC
      3. Storage
      4. Initialization Parameters
      5. Starting and Stopping RAC Instances
      6. Transparent Application Failover
      7. Adding Nodes and Instances to the Cluster
    7. 51 The Hitchhiker's Guide to Database Administration
      1. Creating a Database
      2. Using the Oracle Enterprise Manager
      3. Starting and Stopping the Database
      4. Sizing and Managing Memory Areas
      5. The Initialization Parameter File
      6. Allocating and Managing Space for the Objects
      7. Implications of the storage Clause
      8. Table Segments
      9. Index Segments
      10. System-Managed Undo
      11. Temporary Segments
      12. Free Space
      13. Sizing Database Objects
      14. Monitoring an Undo Tablespace
      15. Automating Storage Management
      16. Configuring ASM
      17. Segment Space Management
      18. Transporting Tablespaces
      19. Generating a Transportable Tablespace Set
      20. Plugging in the Transportable Tablespace Set
      21. Performing Backups
      22. Data Pump Export and Import
      23. Offline Backups
      24. Online Backups
      25. Recovery Manager
      26. Where to Go from Here
    8. 52 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. Insert, Update, and Delete Processing with XSU
      5. XSU and Java
      6. Customizing the Query Process
      7. Using XMLType
      8. Other Features
  13. PART VIII Alphabetical Reference
  14. Index

Product information

  • Title: Oracle Database 11g The Complete Reference
  • Author(s): Kevin Loney
  • Release date: October 2008
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780071598767