Contents

Title and Copyright Information

Send Us Your Comments

Preface

Related Documentation

Documentation Accessibility

What's New in the Oracle Database 10g Administrator's Guide?

Oracle Database 10g Release 1 (10.1) New Features

Part I Basic Database Administration

1 Overview of Administering an Oracle Database

Types of Oracle Database Users

Database Administrators

Security Officers

Network Administrators

Application Developers

Application Administrators

Tasks of a Database Administrator

Task 1: Evaluate the Database Server Hardware

Task 2: Install the Oracle Database Software

Task 3: Plan the Database

Task 4: Create and Open the Database

Task 5: Back Up the Database

Task 6: Enroll System Users

Task 7: Implement the Database Design

Task 8: Back Up the Fully Functional Database

Task 9: Tune Database Performance

Identifying Your Oracle Database Software Release

Release Number Format

Major Database Release Number

Database Maintenance Release Number

Application Server Release Number

Component-Specific Release Number

Platform-Specific Release Number

Checking Your Current Release Number

Database Administrator Security and Privileges

The Database Administrator's Operating System Account

Database Administrator Usernames

Database Administrator Authentication

Administrative Privileges

SYSDBA and SYSOPER

Connecting with Administrative Privileges: Example

Selecting an Authentication Method

Nonsecure Remote Connections

Local Connections and Secure Remote Connections

Using Operating System Authentication

Preparing to Use Operating System Authentication

Connecting Using Operating System Authentication

OSDBA and OSOPER

Using Password File Authentication

Preparing to Use Password File Authentication

Connecting Using Password File Authentication

Creating and Maintaining a Password File

Setting REMOTE_LOGIN_ PASSWORDFILE

Adding Users to a Password File

Granting and Revoking SYSDBA and SYSOPER Privileges

Viewing Password File Members

Maintaining a Password File

Expanding the Number of Password File Users

Removing a Password File

Changing the Password File State

Server Manageability

Automatic Manageability Features

Automatic Workload Repository

Automatic Maintenance Tasks

Server-Generated Alerts

Export and Import Utilities

2 Creating an Oracle Database

Deciding How to Create an Oracle Database

Manually Creating an Oracle Database

Considerations Before Creating the Database

Planning for Database Creation

Meeting Creation Prerequisites

Creating the Database

Step 1: Decide on Your Instance Identifier (SID)

Step 2: Establish the Database Administrator Authentication Method

Step 3: Create the Initialization Parameter File

Step 4: Connect to the Instance

Step 5: Create a Server Parameter File (Recommended)

Step 6: Start the Instance

Step 7: Issue the CREATE DATABASE Statement

Step 8: Create Additional Tablespaces

Step 9: Run Scripts to Build Data Dictionary Views

Step 10: Run Scripts to Install Additional Options (Optional)

Step 11: Back Up the Database.

Understanding the CREATE DATABASE Statement

Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM

Creating a Locally Managed SYSTEM Tablespace

Creating the SYSAUX Tablespace

Using Automatic Undo Management: Creating an Undo Tablespace

Creating a Default Permanent Tablespace

Creating a Default Temporary Tablespace

Specifying Oracle-Managed Files at Database Creation

Supporting Bigfile Tablespaces During Database Creation

Specifying the Default Tablespace Type

Overriding the Default Tablespace Type

Specifying the Database Time Zone and Time Zone File

Specifying the Database Time Zone

Specifying the Database Time Zone File

Specifying FORCE LOGGING Mode

Using the FORCE LOGGING Clause

Performance Considerations of FORCE LOGGING Mode

Initialization Parameters and Database Creation

Determining the Global Database Name

DB_NAME Initialization Parameter

DB_DOMAIN Initialization Parameter

Specifying a Flash Recovery Area

Specifying Control Files

Specifying Database Block Sizes

DB_BLOCK_SIZE Initialization Parameter

Nonstandard Block Sizes

Managing the System Global Area (SGA)

Components and Granules in the SGA

Limiting the Size of the SGA

Using Automatic Shared Memory Management

Using Manual Shared Memory Management

Viewing Information About the SGA

Specifying the Maximum Number of Processes

Specifying the Method of Undo Space Management

UNDO_MANAGEMENT Initialization Parameter

UNDO_TABLESPACE Initialization Parameter

The COMPATIBLE Initialization Parameter and Irreversible Compatibility

Setting the License Parameter

Troubleshooting Database Creation

Dropping a Database

Managing Initialization Parameters Using a Server Parameter File

What Is a Server Parameter File?

Migrating to a Server Parameter File

Creating a Server Parameter File

The SPFILE Initialization Parameter

Using ALTER SYSTEM to Change Initialization Parameter Values

Setting or Changing Initialization Parameter Values

Deleting Initialization Parameter Values

Exporting the Server Parameter File

Backing Up the Server Parameter File

Errors and Recovery for the Server Parameter File

Viewing Parameter Settings

Defining Application Services for Oracle Database 10g

Deploying Services

Configuring Services

Client-side Use

Server-side Use

Considerations After Creating a Database

Some Security Considerations

Installing the Oracle Database Sample Schemas

Viewing Information About the Database

3 Starting Up and Shutting Down

Starting Up a Database

Options for Starting Up a Database

Starting Up a Database Using SQL*Plus

Starting Up a Database Using Recovery Manager

Starting Up a Database Using Oracle Enterprise Manager

Preparing to Start an Instance

Using SQL*Plus to Start Up a Database

Starting an Instance: Scenarios

Starting an Instance, and Mounting and Opening a Database

Starting an Instance Without Mounting a Database

Starting an Instance and Mounting a Database

Restricting Access to an Instance at Startup

Forcing an Instance to Start

Starting an Instance, Mounting a Database, and Starting Complete Media Recovery

Automatic Database Startup at Operating System Start

Starting Remote Instances

Altering Database Availability

Mounting a Database to an Instance

Opening a Closed Database

Opening a Database in Read-Only Mode

Restricting Access to an Open Database

Shutting Down a Database

Shutting Down with the NORMAL Clause

Shutting Down with the IMMEDIATE Clause

Shutting Down with the TRANSACTIONAL Clause

Shutting Down with the ABORT Clause

Quiescing a Database

Placing a Database into a Quiesced State

Restoring the System to Normal Operation

Viewing the Quiesce State of an Instance

Suspending and Resuming a Database

4 Managing Oracle Database Processes

About Dedicated and Shared Server Processes

Dedicated Server Processes

Shared Server Processes

