Oracle9i Database Administrator's Guide
Release 1 (9.0.1)

Part Number A90117-01
Go To Documentation Library
Go To Product List
Book List
Go To Index

Master Index


Go to next page


Title and Copyright Information

Send Us Your Comments


What's New in Oracle9i?

Part I Basic Database Administration

1 The Oracle Database Administrator

Types of Oracle 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 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
Checking Your Current Release Number
Database Administrator Security and Privileges
The Database Administrator's Operating System Account
Database Administrator Usernames
Database Administrator Authentication
Administrative Privileges
Selecting an Authentication Method
Using Operating System (OS) Authentication
Using Password File Authentication
Password File Administration
Adding Users to a Password File
Maintaining a Password File
Database Administrator Utilities
Export and Import

2 Creating an Oracle Database

Considerations Before Creating a Database
Planning for Database Creation
Meeting Creation Prerequisites
Deciding How to Create an Oracle Database
The Oracle Database Configuration Assistant
Advantages of Using the Oracle Database Configuration Assistant
Creating a Database
Configuring Database Options
Deleting a Database
Managing Templates
Manually Creating an Oracle 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: Start the Instance.
Step 6: Issue the CREATE DATABASE Statement
Step 7: Create Additional Tablespaces
Step 8: Run Scripts to Build Data Dictionary Views
Step 9: Run Scripts to Install Additional Options (Optional)
Step 10: Create a Server Parameter File (Recommended)
Step 11: Back Up the Database.
Oracle9i Features that Simplify Database Creation and Management
Creating an Undo Tablespace
Creating a Default Temporary Tablespace
Using Oracle-Managed Files
Setting and Managing the Time Zone
Troubleshooting Database Creation
Dropping a Database
Considerations After Creating a Database
Some Security Considerations
Installing Oracle's Sample Schemas
Initialization Parameters and Database Creation
Determining the Global Database Name
Specifying Control Files
Specifying Database Block Sizes
Setting Initialization Parameters that Affect the Size of the SGA
Specifying the Maximum Number of Processes
Specifying the Method of Undo Space Management
Setting License Parameters
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
Exporting the Server Parameter File
Errors and Recovery for the Server Parameter File
Viewing Parameters Settings

3 Using Oracle-Managed Files

What are Oracle-Managed Files?
Who Can Use Oracle-Managed Files?
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_CREATE_ONLINE_LOG_DEST_n Initialization Parameter
Creating Oracle-Managed Files
How Oracle-Managed Files are Named
Creating Oracle-Managed Files at Database Creation
Creating Datafiles for Tablespaces
Creating Tempfiles for Temporary Tablespaces
Creating Control Files
Creating Online Redo Log Files
Behavior of Oracle-Managed Files
Dropping Datafiles and Tempfiles
Dropping Online Redo Log Files
Renaming Files
Managing Standby Databases
Scenarios for Using Oracle-Managed Files
Scenario 1: Create and Manage a Database with Multiplexed Online Redo Logs
Scenario 2: Add Oracle-Managed Files to an Existing Database

4 Starting Up and Shutting Down

Starting Up a Database
Options for Starting Up a Database
Preparing to Start an Instance
Using SQL*Plus to Start Up a Database
Starting an Instance: Scenarios
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 Option
Shutting Down with the IMMEDIATE Option
Shutting Down with the TRANSACTIONAL Option
Shutting Down with the ABORT Option
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

Part II Oracle Server Processes and Storage Structure

5 Managing Oracle Processes

Server Processes
Dedicated Server Processes
Shared Server Processes
Configuring Oracle for the Shared Server
Initialization Parameters for Shared Server
Setting the Initial Number of Dispatchers (DISPATCHERS)
Setting the Initial Number of Shared Servers (SHARED_SERVERS)
Modifying Dispatcher and Server Processes
Monitoring Shared Server
About Oracle Background Processes
Monitoring the Processes of an Oracle Instance
Process and Session Views
Monitoring Locks
Trace Files and the Alert File
Managing Processes for Parallel Execution
Managing the Parallel Execution Servers
Altering Parallel Execution for a Session
Managing Processes for External Procedures
Setting up an Environment for Calling External Procedures
Example of tnsnames.ora Entry for External Procedure Listener
Example of listener.ora Entry for External Procedures
Terminating Sessions
Identifying Which Session to Terminate
Terminating an Active Session
Terminating an Inactive Session

6 Managing Control Files

