Oracle9i Database Administrator's Guide -- Contents

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

Creating and Maintaining a Password File

Using ORAPWD

Setting REMOTE_LOGIN_ PASSWORDFILE

Adding Users to a Password File

Maintaining a Password File

Database Administrator Utilities

SQL*Loader

Export and Import

Considerations Before Creating a Database

Planning for Database Creation

Meeting Creation Prerequisites

Deciding How to Create an Oracle Database

Using the Database Configuration Assistant

Advantages of Using DBCA

Creating a Database Using DBCA

Configuring Database Options

Deleting a Database Using DBCA

Managing DBCA Templates

Using DBCA Silent Mode

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.

Understanding the CREATE DATABASE Statement

Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM

Clauses that Simplify Database Creation and Management

Creating a Locally Managed SYSTEM Tablespace

Specifying the Database Time Zone and Time Zone File

Specifying FORCE LOGGING Mode

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

Backing Up the Server Parameter File

Errors and Recovery for the Server Parameter File

Viewing Parameter Settings

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

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

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

Terminating Sessions

Identifying Which Session to Terminate

Terminating an Active Session

Terminating an Inactive Session

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

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

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

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

Dynamic Performance Views

The ARCHIVE LOG LIST Command

Potential Uses for Data Stored in Redo Logs

Accessing Information Stored in Redo Logs

Redo Logs and Dictionary Files

Redo Logs

Dictionary Options

Tracking DDL Statements

LogMiner Recommendations and Restrictions

Recommendations

Restrictions

Filtering Data That is Returned

Showing Only Committed Transactions

Skipping Redo Corruptions

Filtering Data By Time

Filtering Data By SCN

Accessing LogMiner Information

Querying V$LOGMNR_CONTENTS

Executing Reconstructed SQL Statements

Formatting of Returned Data

Extracting Actual Data Values from Redo Logs

NULL Returns From the MINE_VALUE Function

Usage Rules for the MINE_VALUE and COLUMN_PRESENT Functions

Supplemental Logging

Database Supplemental Logging

Table Supplemental Logging

Steps in a Typical LogMiner Session

Perform Initial Setup Activities

Extract a Dictionary

Specify Redo Logs for Analysis

Start a LogMiner Session

Query V$LOGMNR_CONTENTS

End a LogMiner Session

Example Uses of LogMiner

Example: Using LogMiner to Track Changes Made By a Specific User

Example: Using LogMiner to Calculate Table Access Statistics

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

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

Coalescing Free Space in Dictionary-Managed Tablespaces

How Oracle Coalesces Free Space

Manually Coalescing Free Space

Monitoring Free Space

Specifying Nonstandard Block Sizes for Tablespaces

Controlling the Writing of Redo Records

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

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

Limitations

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

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

Dropping Datafiles

Verifying Data Blocks in Datafiles

Mapping Files to Physical Devices

Overview of Oracle's File Mapping Interface

How Oracle's File Mapping Interface Works

Using Oracle's File Mapping Interface

File Mapping Examples

Viewing Datafile Information

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

Specifying 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

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

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

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's Definition

Adding Table Columns

Renaming Table Columns

Dropping Table Columns

Redefining Tables Online

Features of Online Table Redefinition

The DBMS_REDEFINITION Package

Steps for Online Redefinition of Tables

Intermediate Synchronization

Abort and Cleanup After Errors

Example of Online Table Redefinition

Restrictions

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

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

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 Range-Hash Partitioning Method

When to Use the Composite Range-List Partitioning Method

Creating Partitioned Tables

Creating Range-Partitioned Tables

Creating Hash-Partitioned Tables

Creating List-Partitioned Tables

Creating Composite Range-Hash Partitioned Tables

Creating Composite Range-List Partitioned Tables

Using Subpartition Templates to Describe 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 Values

Modifying List Partitions: Dropping Values

Modifying a Subpartition Template

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

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

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

Managing Views

Managing Sequences

Creating Sequences

Altering Sequences

Dropping Sequences

Managing Synonyms

Creating Synonyms

Dropping Synonyms

Viewing Information About Views, Synonyms, and Sequences

Creating Multiple Tables and Views in a Single Operation

Renaming Schema Objects

Analyzing Tables, Indexes, and Clusters

Collecting Statistics for Tables, Indexes, and Clusters

Validating Tables, Indexes, Clusters, and Materialized Views

Listing Chained Rows of Tables and Clusters

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

Setting Integrity Constraints Upon Definition

Modifying, Renaming, or Dropping Existing Integrity Constraints

Deferring Constraint Checks

Reporting Constraint Exceptions

Viewing Constraint Information

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

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

DBMS_REPAIR Examples

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

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

Managing Oracle Users

Creating Users

Altering Users

Dropping Users

User Authentication Methods

Database Authentication

External Authentication

Global Authentication and Authorization

Proxy Authentication and Authorization

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

Understanding User Privileges and Roles

System Privileges

Object Privileges

User Roles

Managing User 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

Revoking User Privileges and Roles

Revoking System Privileges and Roles

Revoking Object Privileges

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

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

What Information is Contained in the Audit Trail?

Information Stored in the Database Audit Trail

Information Stored in an Operating System File

Actions Audited by Default

Auditing Administrative Users

Managing the Audit Trail

Enabling and Disabling Auditing

Setting Auditing Options

Auditing in a Multi-Tier Environment

Turning Off Audit Options

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

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

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 for Distributed Environments

Client/Server Environment

Homogeneous Distributed Environment

Heterogeneous Distributed Environment

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

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

What Are Distributed Transactions?

Session Trees for Distributed Transactions

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

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

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 from In-Doubt Transactions

Simulating Distributed Transaction Failure

Managing Read Consistency