Configuring Oracle Database for Shared Server

Initialization Parameters for Shared Server

Enabling Shared Server

Determining a Value for SHARED_SERVERS

Decreasing the Number of Shared Server Processes

Limiting the Number of Shared Server Processes

Limiting the Number of Shared Server Sessions

Protecting Shared Memory

Configuring Dispatchers

DISPATCHERS Initialization Parameter Attributes

Determining the Number of Dispatchers

Setting the Initial Number of Dispatchers

Altering the Number of Dispatchers

Shutting Down Specific Dispatcher Processes

Disabling Shared Servers

Monitoring Shared Server

About Oracle Database Background Processes

Managing Processes for Parallel SQL Execution

About Parallel Execution Servers

Altering Parallel Execution for a Session

Disabling Parallel SQL Execution

Enabling Parallel SQL Execution

Forcing Parallel SQL Execution

Managing Processes for External Procedures

Terminating Sessions

Identifying Which Session to Terminate

Terminating an Active Session

Terminating an Inactive Session

Monitoring the Operation of Your Database

Server-Generated Alerts

Using APIs to Administer Server-Generated Alerts

Viewing Alert Data

Monitoring the Database Using Trace Files and the Alert File

Using the Trace Files

Specifying the Location of Trace Files

Controlling the Size of Trace Files

Controlling When Oracle Database Writes to Trace Files

Reading the Trace File for Shared Server Sessions

Monitoring Locks

Monitoring Wait Events

Process and Session Views

Part II Oracle Database Structure and Storage

5 Managing Control Files

What Is a Control File?

Guidelines for Control Files

Provide Filenames for the Control Files

Multiplex Control Files on Different Disks

Back Up Control Files

Manage the Size of Control Files

Creating Control Files

Creating Initial Control Files

Creating Additional Copies, Renaming, and Relocating Control Files

Creating New Control Files

When to Create New Control Files

The CREATE CONTROLFILE Statement

Steps for Creating New Control Files

Troubleshooting After Creating Control Files

Checking for Missing or Extra Files

Handling Errors During CREATE CONTROLFILE

Backing Up Control Files

Recovering a Control File Using a Current Copy

Recovering from Control File Corruption Using a Control File Copy

Recovering from Permanent Media Failure Using a Control File Copy

Dropping Control Files

Displaying Control File Information

6 Managing the Redo Log

What Is the Redo Log?

Redo Log Contents

How Oracle Database Writes to the Redo Log

Active (Current) and Inactive Redo Log Files

Log Switches and Log Sequence Numbers

Planning the Redo Log

Multiplexing Redo Log Files

Responding to Redo Log Failure

Legal and Illegal Configurations

Placing Redo Log Members on Different Disks

Setting the Size of Redo Log Members

Choosing the Number of Redo Log Files

Controlling Archive Lag

Setting the ARCHIVE_LAG_TARGET Initialization Parameter

Factors Affecting the Setting of ARCHIVE_LAG_TARGET

Creating Redo Log Groups and Members

Creating Redo Log Groups

Creating Redo Log Members

Relocating and Renaming Redo Log Members

Dropping Redo Log Groups and Members

Dropping Log Groups

Dropping Redo Log Members

Forcing Log Switches

Verifying Blocks in Redo Log Files

Clearing a Redo Log File

Viewing Redo Log Information

7 Managing Archived Redo Logs

What Is the Archived Redo Log?

Choosing Between NOARCHIVELOG and ARCHIVELOG Mode

Running a Database in NOARCHIVELOG Mode

Running a Database in ARCHIVELOG Mode

Controlling Archiving

Setting the Initial Database Archiving Mode

Changing the Database Archiving Mode

Performing Manual Archiving

Adjusting the Number of Archiver Processes

Specifying the Archive Destination

Specifying Archive Destinations

Method 1: Using the LOG_ARCHIVE_DEST_n Parameter

Method 2: Using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST

Understanding Archive Destination Status

Specifying the Mode of Log Transmission

Normal Transmission Mode

Standby Transmission Mode

Managing Archive Destination Failure

Specifying the Minimum Number of Successful Destinations

Specifying Mandatory and Optional Destinations

Specifying the Number of Successful Destinations: Scenarios

Rearchiving to a Failed Destination

Controlling Trace Output Generated by the Archivelog Process

Viewing Information About the Archived Redo Log

Dynamic Performance Views

The ARCHIVE LOG LIST Command

8 Managing Tablespaces

Guidelines for Managing Tablespaces

Using Multiple Tablespaces

Assigning Tablespace Quotas to Users

Creating Tablespaces

Locally Managed Tablespaces

Creating a Locally Managed Tablespace

Specifying Segment Space Management in Locally Managed Tablespaces

Altering a Locally Managed Tablespace

Bigfile Tablespaces

Creating a Bigfile Tablespace

Altering a Bigfile Tablespace

Identifying a Bigfile Tablespace

Dictionary-Managed Tablespaces

Creating a Dictionary-Managed Tablespace

Specifying Tablespace Default Storage Parameters

Altering a Dictionary-Managed Tablespace

Coalescing Free Space in Dictionary-Managed Tablespaces

Temporary Tablespaces

Creating a Locally Managed Temporary Tablespace

Creating a Bigfile Temporary Tablespace

Altering a Locally Managed Temporary Tablespace

Creating a Dictionary-Managed Temporary Tablespace

Altering a Dictionary-Managed Temporary Tablespace

Multiple Temporary Tablespaces: Using Tablespace Groups

Creating a Tablespace Group

Changing Members of a Tablespace Group

Assigning a Tablespace Group as the Default Temporary Tablespace

Specifying Nonstandard Block Sizes for Tablespaces

Controlling the Writing of Redo Records

Altering Tablespace Availability

Taking Tablespaces Offline

Bringing Tablespaces Online

Using Read-Only Tablespaces

Making a Tablespace Read-Only

Making a Read-Only Tablespace Writable

Creating a Read-Only Tablespace on a WORM Device

Delaying the Opening of Datafiles in Read-Only Tablespaces

Renaming Tablespaces

Dropping Tablespaces

Managing the SYSAUX Tablespace

Monitoring Occupants of the SYSAUX Tablespace

Moving Occupants Out Of or Into the SYSAUX Tablespace

Controlling the Size of the SYSAUX Tablespace

Diagnosing and Repairing Locally Managed Tablespace Problems

Scenario 1: Fixing Bitmap When Allocated Blocks are Marked Free (No Overlap)

Scenario 2: Dropping a Corrupted Segment