What Is a Control File?
Guidelines for Control Files
Provide Filenames for the Control Files
Multiplex Control Files on Different Disks
Place Control Files Appropriately
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
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

7 Managing the Online Redo Log

What Is the Online Redo Log?
Redo Threads
Online Redo Log Contents
How Oracle Writes to the Online Redo Log
Planning the Online Redo Log
Multiplexing Online Redo Log Files
Placing Online Redo Log Members on Different Disks
Setting the Size of Online Redo Log Members
Choosing the Number of Online Redo Log Files
Controlling Archive Lag
Creating Online Redo Log Groups and Members
Creating Online Redo Log Groups
Creating Online Redo Log Members
Relocating and Renaming Online Redo Log Members
Dropping Online Redo Log Groups and Members
Dropping Log Groups
Dropping Online Redo Log Members
Forcing Log Switches
Verifying Blocks in Redo Log Files
Clearing an Online Redo Log File
Viewing Online Redo Log Information

8 Managing Archived Redo Logs

What Is the Archived Redo Log?
Running a Database in NOARCHIVELOG Mode
Running a Database in ARCHIVELOG Mode
Controlling the Archiving Mode
Setting the Initial Database Archiving Mode
Changing the Database Archiving Mode
Enabling Automatic Archiving
Disabling Automatic Archiving
Performing Manual Archiving
Specifying the Archive Destination
Specifying Archive Destinations
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
Re-Archiving to a Failed Destination
Tuning Archive Performance by Specifying Multiple ARCn Processes
Controlling Trace Output Generated by the Archivelog Process
Viewing Information About the Archived Redo Log
Fixed Views

9 Using LogMiner to Analyze Redo Log Files

Understanding the Value of Analyzing Redo Log Files
Things to Know Before You Begin
Redo Log Files
Dictionary Options
Tracking of DDL Statements
Storage Management
Extracting Data Values from Redo Log Files
LogMiner Restrictions
LogMiner Views
Using LogMiner
Extracting a Dictionary
Specifying Redo Log Files for Analysis
Starting LogMiner
Analyzing Output from V$LOGMNR_CONTENTS
Using LogMiner to Perform Object-Level Recovery
Ending a LogMiner Session
Example Uses of LogMiner
Example: Tracking Changes Made By a Specific User
Example: Calculating Table Access Statistics

10 Managing Job Queues

Enabling Processes Used for Executing Jobs
Managing Job Queues
The DBMS_JOB Package
Submitting a Job to the Job Queue
How Jobs Execute
Removing a Job from the Job Queue
Altering a Job
Broken Jobs
Forcing a Job to Execute
Terminating a Job
Viewing Job Queue Information
Displaying Information About a Job
Displaying Information About Running Jobs

11 Managing Tablespaces

Guidelines for Managing Tablespaces
Use Multiple Tablespaces
Specify Tablespace Default Storage Parameters
Assign Tablespace Quotas to Users
Creating Tablespaces
Locally Managed Tablespaces
Dictionary-Managed Tablespaces
Temporary Tablespaces
Managing Tablespace Allocation
Storage Parameters in Locally Managed Tablespaces
Storage Parameters for Dictionary-Managed Tablespaces
Coalescing Free Space in Dictionary-Managed Tablespaces
Altering Tablespace Availability
Taking Tablespaces Offline
Bringing Tablespaces Online
Altering the Availability of Datafiles or Tempfiles
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
Dropping Tablespaces
Troubleshooting Tablespace Problems with DBMS_SPACE_ADMIN
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
Transporting Tablespaces Between Databases
Introduction to Transportable Tablespaces
Compatibility Considerations for Transportable Tablespaces
Transporting Tablespaces Between Databases: A Procedure
Object Behaviors
Using Transportable Tablespaces
Viewing Tablespace Information
Listing Tablespaces and Default Storage Parameters: Example
Listing the Datafiles and Associated Tablespaces of a Database: Example
Displaying Statistics for Free Space (Extents) of Each Tablespace: Example

12 Managing Datafiles

Guidelines for Managing Datafiles
Determine the Number of Datafiles
Determine the Size of Datafiles
Place Datafiles Appropriately
Store Datafiles Separate from Redo Log Files
Creating Datafiles and Adding Datafiles to a Tablespace
Changing a Datafile's 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
Renaming and Relocating Datafiles for a Single Tablespace
Renaming and Relocating Datafiles for Multiple Tablespaces
Verifying Data Blocks in Datafiles
Viewing Datafile Information

