Oracle Database 11g The Complete Reference | Complete Reference

Oracle Database 11g The Complete Reference

Authors: Kevin Loney

Published: December 2008

eISBN: 9780071598767 0071598766 | ISBN: 9780071598750

Contents

Acknowledgments

Part I: Critical Database Concepts

1 Oracle Database 11g Architecture Options

Databases and Instances

Inside the Database

Choosing Architectures and Options

2 Installing Oracle Database 11g and Creating a Database

Overview of Licensing and Installation Options

Using OUI to Install the Oracle Software

3 Upgrading to Oracle Database 11g

Choosing an Upgrade Method

Before Upgrading

Running the Pre-Upgrade Information Tool

Using the Database Upgrade Assistant

Performing a Manual Direct Upgrade

Using Export and Import

Using the Data-Copying Method

4 Planning Oracle Applications—Approaches, Risks, and Standards

The Cooperative Approach

Everyone Has "Data"

The Familiar Language of Oracle

Some Common, Everyday Examples

What Are the Risks?

The Importance of the New Vision

How to Reduce the Confusion

Capitalization in Names and Data

Normalizing Names

Good Design Has a Human Touch

Understanding the Data

Toward Object Name Normalization

Intelligent Keys and Column Values

The Commandments

Part II: SQL and SQL*Plus

5 The Basic Parts of Speech in SQL

Style

Creating the NEWSPAPER Table

Using SQL to Select Data from Tables

select, from, where, and order by

Logic and Value

Another Use for where: Subqueries

Combining Tables

Creating a View

Expanding the View

6 Basic SQL*Plus Reports and Commands

Building a Simple Report

Other Features

Checking the SQL*Plus Environment

Building Blocks

7 Getting Text Information and Changing It

Datatypes

What Is a String?

Notation

Concatenation ( || )

How to Cut and Paste Strings

Using order by and where with String Functions

Review

8 Searching for Regular Expressions

Search Strings

REGEXP_SUBSTR

9 Playing the Numbers

The Three Classes of Number Functions

Notation

Single-Value Functions

Aggregate Functions

List Functions

Finding Rows with MAX or MIN

Precedence and Parentheses

Review

10 Dates: Then, Now, and the Difference

Date Arithmetic

ROUND and TRUNC in Date Calculations

TO_DATE and TO_CHAR Formatting

Dates in where Clauses

Dealing with Multiple Centuries

Using the EXTRACT Function

Using the TIMESTAMP Datatypes

11 Conversion and Transformation Functions

Elementary Conversion Functions

Specialized Conversion Functions

Transformation Functions

Review

12 Grouping Things Together

The Use of group by and having

Views of Groups

Renaming Columns with Aliases

The Power of Views of Groups

More Grouping Possibilities

13 When One Query Depends upon Another

Advanced Subqueries

Outer Joins

Natural and Inner Joins

UNION, INTERSECT, and MINUS

14 Some Complex Possibilities

Complex Groupings

Using Temporary Tables

Using ROLLUP, GROUPING, and CUBE

Family Trees and connect by

15 Changing Data: insert, update, merge, and delete

insert

rollback, commit, and autocommit

Multitable Inserts

delete

update

Using the merge Command

Handling Errors

16 DECODE and CASE: if, then, and else in SQL

if, then, else

Replacing Values via DECODE

DECODE Within DECODE

Greater Than and Less Than in DECODE

Using CASE

Using PIVOT

17 Creating and Managing Tables, Views, Indexes, Clusters, and Sequences

Creating a Table

Dropping Tables

Altering Tables

Creating a Table from a Table

Creating an Index-Organized Table

Creating a View

Indexes

Clusters

Sequences

18 Partitioning

Creating a Partitioned Table

List Partitioning

Creating Subpartitions

Creating Range and Interval Partitions

Indexing Partitions

Managing Partitioned Tables

19 Basic Oracle Security

Users, Roles, and Privileges

What Users Can Grant

Granting Limited Resources

Part III: Beyond the Basics

20 Advanced Security—Virtual Private Databases

Initial Configuration

Create an Application Context

Create a Logon Trigger

Create a Security Policy

Apply the Security Policy to Tables

Test VPD

How to Implement Column-Level VPD

How to Disable VPD

How to Use Policy Groups

21 Advanced Security: Transparent Data Encryption

Transparent Data Encryption of Columns

Encrypting a Tablespace

22 Working with Tablespaces

Tablespaces and the Structure of the Database

Planning Your Tablespace Usage

23 Using SQL*Loader to Load Data

The Control File

Starting the Load

Logical and Physical Records

Control File Syntax Notes

Managing Data Loads

Repeating Data Loads

Tuning Data Loads

Direct Path Loading

Additional Features

24 Using Data Pump Export and Import

Creating a Directory

Data Pump Export Options

Starting a Data Pump Export Job

Data Pump Import Options

Starting a Data Pump Import Job

25 Accessing Remote Data

Database Links

Using Synonyms for Location Transparency

Using the User Pseudo-Column in Views

26 Using Materialized Views

Functionality

Required System Privileges

Required Table Privileges

Read-Only vs. Updatable

create materialized view Syntax

Using Materialized Views to Alter Query Execution Paths

Using DBMS_ADVISOR

Refreshing Materialized Views

create materialized view log Syntax

Altering Materialized Views and Logs

Dropping Materialized Views and Logs