Scenario 3: Fixing Bitmap Where Overlap is Reported

Scenario 4: Correcting Media Corruption of Bitmap Blocks

Scenario 5: Migrating from a Dictionary-Managed to a Locally Managed Tablespace

Migrating the SYSTEM Tablespace to a Locally Managed Tablespace

Transporting Tablespaces Between Databases

Introduction to Transportable Tablespaces

About Transporting Tablespaces Across Platforms

Limitations on Transportable Tablespace Use

Compatibility Considerations for Transportable Tablespaces

Transporting Tablespaces Between Databases: A Procedure and Example

Step 1: Determine if Platforms are Supported and Endianness

Step 2: Pick a Self-Contained Set of Tablespaces

Step 3: Generate a Transportable Tablespace Set

Step 4: Transport the Tablespace Set

Step 5: Plug In the Tablespace Set

Using Transportable Tablespaces: Scenarios

Transporting and Attaching Partitions for Data Warehousing

Publishing Structured Data on CDs

Mounting the Same Tablespace Read-Only on Multiple Databases

Archiving Historical Data Using Transportable Tablespaces

Using Transportable Tablespaces to Perform TSPITR

Moving Databases Across Platforms Using Transportable Tablespaces

Viewing Tablespace Information

Example 1: Listing Tablespaces and Default Storage Parameters

Example 2: Listing the Datafiles and Associated Tablespaces of a Database

Example 3: Displaying Statistics for Free Space (Extents) of Each Tablespace

9 Managing Datafiles and Tempfiles

Guidelines for Managing Datafiles

Determine the Number of Datafiles

Determine a Value for the DB_FILES Initialization Parameter

Consider Possible Limitations When Adding Datafiles to a Tablespace

Consider the Performance Impact

Determine the Size of Datafiles

Place Datafiles Appropriately

Store Datafiles Separate from Redo Log Files

Creating Datafiles and Adding Datafiles to a Tablespace

Changing Datafile Size

Enabling and Disabling Automatic Extension for a Datafile

Manually Resizing a Datafile

Altering Datafile Availability

Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode

Taking Datafiles Offline in NOARCHIVELOG Mode

Altering the Availability of All Datafiles or Tempfiles in a Tablespace

Renaming and Relocating Datafiles

Procedures for Renaming and Relocating Datafiles in a Single Tablespace

Procedure for Renaming Datafiles in a Single Tablespace

Procedure for Relocating Datafiles in a Single Tablespace

Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces

Dropping Datafiles

Verifying Data Blocks in Datafiles

Copying Files Using the Database Server

Copying a File on a Local File System

Third-Party File Transfer

File Transfer and the DBMS_SCHEDULER Package

Advanced File Transfer Mechanisms

Mapping Files to Physical Devices

Overview of Oracle Database File Mapping Interface

How the Oracle Database File Mapping Interface Works

Components of File Mapping

Mapping Structures

Example of Mapping Structures

Configuration ID

Using the Oracle Database File Mapping Interface

Enabling File Mapping

Using the DBMS_STORAGE_MAP Package

Obtaining Information from the File Mapping Views

File Mapping Examples

Example 1: Map All Database Files that Span a Device

Example 2: Map a File into Its Corresponding Devices

Example 3: Map a Database Object

Viewing Datafile Information

10 Managing the Undo Tablespace

Introduction to Automatic Undo Management

Overview of Automatic Undo Management

Automatic Tuning of Undo Retention

Setting the UNDO_RETENTION Initialization Parameter

Retention Guarantee

Sizing the Undo Tablespace

Using Auto-Extensible Tablespaces

Sizing Fixed-Size Undo Tablespaces

The Undo Advisor PL/SQL Interface

Calculating the Space Requirements For Undo Retention

Managing Undo Tablespaces

Creating an Undo Tablespace

Using CREATE DATABASE to Create an Undo Tablespace

Using the CREATE UNDO TABLESPACE Statement

Altering an Undo Tablespace

Dropping an Undo Tablespace

Switching Undo Tablespaces

Establishing User Quotas for Undo Space

Monitoring the Undo Tablespace

Flashback Features and Undo Space

Flashback Query

Flashback Versions Query

Flashback Transaction Query

Flashback Table

Migration to Automatic Undo Management

Part III Automated File and Storage Management

11 Using Oracle-Managed Files

What Are Oracle-Managed Files?

Who Can Use Oracle-Managed Files?

What Is a Logical Volume Manager?

What Is a File System?

Benefits of Using Oracle-Managed Files

Oracle-Managed Files and Existing Functionality

Enabling the Creation and Use of Oracle-Managed Files

Setting the DB_CREATE_FILE_DEST Initialization Parameter

Setting the DB_RECOVERY_FILE_DEST Parameter

Setting the DB_CREATE_ONLINE_LOG_DEST_n Initialization Parameter

Creating Oracle-Managed Files

How Oracle-Managed Files Are Named

Creating Oracle-Managed Files at Database Creation

Specifying Control Files at Database Creation

Specifying Redo Log Files at Database Creation

Specifying the SYSTEM and SYSAUX Tablespace Datafiles at Database Creation

Specifying the Undo Tablespace Datafile at Database Creation

Specifying the Default Temporary Tablespace Tempfile at Database Creation

CREATE DATABASE Statement Using Oracle-Managed Files: Examples

Creating Datafiles for Tablespaces Using Oracle-Managed Files

CREATE TABLESPACE: Examples

CREATE UNDO TABLESPACE: Example

ALTER TABLESPACE: Example

Creating Tempfiles for Temporary Tablespaces Using Oracle-Managed Files

CREATE TEMPORARY TABLESPACE: Example

ALTER TABLESPACE ... ADD TEMPFILE: Example

Creating Control Files Using Oracle-Managed Files

CREATE CONTROLFILE Using NORESETLOGS Keyword: Example

CREATE CONTROLFILE Using RESETLOGS Keyword: Example

Creating Redo Log Files Using Oracle-Managed Files

Using the ALTER DATABASE ADD LOGFILE Statement

Using the ALTER DATABASE OPEN RESETLOGS Statement

Creating Archived Logs Using Oracle-Managed Files

Behavior of Oracle-Managed Files

Dropping Datafiles and Tempfiles

Dropping Redo Log Files

Managing Standby Databases

Scenarios for Using Oracle-Managed Files

Scenario 1: Create and Manage a Database with Multiplexed Redo Logs

Scenario 2: Create and Manage a Database with Database Area and Flash Recovery Area