13 Managing Undo Space

What is Undo?
Specifying the Mode for Undo Space Management
Starting an Instance in Automatic Undo Management Mode
Starting an Instance in Manual Undo Management Mode
Managing Undo Tablespaces
Creating an Undo Tablespace
Altering an Undo Tablespace
Dropping an Undo Tablespace
Switching Undo Tablespaces
Establishing User Quotas for Undo Space
Setting the Retention Period for Undo Information
Viewing Information About Undo Space
Managing Rollback Segments
Guidelines for Managing Rollback Segments
Creating Rollback Segments
Altering Rollback Segments
Explicitly Assigning a Transaction to a Rollback Segment
Dropping Rollback Segments
Viewing Rollback Segment Information

Part III Schema Objects

14 Managing Space for Schema Objects

Managing Space in Data Blocks
Specifying the PCTFREE Parameter
Specifying the PCTUSED Parameter
Selecting Associated PCTUSED and PCTFREE Values
Specifying the Transaction Entry Parameters: INITRANS and MAXTRANS
Setting Storage Parameters
Identifying the Storage Parameters
Setting Default Storage Parameters for Segments in a Tablespace
Setting Storage Parameters for Data Segments
Setting Storage Parameters for Index Segments
Setting Storage Parameters for LOBs, Varrays, and Nested Tables
Changing Values for Storage Parameters
Understanding Precedence in Storage Parameters
Example of How Storage Parameters Effect Space Allocation
Managing Resumable Space Allocation
Resumable Space Allocation Overview
Enabling and Disabling Resumable Space Allocation
Detecting Suspended Statements
Resumable Space Allocation Example: Registering an AFTER SUSPEND Trigger
Deallocating Space
Viewing the High Water Mark
Issuing Space Deallocation Statements
Examples of Deallocating Space
Understanding Space Use of Datatypes

15 Managing Tables

Guidelines for Managing Tables
Design Tables Before Creating Them
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
Estimate Table Size and Set Storage Parameters
Plan for Large Tables
Table Restrictions
Creating Tables
Creating a Table
Creating a Temporary Table
Parallelizing Table Creation
Automatically Collecting Statistics on Tables
Altering Tables
Moving a Table to a New Segment or Tablespace
Manually Allocating Storage for a Table
Dropping Columns
Redefining Tables Online
Steps for Online Redefinition of Tables
Intermediate Synchronization
Abort and Cleanup After Errors
Example of Online Table Redefinition
Dropping Tables
Managing Index-Organized Tables
What are Index-Organized Tables
Creating Index-Organized Tables
Maintaining Index-Organized Tables
Analyzing 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

16 Managing Indexes

Guidelines for Managing Indexes
Create Indexes After Inserting Table Data
Index the Correct Tables and Columns
Order Index Columns for Performance
Limit the Number of Indexes for Each Table
Drop Indexes That Are No Longer Required
Specify Index Block Space Use
Estimate Index Size and Set Storage Parameters
Specify the Tablespace for Each Index
Consider Parallelizing Index Creation
Consider Creating Indexes with NOLOGGING
Consider Costs and Benefits of Coalescing or Rebuilding Indexes
Consider Cost Before Disabling or Dropping Constraints
Creating Indexes
Creating an Index Explicitly
Creating a Unique Index Explicitly
Creating an Index Associated with a Constraint
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

17 Managing Partitioned Tables and Indexes

What Are Partitioned Tables and Indexes?
Partitioning Methods
When to Use the Range Partitioning Method
When to Use the Hash Partitioning Method
When to Use the List Partitioning Method
When to Use the Composite Partitioning Method
Creating Partitioned Tables
Creating Range-Partitioned Tables
Creating Hash-Partitioned Tables
Creating List-Partitioned Tables
Creating Composite Partitioned Tables
Creating Partitioned Index-Organized Tables
Partitioning Restrictions for Multiple Block Sizes
Maintaining Partitioned Tables
Updating Global Indexes Automatically
Adding Partitions
Coalescing Partitions
Dropping Partitions
Exchanging Partitions
Merging Partitions
Modifying Default Attributes
Modifying Real Attributes of Partitions
Modifying List Partitions: Adding or Dropping Values
Moving Partitions
Rebuilding Index Partitions
Renaming Partitions
Splitting Partitions
Truncating Partitions
Partitioned Tables and Indexes Examples
Moving the Time Window in a Historical Table
Converting a Partition View into a Partitioned Table
Viewing Information About Partitioned Tables and Indexes

