Skip Headers
Oracle® Database Administrator's Guide
11g Release 1 (11.1)
Part Number B28310-04
Home
Book List
Index
Master Index
Contact Us
Next
View PDF
Contents
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
What's New in Oracle Database Administrator's Guide?
Oracle Database 11
g
Release 1 (11.1) New Features in the Administrator's Guide
Part I Basic Database Administration
1
Getting Started with Database Administration
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
Task 10: Download and Install Patches
Task 11: Roll Out to Additional Hosts
Submitting Commands and SQL to the Database
About SQL*Plus
Connecting to the Database with SQL*Plus
Step 1: Open a Command Window
Step 2: Set Operating System Environment Variables
Step 3: Start SQL*Plus
Step 4: Submit the SQL*Plus CONNECT Statement
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
About Database Administrator Security and Privileges
The Database Administrator's Operating System Account
Administrative User Accounts
SYS
SYSTEM
The DBA Role
Database Administrator Authentication
Administrative Privileges
SYSDBA and SYSOPER
Connecting with Administrative Privileges: Example
Selecting an Authentication Method for Database Administrators
Nonsecure Remote Connections
Local Connections and Secure Remote Connections
Using Operating System Authentication
OSDBA and OSOPER
Preparing to Use Operating System Authentication
Connecting Using Operating System Authentication
Using Password File Authentication
Preparing to Use Password File Authentication
Connecting Using Password File Authentication
Creating and Maintaining a Password File
Using ORAPWD
ORAPWD Command Line Argument Descriptions
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
Data Utilities
2
Creating and Configuring an Oracle Database
About Creating an Oracle Database
Considerations Before Creating the Database
Planning for Database Creation
Meeting Creation Prerequisites
Creating a Database with DBCA
Creating a Database with Interactive DBCA
Creating a Database with Noninteractive/Silent DBCA
Creating a Database with the CREATE DATABASE Statement
Step 1: Specify an Instance Identifier (SID)
Step 2: Ensure That the Required Environment Variables Are Set
Step 3: Choose a Database Administrator Authentication Method
Step 4: Create the Initialization Parameter File
Step 5: (Windows Only) Create an Instance
Step 6: Connect to the Instance
Step 7: Create a Server Parameter File
Step 8: Start the Instance
Step 9: Issue the CREATE DATABASE Statement
Step 10: Create Additional Tablespaces
Step 11: Run Scripts to Build Data Dictionary Views
Step 12: Run Scripts to Install Additional Options (Optional)
Step 13: Back Up the Database.
Step 14: (Optional) Enable Automatic Instance Startup
Specifying CREATE DATABASE Statement Clauses
Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM
Creating a Locally Managed SYSTEM Tablespace
About 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
Setting 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
Specifying Initialization Parameters
About Initialization Parameters and Initialization Parameter Files
Text Initialization Parameter File Format
Sample Initialization Parameter File
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
Specifying the Maximum Number of Processes
Specifying the DDL Lock Timeout
Specifying the Method of Undo Space Management
UNDO_MANAGEMENT Initialization Parameter
UNDO_TABLESPACE Initialization Parameter
About The COMPATIBLE Initialization Parameter
Setting the License Parameter
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
Storing the Server Parameter File on HARD-Enabled Storage
The SPFILE Initialization Parameter
Changing Initialization Parameter Values
Setting or Changing Initialization Parameter Values
Clearing Initialization Parameter Values
Exporting the Server Parameter File
Backing Up the Server Parameter File
Recovering a Lost or Damaged Server Parameter File
Viewing Parameter Settings
Defining Database Services
Deploying Services
Configuring Services
Using Services
Client-Side Use
Server-Side Use
Considerations After Creating a Database
Some Security Considerations
Enabling Transparent Data Encryption
Creating a Secure External Password Store
Installing the Oracle Database Sample Schemas
Dropping a Database
Database Data Dictionary Views
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
About Initialization Parameters and Startup
Preparing to Start Up an Instance
Starting Up an Instance
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
Shutdown Timeout and Abort
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 Processes
About Dedicated and Shared Server Processes
Dedicated Server Processes
Shared Server Processes
About Database Resident Connection Pooling
Differences Between Dedicated Servers, Shared Servers, and Database Resident Connection Pooling
Restrictions on Using Database Resident Connection Pooling
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
Shared Server Data Dictionary Views
Configuring Database Resident Connection Pooling
Enabling Database Resident Connection Pooling
Configuring the Connection Pool for Database Resident Connection Pooling
Data Dictionary Views for Database Resident Connection Pooling
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
Process and Session Data Dictionary Views
5
Managing Memory
About Memory Management
Memory Architecture Overview
Using Automatic Memory Management
About Automatic Memory Management
Enabling Automatic Memory Management
Monitoring and Tuning Automatic Memory Management
Configuring Memory Manually
Using Automatic Shared Memory Management
About Automatic Shared Memory Management
Components and Granules in the SGA
Setting Maximum SGA Size
Setting SGA Target Size
Enabling Automatic Shared Memory Management
Automatic Shared Memory Management Advanced Topics
Using Manual Shared Memory Management
Enabling Manual Shared Memory Management
Setting the Buffer Cache Initialization Parameters
Specifying the Shared Pool Size
Specifying the Large Pool Size
Specifying the Java Pool Size
Specifying the Streams Pool Size
Specifying the Result Cache Maximum Size
Specifying Miscellaneous SGA Initialization Parameters
Using Automatic PGA Memory Management
Using Manual PGA Memory Management
Memory Management Reference
Platforms That Support Automatic Memory Management
Memory Management Data Dictionary Views
6
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
Predefined User Accounts
7
Monitoring Database Operations
Monitoring Errors and Alerts
Monitoring Errors with Trace Files and the Alert Log
Controlling the Size of Trace Files
Controlling When Oracle Database Writes to Trace Files
Reading the Trace File for Shared Server Sessions
Monitoring Database Operations with Server-Generated Alerts
Setting and Retrieving Thresholds for Server-Generated Alerts
Viewing Server-Generated Alerts
Server-Generated Alerts Data Dictionary Views
Monitoring Performance
Monitoring Locks
Monitoring Wait Events
Performance Monitoring Data Dictionary Views
8
Managing Diagnostic Data
About the Oracle Database Fault Diagnosability Infrastructure
Fault Diagnosability Infrastructure Overview
About Incidents and Problems
Fault Diagnosability Infrastructure Components
Automatic Diagnostic Repository (ADR)
Alert Log
Trace Files, Dumps, and Core Files
Other ADR Contents
Enterprise Manager Support Workbench
ADRCI Command-Line Utility
Structure, Contents, and Location of the Automatic Diagnostic Repository
Investigating, Reporting, and Resolving a Problem
Roadmap—Investigating, Reporting, and Resolving a Problem
Task 1 – View Critical Error Alerts in Enterprise Manager
Task 2 –View Problem Details
Task 3 – (Optional) Gather Additional Diagnostic Information
Task 4 – (Optional) Create a Service Request
Task 5 – Package and Upload Diagnostic Data to Oracle Support
Task 6 – Track the Service Request and Implement Any Repairs
Task 7 – Close Incidents
Viewing Problems with the Enterprise Manager Support Workbench
Creating a User-Reported Problem
Viewing the Alert Log
Finding Trace Files
Running Health Checks with Health Monitor
About Health Monitor
About Health Monitor Checks
Types of Health Checks
Running Health Checks Manually
Running Health Checks Using the DBMS_HM PL/SQL Package
Running Health Checks Using Enterprise Manager
Viewing Checker Reports
Viewing Reports Using Enterprise Manager
Viewing Reports Using DBMS_HM
Viewing Reports Using the ADRCI Utility
Health Monitor Views
Health Check Parameters Reference
Repairing SQL Failures with the SQL Repair Advisor
About the SQL Repair Advisor
Running the SQL Repair Advisor
Viewing, Disabling, or Removing a SQL Patch
Repairing Data Corruptions with the Data Recovery Advisor
Creating, Editing, and Uploading Custom Incident Packages
About Incident Packages
About Correlated Diagnostic Data in Incident Packages
About Quick Packaging and Custom Packaging
Packaging and Uploading Problems with Custom Packaging
Viewing and Modifying Incident Packages
Viewing Package Details
Accessing the Customize Package Page
Editing Incident Package Files (Copying Out and In)
Adding an External File to an Incident Package
Removing Incident Package Files
Viewing and Updating the Incident Package Activity Log
Setting Incident Packaging Preferences
Part II Oracle Database Structure and Storage
9
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
Control Files Data Dictionary Views
10
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
Redo Log Data Dictionary Views
11
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
Archived Redo Logs Views
The ARCHIVE LOG LIST Command
12
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
Bigfile Tablespaces
Creating a Bigfile Tablespace
Identifying a Bigfile Tablespace
Encrypted Tablespaces
Temporary Tablespaces
Creating a Locally Managed Temporary Tablespace
Creating a Bigfile Temporary Tablespace
Viewing Space Usage for Temporary Tablespaces
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
Altering and Maintaining Tablespaces
Altering a Locally Managed Tablespace
Altering a Bigfile Tablespace
Altering a Locally Managed Temporary Tablespace
Shrinking a Locally Managed Temporary Tablespace
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 Determine 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: Import 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
Tablespace Data Dictionary Views
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
13
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
Datafiles Data Dictionary Views
14
Managing Undo
What Is Undo?
Introduction to Automatic Undo Management
Overview of Automatic Undo Management
About the Undo Retention Period
Automatic Tuning of Undo Retention
Retention Guarantee
Undo Retention Tuning and Alert Thresholds
Tracking the Tuned Undo Retention Period
Setting the Minimum Undo Retention Period
Sizing a Fixed-Size Undo Tablespace
The Undo Advisor PL/SQL Interface
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
Managing Space Threshold Alerts for the Undo Tablespace
Migrating to Automatic Undo Management
Undo Space Data Dictionary Views
15
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 Parameters
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 and Flash Recovery Areas
Scenario 3: Adding Oracle-Managed Files to an Existing Database
Part III Schema Objects
16
Managing 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
About Object Dependencies and Object Invalidation
Manually Recompiling Invalid Objects with DDL
Manually Recompiling Invalid Objects with PL/SQL Package Procedures
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
Schema Objects Data Dictionary Views
Example 1: Displaying Schema Objects By Type
Example 2: Displaying Dependencies of Views and Synonyms
17
Managing Space for Schema Objects
Managing Tablespace Alerts
Setting Alert Thresholds
Viewing Alerts
Limitations
Managing Resumable Space Allocation
Resumable Space Allocation Overview
How Resumable Space Allocation Works
What Operations are Resumable?
What Errors are Correctable?
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 Wasted Space
Understanding Reclaimable Unused Space
Using the Segment Advisor
Automatic Segment Advisor
Running the Segment Advisor Manually
Viewing Segment Advisor Results
Configuring the Automatic Segment Advisor
Viewing Automatic Segment Advisor Information
Shrinking Database Segments Online
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
Schema Objects Space Usage Data Dictionary Views
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
18
Managing Tables
About Tables
Guidelines for Managing Tables
Design Tables Before Creating Them
Consider Your Options for the Type of Table to Create
Specify the Location of Each Table
Consider Parallelizing Table Creation
Consider Using NOLOGGING When Creating Tables
Consider Using Table Compression
Consider Encrypting Columns That Contain Sensitive Data
Estimate Table Size and Plan Accordingly
Restrictions to Consider When Creating Tables
Creating Tables
Example: Creating a Table
Creating a Temporary Table
Parallelizing Table Creation
Loading Tables
Inserting Data with DML Error Logging
Error Logging Table Format
Creating an Error Logging Table
Error Logging Restrictions and Caveats
Inserting Data Into Tables Using Direct-Path INSERT
Advantages of Using Direct-Path INSERT
Enabling Direct-Path INSERT
How Direct-Path INSERT Works
Specifying the Logging Mode for Direct-Path INSERT
Additional Considerations for 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
Adding a Column to a Compressed Table
Adding a Virtual Column
Renaming Table Columns
Dropping Table Columns
Removing Columns from Tables
Marking Columns Unused
Removing Unused Columns
Dropping Columns in Compressed Tables
Placing a Table in Read-Only Mode
Redefining Tables Online
Features of Online Table Redefinition
Performing Online Redefinition with DBMS_REDEFINITION
Constructing a Column Mapping String
Creating Dependent Objects Automatically
Creating Dependent Objects Manually
Results of the Redefinition Process
Performing Intermediate Synchronization
Aborting Online Table Redefinition and Cleaning Up After Errors
Restrictions for Online Redefinition of Tables
Online Redefinition of a Single Partition
Rules for Online Redefinition of a Single Partition
Online Table Redefinition Examples
Privileges Required for the DBMS_REDEFINITION Package
Researching and Reversing Erroneous Table Changes
Recovering Tables Using Oracle Flashback Table
Dropping Tables
Using Flashback Drop and Managing the Recycle Bin
What Is the Recycle Bin?
Enabling and Disabling 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
Example: Creating an Index-Organized Table
Restrictions for Index-Organized Tables
Creating Index-Organized Tables that Contain Object Types
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
Tables Data Dictionary Views
19
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
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
Creating an Invisible Index
Altering Indexes
Altering Storage Characteristics of an Index
Rebuilding an Existing Index
Making an Index Invisible
Renaming an Index
Monitoring Index Usage
Monitoring Space Use of Indexes
Dropping Indexes
Indexes Data Dictionary Views
20
Managing Clusters
About Clusters
Guidelines for Managing Clusters
Choose Appropriate Tables for the Cluster
Choose Appropriate Columns for the Cluster Key
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
Clusters Data Dictionary Views
21
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
Hash Clusters Data Dictionary Views
22
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
Views, Synonyms, and Sequences Data Dictionary Views
23
Repairing Corrupted Data
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: Reporting Corruption
DB_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
Fix Segment Bitmaps Using the SEGMENT_FIX_STATUS Procedure
DBMS_REPAIR Examples
Examples: Building a Repair Table or Orphan Key Table
Example: Creating a Repair Table
Example: Creating an Orphan Key Table
Example: Detecting Corruption
Example: Fixing Corrupt Blocks
Example: Finding Index Entries Pointing to Corrupt Data Blocks
Example: Skipping Corrupt Blocks
Part IV Database Resource Management and Task Scheduling
24
Managing Automated Database Maintenance Tasks
About Automated Maintenance Tasks
About Maintenance Windows
Configuring Automated Maintenance Tasks
Enabling and Disabling Maintenance Tasks for all Maintenance Windows
Enabling and Disabling Maintenance Tasks for Specific Maintenance Windows
Configuring Maintenance Windows
Modifying a Maintenance Window
Creating a New Maintenance Window
Removing a Maintenance Window
Configuring Resource Allocations for Automated Maintenance Tasks
About Resource Allocations for Automated Maintenance Tasks
Changing Resource Allocations for Automated Maintenance Tasks
Automated Maintenance Tasks Reference
Predefined Maintenance Windows
Automated Maintenance Tasks Database Dictionary Views
25
Managing Resource Allocation with Oracle Database Resource Manager
About Oracle Database Resource Manager
What Problems Does the Resource Manager Address?
How Does the Resource Manager Address These Problems?
Elements of the Resource Manager
About Resource Consumer Groups
About Resource Plan Directives
About Resource Plans
Example: A Simple Resource Plan
About Subplans
Example: A Resource Plan with Subplans
About Resource Allocation Methods
CPU
Active Session Pool with Queuing
Degree of Parallelism Limit
Automatic Consumer Group Switching
Canceling SQL and Terminating Sessions
Execution Time Limit
Undo Pool
Idle Time Limit
About Resource Manager Administration Privileges
Creating a Simple Resource Plan
Creating a Complex Resource Plan
About the Pending Area
Creating a Pending Area
Creating Resource Consumer Groups
Creating a Resource Plan
About the RATIO CPU Allocation Method
Creating Resource Plan Directives
How Resource Plan Directives Interact
Validating the Pending Area
Submitting the Pending Area
Clearing the Pending Area
Assigning Sessions to Resource Consumer Groups
Overview of Assigning Sessions to Resource Consumer Groups
Assigning an Initial Resource Consumer Group
Manually Switching Resource Consumer Groups
Switching a Single Session
Switching All Sessions for a User
Specifying Automatic Resource Consumer Group Switching
Specifying Automatic Switching with Mapping Rules
Specifying Automatic Switching by Setting Resource Limits
Specifying Session-to–Consumer Group Mapping Rules
About Session-to–Consumer Group Mapping Rules
Creating Consumer Group Mapping Rules
Creating Mapping Rule Priorities
Enabling Users or Applications to Manually Switch Consumer Groups
Granting and Revoking the Switch Privilege
Granting the Switch Privilege
Revoking Switch Privileges
Enabling Oracle Database Resource Manager and Switching Plans
Putting It All Together: Oracle Database Resource Manager Examples
Multilevel Plan Example
Example of Using Several Resource Allocation Methods
An Oracle-Supplied Mixed Workload Plan
Maintaining Consumer Groups, Plans, and Directives
Updating a Consumer Group
Deleting a Consumer Group
Updating a Plan
Deleting a Plan
Updating a Resource Plan Directive
Deleting a Resource Plan Directive
Viewing Database Resource Manager Configuration and Status
Viewing Consumer Groups Granted to Users or Roles
Viewing Plan Information
Viewing Current Consumer Groups for Sessions
Viewing the Currently Active Plans
Monitoring Oracle Database Resource Manager
Interacting with Operating-System Resource Control
Guidelines for Using Operating-System Resource Control
Oracle Database Resource Manager Reference
Predefined Resource Plans and Consumer Groups
DBMS_RESOURCE_MANAGER Package Procedures Summary
Resource Manager Data Dictionary Views
26
Oracle Scheduler Concepts
Overview of Oracle Scheduler
What Can the Scheduler Do?
Scheduler Objects
Programs
Schedules
Jobs
Job Instances
Job Arguments
How Programs, Jobs, and Schedules are Related
Job Categories
Database Jobs
Chain Jobs
External Jobs
Detached Jobs
Lightweight Jobs
Chains
Job Classes
Windows
Window Groups
Scheduler Architecture
The Job Table
The Job Coordinator
How Jobs Execute
Job Slaves
Using the Scheduler in Real Application Clusters Environments
Service Affinity when Using the Scheduler
Scheduler Support for Oracle Data Guard
27
Scheduling Jobs with Oracle Scheduler
Scheduler Objects and Their Naming
Using Jobs
Job Tasks and Their Procedures
Creating Jobs
Overview of Creating Jobs
Specifying a Job Action and Job Schedule
Setting Job Arguments
Setting Job Attributes
Creating External Jobs
Creating Detached Jobs
Creating Multiple Jobs in a Single Transaction
Altering Jobs
Running Jobs
Running Jobs Asynchronously with DBMS_SCHEDULER.RUN_JOB
Running Jobs Synchronously with DBMS_SCHEDULER.RUN_JOB
Job Run Environment
Stopping Jobs
Stopping External Jobs
Stopping a Chain Job
Dropping Jobs
Disabling Jobs
Enabling Jobs
Copying Jobs
Viewing the Job Log
Run Details
Precedence of Logging Levels in Jobs and Job Classes
Viewing stdout and stderr for External Jobs
Using Programs
Program Tasks and Their Procedures
Creating Programs
Defining Program Arguments
Altering Programs
Dropping Programs
Disabling Programs
Enabling Programs
Using 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
Using Job Classes
Job Class Tasks and Their Procedures
Creating Job Classes
Altering Job Classes
Dropping Job Classes
Using Windows
Window Tasks and Their Procedures
Creating Windows
Altering Windows
Opening Windows
Closing Windows
Dropping Windows
Disabling Windows
Enabling Windows
Overlapping Windows
Examples of Overlapping Windows
Using 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
Using Events
About Events
Using Events Raised by the Scheduler
Altering a Job to Raise Events
Consuming Scheduler-Raised Events with your Application
Using Events Raised by an Application
Creating an Event-Based Job
Altering an Event-Based Job
Creating an Event Schedule
Altering an Event Schedule
Passing Event Messages into an Event-Based Job
Using Chains
Chain Tasks and Their Procedures
Creating Chains
Defining Chain Steps
Adding Rules to a Chain
Setting an Evaluation Interval for Chain Rules
Enabling Chains
Creating Jobs for Chains
Dropping Chains
Running Chains
Dropping Chain Rules
Disabling Chains
Dropping Chain Steps
Stopping Chains
Stopping Individual Chain Steps
Pausing Chains
Skipping Chain Steps
Running Part of a Chain
Monitoring Running Chains
Handling Stalled Chains
Allocating Resources Among Jobs
Allocating Resources Among Jobs Using Resource Manager
Example of Resource Allocation for Jobs
28
Administering Oracle Scheduler
Configuring Oracle Scheduler
Monitoring and Managing the Scheduler
Viewing the Currently Active Window and Resource Plan
Finding Information About Currently Running Jobs
Monitoring and Managing Window and Job Logs
Job Log
Window Log
Purging Logs
Changing Job Priorities
Managing Scheduler Security
Enabling and Disabling Remote External Jobs
Setting Up the Database for Remote External Jobs
Installing, Configuring, and Starting the Scheduler Agent
Stopping the Scheduler Agent
Disabling Remote External Jobs
Import/Export and the Scheduler
Troubleshooting the Scheduler
Understanding Why a Job Fails to Run
Failed Jobs
Broken Jobs
Disabled Jobs
Completed Jobs
Job Recovery After a Failure
Understanding Why a Program Becomes Disabled
Understanding Why a Window Fails to Take Effect
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
Examples of Creating Chains
Examples of Creating Jobs and Schedules Based on Events
Example of Creating a Job In an Oracle Data Guard Environment
Scheduler Reference
Scheduler Privileges
Scheduler Data Dictionary Views
Part V 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
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
Part VI Appendices
A
Support for DBMS_JOB in Release 11g
About DBMS_JOB
Configuring DBMS_JOB
Using Both DBMS_JOB and Oracle Scheduler
Moving from DBMS_JOB to Oracle Scheduler
Creating a Job
Altering a Job
Removing a Job from the Job Queue
Index