Scenario 3: Adding Oracle-Managed Files to an Existing Database

12 Using Automatic Storage Management

What Is Automatic Storage Management?

Overview of the Components of Automatic Storage Management

Administering an ASM Instance

Installation of ASM

Authentication for Accessing an ASM Instance

Setting Initialization Parameters for an ASM Instance

Initialization Parameters for ASM Instances

Tuning Rebalance Operations

Improving Disk Discovery Time

Behavior of Database Initialization Parameters in an ASM Instance

Starting Up an ASM Instance

ASM Instance Memory Requirements

Disk Group Recovery

Shutting Down an ASM Instance

Disk Group Fixed Tables

Configuring the Components of Automatic Storage Management

Considerations and Guidelines for Configuring an ASM Instance

Determining the Number of Disk Groups

Storage Arrays and Automatic Storage Management

Consider Performance Characteristics when Grouping Disks

Effects of Adding and Dropping Disks from a Disk Group

Failure Groups and Mirroring

Creating a Disk Group

Altering the Disk Membership of a Disk Group

Adding Disks to a Disk Group

Dropping Disks from Disk Groups

Resizing Disks in Disk Groups

Undropping Disks in Disk Groups

Manually Rebalancing a Disk Group

Mounting and Dismounting Disk Groups

Managing Disk Group Templates

Adding Templates to a Disk Group

Modifying a Disk Group Template

Dropping Templates from a Disk Group

Managing Disk Group Directories

Creating a New Directory

Renaming a Directory

Dropping a Directory

Managing Alias Names for ASM Filenames

Adding an Alias Name for an ASM Filename

Renaming an Alias Name for an ASM Filename

Dropping an Alias Name for an ASM Filename

Dropping Files and Associated Aliases from a Disk Group

Checking Internal Consistency of Disk Group Metadata

Dropping Disk Groups

Using Automatic Storage Management in the Database

What Types of Files Does Automatic Storage Management Support?

About ASM Filenames

Fully Qualified ASM Filename

Numeric ASM Filename

Alias ASM Filenames

Alias ASM Filename with Template

Incomplete ASM Filename

Incomplete ASM Filename with Template

Starting the ASM and Database Instances

Creating and Referencing ASM Files in the Database

Creating ASM Files Using a Default Disk Group Specification

Using ASM Filenames in SQL Statements

Creating a Database Using Automatic Storage Management

Creating Tablespaces Using Automatic Storage Management

Creating Redo Logs Using Automatic Storage Management

Creating a Control File Using Automatic Storage Management

Creating Archive Log Files Using Automatic Storage Management

Recovery Manager (RMAN) and Automatic Storage Management

Viewing Information About Automatic Storage Management

Part IV Schema Objects

13 Managing Space for Schema Objects

Managing Space in Data Blocks

Specifying the PCTFREE Parameter

Effects of Specifying a Smaller PCTFREE

Effects of Specifying a Larger PCTFREE

PCTFREE for Nonclustered Tables

PCTFREE for Clustered Tables

PCTFREE for Indexes

Specifying the PCTUSED Parameter

Effects of Specifying a Smaller PCTUSED

Effects of Specifying a Larger PCTUSED

Selecting Associated PCTUSED and PCTFREE Values

Specifying the INITRANS Parameter

Managing Space in Tablespaces

Managing Storage Parameters

Identifying the Storage Parameters

Setting Default Storage Parameters for Objects Created in a Tablespace

Specifying Storage Parameters at Object Creation

Setting Storage Parameters for Clusters

Setting Storage Parameters for Partitioned Tables

Setting Storage Parameters for Index Segments

Setting Storage Parameters for LOBs, Varrays, and Nested Tables

Changing Values of Storage Parameters

Understanding Precedence in Storage Parameters

Example of How Storage Parameters Effect Space Allocation

Managing Resumable Space Allocation

Resumable Space Allocation Overview

How Resumable Space Allocation Works

What Operations are Resumable?

What Errors are Correctable?

Resumable Space Allocation Limitations for Dictionary-Managed Tablespaces

Resumable Space Allocation and Distributed Operations

Parallel Execution and Resumable Space Allocation

Enabling and Disabling Resumable Space Allocation

Setting the RESUMABLE_TIMEOUT Initialization Parameter

Using ALTER SESSION to Enable and Disable Resumable Space Allocation

Using a LOGON Trigger to Set Default Resumable Mode

Detecting Suspended Statements

Notifying Users: The AFTER SUSPEND System Event and Trigger

Using Views to Obtain Information About Suspended Statements

Using the DBMS_RESUMABLE Package

Operation-Suspended Alert

Resumable Space Allocation Example: Registering an AFTER SUSPEND Trigger

Reclaiming Unused Space

Segment Advisor

Shrinking Database Segments

Deallocating Unused Space

Understanding Space Usage of Datatypes

Displaying Information About Space Usage for Schema Objects

Using PL/SQL Packages to Display Information About Schema Object Space Usage

Using Views to Display Information About Space Usage in Schema Objects

Example 1: Displaying Segment Information

Example 2: Displaying Extent Information

Example 3: Displaying the Free Space (Extents) in a Tablespace

Example 4: Displaying Segments that Cannot Allocate Additional Extents

Capacity Planning for Database Objects

Estimating the Space Use of a Table

Estimating the Space Use of an Index

Obtaining Object Growth Trends

14 Managing Tables

Guidelines for Managing Tables

Design Tables Before Creating Them

Consider Your Options for the Type of Table to Create

Specify How Data Block Space Is to Be Used

Specify the Location of Each Table

Consider Parallelizing Table Creation

Consider Using NOLOGGING When Creating Tables

Consider Using Table Compression when Creating Tables

Estimate Table Size and Plan Accordingly

Restrictions to Consider When Creating Tables

Creating Tables

Creating a Table

Creating a Temporary Table

Parallelizing Table Creation

Inserting Data Into Tables Using Direct-Path INSERT

Advantages of Using Direct-Path INSERT

Enabling Direct-Path INSERT

How Direct-Path INSERT Works

Serial Direct-Path INSERT into Partitioned or Nonpartitioned Tables

Parallel Direct-Path INSERT into Partitioned Tables

Parallel Direct-Path INSERT into Nonpartitioned Tables

Specifying the Logging Mode for Direct-Path INSERT

Direct-Path INSERT with Logging

Direct-Path INSERT without Logging

Additional Considerations for Direct-Path INSERT

Index Maintenance with Direct-Path INSERT