18 Managing Clusters

Guidelines for Managing Clusters
Choose Appropriate Tables for the Cluster
Choose Appropriate Columns for the Cluster Key
Specify Data Block Space Use
Specify the Space Required by an Average Cluster Key and Its Associated Rows
Specify the Location of Each Cluster and Cluster Index Rows
Estimate Cluster Size and Set Storage Parameters
Creating Clusters
Creating Clustered Tables
Creating Cluster Indexes
Altering Clusters
Altering Clustered Tables
Altering Cluster Indexes
Dropping Clusters
Dropping Clustered Tables
Dropping Cluster Indexes
Viewing Information About Clusters

19 Managing Hash Clusters

When to Use Hash Clusters
Situations Where Hashing Is Useful
Situations Where Hashing Is Not Advantageous
Creating Hash Clusters
Creating Single-Table Hash Clusters
Controlling Space Use Within a Hash Cluster
Estimating Size Required by Hash Clusters
Altering Hash Clusters
Dropping Hash Clusters
Viewing Information About Hash Clusters

20 Managing Views, Sequences, and Synonyms

Managing Views
Creating Views
Updating a Join View
Altering Views
Dropping Views
Replacing Views
Managing Sequences
Creating Sequences
Altering Sequences
Dropping Sequences
Managing Synonyms
Creating Synonyms
Dropping Synonyms
Viewing Information About Views, Synonyms, and Sequences

21 General Management of Schema Objects

Creating Multiple Tables and Views in a Single Operation
Renaming Schema Objects
Analyzing Tables, Indexes, and Clusters
Using Statistics for Tables, Indexes, and Clusters
Validating Tables, Indexes, Clusters, and Materialized Views
Listing Chained Rows of Tables and Clusters
Truncating Tables and Clusters
Enabling and Disabling Triggers
Enabling Triggers
Disabling Triggers
Managing Integrity Constraints
Integrity Constraint States
Setting Integrity Constraints Upon Definition
Modifying or Dropping Existing Integrity Constraints
Deferring Constraint Checks
Reporting Constraint Exceptions
Managing Object Dependencies
Manually Recompiling Views
Manually Recompiling Procedures and Functions
Manually Recompiling Packages
Managing Object Name Resolution
Changing Storage Parameters for the Data Dictionary
Structures in the Data Dictionary
Errors that Require Changing Data Dictionary Storage
Displaying Information About Schema Objects
Using PL/SQL Packages to Display Information About Schema Objects
Using Views to Display Information About Schema Objects

22 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
Task 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR
Task 3: Make Objects Usable
Task 4: Repair Corruptions and Rebuild Lost Data
Using ADMIN_TABLES to Build a Repair Table or 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 IV Database Security

23 Establishing Security Policies

System Security Policy
Database User Management
User Authentication
Operating System Security
Data Security Policy
User Security Policy
General User Security
End-User Security
Administrator Security
Application Developer Security
Application Administrator Security
Password Management Policy
Account Locking
Password Aging and Expiration
Password History
Password Complexity Verification
Auditing Policy
A Security Checklist

24 Managing Users and Resources

Session and User Licensing
Concurrent Usage Licensing
Named User Limits
Viewing Licensing Limits and Current Values
User Authentication Methods
Database Authentication
External Authentication
Global Authentication and Authorization
Proxy Authentication and Authorization
Managing Oracle Users
Creating Users
Altering Users
Dropping Users
Managing Resources with Profiles
Enabling and Disabling Resource Limits
Creating Profiles
Assigning Profiles
Altering Profiles
Using Composite Limits
Dropping Profiles
Viewing Information About Database Users and Profiles
Listing All Users and Associated Information
Listing All Tablespace Quotas
Listing All Profiles and Assigned Limits
Viewing Memory Use for Each User Session

25 Managing User Privileges and Roles

Identifying User Privileges
System Privileges
Object Privileges
Managing User Roles
Predefined Roles
Creating a Role
Specifying the Type of Role Authorization
Dropping Roles
Granting User Privileges and Roles
Granting System Privileges and Roles
Granting Object Privileges and Roles
Granting Privileges on Columns
Revoking User Privileges and Roles
Revoking System Privileges and Roles
Revoking Object Privileges and Roles
Cascading Effects of Revoking Privileges
Granting to and Revoking from the User Group PUBLIC
When Do Grants and Revokes Take Effect?
The SET ROLE Statement
Specifying Default Roles
Restricting the Number of Roles that a User Can Enable
Granting Roles Using the Operating System or Network
Using Operating System Role Identification
Using Operating System Role Management
Granting and Revoking Roles When OS_ROLES=TRUE
Enabling and Disabling Roles When OS_ROLES=TRUE
Using Network Connections with Operating System Role Management
Viewing Privilege and Role Information
Listing All System Privilege Grants
Listing All Role Grants
Listing Object Privileges Granted to a User
Listing the Current Privilege Domain of Your Session
Listing Roles of the Database
Listing Information About the Privilege Domains of Roles

