Contents

Title and Copyright Information

Send Us Your Comments

Preface

• Audience
• Documentation Accessibility
• Structure
• Related Documents
• Conventions

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

• Oracle Database 10g Release 2 (10.2) New Features in the Administrator's Guide
• Oracle Database 10g Release 1 (10.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
  • • 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
• Server Manageability
• • Automatic Manageability Features
  • • Automatic Workload Repository
    • Automatic Maintenance Tasks
    • Server-Generated Alerts
    • Advisors
  • Data Utilities
  • • SQL*Loader
    • Export and Import Utilities

2 Creating an Oracle Database

• Deciding How to Create an Oracle Database
• Manually Creating an Oracle Database
• • Considerations Before Creating the Database
  • • Planning for Database Creation
    • Meeting Creation Prerequisites
  • Creating the Database
  • • Step 1: Decide on Your Instance Identifier (SID)
    • Step 2: Establish the Database Administrator Authentication Method
    • Step 3: Create the Initialization Parameter File
    • Step 4: Connect to the Instance
    • Step 5: Create a Server Parameter File (Recommended)
    • Step 6: Start the Instance
    • Step 7: Issue the CREATE DATABASE Statement
    • Step 8: Create Additional Tablespaces
    • Step 9: Run Scripts to Build Data Dictionary Views
    • Step 10: Run Scripts to Install Additional Options (Optional)
    • Step 11: Back Up the Database.
• Understanding the CREATE DATABASE Statement
• • Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM
  • Creating a Locally Managed SYSTEM Tablespace
  • Creating the SYSAUX Tablespace
  • Using Automatic Undo Management: Creating an Undo Tablespace
  • Creating a Default Permanent Tablespace
  • Creating a Default Temporary Tablespace
  • Specifying Oracle-Managed Files at Database Creation
  • Supporting Bigfile Tablespaces During Database Creation
  • • Specifying the Default Tablespace Type
    • Overriding the Default Tablespace Type
  • Specifying the Database Time Zone and Time Zone File
  • • 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
  • Managing the System Global Area (SGA)
  • • Components and Granules in the SGA
    • Limiting the Size of the SGA
    • Using Automatic Shared Memory Management
    • Using Manual Shared Memory Management
    • Viewing Information About the SGA
  • Specifying the Maximum Number of Processes
  • Specifying the Method of Undo Space Management
  • • UNDO_MANAGEMENT Initialization Parameter
    • UNDO_TABLESPACE Initialization Parameter
  • The COMPATIBLE Initialization Parameter and Irreversible Compatibility
  • Setting the License Parameter
• Troubleshooting Database Creation
• Dropping a Database
• Managing Initialization Parameters Using a Server Parameter File
• • What Is a Server Parameter File?
  • Migrating to a Server Parameter File
  • Creating a Server Parameter File
  • The SPFILE Initialization Parameter
  • Using ALTER SYSTEM to Change Initialization Parameter Values
  • • Setting or Changing Initialization Parameter Values
  • Exporting the Server Parameter File
  • Backing Up the Server Parameter File
  • Errors and Recovery for the Server Parameter File
  • Viewing Parameter Settings
• Defining Application Services for Oracle Database 10g
• • Deploying Services
  • Configuring Services
  • Using Services
  • • Client-Side Use
    • Server-Side Use
• Considerations After Creating a Database
• • Some Security Considerations
  • Enabling Transparent Data Encryption
  • Creating a Secure External Password Store
  • Installing the Oracle Database Sample Schemas
• Viewing Information About the Database

3 Starting Up and Shutting Down

• Starting Up a Database
• • Options for Starting Up a Database
  • • Starting Up a Database Using SQL*Plus
    • Starting Up a Database Using Recovery Manager
    • Starting Up a Database Using Oracle Enterprise Manager
  • 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 Oracle Database Processes

• About Dedicated and Shared Server Processes
• • Dedicated Server Processes
  • Shared Server Processes
• Configuring Oracle Database for Shared Server
• • Initialization Parameters for Shared Server
  • Enabling Shared Server
  • • Determining a Value for SHARED_SERVERS
    • Decreasing the Number of Shared Server Processes
    • Limiting the Number of Shared Server Processes
    • Limiting the Number of Shared Server Sessions
    • Protecting Shared Memory
  • Configuring Dispatchers
  • • DISPATCHERS Initialization Parameter Attributes
    • Determining the Number of Dispatchers
    • Setting the Initial Number of Dispatchers
    • Altering the Number of Dispatchers
    • Shutting Down Specific Dispatcher Processes
    • Disabling Shared Servers
  • Monitoring Shared Server
• About Oracle Database Background Processes
• Managing Processes for Parallel SQL Execution
• • About Parallel Execution Servers
  • Altering Parallel Execution for a Session
  • • Disabling Parallel SQL Execution
    • Enabling Parallel SQL Execution
    • Forcing Parallel SQL Execution
• Managing Processes for External Procedures
• Terminating Sessions
• • Identifying Which Session to Terminate
  • Terminating an Active Session
  • Terminating an Inactive Session
• Monitoring the Operation of Your Database
• • Server-Generated Alerts
  • • Using APIs to Administer Server-Generated Alerts
    • Viewing Alert Data
  • Monitoring the Database Using Trace Files and the Alert Log
  • • Using the Trace Files
    • Specifying the Location of Trace Files
    • Controlling the Size of Trace Files
    • Controlling When Oracle Database Writes to Trace Files
    • Reading the Trace File for Shared Server Sessions
  • Monitoring Locks
  • Monitoring Wait Events
  • Process and Session Views

Part II Oracle Database Structure and Storage

5 Managing Control Files

• What Is a Control File?
• Guidelines for Control Files
• • Provide Filenames for the Control Files
  • Multiplex Control Files on Different Disks
  • Back Up Control Files
  • Manage the Size of Control Files
• Creating Control Files
• • Creating Initial Control Files
  • Creating Additional Copies, Renaming, and Relocating Control Files
  • Creating New Control Files
  • • When to Create New Control Files
    • The CREATE CONTROLFILE Statement
    • Steps for Creating New Control Files
• Troubleshooting After Creating Control Files
• • Checking for Missing or Extra Files
  • Handling Errors During CREATE CONTROLFILE
• Backing Up Control Files
• Recovering a Control File Using a Current Copy
• • Recovering from Control File Corruption Using a Control File Copy
  • Recovering from Permanent Media Failure Using a Control File Copy
• Dropping Control Files
• Displaying Control File Information

6 Managing the Redo Log

• What Is the Redo Log?
• • Redo Threads
  • Redo Log Contents
  • How Oracle Database Writes to the Redo Log
  • • Active (Current) and Inactive Redo Log Files
    • Log Switches and Log Sequence Numbers
• Planning the Redo Log
• • Multiplexing Redo Log Files
  • • Responding to Redo Log Failure
    • Legal and Illegal Configurations
  • Placing Redo Log Members on Different Disks
  • Setting the Size of Redo Log Members
  • Choosing the Number of Redo Log Files
  • Controlling Archive Lag
  • • Setting the ARCHIVE_LAG_TARGET Initialization Parameter
    • Factors Affecting the Setting of ARCHIVE_LAG_TARGET
• Creating Redo Log Groups and Members
• • Creating Redo Log Groups
  • Creating Redo Log Members
• Relocating and Renaming Redo Log Members
• Dropping Redo Log Groups and Members
• • Dropping Log Groups
  • Dropping Redo Log Members
• Forcing Log Switches
• Verifying Blocks in Redo Log Files
• Clearing a Redo Log File
• Viewing Redo Log Information

7 Managing Archived Redo Logs

• What Is the Archived Redo Log?
• Choosing Between NOARCHIVELOG and ARCHIVELOG Mode
• • Running a Database in NOARCHIVELOG Mode
  • Running a Database in ARCHIVELOG Mode
• Controlling Archiving
• • Setting the Initial Database Archiving Mode
  • Changing the Database Archiving Mode
  • Performing Manual Archiving
  • Adjusting the Number of Archiver Processes
• Specifying the Archive Destination
• • Specifying Archive Destinations
  • • Method 1: Using the LOG_ARCHIVE_DEST_n Parameter
    • Method 2: Using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST
  • Understanding Archive Destination Status
• Specifying the Mode of Log Transmission
• • Normal Transmission Mode
  • Standby Transmission Mode
• Managing Archive Destination Failure
• • Specifying the Minimum Number of Successful Destinations
  • • Specifying Mandatory and Optional Destinations
    • Specifying the Number of Successful Destinations: Scenarios
  • Rearchiving to a Failed Destination
• Controlling Trace Output Generated by the Archivelog Process
• Viewing Information About the Archived Redo Log
• • Dynamic Performance Views
  • The ARCHIVE LOG LIST Command

8 Managing Tablespaces

• Guidelines for Managing Tablespaces
• • Using Multiple Tablespaces
  • Assigning Tablespace Quotas to Users
• Creating Tablespaces
• • Locally Managed Tablespaces
  • • Creating a Locally Managed Tablespace
    • Specifying Segment Space Management in Locally Managed Tablespaces
    • Altering a Locally Managed Tablespace
  • Bigfile Tablespaces
  • • Creating a Bigfile Tablespace
    • Altering a Bigfile Tablespace
    • Identifying a Bigfile Tablespace
  • Temporary Tablespaces
  • • Creating a Locally Managed Temporary Tablespace
    • Creating a Bigfile Temporary Tablespace
    • Altering a Locally Managed Temporary Tablespace
  • Multiple Temporary Tablespaces: Using Tablespace Groups
  • • Creating a Tablespace Group
    • Changing Members of a Tablespace Group
    • Assigning a Tablespace Group as the Default Temporary Tablespace
• Specifying Nonstandard Block Sizes for Tablespaces
• Controlling the Writing of Redo Records
• Altering Tablespace Availability
• • Taking Tablespaces Offline
  • Bringing Tablespaces Online
• Using Read-Only Tablespaces
• • Making a Tablespace Read-Only
  • Making a Read-Only Tablespace Writable
  • Creating a Read-Only Tablespace on a WORM Device
  • Delaying the Opening of Datafiles in Read-Only Tablespaces
• Renaming Tablespaces
• Dropping Tablespaces
• Managing the SYSAUX Tablespace
• • Monitoring Occupants of the SYSAUX Tablespace
  • Moving Occupants Out Of or Into the SYSAUX Tablespace
  • Controlling the Size of the SYSAUX Tablespace
• Diagnosing and Repairing Locally Managed Tablespace Problems
• • Scenario 1: Fixing Bitmap When Allocated Blocks are Marked Free (No Overlap)
  • Scenario 2: Dropping a Corrupted Segment
  • Scenario 3: Fixing Bitmap Where Overlap is Reported
  • Scenario 4: Correcting Media Corruption of Bitmap Blocks
  • Scenario 5: Migrating from a Dictionary-Managed to a Locally Managed Tablespace
• Migrating the SYSTEM Tablespace to a Locally Managed Tablespace
• Transporting Tablespaces Between Databases
• • Introduction to Transportable Tablespaces
  • About Transporting Tablespaces Across Platforms
  • Limitations on Transportable Tablespace Use
  • Compatibility Considerations for Transportable Tablespaces
  • Transporting Tablespaces Between Databases: A Procedure and Example
  • • Step 1: Determine if Platforms are Supported and Endianness
    • Step 2: Pick a Self-Contained Set of Tablespaces
    • Step 3: Generate a Transportable Tablespace Set
    • Step 4: Transport the Tablespace Set
    • Step 5: 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
• Viewing Tablespace Information
• • Example 1: Listing Tablespaces and Default Storage Parameters
  • Example 2: Listing the Datafiles and Associated Tablespaces of a Database
  • Example 3: Displaying Statistics for Free Space (Extents) of Each Tablespace

9 Managing Datafiles and Tempfiles

• Guidelines for Managing Datafiles
• • Determine the Number of Datafiles
  • • Determine a Value for the DB_FILES Initialization Parameter
    • Consider Possible Limitations When Adding Datafiles to a Tablespace
    • Consider the Performance Impact
  • Determine the Size of Datafiles
  • Place Datafiles Appropriately
  • Store Datafiles Separate from Redo Log Files
• Creating Datafiles and Adding Datafiles to a Tablespace
• Changing Datafile Size
• • Enabling and Disabling Automatic Extension for a Datafile
  • Manually Resizing a Datafile
• Altering Datafile Availability
• • Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode
  • Taking Datafiles Offline in NOARCHIVELOG Mode
  • Altering the Availability of All Datafiles or Tempfiles in a Tablespace
• Renaming and Relocating Datafiles
• • Procedures for Renaming and Relocating Datafiles in a Single Tablespace
  • • Procedure for Renaming Datafiles in a Single Tablespace
    • Procedure for Relocating Datafiles in a Single Tablespace
  • Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces
• Dropping Datafiles
• Verifying Data Blocks in Datafiles
• Copying Files Using the Database Server
• • Copying a File on a Local File System
  • Third-Party File Transfer
  • File Transfer and the DBMS_SCHEDULER Package
  • Advanced File Transfer Mechanisms
• Mapping Files to Physical Devices
• • Overview of Oracle Database File Mapping Interface
  • How the Oracle Database File Mapping Interface Works
  • • Components of File Mapping
    • Mapping Structures
    • Example of Mapping Structures
    • Configuration ID
  • Using the Oracle Database File Mapping Interface
  • • Enabling File Mapping
    • Using the DBMS_STORAGE_MAP Package
    • Obtaining Information from the File Mapping Views
  • File Mapping Examples
  • • Example 1: Map All Database Files that Span a Device
    • Example 2: Map a File into Its Corresponding Devices
    • Example 3: Map a Database Object
• Viewing Datafile Information

10 Managing the Undo Tablespace

• What Is Undo?
• Introduction to Automatic Undo Management
• • Overview of Automatic Undo Management
  • Undo Retention
  • • Retention Guarantee
    • Automatic Tuning of Undo Retention
• Setting the Undo Retention Period
• Sizing the Undo Tablespace
• • Using Auto-Extensible Tablespaces
  • Sizing Fixed-Size Undo Tablespaces
  • • 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
• Migrating to Automatic Undo Management
• Viewing Information About Undo

Part III Automated File and Storage Management

11 Using Oracle-Managed Files

• What Are Oracle-Managed Files?
• • Who Can Use Oracle-Managed Files?
  • • What Is a Logical Volume Manager?
    • What Is a File System?
  • Benefits of Using Oracle-Managed Files
  • Oracle-Managed Files and Existing Functionality
• Enabling the Creation and Use of Oracle-Managed Files
• • Setting the DB_CREATE_FILE_DEST Initialization Parameter
  • Setting the DB_RECOVERY_FILE_DEST Parameter
  • Setting the DB_CREATE_ONLINE_LOG_DEST_n Initialization Parameter
• Creating Oracle-Managed Files
• • How Oracle-Managed Files Are Named
  • Creating Oracle-Managed Files at Database Creation
  • • Specifying Control Files at Database Creation
    • Specifying Redo Log Files at Database Creation
    • Specifying the SYSTEM and SYSAUX Tablespace Datafiles at Database Creation
    • Specifying the Undo Tablespace Datafile at Database Creation
    • Specifying the Default Temporary Tablespace Tempfile at Database Creation
    • CREATE DATABASE Statement Using Oracle-Managed Files: Examples
  • Creating Datafiles for Tablespaces Using Oracle-Managed Files
  • • CREATE TABLESPACE: Examples
    • CREATE UNDO TABLESPACE: Example
    • ALTER TABLESPACE: Example
  • Creating Tempfiles for Temporary Tablespaces Using Oracle-Managed Files
  • • CREATE TEMPORARY TABLESPACE: Example
    • ALTER TABLESPACE... ADD TEMPFILE: Example
  • Creating Control Files Using Oracle-Managed Files
  • • CREATE CONTROLFILE Using NORESETLOGS Keyword: Example
    • CREATE CONTROLFILE Using RESETLOGS Keyword: Example
  • Creating Redo Log Files Using Oracle-Managed Files
  • • Using the ALTER DATABASE ADD LOGFILE Statement
    • Using the ALTER DATABASE OPEN RESETLOGS Statement
  • Creating Archived Logs Using Oracle-Managed Files
• Behavior of Oracle-Managed Files
• • Dropping Datafiles and Tempfiles
  • Dropping Redo Log Files
  • Renaming Files
  • Managing Standby Databases
• Scenarios for Using Oracle-Managed Files
• • Scenario 1: Create and Manage a Database with Multiplexed Redo Logs
  • Scenario 2: Create and Manage a Database with Database and Flash Recovery Areas
  • Scenario 3: Adding Oracle-Managed Files to an Existing Database

12 Using Automatic Storage Management

• What Is Automatic Storage Management?
• Overview of the Components of Automatic Storage Management
• Administering an Automatic Storage Management Instance
• • Installing ASM
  • Authentication for Accessing an ASM Instance
  • • Operating System Authentication for ASM
    • Password File Authentication for ASM
  • Setting Initialization Parameters for an ASM Instance
  • • Initialization Parameters for ASM Instances
    • Tuning Rebalance Operations
    • Improving Disk Discovery Time
    • Behavior of Database Initialization Parameters in an ASM Instance
    • Behavior of ASM Initialization Parameters in a Database Instance
  • Starting Up an ASM Instance
  • • ASM Instance Memory Requirements
    • CSS Requirement
    • Disk Discovery
    • Disk Group Recovery
  • Shutting Down an ASM Instance
• Administering Automatic Storage Management Disk Groups
• • Considerations and Guidelines for Configuring Disk Groups
  • • Determining the Number of Disk Groups
    • Performance Characteristics when Grouping Disks
    • Effects of Adding and Dropping Disks from a Disk Group
    • How ASM Handles Disk Failures
    • Failure Groups and Mirroring
    • Managing Capacity in Disk Groups
    • Scalability
  • Creating a Disk Group
  • Altering the Disk Membership of a Disk Group
  • • Adding Disks to a Disk Group
    • Dropping Disks from Disk Groups
    • Resizing Disks in Disk Groups
    • Undropping Disks in Disk Groups
    • Manually Rebalancing a Disk Group
  • Mounting and Dismounting Disk Groups
  • Checking Internal Consistency of Disk Group Metadata
  • Dropping Disk Groups
  • Managing Disk Group Directories
  • • Creating a New Directory
    • Renaming a Directory
    • Dropping a Directory
  • Managing Alias Names for ASM Filenames
  • • Adding an Alias Name for an ASM Filename
    • Renaming an Alias Name for an ASM Filename
    • Dropping an Alias Name for an ASM Filename
  • Dropping Files and Associated Aliases from a Disk Group
  • Managing Disk Group Templates
  • • Adding Templates to a Disk Group
    • Modifying a Disk Group Template
    • Dropping Templates from a Disk Group
• Using Automatic Storage Management in the Database
• • What Types of Files Does ASM Support?
  • About ASM Filenames
  • • Fully Qualified ASM Filename
    • Numeric ASM Filename
    • Alias ASM Filenames
    • Alias ASM Filename with Template
    • Incomplete ASM Filename
    • Incomplete ASM Filename with Template
  • Starting the ASM and Database Instances
  • Creating and Referencing ASM Files in the Database
  • • Creating ASM Files Using a Default Disk Group Specification
    • Using ASM Filenames in SQL Statements
  • Creating a Database in ASM
  • Creating Tablespaces in ASM
  • Creating Redo Logs in ASM
  • Creating a Control File in ASM
  • Creating Archive Log Files in ASM
  • Recovery Manager (RMAN) and ASM
• Migrating a Database to Automatic Storage Management
• Accessing Automatic Storage Management Files with the XML DB Virtual Folder
• • Inside /sys/asm
  • • Restrictions
  • Sample FTP Session
• Viewing Information About Automatic Storage Management

Part IV Schema Objects

13 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 Dependencies Among Schema Objects
  • Manually Recompiling Views
  • Manually Recompiling Procedures and Functions
  • Manually Recompiling Packages
• Managing Object Name Resolution
• Switching to a Different Schema
• Displaying Information About Schema Objects
• • Using a PL/SQL Package to Display Information About Schema Objects
  • Using Views to Display Information About Schema Objects
  • • Example 1: Displaying Schema Objects By Type
    • Example 2: Displaying Dependencies of Views and Synonyms

14 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 Job
    • 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
  • Using Views to Display Information About Space Usage in Schema Objects
  • • Example 1: Displaying Segment Information
    • Example 2: Displaying Extent Information
    • Example 3: Displaying the Free Space (Extents) in a Tablespace
    • Example 4: Displaying Segments that Cannot Allocate Additional Extents
• Capacity Planning for Database Objects
• • Estimating the Space Use of a Table
  • Estimating the Space Use of an Index
  • Obtaining Object Growth Trends

15 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 when Creating Tables
  • Estimate Table Size and Plan Accordingly
  • Restrictions to Consider When Creating Tables
• Creating Tables
• • Creating a Table
  • Creating a Temporary Table
  • Parallelizing Table Creation
• 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
• 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
• Viewing Information About Tables

16 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
• 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

• About Partitioned Tables and Indexes
• Partitioning Methods
• • When to Use Range Partitioning
  • When to Use Hash Partitioning
  • When to Use List Partitioning
  • When to Use Composite Range-Hash Partitioning
  • When to Use Composite Range-List Partitioning
• Creating Partitioned Tables
• • Creating Range-Partitioned Tables and Global Indexes
  • • Creating a Range Partitioned Table
    • Creating a Range-Partitioned Global Index
  • Creating Hash-Partitioned Tables and Global Indexes
  • • Creating a Hash Partitioned Table
    • Creating a Hash-Partitioned Global Index
  • Creating List-Partitioned Tables
  • Creating Composite Range-Hash Partitioned Tables
  • Creating Composite Range-List Partitioned Tables
  • Using Subpartition Templates to Describe Composite Partitioned Tables
  • • Specifying a Subpartition Template for a Range-Hash Partitioned Table
    • Specifying a Subpartition Template for a Range-List Partitioned Table
  • Using Multicolumn Partitioning Keys
  • Using Table Compression with Partitioned Tables
  • Using Key Compression with Partitioned Indexes
  • Creating Partitioned Index-Organized Tables
  • • Creating Range-Partitioned Index-Organized Tables
    • Creating List-Partitioned Index-Organized Tables
    • Creating Hash-Partitioned Index-Organized Tables
  • Partitioning Restrictions for Multiple Block Sizes
• Maintaining Partitioned Tables
• • Updating Indexes Automatically
  • Adding Partitions
  • • Adding a Partition to a Range-Partitioned Table
    • Adding a Partition to a Hash-Partitioned Table
    • Adding a Partition to a List-Partitioned Table
    • Adding Partitions to a Range-Hash Composite-Partitioned Table
    • Adding Partitions to a Range-List Partitioned Table
    • Adding Index Partitions
  • Coalescing Partitions
  • • Coalescing a Partition in a Hash-Partitioned Table
    • Coalescing a Subpartition in a Range-Hash Partitioned Table
    • Coalescing Hash-partitioned Global Indexes
  • Dropping Partitions
  • • Dropping Table Partitions
    • Dropping Index Partitions
  • Exchanging Partitions
  • • Exchanging a Range, Hash, or List Partition
    • Exchanging a Hash-Partitioned Table with a Range-Hash Partition
    • Exchanging a Subpartition of a Range-Hash Partitioned Table
    • Exchanging a List-Partitioned Table with a Range-List Partition
    • Exchanging a Subpartition of a Range-List Partitioned Table
  • Merging Partitions
  • • Merging Range Partitions
    • Merging List Partitions
    • Merging Range-Hash Partitions
    • Merging Range-List Partitions
  • Modifying Default Attributes
  • • Modifying Default Attributes of a Table
    • Modifying Default Attributes of a Partition
    • Modifying Default Attributes of Index Partitions
  • Modifying Real Attributes of Partitions
  • • Modifying Real Attributes for a Range or List Partition
    • Modifying Real Attributes for a Hash Partition
    • Modifying Real Attributes of a Subpartition
    • Modifying Real Attributes of Index Partitions
  • Modifying List Partitions: Adding Values
  • • Adding Values for a List Partition
    • Adding Values for a List Subpartition
  • Modifying List Partitions: Dropping Values
  • • Dropping Values from a List Partition
    • Dropping Values from a List Subpartition
  • Modifying a Subpartition Template
  • Moving Partitions
  • • Moving Table Partitions
    • Moving Subpartitions
    • Moving Index Partitions
  • Redefining Partitions Online
  • Rebuilding Index Partitions
  • • Rebuilding Global Index Partitions
    • Rebuilding Local Index Partitions
  • Renaming Partitions
  • • Renaming a Table Partition
    • Renaming a Table Subpartition
    • Renaming Index Partitions
  • Splitting Partitions
  • • Splitting a Partition of a Range-Partitioned Table
    • Splitting a Partition of a List-Partitioned Table
    • Splitting a Range-Hash Partition
    • Splitting Partitions in a Range-List Partitioned Table
    • Splitting Index Partitions
    • Optimizing SPLIT PARTITION and SPLIT SUBPARTITION Operations
  • Truncating Partitions
  • • Truncating a Table Partition
    • Truncating a Subpartition
• Dropping Partitioned Tables
• Partitioned Tables and Indexes Example
• Viewing Information About Partitioned Tables and Indexes

18 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
• Viewing Information About Clusters

19 Managing Hash Clusters

• About Hash Clusters
• When to Use Hash Clusters
• • Situations Where Hashing Is Useful
  • Situations Where Hashing Is Not Advantageous
• Creating Hash Clusters
• • Creating a Sorted Hash Cluster
  • Creating Single-Table Hash Clusters
  • Controlling Space Use Within a Hash Cluster
  • • Choosing the Key
    • Setting HASH IS
    • Setting SIZE
    • Setting HASHKEYS
    • Controlling Space in Hash Clusters
  • Estimating Size Required by Hash Clusters
• Altering Hash Clusters
• Dropping Hash Clusters
• Viewing Information About Hash Clusters

20 Managing Views, Sequences, and Synonyms

• Managing Views
• • About Views
  • Creating Views
  • • Join Views
    • Expansion of Defining Queries at View Creation Time
    • Creating Views with Errors
  • Replacing Views
  • Using Views in Queries
  • Updating a Join View
  • • Key-Preserved Tables
    • DML Statements and Join Views
    • Updating Views That Involve Outer Joins
    • Using the UPDATABLE_ COLUMNS Views
  • Altering Views
  • Dropping Views
• Managing Sequences
• • About Sequences
  • Creating Sequences
  • Altering Sequences
  • Using Sequences
  • • Referencing a Sequence
    • Caching Sequence Numbers
  • Dropping Sequences
• Managing Synonyms
• • About Synonyms
  • Creating Synonyms
  • Using Synonyms in DML Statements
  • Dropping Synonyms
• Viewing Information About Views, Synonyms, and Sequences

21 Using DBMS_REPAIR to Repair Data Block Corruption

• Options for Repairing Data Block Corruption
• About the DBMS_REPAIR Package
• • DBMS_REPAIR Procedures
  • Limitations and Restrictions
• Using the DBMS_REPAIR Package
• • Task 1: Detect and Report Corruptions
  • • DBMS_REPAIR: Using the CHECK_OBJECT and ADMIN_TABLES Procedures
    • DB_VERIFY: Performing an Offline Database Check
    • ANALYZE: 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 V Database Security

22 Managing Users and Securing the Database

• The Importance of Establishing a Security Policy for Your Database
• Managing Users and Resources
• Managing User Privileges and Roles
• Auditing Database Use

Part VI Database Resource Management and Task Scheduling

23 Managing Automatic System Tasks Using the Maintenance Window

• Maintenance Windows
• Predefined Automatic System Tasks
• • Automatic Statistics Collection Job
  • Automatic Segment Advisor Job
• Resource Management for Automatic System Tasks

24 Using the Database Resource Manager

• What Is the Database Resource Manager?
• • What Problems Does the Database Resource Manager Address?
  • How Does the Database Resource Manager Address These Problems?
  • What Are the Elements of the Database Resource Manager?
  • Understanding Resource Plans
  • • A Simple Resource Plan
    • A Resource Plan with Subplans
    • Resource Consumer Groups
    • Resource Plan Directives
• Administering the Database Resource Manager
• Creating a Simple Resource Plan
• Creating Complex Resource Plans
• • Using the Pending Area for Creating Plan Schemas
  • • Creating a Pending Area
    • Validating Changes
    • Submitting Changes
    • Clearing the Pending Area
  • Creating Resource Plans
  • • Creating a Plan
    • Updating a Plan
    • Deleting a Plan
    • Using the Ratio Policy
  • Creating Resource Consumer Groups
  • • Creating a Consumer Group
    • Updating a Consumer Group
    • Deleting a Consumer Group
  • Specifying Resource Plan Directives
  • • Creating a Resource Plan Directive
    • Updating Resource Plan Directives
    • Deleting Resource Plan Directives
    • How Resource Plan Directives Interact
• Managing Resource Consumer Groups
• • Assigning an Initial Resource Consumer Group
  • Changing Resource Consumer Groups
  • • Switching a Session
    • Switching Sessions for a User
    • Using the DBMS_SESSION Package to Switch Consumer Group
  • Managing the Switch Privilege
  • • Granting the Switch Privilege
    • Revoking Switch Privileges
  • Automatically Assigning Resource Consumer Groups to Sessions
  • • Creating Consumer Group Mappings
    • Creating Attribute Mapping Priorities
    • Automatic Group Switching
• Enabling the Database Resource Manager
• Putting It All Together: Database Resource Manager Examples
• • Multilevel Schema Example
  • Example of Using Several Resource Allocation Methods
  • An Oracle-Supplied Plan
• Monitoring and Tuning the Database Resource Manager
• • Creating the Environment
  • Why Is This Necessary to Produce Expected Results?
  • Monitoring Results
• Interaction with Operating-System Resource Control
• • Guidelines for Using Operating-System Resource Control
  • Dynamic Reconfiguration
• Viewing Database Resource Manager Information
• • Viewing Consumer Groups Granted to Users or Roles
  • Viewing Plan Schema Information
  • Viewing Current Consumer Groups for Sessions
  • Viewing the Currently Active Plans

25 Moving from DBMS_JOB to DBMS_SCHEDULER

• Moving from DBMS_JOB to DBMS_SCHEDULER
• • Creating a Job
  • Altering a Job
  • Removing a Job from the Job Queue

26 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
• 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 Using the 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
  • Copying Jobs
  • Altering Jobs
  • Running Jobs
  • • Running Jobs Asynchronously
    • Running Jobs Synchronously
    • Job Run Environment
    • Running 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 the Scheduler

• Configuring the Scheduler
• Monitoring and Managing the Scheduler
• • How to View Scheduler Information
  • How to View the Currently Active Window and Resource Plan
  • How to View Scheduler Privileges
  • How to Find Information About Currently Running Jobs
  • How the Job Coordinator Works
  • • Job Coordinator and Real Application Clusters
    • Using DBMS_SCHEDULER and DBMS_JOB at the Same Time
    • Scheduler Attribute max_job_slave_processes
  • How to Monitor and Manage Window and Job Logs
  • • Job Logs
    • Job Run Details
    • Controlling Job Logging
    • Window Logs
    • Purging Logs
  • How to Manage Scheduler Privileges
  • • Types of Privileges and Their Descriptions
  • How to Drop a Job
  • How to Drop a Running Job
  • Why Does a Job Fail to Run?
  • • Failed Jobs
    • Broken Jobs
    • Disabled Jobs
    • Completed Jobs
  • Job Recovery After a Failure
  • How to Change Job Priorities
  • How to Monitor Running Chains
  • Why Does a Program Become Disabled?
  • Why Does a Window Fail to Take Effect?
  • How the Scheduler Guarantees Availability
  • How to Handle Scheduler Security
  • How to Manage the Scheduler in a RAC Environment
• Import/Export and the Scheduler
• Examples of Using the Scheduler
• • Examples of Creating Jobs
  • Examples of Creating Job Classes
  • Examples of Creating Programs
  • Examples of Creating Windows
  • Example of Creating Window Groups
  • Examples of Setting Attributes
  • Examples of Creating Chains
  • Examples of Creating Jobs and Schedules Based on Events

Part VII Distributed Database Management

29 Distributed Database Concepts

• Distributed Database Architecture
• • Homogenous Distributed Database Systems
  • • Distributed Databases Versus Distributed Processing
    • Distributed Databases Versus Replicated Databases
  • Heterogeneous Distributed Database Systems
  • • Heterogeneous Services
    • Transparent Gateway Agents
    • Generic Connectivity
  • Client/Server Database Architecture
• Database Links
• • What Are Database Links?
  • What Are Shared Database Links?
  • Why Use Database Links?
  • Global Database Names in Database Links
  • Names for Database Links
  • Types of Database Links
  • Users of Database Links
  • • Connected User Database Links
    • Fixed User Database Links
    • Current User Database Links
  • Creation of Database Links: Examples
  • Schema Objects and Database Links
  • • Naming of Schema Objects Using Database Links
    • Authorization for Accessing Remote Schema Objects
    • Synonyms for Schema Objects
    • Schema Object Name Resolution
  • Database Link Restrictions
• Distributed Database Administration
• • Site Autonomy
  • Distributed Database Security
  • • Authentication Through Database Links
    • Authentication Without Passwords
    • Supporting User Accounts and Roles
    • Centralized User and Privilege Management
    • Data Encryption
  • Auditing Database Links
  • Administration Tools
  • • Enterprise Manager
    • Third-Party Administration Tools
    • SNMP Support
• Transaction Processing in a Distributed System
• • Remote SQL Statements
  • Distributed SQL Statements
  • Shared SQL for Remote and Distributed Statements
  • Remote Transactions
  • Distributed Transactions
  • Two-Phase Commit Mechanism
  • Database Link Name Resolution
  • • Name Resolution When the Global Database Name Is Complete
    • Name Resolution When the Global Database Name Is Partial
    • Name Resolution When No Global Database Name Is Specified
    • Terminating the Search for Name Resolution
  • Schema Object Name Resolution
  • • Example of Global Object Name Resolution: Complete Object Name
    • Example of Global Object Name Resolution: Partial Object Name
  • Global Name Resolution in Views, Synonyms, and Procedures
  • • What Happens When Global Names Change
    • Scenarios for Global Name Changes
• Distributed Database Application Development
• • Transparency in a Distributed Database System
  • • Location Transparency
    • SQL and COMMIT Transparency
    • Replication Transparency
  • Remote Procedure Calls (RPCs)
  • Distributed Query Optimization
• Character Set Support for Distributed Environments
• • Client/Server Environment
  • Homogeneous Distributed Environment
  • Heterogeneous Distributed Environment

30 Managing a Distributed Database

• Managing Global Names in a Distributed System
• • Understanding How Global Database Names Are Formed
  • Determining Whether Global Naming Is Enforced
  • Viewing a Global Database Name
  • Changing the Domain in a Global Database Name
  • Changing a Global Database Name: Scenario
• Creating Database Links
• • Obtaining Privileges Necessary for Creating Database Links
  • Specifying Link Types
  • • Creating Private Database Links
    • Creating Public Database Links
    • Creating Global Database Links
  • Specifying Link Users
  • • Creating Fixed User Database Links
    • Creating Connected User and Current User Database Links
  • Using Connection Qualifiers to Specify Service Names Within Link Names
• Using Shared Database Links
• • Determining Whether to Use Shared Database Links
  • Creating Shared Database Links
  • Configuring Shared Database Links
  • • Creating Shared Links to Dedicated Servers
    • Creating Shared Links to Shared Servers
• Managing Database Links
• • Closing Database Links
  • Dropping Database Links
  • • Procedure for Dropping a Private Database Link
    • Procedure for Dropping a Public Database Link
  • Limiting the Number of Active Database Link Connections
• Viewing Information About Database Links
• • Determining Which Links Are in the Database
  • Determining Which Link Connections Are Open
• Creating Location Transparency
• • Using Views to Create Location Transparency
  • Using Synonyms to Create Location Transparency
  • • Creating Synonyms
    • Managing Privileges and Synonyms
  • Using Procedures to Create Location Transparency
  • • Using Local Procedures to Reference Remote Data
    • Using Local Procedures to Call Remote Procedures
    • Using Local Synonyms to Reference Remote Procedures
    • Managing Procedures and Privileges
• Managing Statement Transparency
• Managing a Distributed Database: Examples
• • Example 1: Creating a Public Fixed User Database Link
  • Example 2: Creating a Public Fixed User Shared Database Link
  • Example 3: Creating a Public Connected User Database Link
  • Example 4: Creating a Public Connected User Shared Database Link
  • Example 5: Creating a Public Current User Database Link

31 Developing Applications for a Distributed Database System

• Managing the Distribution of Application Data
• Controlling Connections Established by Database Links
• Maintaining Referential Integrity in a Distributed System
• Tuning Distributed Queries
• • Using Collocated Inline Views
  • Using Cost-Based Optimization
  • • How Does Cost-Based Optimization Work?
    • Setting Up Cost-Based Optimization
  • Using Hints
  • • Using the NO_MERGE Hint
    • Using the DRIVING_SITE Hint
  • Analyzing the Execution Plan
  • • Preparing the Database to Store the Plan
    • Generating the Execution Plan
    • Viewing the Execution Plan
• Handling Errors in Remote Procedures

32 Distributed Transactions Concepts

• What Are Distributed Transactions?
• • DML and DDL Transactions
  • Transaction Control Statements
• Session Trees for Distributed Transactions
• • Clients
  • Database Servers
  • Local Coordinators
  • Global Coordinator
  • Commit Point Site
  • • How a Distributed Transaction Commits
    • Commit Point Strength
• Two-Phase Commit Mechanism
• • Prepare Phase
  • • Types of Responses in the Prepare Phase
    • Steps in the Prepare Phase
  • Commit Phase
  • • Steps in the Commit Phase
    • Guaranteeing Global Database Consistency
  • Forget Phase
• In-Doubt Transactions
• • Automatic Resolution of In-Doubt Transactions
  • • Failure During the Prepare Phase
    • Failure During the Commit Phase
  • Manual Resolution of In-Doubt Transactions
  • Relevance of System Change Numbers for In-Doubt Transactions
• Distributed Transaction Processing: Case Study
• • Stage 1: Client Application Issues DML Statements
  • Stage 2: Oracle Database Determines Commit Point Site
  • Stage 3: Global Coordinator Sends Prepare Response
  • Stage 4: Commit Point Site Commits
  • Stage 5: Commit Point Site Informs Global Coordinator of Commit
  • Stage 6: Global and Local Coordinators Tell All Nodes to Commit
  • Stage 7: Global Coordinator and Commit Point Site Complete the Commit

33 Managing Distributed Transactions

• Specifying the Commit Point Strength of a Node
• Naming Transactions
• Viewing Information About Distributed Transactions
• • Determining the ID Number and Status of Prepared Transactions
  • Tracing the Session Tree of In-Doubt Transactions
• Deciding How to Handle In-Doubt Transactions
• • Discovering Problems with a Two-Phase Commit
  • Determining Whether to Perform a Manual Override
  • Analyzing the Transaction Data
  • • Find a Node that Committed or Rolled Back
    • Look for Transaction Comments
    • Look for Transaction Advice
• Manually Overriding In-Doubt Transactions
• • Manually Committing an In-Doubt Transaction
  • • Committing Using Only the Transaction ID
    • Committing Using an SCN
  • Manually Rolling Back an In-Doubt Transaction
• Purging Pending Rows from the Data Dictionary
• • Executing the PURGE_LOST_DB_ENTRY Procedure
  • Determining When to Use DBMS_TRANSACTION
• Manually Committing an In-Doubt Transaction: Example
• • Step 1: Record User Feedback
  • Step 2: Query DBA_2PC_PENDING
  • • Determining the Global Transaction ID
    • Determining the State of the Transaction
    • Looking for Comments or Advice
  • Step 3: Query DBA_2PC_NEIGHBORS on Local Node
  • • Obtaining Database Role and Database Link Information
    • Determining the Commit Point Site
  • Step 4: Querying Data Dictionary Views on All Nodes
  • • Checking the Status of Pending Transactions at sales
    • Determining the Coordinators and Commit Point Site at sales
    • Checking the Status of Pending Transactions at HQ
  • Step 5: Commit the In-Doubt Transaction
  • Step 6: Check for Mixed Outcome Using DBA_2PC_PENDING
• Data Access Failures Due to Locks
• • Transaction Timeouts
  • Locks from In-Doubt Transactions
• Simulating Distributed Transaction Failure
• • Forcing a Distributed Transaction to Fail
  • Disabling and Enabling RECO
• Managing Read Consistency

Index