Space Considerations with Direct-Path INSERT

Locking Considerations with Direct-Path INSERT

Automatically Collecting Statistics on Tables

Altering Tables

Reasons for Using the ALTER TABLE Statement

Altering Physical Attributes of a Table

Moving a Table to a New Segment or Tablespace

Manually Allocating Storage for a Table

Modifying an Existing Column Definition

Adding Table Columns

Renaming Table Columns

Dropping Table Columns

Removing Columns from Tables

Marking Columns Unused

Removing Unused Columns

Redefining Tables Online

Features of Online Table Redefinition

The DBMS_REDEFINITION Package

Steps for Online Redefinition of Tables

Intermediate Synchronization

Terminate and Clean Up After Errors

Example of Online Table Redefinition

Restrictions for Online Redefinition of Tables

Auditing Table Changes Using Flashback Transaction Query

Recovering Tables Using the Flashback Table Feature

Dropping Tables

Using Flashback Drop and Managing the Recycle Bin

What Is the Recycle Bin?

Renaming Objects in the Recycle Bin

Viewing and Querying Objects in the Recycle Bin

Purging Objects in the Recycle Bin

Restoring Tables from the Recycle Bin

Managing Index-Organized Tables

What Are Index-Organized Tables?

Creating Index-Organized Tables

Creating an Index-Organized Table

Creating Index-Organized Tables that Contain Object Types

Using the Overflow Clause

Choosing and Monitoring a Threshold Value

Using the INCLUDING Clause

Parallelizing Index-Organized Table Creation

Using Key Compression

Maintaining Index-Organized Tables

Altering Index-Organized Tables

Moving (Rebuilding) Index-Organized Tables

Creating Secondary Indexes on Index-Organized Tables

Creating a Secondary Index on an Index-Organized Table

Maintaining Physical Guesses in Logical Rowids

Analyzing Index-Organized Tables

Collecting Optimizer Statistics for Index-Organized Tables

Validating the Structure of Index-Organized Tables

Using the ORDER BY Clause with Index-Organized Tables

Converting Index-Organized Tables to Regular Tables

Managing External Tables

Creating External Tables

Altering External Tables

Dropping External Tables

System and Object Privileges for External Tables

Viewing Information About Tables

15 Managing Indexes

Guidelines for Managing Indexes

Create Indexes After Inserting Table Data

Index the Correct Tables and Columns

Order Index Columns for Performance

Limit the Number of Indexes for Each Table

Drop Indexes That Are No Longer Required

Specify Index Block Space Use

Estimate Index Size and Set Storage Parameters

Specify the Tablespace for Each Index

Consider Parallelizing Index Creation

Consider Creating Indexes with NOLOGGING

Consider Costs and Benefits of Coalescing or Rebuilding Indexes

Consider Cost Before Disabling or Dropping Constraints

Creating Indexes

Creating an Index Explicitly

Creating a Unique Index Explicitly

Creating an Index Associated with a Constraint

Specifying Storage Options for an Index Associated with a Constraint

Specifying the Index Associated with a Constraint

Collecting Incidental Statistics when Creating an Index

Creating a Large Index

Creating an Index Online

Creating a Function-Based Index

Creating a Key-Compressed Index

Altering Indexes

Altering Storage Characteristics of an Index

Rebuilding an Existing Index

Monitoring Index Usage

Monitoring Space Use of Indexes

Dropping Indexes

Viewing Index Information

16 Managing Partitioned Tables and Indexes

About Partitioned Tables and Indexes

Partitioning Methods

When to Use Range Partitioning

When to Use Hash Partitioning

When to Use List Partitioning

When to Use Composite Range-Hash Partitioning

When to Use Composite Range-List Partitioning

Creating Partitioned Tables

Creating Range-Partitioned Tables and Global Indexes

Creating a Range Partitioned Table

Creating a Range-Partitioned Global Index

Creating Hash-Partitioned Tables and Global Indexes

Creating a Hash Partitioned Table

Creating a Hash-Partitioned Global Index

Creating List-Partitioned Tables

Creating Composite Range-Hash Partitioned Tables

Creating Composite Range-List Partitioned Tables

Using Subpartition Templates to Describe Composite Partitioned Tables

Specifying a Subpartition Template for a Range-Hash Partitioned Table

Specifying a Subpartition Template for a Range-List Partitioned Table

Using Multicolumn Partitioning Keys

Using Table Compression with Partitioned Tables

Using Key Compression with Partitioned Indexes

Creating Partitioned Index-Organized Tables

Creating Range-Partitioned Index-Organized Tables

Creating List-Partitioned Index-Organized Tables

Creating Hash-Partitioned Index-Organized Tables

Partitioning Restrictions for Multiple Block Sizes

Maintaining Partitioned Tables

Updating Indexes Automatically

Adding Partitions

Adding a Partition to a Range-Partitioned Table

Adding a Partition to a Hash-Partitioned Table

Adding a Partition to a List-Partitioned Table

Adding Partitions to a Range-Hash Composite-Partitioned Table

Adding Partitions to a Range-List Partitioned Table

Adding Index Partitions

Coalescing Partitions

Coalescing a Partition in a Hash-Partitioned Table

Coalescing a Subpartition in a Range-Hash Partitioned Table

Coalescing Hash-partitioned Global Indexes

Dropping Partitions

Dropping a Table Partition

Dropping Index Partitions

Exchanging Partitions

Exchanging a Range, Hash, or List Partition

Exchanging a Hash-Partitioned Table with a Range-Hash Partition

Exchanging a Subpartition of a Range-Hash Partitioned Table

Exchanging a List-Partitioned Table with a Range-List Partition

Exchanging a Subpartition of a Range-List Partitioned Table

Merging Partitions

Merging Range Partitions

Merging List Partitions

Merging Range-Hash Partitions

Merging Range-List Partitions

Modifying Default Attributes

Modifying Default Attributes of a Table

Modifying Default Attributes of a Partition

Modifying Default Attributes of Index Partitions

Modifying Real Attributes of Partitions

Modifying Real Attributes for a Range or List Partition

Modifying Real Attributes for a Hash Partition

Modifying Real Attributes of a Subpartition

Modifying Real Attributes of Index Partitions

Modifying List Partitions: Adding Values

Adding Values for a List Partition

Adding Values for a List Subpartition

Modifying List Partitions: Dropping Values

Dropping Values from a List Partition

Dropping Values from a List Subpartition

Modifying a Subpartition Template

Moving Partitions