26 Auditing Database Use

Guidelines for Auditing
Decide Whether to Use the Database or Operating System Audit Trail
Keep Audited Information Manageable
Guidelines for Auditing Suspicious Database Activity
Guidelines for Auditing Normal Database Activity
Managing Audit Trail Information
What Information is Contained in the Audit Trail?
Events Audited by Default
Setting Auditing Options
Turning Off Audit Options
Enabling and Disabling Database Auditing
Controlling the Growth and Size of the Audit Trail
Protecting the Audit Trail
Fine-Grained Auditing
Viewing Database Audit Trail Information
Creating the Audit Trail Views
Deleting the Audit Trail Views
Using Audit Trail Views to Investigate Suspicious Activities

Part V Database Resource Management

27 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
Administering the Database Resource Manager
Creating a Simple Resource Plan
Creating Complex Resource Plans
Using the Pending Area for Creating Plan Schemas
Creating Resource Plans
Creating Resource Consumer Groups
Specifying Resource Plan Directives
Managing Resource Consumer Groups
Assigning an Initial Resource Consumer Group
Changing Resource Consumer Groups
Managing the Switch Privilege
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
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

Part VI Distributed Database Management

28 Distributed Database Concepts

Distributed Database Architecture
Homogenous Distributed Database Systems
Heterogeneous Distributed Database Systems
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
Creation of Database Links: Examples
Schema Objects and Database Links
Database Link Restrictions
Distributed Database Administration
Site Autonomy
Distributed Database Security
Auditing Database Links
Administration Tools
Transaction Processing in a Distributed System
Remote SQL Statements
Distributed SQL Statements
Shared SQL for Remote and Distributed Statements
Remote Transactions
Distributed Transactions
Two-Phase Commit Mechanism
Database Link Name Resolution
Schema Object Name Resolution
Global Name Resolution in Views, Synonyms, and Procedures
Distributed Database Application Development
Transparency in a Distributed Database System
Remote Procedure Calls (RPCs)
Distributed Query Optimization
Character Set Support
Client/Server Environment
Homogeneous Distributed Environment
Heterogeneous Distributed Environment

29 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
Specifying Link Users
Using Connection Qualifiers to Specify Service Names Within Link Names
Creating Shared Database Links
Determining Whether to Use Shared Database Links
Creating Shared Database Links
Configuring Shared Database Links
Managing Database Links
Closing Database Links
Dropping Database Links
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
Using Procedures to Create Location Transparency
Managing Statement Transparency
Managing a Distributed Database: Scenarios
Creating a Public Fixed User Database Link
Creating a Public Fixed User Shared Database Link
Creating a Public Connected User Database Link
Creating a Public Connected User Shared Database Link
Creating a Public Current User Database Link

30 Developing Applications for a Distributed Database System

Managing the Distribution of an Application's 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
Using Hints
Analyzing the Execution Plan
Handling Errors in Remote Procedures

31 Distributed Transactions Concepts

What Are Distributed Transactions?
Session Trees for Distributed Transactions
Database Servers
Local Coordinators
Global Coordinator
Commit Point Site
Two-Phase Commit Mechanism
Prepare Phase
Commit Phase
Forget Phase
In-Doubt Transactions
Automatic Resolution of In-Doubt Transactions
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 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

32 Managing Distributed Transactions

Setting Distributed Transaction Initialization Parameters
Limiting the Number of Distributed Transactions
Specifying the Commit Point Strength of a Node
Viewing Information About Distributed Transactions
Transaction Naming
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
Manually Overriding In-Doubt Transactions
Manually Committing an In-Doubt Transaction
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
Step 3: Query DBA_2PC_NEIGHBORS on Local Node
Step 4: Querying Data Dictionary Views on All Nodes
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 fenables you torom In-Doubt Transactions
Simulating Distributed Transaction Failure
Managing Read Consistency


Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Index

Master Index