27 Using Oracle Text for Text Searches

Adding Text to the Database

Text Queries and Text Indexes

Index Sets

28 Using External Tables

Accessing the External Data

Creating an External Table

Altering External Tables

Limitations, Benefits, and Potential Uses of External Tables

29 Using Flashback Queries

Time-Based Flashback Example

Saving the Data

SCN-Based Flashback Example

What If the Flashback Query Fails?

What SCN Is Associated with Each Row?

Flashback Version Queries

Planning for Flashbacks

30 Flashback—Tables and Databases

The flashback table Command

The flashback database Command

31 SQL Replay

High-level Configuration

Capturing the Workload

Processing the Workload

Replaying the Workload

Part IV: PL/SQL

32 An Introduction to PL/SQL

PL/SQL Overview

Declarations Section

Executable Commands Section

Exception Handling Section

33 Online Application Upgrades

Highly Available Databases

Making Low-Impact DDL Changes

34 Triggers

Required System Privileges

Required Table Privileges

Types of Triggers

Trigger Syntax

Enabling and Disabling Triggers

Replacing Triggers

Dropping Triggers

35 Procedures, Functions, and Packages

Required System Privileges

Required Table Privileges

Procedures vs. Functions

Procedures vs. Packages

create procedure Syntax

create function Syntax

create package Syntax

Viewing Source Code for Procedural Objects

Compiling Procedures, Functions, and Packages

Replacing Procedures, Functions, and Packages

Dropping Procedures, Functions, and Packages

36 Using Native Dynamic SQL and DBMS_SQL

Using EXECUTE IMMEDIATE

Using Bind Variables

Using DBMS_SQL

37 PL/SQL Tuning

Tune the SQL

Steps for Tuning the PL/SQL

Use DBMS_PROFILER to Identify Problems

Use PL/SQL Features for Bulk Operations

Part V: Object-Relational Databases

38 Implementing Object Types, Object Views, and Methods

Working with Object Types

Implementing Object Views

Methods

39 Collectors (Nested Tables and Varying Arrays)

Varying Arrays

Nested Tables

Additional Functions for Nested Tables and Varying Arrays

Management Issues for Nested Tables and Varying Arrays

40 Using Large Objects

Available Datatypes

Specifying Storage for LOB Data

Manipulating and Selecting LOB Values

41 Advanced Object-Oriented Concepts

Row Objects vs. Column Objects

Object Tables and OIDs

Object Views with REFs

Object PL/SQL

Objects in the Database

Part VI: Java in Oracle

42 An Introduction to Java

Java vs. PL/SQL: An Overview

Getting Started

Declarations

Executable Commands

Classes

43 JDBC Programming

Using the JDBC Classes

Using JDBC for Data Manipulation

44 Java Stored Procedures

Loading the Class into the Database

How to Access the Class

Part VII: Hitchhiker's Guides

45 The Hitchhiker's Guide to the Oracle Data Dictionary

A Note about Nomenclature

New Views Introduced in Oracle Database 11g

The Road Maps: DICTIONARY (DICT) and DICT_COLUMNS

Things You Select From: Tables (and Columns), Views, Synonyms, and Sequences

Recycle Bin: USER_RECYCLEBIN and DBA_RECYCLEBIN

Constraints and Comments

Indexes and Clusters

Abstract Datatypes and LOBs

Database Links and Materialized Views

Triggers, Procedures, Functions, and Packages

Dimensions

Space Allocation and Usage, Including Partitions and Subpartitions

Users and Privileges

Roles

Auditing

Miscellaneous

Monitoring: The V$ Dynamic Performance Tables

46 The Hitchhiker's Guide to Tuning Applications and SQL

New Tuning Features in Oracle Database 11g

New Tuning Features in Oracle 11g

Tuning—Best Practices

Generating and Reading Explain Plans

Major Operations Within Explain Plans

Implementing Stored Outlines

Review

47 SQL Result Cache and Client-Side Query Cache

Database Parameter Settings for SQL Result Cache

The DBMS_RESULT_CACHE Package

Dictionary Views for the SQL Result Cache

Additional Details for SQL Result Cache

Oracle Call Interface (OCI) Client Query Cache

Oracle Call Interface (OCI) Client Query Cache Restrictions

48 Case Studies in Tuning

Case Study 1: Waits, Waits, and More Waits

Case Study 2: Application-Killing Queries

Case Study 3: Long-Running Batch Jobs

49 Advanced Architecture Options—DB Vault, Content DB, and Records DB

Oracle Database Vault

Oracle Content Database Suite

Oracle Records Database

50 Oracle Real Application Clusters

Preinstallation Steps

Installing RAC

Starting and Stopping RAC Instances

Transparent Application Failover

Adding Nodes and Instances to the Cluster

51 The Hitchhiker's Guide to Database Administration

Creating a Database

Starting and Stopping the Database

Sizing and Managing Memory Areas

Allocating and Managing Space for the Objects

Monitoring an Undo Tablespace

Automating Storage Management

Segment Space Management

Transporting Tablespaces

Performing Backups

Where to Go from Here

52 The Hitchhiker's Guide to XML in Oracle

Document Type Definitions, Elements, and Attributes

XML Schema

Using XSU to Select, Insert, Update, and Delete XML Values

Using XMLType

Other Features

Part VIII: Alphabetical Reference

Index