Moving Table Partitions

Moving Subpartitions

Moving Index Partitions

Rebuilding Index Partitions

Rebuilding Global Index Partitions

Rebuilding Local Index Partitions

Renaming Partitions

Renaming a Table Partition

Renaming a Table Subpartition

Renaming Index Partitions

Splitting Partitions

Splitting a Partition of a Range-Partitioned Table

Splitting a Partition of a List-Partitioned Table

Splitting a Range-Hash Partition

Splitting Partitions in a Range-List Partitioned Table

Splitting Index Partitions

Optimizing SPLIT PARTITION and SPLIT SUBPARTITION Operations

Truncating Partitions

Truncating a Table Partition

Truncating a Subpartition

Partitioned Tables and Indexes Example

Viewing Information About Partitioned Tables and Indexes

17 Managing Clusters

Guidelines for Managing Clusters

Choose Appropriate Tables for the Cluster

Choose Appropriate Columns for the Cluster Key

Specify Data Block Space Use

Specify the Space Required by an Average Cluster Key and Its Associated Rows

Specify the Location of Each Cluster and Cluster Index Rows

Estimate Cluster Size and Set Storage Parameters

Creating Clusters

Creating Clustered Tables

Creating Cluster Indexes

Altering Clusters

Altering Clustered Tables

Altering Cluster Indexes

Dropping Clusters

Dropping Clustered Tables

Dropping Cluster Indexes

Viewing Information About Clusters

18 Managing Hash Clusters

About Hash Clusters

When to Use Hash Clusters

Situations Where Hashing Is Useful

Situations Where Hashing Is Not Advantageous

Creating Hash Clusters

Creating a Sorted Hash Cluster

Creating Single-Table Hash Clusters

Controlling Space Use Within a Hash Cluster

Choosing the Key

Setting HASH IS

Setting HASHKEYS

Controlling Space in Hash Clusters

Estimating Size Required by Hash Clusters

Altering Hash Clusters

Dropping Hash Clusters

Viewing Information About Hash Clusters

19 Managing Views, Sequences, and Synonyms

Expansion of Defining Queries at View Creation Time

Creating Views with Errors

Replacing Views

Using Views in Queries

Updating a Join View

Key-Preserved Tables

DML Statements and Join Views

Updating Views That Involve Outer Joins

Using the UPDATABLE_ COLUMNS Views

Managing Sequences

About Sequences

Creating Sequences

Altering Sequences

Using Sequences

Referencing a Sequence

Caching Sequence Numbers

Dropping Sequences

Managing Synonyms

Creating Synonyms

Using Synonyms in DML Statements

Dropping Synonyms

Viewing Information About Views, Synonyms, and Sequences

20 General Management of Schema Objects

Creating Multiple Tables and Views in a Single Operation

Analyzing Tables, Indexes, and Clusters

Using DBMS_STATS to Collect Table and Index Statistics

Validating Tables, Indexes, Clusters, and Materialized Views

Listing Chained Rows of Tables and Clusters

Creating a CHAINED_ROWS Table

Eliminating Migrated or Chained Rows in a Table

Truncating Tables and Clusters

Using DROP and CREATE

Enabling and Disabling Triggers

Enabling Triggers

Disabling Triggers

Managing Integrity Constraints

Integrity Constraint States

Disabling Constraints

Enabling Constraints

Enable Novalidate Constraint State

Efficient Use of Integrity Constraints: A Procedure

Setting Integrity Constraints Upon Definition

Disabling Constraints Upon Definition

Enabling Constraints Upon Definition

Modifying, Renaming, or Dropping Existing Integrity Constraints

Disabling Enabled Constraints

Renaming Constraints

Dropping Constraints

Deferring Constraint Checks

Set All Constraints Deferred

Check the Commit (Optional)

Reporting Constraint Exceptions

Viewing Constraint Information

Renaming Schema Objects

Managing Object Dependencies

Manually Recompiling Views

Manually Recompiling Procedures and Functions

Manually Recompiling Packages

Managing Object Name Resolution

Switching to a Different Schema

Displaying Information About Schema Objects

Using a PL/SQL Package to Display Information About Schema Objects

Using Views to Display Information About Schema Objects

Example 1: Displaying Schema Objects By Type

Example 2: Displaying Dependencies of Views and Synonyms

21 Detecting and Repairing Data Block Corruption

Options for Repairing Data Block Corruption

About the DBMS_REPAIR Package

DBMS_REPAIR Procedures

Limitations and Restrictions

Using the DBMS_REPAIR Package

Task 1: Detect and Report Corruptions

DBMS_REPAIR: Using the CHECK_OBJECT and ADMIN_TABLES Procedures

DB_VERIFY: Performing an Offline Database Check

ANALYZE: Corruption Reporting

DB_BLOCK_CHECKING (Block Checking Initialization Parameter)

Task 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR

Task 3: Make Objects Usable

Corruption Repair: Using the FIX_CORRUPT_BLOCKS and SKIP_CORRUPT_BLOCKS Procedures

Implications when Skipping Corrupt Blocks

Task 4: Repair Corruptions and Rebuild Lost Data

Recover Data Using the DUMP_ORPHAN_KEYS Procedures

Repair Free Lists Using the REBUILD_FREELISTS Procedure

Fix Segment Bitmaps Using the SEGMENT_FIX_STATUS Procedure

DBMS_REPAIR Examples

Using ADMIN_TABLES to Build a Repair Table or Orphan Key Table

Creating a Repair Table

Creating an Orphan Key Table

Using the CHECK_OBJECT Procedure to Detect Corruption

Fixing Corrupt Blocks with the FIX_CORRUPT_BLOCKS Procedure

Finding Index Entries Pointing into Corrupt Data Blocks: DUMP_ORPHAN_KEYS

Rebuilding Free Lists Using the REBUILD_FREELISTS Procedure

Enabling or Disabling the Skipping of Corrupt Blocks: SKIP_CORRUPT_BLOCKS

Part V Database Security

22 Managing Users and Securing the Database

The Importance of Establishing a Security Policy for Your Database

Managing Users and Resources

Managing User Privileges and Roles

Auditing Database Use

Part VI Database Resource Management and Task Scheduling

23 Managing Automatic System Tasks Using the Maintenance Window

Maintenance Windows

Automatic Statistics Collection Job

Resource Management

24 Using the Database Resource Manager

What Is the Database Resource Manager?

What Problems Does the Database Resource Manager Address?

How Does the Database Resource Manager Address These Problems?

