Contents

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Oracle Database Administrator's Guide?

• Oracle Database 11g Release 1 (11.1) New Features in the Administrator's Guide

Part I Basic Database Administration

1 Overview of Administering an Oracle Database

• Types of Oracle Database Users
  • Database Administrators
  • Security Officers
  • Network Administrators
  • Application Developers
  • Application Administrators
  • Database Users
• Tasks of a Database Administrator
  • Task 1: Evaluate the Database Server Hardware
  • Task 2: Install the Oracle Database Software
  • Task 3: Plan the Database
  • Task 4: Create and Open the Database
  • Task 5: Back Up the Database
  • Task 6: Enroll System Users
  • Task 7: Implement the Database Design
  • Task 8: Back Up the Fully Functional Database
  • Task 9: Tune Database Performance
  • Task 10: Download and Install Patches
  • Task 11: Roll Out to Additional Hosts
• Selecting an Instance with Environment Variables
• Identifying Your Oracle Database Software Release
  • Release Number Format
    • Major Database Release Number
    • Database Maintenance Release Number
    • Application Server Release Number
    • Component-Specific Release Number
    • Platform-Specific Release Number
  • Checking Your Current Release Number
• Database Administrator Security and Privileges
  • The Database Administrator's Operating System Account
  • Database Administrator Usernames
    • SYS
    • SYSTEM
    • The DBA Role
• Database Administrator Authentication
  • Administrative Privileges
    • SYSDBA and SYSOPER
    • Connecting with Administrative Privileges: Example
  • Selecting an Authentication Method 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
  • 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

• Deciding How to Create an Oracle Database
• Manually Creating an Oracle Database
  • Considerations Before Creating the Database
    • Planning for Database Creation
    • Meeting Creation Prerequisites
  • Creating the Database
    • Step 1: Decide on Your Instance Identifier (SID)
    • Step 2: Establish the Database Administrator Authentication Method
    • Step 3: Create the Initialization Parameter File
    • Step 4: Connect to the Instance
    • Step 5: Create a Server Parameter File (Recommended)
    • Step 6: Start the Instance
    • Step 7: Issue the CREATE DATABASE Statement
    • Step 8: Create Additional Tablespaces
    • Step 9: Run Scripts to Build Data Dictionary Views
    • Step 10: Run Scripts to Install Additional Options (Optional)
    • Step 11: Back Up the Database.
• Understanding the CREATE DATABASE Statement
  • Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM
  • Creating a Locally Managed SYSTEM Tablespace
  • Creating the SYSAUX Tablespace
  • Using Automatic Undo Management: Creating an Undo Tablespace
  • Creating a Default Permanent Tablespace
  • Creating a Default Temporary Tablespace
  • Specifying Oracle-Managed Files at Database Creation
  • Supporting Bigfile Tablespaces During Database Creation
    • Specifying the Default Tablespace Type
    • Overriding the Default Tablespace Type
  • Specifying the Database Time Zone and Time Zone File
    • 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
• Understanding Initialization Parameters
  • 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
  • The COMPATIBLE Initialization Parameter and Irreversible Compatibility
  • Setting the License Parameter
• Troubleshooting Database Creation
• Dropping a Database
• Managing Initialization Parameters Using a Server Parameter File
  • What Is a Server Parameter File?
  • Migrating to a Server Parameter File
  • Creating a Server Parameter File
  • 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
• 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
  • Understanding Initialization Parameter Files
  • 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
• 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 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
• 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 Parameter
• Creating Oracle-Managed Files
  • How Oracle-Managed Files Are Named
  • Creating Oracle-Managed Files at Database Creation
    • Specifying Control Files at Database Creation
    • Specifying Redo Log Files at Database Creation
    • Specifying the SYSTEM and SYSAUX Tablespace Datafiles at Database Creation
    • Specifying the Undo Tablespace Datafile at Database Creation
    • Specifying the Default Temporary Tablespace Tempfile at Database Creation
    • CREATE DATABASE Statement Using Oracle-Managed Files: Examples
  • Creating Datafiles for Tablespaces Using Oracle-Managed Files
    • CREATE TABLESPACE: Examples
    • CREATE UNDO TABLESPACE: Example
    • ALTER TABLESPACE: Example
  • Creating Tempfiles for Temporary Tablespaces Using Oracle-Managed Files
    • CREATE TEMPORARY TABLESPACE: Example
    • ALTER TABLESPACE... ADD TEMPFILE: Example
  • Creating Control Files Using Oracle-Managed Files
    • CREATE CONTROLFILE Using NORESETLOGS Keyword: Example
    • CREATE CONTROLFILE Using RESETLOGS Keyword: Example
  • Creating Redo Log Files Using Oracle-Managed Files
    • Using the ALTER DATABASE ADD LOGFILE Statement
    • Using the ALTER DATABASE OPEN RESETLOGS Statement
  • Creating Archived Logs Using Oracle-Managed Files
