Skip Headers

Oracle® Database Administrator's Guide
10g Release 1 (10.1)

Part Number B10739-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to next page
Next
View PDF

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Audience
Organization
Related Documentation
Conventions
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
Database Users
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
SYS
SYSTEM
The DBA Role
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
Using ORAPWD
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
Advisors
Data Utilities
SQL*Loader
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
Using 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 Threads
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

What Is Undo?
Introduction to Automatic Undo Management
Overview of Automatic Undo Management
Undo Retention
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
Best Practices

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
Renaming 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 Discovery
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
Scalability
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

About 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
Bitmap Indexes
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

About 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

About 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 SIZE
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

Managing Views
About Views
Creating Views
Join Views
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
Altering Views
Dropping Views
Managing Sequences
About Sequences
Creating Sequences
Altering Sequences
Using Sequences
Referencing a Sequence
Caching Sequence Numbers
Dropping Sequences
Managing Synonyms
About 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 DELETE
Using DROP and CREATE
Using TRUNCATE
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
Creating a Job
Altering a Job
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
Programs
Schedules
Jobs
Job Instances
How Programs, Jobs, and Schedules are Related
Advanced Scheduler Concepts
Job Classes
Windows
Overlapping Windows
Window Groups
Example of Window Group
Scheduler Architecture
The Job Table
The Job Coordinator
How Jobs Execute
Job Slaves
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
Creating Jobs
Job Attributes
Setting Job Arguments
Ways of Creating Jobs
Copying Jobs
Privileges Required for Copying a Job
Altering Jobs
Running Jobs
Running Jobs Asynchronously
Running Jobs Synchronously
Job Run Environment
Running External Jobs
Stopping Jobs
Dropping Jobs
Disabling Jobs
Enabling 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
Window Logging
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 Logs
Job Run Details
Controlling Job Logging
Window Logs
Purging Logs
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?
Failed Jobs
Broken Jobs
Disabled Jobs
Completed Jobs
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
Database Links
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
Site Autonomy
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
SNMP Support
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 Hints
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
Clients
Database Servers
Local Coordinators
Global Coordinator
Commit Point Site
How a Distributed Transaction Commits
Commit Point Strength
Two-Phase Commit Mechanism
Prepare Phase
Types of Responses in the Prepare Phase
Steps in the Prepare Phase
Commit Phase
Steps in the Commit Phase
Guaranteeing Global Database Consistency
Forget Phase
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