What Are the Elements of the Database Resource Manager?

Understanding Resource Plans

A Single-Level Resource Plan

A Multilevel Resource Plan

Resource Consumer Groups

Resource Plan Directives

Administering the Database Resource Manager

Creating a Simple Resource Plan

Creating Complex Resource Plans

Using the Pending Area for Creating Plan Schemas

Creating a Pending Area

Validating Changes

Submitting Changes

Clearing the Pending Area

Creating Resource Plans

Creating a Plan

Updating a Plan

Deleting a Plan

Using the Ratio Policy

Creating Resource Consumer Groups

Creating a Consumer Group

Updating a Consumer Group

Deleting a Consumer Group

Specifying Resource Plan Directives

Creating a Resource Plan Directive

Updating Resource Plan Directives

Deleting Resource Plan Directives

How Resource Plan Directives Interact

Managing Resource Consumer Groups

Assigning an Initial Resource Consumer Group

Changing Resource Consumer Groups

Switching a Session

Switching Sessions for a User

Using the DBMS_SESSION Package to Switch Consumer Group

Managing the Switch Privilege

Granting the Switch Privilege

Revoking Switch Privileges

Automatically Assigning Resource Consumer Groups to Sessions

Creating Consumer Group Mappings

Creating Attribute Mapping Priorities

Explicit Session Switching

Automatic Group Switching

Enabling the Database Resource Manager

Putting It All Together: Database Resource Manager Examples

Multilevel Schema Example

Example of Using Several Resource Allocation Methods

An Oracle-Supplied Plan

Monitoring and Tuning the Database Resource Manager

Creating the Environment

Why Is This Necessary to Produce Expected Results?

Monitoring Results

Interaction with Operating-System Resource Control

Guidelines for Using Operating-System Resource Control

Dynamic Reconfiguration

Viewing Database Resource Manager Information

Viewing Consumer Groups Granted to Users or Roles

Viewing Plan Schema Information

Viewing Current Consumer Groups for Sessions

Viewing the Currently Active Plans

25 Moving from DBMS_JOB to DBMS_SCHEDULER

Moving from DBMS_JOB to DBMS_SCHEDULER

Removing a Job from the Job Queue

26 Overview of Scheduler Concepts

Overview of the Scheduler

What Can the Scheduler Do?

Basic Scheduler Concepts

General Rules for all Database Objects

How Programs, Jobs, and Schedules are Related

Advanced Scheduler Concepts

Overlapping Windows

Example of Window Group

Scheduler Architecture

The Job Coordinator

How Jobs Execute

Using the Scheduler in RAC Environments

Service Affinity when Using the Scheduler

27 Using the Scheduler

Scheduler Objects and Their Naming

Administering Jobs

Job Tasks and Their Procedures

Setting Job Arguments

Ways of Creating Jobs

Privileges Required for Copying a Job

Running Jobs Asynchronously

Running Jobs Synchronously

Job Run Environment

Running External Jobs

Administering Programs

Program Tasks and Their Procedures

Creating Programs

Defining Program Arguments

Altering Programs

Dropping Programs

Disabling Programs

Enabling Programs

Administering Schedules

Schedule Tasks and Their Procedures

Creating Schedules

Altering Schedules

Dropping Schedules

Setting the Repeat Interval

Using the Scheduler Calendaring Syntax

Using a PL/SQL Expression

Differences Between PL/SQL Expression and Calendaring Syntax Behavior

Repeat Intervals and Daylight Savings

Administering Job Classes

Job Class Tasks and Their Procedures

Creating Job Classes

Altering Job Classes

Dropping Job Classes

Administering Windows

Window Tasks and Their Procedures

Creating Windows

Creating Windows Using a Saved Schedule

Altering Windows

Opening Windows

Closing Windows

Dropping Windows

Disabling Windows

Enabling Windows

Overlapping Windows

Changing Resource Plans

Examples of Overlapping Windows

Administering Window Groups

Window Group Tasks and Their Procedures

Creating Window Groups

Dropping Window Groups

Adding a Member to a Window Group

Dropping a Member from a Window Group

Enabling a Window Group

Disabling a Window Group

Allocating Resources Among Jobs

Allocating Resources Among Jobs Using Resource Manager

Example of Resource Allocation for Jobs

28 Administering the Scheduler

Configuring the Scheduler

Monitoring and Managing the Scheduler

How to View Scheduler Information

How to View the Currently Active Window and Resource Plan

How to View Scheduler Privileges

How to Find Information About Currently Running Jobs

How the Job Coordinator Works

Job Coordinator and Real Application Clusters

Using DBMS_SCHEDULER and DBMS_JOB at the Same Time

Scheduler Attribute max_job_slave_processes

How to Monitor and Manage Window and Job Logs

Job Run Details

Controlling Job Logging

How to Manage Scheduler Privileges

Types of Privileges and Their Descriptions

How to Drop a Job

How to Drop a Running Job

Why Does a Job Not Run?

How to Change Job Priorities

How the Scheduler Guarantees Availability

How to Handle Scheduler Security

How to Manage the Scheduler in a RAC Environment

Import/Export and the Scheduler

Examples of Using the Scheduler

Examples of Creating Jobs

Examples of Creating Job Classes

Examples of Creating Programs

Examples of Creating Windows

Example of Creating Window Groups

Examples of Setting Attributes

Part VII Distributed Database Management

29 Distributed Database Concepts

Distributed Database Architecture

Homogenous Distributed Database Systems

Distributed Databases Versus Distributed Processing

Distributed Databases Versus Replicated Databases

Heterogeneous Distributed Database Systems

Heterogeneous Services

Transparent Gateway Agents

Generic Connectivity

Client/Server Database Architecture

What Are Database Links?

What Are Shared Database Links?

Why Use Database Links?

Global Database Names in Database Links

Names for Database Links

Types of Database Links

Users of Database Links

Connected User Database Links

Fixed User Database Links

Current User Database Links

Creation of Database Links: Examples

Schema Objects and Database Links

Naming of Schema Objects Using Database Links

Authorization for Accessing Remote Schema Objects

Synonyms for Schema Objects

Schema Object Name Resolution

Database Link Restrictions

Distributed Database Administration

Distributed Database Security

Authentication Through Database Links

Authentication Without Passwords

Supporting User Accounts and Roles

Centralized User and Privilege Management

Data Encryption

Auditing Database Links

Administration Tools

Enterprise Manager

Third-Party Administration Tools

Transaction Processing in a Distributed System

Remote SQL Statements