• Behavior of Oracle-Managed Files
  • Dropping Datafiles and Tempfiles
  • Dropping Redo Log Files
  • Renaming Files
  • Managing Standby Databases
• Scenarios for Using Oracle-Managed Files
  • Scenario 1: Create and Manage a Database with Multiplexed Redo Logs
  • Scenario 2: Create and Manage a Database with Database 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 Space in Data Blocks
  • Specifying the INITRANS Parameter
• Managing Storage Parameters
  • Identifying the Storage Parameters
  • Specifying Storage Parameters at Object Creation
  • Setting Storage Parameters for Clusters
  • Setting Storage Parameters for Partitioned Tables
  • Setting Storage Parameters for Index Segments
  • Setting Storage Parameters for LOBs, Varrays, and Nested Tables
  • Changing Values of Storage Parameters
  • Understanding Precedence in Storage Parameters
• 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
  • Renaming Table Columns
  • Dropping Table Columns
    • Removing Columns from Tables
    • Marking Columns Unused
    • Removing Unused Columns
  • 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
• Auditing Table Changes Using Flashback Transaction Query
• Recovering Tables Using the Flashback Table Feature
• Dropping Tables
• Using Flashback Drop and Managing the Recycle Bin
  • What Is the Recycle Bin?
  • 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
    • Creating an Index-Organized Table
    • Creating Index-Organized Tables that Contain Object Types
    • Using the Overflow Clause
    • Choosing and Monitoring a Threshold Value
    • Using the INCLUDING Clause
    • Parallelizing Index-Organized Table Creation
    • Using Key Compression
  • Maintaining Index-Organized Tables
    • Altering Index-Organized Tables
    • Moving (Rebuilding) Index-Organized Tables
  • Creating Secondary Indexes on Index-Organized Tables
    • Creating a Secondary Index on an Index-Organized Table
    • Maintaining Physical Guesses in Logical Rowids
    • Bitmap Indexes
  • Analyzing Index-Organized Tables
    • Collecting Optimizer Statistics for Index-Organized Tables
    • Validating the Structure of Index-Organized Tables
  • Using the ORDER BY Clause with Index-Organized Tables
  • Converting Index-Organized Tables to Regular Tables
• Managing External Tables
  • Creating External Tables
  • Altering External Tables
  • Dropping External Tables
  • System and Object Privileges for External Tables
• 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
  • 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 the Scheduler
  • What Can the Scheduler Do?
• Basic Scheduler Concepts
  • Programs
  • Schedules
  • Jobs
    • Job Instances
  • Events
  • Chains
  • How Programs, Jobs, and Schedules are Related
• Advanced Scheduler Concepts
  • Job Classes
  • Windows
  • Window Groups
  • External Jobs
    • About Credentials
    • About Local External Jobs
    • About Remote External Jobs
  • Scheduler Support for Oracle Data Guard
• 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

27 Scheduling Jobs with Oracle Scheduler

• Scheduler Objects and Their Naming
• Using Jobs
  • Job Tasks and Their Procedures
  • Creating Jobs
    • Setting Job Attributes
    • Setting Job Arguments
    • Ways of Creating Jobs
    • Creating Remote External Jobs
  • Copying Jobs
  • Altering Jobs
  • Running Jobs
    • Running Jobs Asynchronously
    • Running Jobs Synchronously
    • Job Run Environment
    • Capturing Standard Error Output for External Jobs
  • Stopping Jobs
    • Stopping External Jobs
  • Dropping Jobs
  • Disabling Jobs
  • Enabling 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
  • 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
  • Enabling Chains
  • Creating Jobs for Chains
  • Dropping Chains
  • Running Chains
  • Dropping Rules from a Chain
  • Disabling Chains
  • Dropping Chain Steps
  • Altering Chain Steps
  • 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 the 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 Logs
    • Job Run Details
    • Controlling Job Logging
    • Window Logs
    • Purging Logs
  • Changing Job Priorities
  • Monitoring Running Chains
  • Managing Scheduler Security
• Enabling and Disabling Remote External Jobs
  • Setting Up the Database
  • Installing and Configuring 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 Moving from DBMS_JOB to DBMS_SCHEDULER

• Creating a Job
• Altering a Job
• Removing a Job from the Job Queue

Index