Distributed SQL Statements

Shared SQL for Remote and Distributed Statements

Remote Transactions

Distributed Transactions

Two-Phase Commit Mechanism

Database Link Name Resolution

Name Resolution When the Global Database Name Is Complete

Name Resolution When the Global Database Name Is Partial

Name Resolution When No Global Database Name Is Specified

Terminating the Search for Name Resolution

Schema Object Name Resolution

Example of Global Object Name Resolution: Complete Object Name

Example of Global Object Name Resolution: Partial Object Name

Global Name Resolution in Views, Synonyms, and Procedures

What Happens When Global Names Change

Scenarios for Global Name Changes

Distributed Database Application Development

Transparency in a Distributed Database System

Location Transparency

SQL and COMMIT Transparency

Replication Transparency

Remote Procedure Calls (RPCs)

Distributed Query Optimization

Character Set Support for Distributed Environments

Client/Server Environment

Homogeneous Distributed Environment

Heterogeneous Distributed Environment

30 Managing a Distributed Database

Managing Global Names in a Distributed System

Understanding How Global Database Names Are Formed

Determining Whether Global Naming Is Enforced

Viewing a Global Database Name

Changing the Domain in a Global Database Name

Changing a Global Database Name: Scenario

Creating Database Links

Obtaining Privileges Necessary for Creating Database Links

Specifying Link Types

Creating Private Database Links

Creating Public Database Links

Creating Global Database Links

Specifying Link Users

Creating Fixed User Database Links

Creating Connected User and Current User Database Links

Using Connection Qualifiers to Specify Service Names Within Link Names

Using Shared Database Links

Determining Whether to Use Shared Database Links

Creating Shared Database Links

Configuring Shared Database Links

Creating Shared Links to Dedicated Servers

Creating Shared Links to Shared Servers

Managing Database Links

Closing Database Links

Dropping Database Links

Procedure for Dropping a Private Database Link

Procedure for Dropping a Public Database Link

Limiting the Number of Active Database Link Connections

Viewing Information About Database Links

Determining Which Links Are in the Database

Authorization for Viewing Password Information

Viewing Password Information

Viewing Authentication Passwords

Determining Which Link Connections Are Open

Creating Location Transparency

Using Views to Create Location Transparency

Using Synonyms to Create Location Transparency

Creating Synonyms

Managing Privileges and Synonyms

Using Procedures to Create Location Transparency

Using Local Procedures to Reference Remote Data

Using Local Procedures to Call Remote Procedures

Using Local Synonyms to Reference Remote Procedures

Managing Procedures and Privileges

Managing Statement Transparency

Managing a Distributed Database: Examples

Example 1: Creating a Public Fixed User Database Link

Example 2: Creating a Public Fixed User Shared Database Link

Example 3: Creating a Public Connected User Database Link

Example 4: Creating a Public Connected User Shared Database Link

Example 5: Creating a Public Current User Database Link

31 Developing Applications for a Distributed Database System

Managing the Distribution of Application Data

Controlling Connections Established by Database Links

Maintaining Referential Integrity in a Distributed System

Tuning Distributed Queries

Using Collocated Inline Views

Using Cost-Based Optimization

How Does Cost-Based Optimization Work?

Setting Up Cost-Based Optimization

Using the NO_MERGE Hint

Using the DRIVING_SITE Hint

Analyzing the Execution Plan

Preparing the Database to Store the Plan

Generating the Execution Plan

Viewing the Execution Plan

Handling Errors in Remote Procedures

32 Distributed Transactions Concepts

What Are Distributed Transactions?

DML and DDL Transactions

Transaction Control Statements

Session Trees for Distributed Transactions

Database Servers

Local Coordinators

Global Coordinator

Commit Point Site

How a Distributed Transaction Commits

Commit Point Strength

Two-Phase Commit Mechanism

Types of Responses in the Prepare Phase

Steps in the Prepare Phase

Steps in the Commit Phase

Guaranteeing Global Database Consistency

In-Doubt Transactions

Automatic Resolution of In-Doubt Transactions

Failure During the Prepare Phase

Failure During the Commit Phase

Manual Resolution of In-Doubt Transactions

Relevance of System Change Numbers for In-Doubt Transactions

Distributed Transaction Processing: Case Study

Stage 1: Client Application Issues DML Statements

Stage 2: Oracle Database Determines Commit Point Site

Stage 3: Global Coordinator Sends Prepare Response

Stage 4: Commit Point Site Commits

Stage 5: Commit Point Site Informs Global Coordinator of Commit

Stage 6: Global and Local Coordinators Tell All Nodes to Commit

Stage 7: Global Coordinator and Commit Point Site Complete the Commit

33 Managing Distributed Transactions

Specifying the Commit Point Strength of a Node

Naming Transactions

Viewing Information About Distributed Transactions

Determining the ID Number and Status of Prepared Transactions

Tracing the Session Tree of In-Doubt Transactions

Deciding How to Handle In-Doubt Transactions

Discovering Problems with a Two-Phase Commit

Determining Whether to Perform a Manual Override

Analyzing the Transaction Data

Find a Node that Committed or Rolled Back

Look for Transaction Comments

Look for Transaction Advice

Manually Overriding In-Doubt Transactions

Manually Committing an In-Doubt Transaction

Committing Using Only the Transaction ID

Committing Using an SCN

Manually Rolling Back an In-Doubt Transaction

Purging Pending Rows from the Data Dictionary

Executing the PURGE_LOST_DB_ENTRY Procedure

Determining When to Use DBMS_TRANSACTION

Manually Committing an In-Doubt Transaction: Example

Step 1: Record User Feedback

Step 2: Query DBA_2PC_PENDING

Determining the Global Transaction ID

Determining the State of the Transaction

Looking for Comments or Advice

Step 3: Query DBA_2PC_NEIGHBORS on Local Node

Obtaining Database Role and Database Link Information

Determining the Commit Point Site

Step 4: Querying Data Dictionary Views on All Nodes

Checking the Status of Pending Transactions at sales

Determining the Coordinators and Commit Point Site at sales

Checking the Status of Pending Transactions at HQ

Step 5: Commit the In-Doubt Transaction

Step 6: Check for Mixed Outcome Using DBA_2PC_PENDING

Data Access Failures Due to Locks

Transaction Timeouts

Locks from In-Doubt Transactions

Simulating Distributed Transaction Failure

Forcing a Distributed Transaction to Fail

Disabling and Enabling RECO

Managing Read Consistency

Index