1/49

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 2 (11.2.0.3) New Features in the Administrator's Guide
• Oracle Database 11g Release 2 (11.2.0.2) New Features in the Administrator's Guide
• Oracle Database 11g Release 2 (11.2.0.1) New Features in the Administrator's Guide

Part I Basic Database Administration

1 Getting Started with Database Administration

• Types of Oracle Database Users
  • Database Administrators
  • Security Officers
  • Network Administrators
  • Application Developers
  • Application Administrators
  • Database Users
• Tasks of a Database Administrator
  • Task 1: Evaluate the Database Server Hardware
  • Task 2: Install the Oracle Database Software
  • Task 3: Plan the Database
  • Task 4: Create and Open the Database
  • Task 5: Back Up the Database
  • Task 6: Enroll System Users
  • Task 7: Implement the Database Design
  • Task 8: Back Up the Fully Functional Database
  • Task 9: Tune Database Performance
  • Task 10: Download and Install Patches
  • Task 11: Roll Out to Additional Hosts
• Submitting Commands and SQL to the Database
  • About SQL*Plus
  • Connecting to the Database with SQL*Plus
    • Step 1: Open a Command Window
    • Step 2: Set Operating System Environment Variables
    • Step 3: Start SQL*Plus
    • Step 4: Submit the SQL*Plus CONNECT Statement
• Identifying Your Oracle Database Software Release
  • Release Number Format
    • Major Database Release Number
    • Database Maintenance Release Number
    • Fusion Middleware Release Number
    • Component-Specific Release Number
    • Platform-Specific Release Number
  • Checking Your Current Release Number
• About Database Administrator Security and Privileges
  • The Database Administrator's Operating System Account
  • Administrative User Accounts
    • SYS
    • SYSTEM
    • The DBA Role
• Database Administrator Authentication
  • Administrative Privileges
    • SYSDBA and SYSOPER
    • Connecting with Administrative Privileges: Example
  • Selecting an Authentication Method for Database Administrators
    • Nonsecure Remote Connections
    • Local Connections and Secure Remote Connections
  • Using Operating System Authentication
    • OSDBA and OSOPER
    • Preparing to Use Operating System Authentication
    • Connecting Using Operating System Authentication
  • Using Password File Authentication
    • Preparing to Use Password File Authentication
    • Connecting Using Password File Authentication
• Creating and Maintaining a Password File
  • Creating a Password File with ORAPWD
    • ORAPWD Command Line Argument Descriptions
  • Sharing and Disabling the Password File
  • Adding Users to a Password File
    • Granting and Revoking SYSDBA and SYSOPER Privileges
    • Viewing Password File Members
  • Maintaining a Password File
    • Expanding the Number of Password File Users
    • Removing a Password File
• Data Utilities

2 Creating and Configuring an Oracle Database

• About Creating an Oracle Database
  • Considerations Before Creating the Database
    • Planning for Database Creation
    • Meeting Creation Prerequisites
• Creating a Database with DBCA
  • Creating a Database with Interactive DBCA
  • Creating a Database with Noninteractive/Silent DBCA
• Creating a Database with the CREATE DATABASE Statement
  • Step 1: Specify an Instance Identifier (SID)
  • Step 2: Ensure That the Required Environment Variables Are Set
  • Step 3: Choose a Database Administrator Authentication Method
  • Step 4: Create the Initialization Parameter File
  • Step 5: (Windows Only) Create an Instance
  • Step 6: Connect to the Instance
  • Step 7: Create a Server Parameter File
  • Step 8: Start the Instance
  • Step 9: Issue the CREATE DATABASE Statement
  • Step 10: Create Additional Tablespaces
  • Step 11: Run Scripts to Build Data Dictionary Views
  • Step 12: (Optional) Run Scripts to Install Additional Options
  • Step 13: Back Up the Database.
  • Step 14: (Optional) Enable Automatic Instance Startup
• Specifying CREATE DATABASE Statement Clauses
  • Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM
  • Creating a Locally Managed SYSTEM Tablespace
  • About the SYSAUX Tablespace
  • Using Automatic Undo Management: Creating an Undo Tablespace
  • Creating a Default Permanent Tablespace
  • Creating a Default Temporary Tablespace
  • Specifying Oracle Managed Files at Database Creation
  • Supporting Bigfile Tablespaces During Database Creation
    • Specifying the Default Tablespace Type
    • Overriding the Default Tablespace Type
  • Specifying the Database Time Zone and Time Zone File
    • Setting the Database Time Zone
    • About the Database Time Zone Files
    • Specifying the Database Time Zone File
  • Specifying FORCE LOGGING Mode
    • Using the FORCE LOGGING Clause
    • Performance Considerations of FORCE LOGGING Mode
• Specifying Initialization Parameters
  • About Initialization Parameters and Initialization Parameter Files
    • Text Initialization Parameter File Format
    • Sample Initialization Parameter File
  • Determining the Global Database Name
    • DB_NAME Initialization Parameter
    • DB_DOMAIN Initialization Parameter
  • Specifying a Fast Recovery Area
  • Specifying Control Files
  • Specifying Database Block Sizes
    • DB_BLOCK_SIZE Initialization Parameter
    • Nonstandard Block Sizes
  • Specifying the Maximum Number of Processes
  • Specifying the DDL Lock Timeout
  • Specifying the Method of Undo Space Management
    • UNDO_MANAGEMENT Initialization Parameter
    • UNDO_TABLESPACE Initialization Parameter
  • About The COMPATIBLE Initialization Parameter
  • Setting the License Parameter
• Managing Initialization Parameters Using a Server Parameter File
  • What Is a Server Parameter File?
  • Migrating to a Server Parameter File
  • Creating a Server Parameter File
  • 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
• Managing Application Workloads with Database Services
  • About Database Services
  • Creating Database Services
  • Database Service Data Dictionary Views
• Considerations After Creating a Database
  • Some Security Considerations
  • Enabling Transparent Data Encryption
  • Creating a Secure External Password Store
  • Installing the Oracle Database Sample Schemas
• Cloning a Database with CloneDB
  • About Cloning a Database with CloneDB
  • Cloning a Database with CloneDB
  • After Cloning a Database with CloneDB
• Dropping a Database
• Database Data Dictionary Views

3 Starting Up and Shutting Down

• Starting Up a Database
  • About Database Startup Options
    • Starting Up a Database Using SQL*Plus
    • Starting Up a Database Using Recovery Manager
    • Starting Up a Database Using Oracle Enterprise Manager
    • Starting Up a Database Using SRVCTL
  • Specifying Initialization Parameters at Startup
    • About Initialization Parameter Files and Startup
    • Starting Up with SQL*Plus with a Nondefault Server Parameter File
    • Starting Up with SRVCTL with a Nondefault Server Parameter File
  • About Automatic Startup of Database Services
  • 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 Mode
  • Shutting Down with the Immediate Mode
  • Shutting Down with the Transactional Mode
  • Shutting Down with the Abort Mode
  • 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 Configuring Automatic Restart of an Oracle Database

• About Oracle Restart
  • Oracle Restart Overview
  • About Startup Dependencies
  • About Starting and Stopping Components with Oracle Restart
  • About Starting and Stopping Oracle Restart
  • Oracle Restart Configuration
  • Oracle Restart Integration with Oracle Data Guard
  • Fast Application Notification with Oracle Restart
    • Overview of Fast Application Notification
    • Application High Availability with Services and FAN
• Configuring Oracle Restart
  • Preparing to Run SRVCTL
  • Obtaining Help for SRVCTL
  • Adding Components to the Oracle Restart Configuration
    • Adding Components with SRVCTL
    • Adding Components with Oracle Enterprise Manager Database Control
  • Removing Components from the Oracle Restart Configuration
  • Disabling and Enabling Oracle Restart Management for a Component
  • Viewing Component Status
  • Viewing the Oracle Restart Configuration for a Component
  • Modifying the Oracle Restart Configuration for a Component
  • Managing Environment Variables in the Oracle Restart Configuration
    • Setting and Unsetting Environment Variables
    • Viewing Environment Variables
  • Creating and Deleting Database Services with SRVCTL
  • Enabling FAN Events in an Oracle Restart Environment
  • Automating the Failover of Connections Between Primary and Standby Databases
  • Enabling Clients for Fast Connection Failover
    • Enabling Fast Connection Failover for JDBC Clients
    • Enabling Fast Connection Failover for Oracle Call Interface Clients
    • Enabling Fast Connection Failover for ODP.NET Clients
• Starting and Stopping Components Managed by Oracle Restart
  • Starting and Stopping Components Managed by Oracle Restart with SRVCTL
  • Starting a Database Managed by Oracle Restart with Oracle Enterprise Manager
• Stopping and Restarting Oracle Restart for Maintenance Operations
• SRVCTL Command Reference for Oracle Restart
  • add
    • srvctl add asm
    • srvctl add database
    • srvctl add listener
    • srvctl add ons
    • srvctl add service
  • config
    • srvctl config asm
    • srvctl config database
    • srvctl config listener
    • srvctl config ons
    • srvctl config service
  • disable
    • srvctl disable asm
    • srvctl disable database
    • srvctl disable diskgroup
    • srvctl disable listener
    • srvctl disable ons
    • srvctl disable service
  • enable
    • srvctl enable asm
    • srvctl enable database
    • srvctl enable diskgroup
    • srvctl enable listener
    • srvctl enable ons
    • srvctl enable service
  • getenv
    • srvctl getenv asm
    • srvctl getenv database
    • srvctl getenv listener
  • modify
    • srvctl modify asm
    • srvctl modify database
    • srvctl modify listener
    • srvctl modify ons
    • srvctl modify service
  • remove
    • srvctl remove asm
    • srvctl remove database
    • srvctl remove diskgroup
    • srvctl remove listener
    • srvctl remove ons
    • srvctl remove service
  • setenv
    • srvctl setenv asm
    • srvctl setenv database
    • srvctl setenv listener
  • start
    • srvctl start asm
    • srvctl start database
    • srvctl start diskgroup
    • srvctl start home
    • srvctl start listener
    • srvctl start ons
    • srvctl start service
  • status
    • srvctl status asm
    • srvctl status database
    • srvctl status diskgroup
    • srvctl status home
    • srvctl status listener
    • srvctl status ons
    • srvctl status service
  • stop
    • srvctl stop asm
    • srvctl stop database
    • srvctl stop diskgroup
    • srvctl stop home
    • srvctl stop listener
    • srvctl stop ons
    • srvctl stop service
  • unsetenv
    • srvctl unsetenv asm
    • srvctl unsetenv database
    • srvctl unsetenv listener
• CRSCTL Command Reference
  • check
  • config
  • disable
  • enable
  • start
  • stop

5 Managing Processes

• About Dedicated and Shared Server Processes
  • Dedicated Server Processes
  • Shared Server Processes
• About Database Resident Connection Pooling
  • Comparing DRCP to Dedicated Server and Shared Server
  • Restrictions on Using Database Resident Connection Pooling
• Configuring Oracle Database for Shared Server
  • Initialization Parameters for Shared Server
  • Memory Management 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 Server
  • 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
  • About External Procedures
  • DBA Tasks to Enable External Procedure Calls
• Terminating Sessions
  • Identifying Which Session to Terminate
  • Terminating an Active Session
  • Terminating an Inactive Session
• Process and Session Data Dictionary Views

6 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
• Configuring Database Smart Flash Cache
  • When to Configure the Flash Cache
  • Sizing the Flash Cache
  • Tuning Memory for the Flash Cache
  • Flash Cache Initialization Parameters
  • Flash Cache in an Oracle Real Applications Clusters Environment
• Memory Management Reference
  • Platforms That Support Automatic Memory Management
  • Memory Management Data Dictionary Views

7 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 Activity
• Predefined User Accounts

8 Monitoring Database Operations

• Monitoring Errors and Alerts
  • Monitoring Errors with Trace Files and the Alert Log
    • Controlling the Size of Trace Files
    • Controlling When Oracle Database Writes to Trace Files
    • Reading the Trace File for Shared Server Sessions
  • Monitoring Database Operations with Server-Generated Alerts
    • Setting and Retrieving Thresholds for Server-Generated Alerts
    • Viewing Server-Generated Alerts
    • Server-Generated Alerts Data Dictionary Views
• Monitoring Performance
  • Monitoring Locks
  • Monitoring Wait Events
  • Performance Monitoring Data Dictionary Views

9 Managing Diagnostic Data

• About the Oracle Database Fault Diagnosability Infrastructure
  • Fault Diagnosability Infrastructure Overview
  • About Incidents and Problems
    • Incident Flood Control
    • Related Problems Across the Topology
  • Fault Diagnosability Infrastructure Components
    • Automatic Diagnostic Repository (ADR)
    • Alert Log
    • Trace Files, Dumps, and Core Files
    • Other ADR Contents
    • Enterprise Manager Support Workbench
    • ADRCI Command-Line Utility
  • Structure, Contents, and Location of the Automatic Diagnostic Repository
• Investigating, Reporting, and Resolving a Problem
  • Roadmap—Investigating, Reporting, and Resolving a Problem
  • Task 1: View Critical Error Alerts in Enterprise Manager
  • Task 2: View Problem Details
  • Task 3: (Optional) Gather Additional Diagnostic Information
  • Task 4: (Optional) Create a Service Request
  • Task 5: Package and Upload Diagnostic Data to Oracle Support
  • Task 6: Track the Service Request and Implement Any Repairs
  • Task 7: Close Incidents
• Viewing Problems with the Enterprise Manager Support Workbench
• Creating a User-Reported Problem
• Viewing the Alert Log
• Finding Trace Files
• Running Health Checks with Health Monitor
  • About Health Monitor
    • About Health Monitor Checks
    • Types of Health Checks
  • Running Health Checks Manually
    • Running Health Checks Using the DBMS_HM PL/SQL Package
    • Running Health Checks Using Enterprise Manager
  • Viewing Checker Reports
    • Viewing Reports Using Enterprise Manager
    • Viewing Reports Using DBMS_HM
    • Viewing Reports Using the ADRCI Utility
  • Health Monitor Views
  • Health Check Parameters Reference
• Repairing SQL Failures with the SQL Repair Advisor
  • About the SQL Repair Advisor
  • Running the SQL Repair Advisor
  • Viewing, Disabling, or Removing a SQL Patch
• Repairing Data Corruptions with the Data Recovery Advisor
• Creating, Editing, and Uploading Custom Incident Packages
  • About Incident Packages
    • About Correlated Diagnostic Data in Incident Packages
    • About Quick Packaging and Custom Packaging
    • About Correlated Packages
  • 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
  • Creating, Editing, and Uploading Correlated Packages
  • Deleting Correlated Packages
  • Setting Incident Packaging Preferences

10 Managing Oracle Enterprise Manager Database Control

• Database Control Overview
  • Understanding the Enterprise Manager Directories
• Configuring Database Control During and After Installation
  • Overview of Configuration Options
  • Configuring Database Control During Installation
  • Configuring Database Control with DBCA
  • Configuring Database Control Using EMCA
  • Using an Input File for EMCA Parameters
  • Specifying the Ports Used by Database Control
  • Using EMCA With Oracle RAC
• Administering Database Control
  • Starting and Stopping the Database Control Process
  • Accessing the Database Home Page
  • Creating Database Control Administrative Users
  • Granting Access to Database Control for Non-Administrative Users
  • Setting Database Control Preferences
    • About Database Control Preferences
    • Defining Blackout Periods
    • Setting Preferred Credentials
• Managing Database Control in an Oracle RAC Environment
  • Verifying that Database Control is Running in an Oracle RAC Environment
  • Backing Up the emkey.ora File
• Deleting and Creating Database Control Objects
  • Deleting Database Control Objects
    • Option 1: Deleting Database Control Configuration Files Using EMCA Scripts
    • Option 2: Deleting Database Control Configuration Files and Repository Objects Using An EMCA Script
    • Option 3: Deleting A Database Control Repository Using EMCA Scripts
    • Option 4: Deleting Database Control Configuration Files Manually
    • Option 5: Deleting the Database Control Repository Using RepManager
  • Creating Database Control Objects
    • Option 1: Creating the DB Control Configuration Files
    • Option 2: Creating the Database Control Repository Objects and Configuration Files
    • Option 3: Creating the Database Control Repository Objects and Configuration Files For a Cloned Database Home
  • Re-Creating Or Reconfiguring Database Control
    • Option 1: Re-Creating the Database Control Configuration Files Only (Leaving Repository Intact)
    • Option 2: Re-Creating the Database Control Configuration Files and Repository
• EMCA Troubleshooting Tips
  • Using EMCA After Changing the Database Listener Port
  • Upgrading 11g Release 2 Grid Control Agents
  • Using EMCA When the Database Host Name or IP Address Changes
  • Using EMCA When the TNS Configuration Is Changed
  • Using EMCA for Desktop Class Installations
  • Handling EMCA Error Messages
    • Failed To Allocate Port(s) In the Specified Range(s)
    • Listener Is Not Up or Database Service Is Not Registered With It

Part II Oracle Database Structure and Storage

11 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

12 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
  • Planning the Size of Redo Log Files
  • Planning the Block Size of Redo Log Files
  • 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

13 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 Archive Destinations
  • Setting Initialization Parameters for 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 Alternate Destinations
• About Log Transmission Modes
  • 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

14 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
  • Compressed Tablespaces
  • 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 Data Files in Read-Only Tablespaces
• Altering and Maintaining Tablespaces
  • Increasing the Size of a Tablespace
  • 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
    • Task 1: Determine if Platforms are Supported and Determine Endianness
    • Task 2: Pick a Self-Contained Set of Tablespaces
    • Task 3: Generate a Transportable Tablespace Set
    • Task 4: Transport the Tablespace Set
    • Task 5: (Optional) Restore Tablespaces to Read/Write Mode
    • Task 6: 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 Data Files and Associated Tablespaces of a Database
  • Example 3: Displaying Statistics for Free Space (Extents) of Each Tablespace

15 Managing Data Files and Temp Files

• Guidelines for Managing Data Files
  • Determine the Number of Data Files
    • Determine a Value for the DB_FILES Initialization Parameter
    • Consider Possible Limitations When Adding Data Files to a Tablespace
    • Consider the Performance Impact
  • Determine the Size of Data Files
  • Place Data Files Appropriately
  • Store Data Files Separate from Redo Log Files
• Creating Data Files and Adding Data Files to a Tablespace
• Changing Data File Size
  • Enabling and Disabling Automatic Extension for a Data File
  • Manually Resizing a Data File
• Altering Data File Availability
  • Bringing Data Files Online or Taking Offline in ARCHIVELOG Mode
  • Taking Data Files Offline in NOARCHIVELOG Mode
  • Altering the Availability of All Data Files or Temp Files in a Tablespace
• Renaming and Relocating Data Files
  • Procedures for Renaming and Relocating Data Files in a Single Tablespace
    • Procedure for Renaming Data Files in a Single Tablespace
    • Procedure for Relocating Data Files in a Single Tablespace
  • Procedure for Renaming and Relocating Data Files in Multiple Tablespaces
• Dropping Data Files
• Verifying Data Blocks in Data Files
• 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
• Data Files Data Dictionary Views

16 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

17 Using Oracle Managed Files

• What Are Oracle Managed Files?
  • Who Can Use Oracle Managed Files?
    • What Is a Logical Volume Manager?
    • What Is a File System?
  • Benefits of Using Oracle Managed Files
  • Oracle Managed Files and Existing Functionality
• Enabling the Creation and Use of Oracle Managed Files
  • Setting the DB_CREATE_FILE_DEST Initialization Parameter
  • Setting the DB_RECOVERY_FILE_DEST Parameter
  • Setting the DB_CREATE_ONLINE_LOG_DEST_n Initialization Parameters
• Creating Oracle Managed Files
  • How Oracle Managed Files Are Named
  • Creating Oracle Managed Files at Database Creation
    • Specifying Control Files at Database Creation
    • Specifying Redo Log Files at Database Creation
    • Specifying the SYSTEM and SYSAUX Tablespace Data Files at Database Creation
    • Specifying the Undo Tablespace Data File at Database Creation
    • Specifying the Default Temporary Tablespace Temp File at Database Creation
    • CREATE DATABASE Statement Using Oracle Managed Files: Examples
  • Creating Data Files for Tablespaces Using Oracle Managed Files
    • CREATE TABLESPACE: Examples
    • CREATE UNDO TABLESPACE: Example
    • ALTER TABLESPACE: Example
  • Creating Temp Files 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
• Operation of Oracle Managed Files
  • Dropping Data Files and Temp Files
  • 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 Fast Recovery Areas
  • Scenario 3: Adding Oracle Managed Files to an Existing Database

Part III Schema Objects

18 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
• Managing Editions
  • About Editions and Edition-Based Redefinition
  • DBA Tasks for Edition-Based Redefinition
  • Setting the Database Default Edition
  • Querying the Database Default Edition
  • Setting the Edition Attribute of a Database Service
    • Setting the Edition Attribute During Database Service Creation
    • Setting the Edition Attribute of an Existing Database Service
  • Using an Edition
  • Editions Data Dictionary Views
• 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

19 Managing Space for Schema Objects

• Managing Tablespace Alerts
  • Setting Alert Thresholds
  • Viewing Alerts
  • Limitations
• Managing Resumable Space Allocation
  • Resumable Space Allocation Overview
    • How Resumable Space Allocation Works
    • What Operations are Resumable?
    • What Errors are Correctable?
    • Resumable Space Allocation and Distributed Operations
    • Parallel Execution and Resumable Space Allocation
  • Enabling and Disabling Resumable Space Allocation
    • Setting the RESUMABLE_TIMEOUT Initialization Parameter
    • Using ALTER SESSION to Enable and Disable Resumable Space Allocation
  • Using a LOGON Trigger to Set Default Resumable Mode
  • Detecting Suspended Statements
    • Notifying Users: The AFTER SUSPEND System Event and Trigger
    • Using Views to Obtain Information About Suspended Statements
    • Using the DBMS_RESUMABLE Package
  • Operation-Suspended Alert
  • Resumable Space Allocation Example: Registering an AFTER SUSPEND Trigger
• Reclaiming Wasted Space
  • Understanding Reclaimable Unused Space
  • Using the Segment Advisor
    • Automatic Segment Advisor
    • Running the Segment Advisor Manually
    • Viewing Segment Advisor Results
    • Configuring the Automatic Segment Advisor
    • Viewing Automatic Segment Advisor Information
  • Shrinking Database Segments Online
  • Deallocating Unused Space
• Dropping Unused Object Storage
• Understanding Space Usage of Data Types
• 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
• Capacity Planning for Database Objects
  • Estimating the Space Use of a Table
  • Estimating the Space Use of an Index
  • Obtaining Object Growth Trends

20 Managing Tables

• About Tables
• Guidelines for Managing Tables
  • Design Tables Before Creating Them
  • Specify 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
    • Examples Related to Table Compression
    • Compression and Partitioned Tables
    • Determining If a Table Is Compressed
    • Determining Which Rows Are Compressed
    • Changing the Compression Level
    • Adding and Dropping Columns in Compressed Tables
    • Exporting and Importing Hybrid Columnar Compression Tables
    • Restoring a Hybrid Columnar Compression Table
    • Notes and Other Restrictions for Compressed Tables
    • Packing Compressed Tables
  • Consider Encrypting Columns That Contain Sensitive Data
  • Understand Deferred Segment Creation
  • Materializing Segments
  • 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
  • Methods for Loading Tables
  • Improving INSERT Performance with Direct-Path INSERT
    • About Direct-Path INSERT
    • How Direct-Path INSERT Works
    • Loading Data with Direct-Path INSERT
    • Specifying the Logging Mode for Direct-Path INSERT
    • Additional Considerations for Direct-Path INSERT
  • Using Conventional Inserts to Load Tables
  • Avoiding Bulk INSERT Failures with DML Error Logging
    • Error Logging Table Format
    • Creating an Error Logging Table
    • Error Logging Restrictions and Caveats
• Automatically Collecting Statistics on Tables
• Altering Tables
  • Reasons for Using the ALTER TABLE Statement
  • Altering Physical Attributes of a Table
  • Moving a Table to a New Segment or Tablespace
  • Manually Allocating Storage for a Table
  • Modifying an Existing Column Definition
  • Adding Table Columns
    • Adding a Column to a Compressed Table
    • Adding a Virtual Column
  • Renaming Table Columns
  • Dropping Table Columns
    • Removing Columns from Tables
    • Marking Columns Unused
    • Removing Unused Columns
    • Dropping Columns in Compressed Tables
  • Placing a Table in Read-Only Mode
• Redefining Tables Online
  • Features of Online Table Redefinition
  • Performing Online Redefinition with DBMS_REDEFINITION
    • Constructing a Column Mapping String
    • Creating Dependent Objects Automatically
    • Creating Dependent Objects Manually
  • Results of the Redefinition Process
  • Performing Intermediate Synchronization
  • Aborting Online Table Redefinition and Cleaning Up After Errors
  • Restrictions for Online Redefinition of Tables
  • Online Redefinition of a Single Partition
    • Rules for Online Redefinition of a Single Partition
  • Online Table Redefinition Examples
  • Privileges Required for the DBMS_REDEFINITION Package
• Researching and Reversing Erroneous Table Changes
• Recovering Tables Using Oracle Flashback Table
• Dropping Tables
• Using Flashback Drop and Managing the Recycle Bin
  • What Is the Recycle Bin?
  • Enabling and Disabling the Recycle Bin
  • Viewing and Querying Objects in the Recycle Bin
  • Purging Objects in the Recycle Bin
  • Restoring Tables from the Recycle Bin
• Managing Index-Organized Tables
  • What Are Index-Organized Tables?
  • Creating Index-Organized Tables
    • Example: Creating an Index-Organized Table
    • Restrictions for Index-Organized Tables
    • Creating Index-Organized Tables that Contain Object Types
    • Choosing and Monitoring a Threshold Value
    • Using the INCLUDING Clause
    • Parallelizing Index-Organized Table Creation
    • Using Key Compression
  • Maintaining Index-Organized Tables
    • Altering Index-Organized Tables
    • Moving (Rebuilding) Index-Organized Tables
  • Creating Secondary Indexes on Index-Organized Tables
    • Syntax for Creating the Secondary Index
    • 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
  • About External Tables
  • Creating External Tables
  • Altering External Tables
  • Preprocessing External Tables
  • Dropping External Tables
  • System and Object Privileges for External Tables
• Tables Data Dictionary Views

21 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
  • Indexes and Deferred Segment Creation
  • Estimate Index Size and Set Storage Parameters
  • Specify the Tablespace for Each Index
  • Consider Parallelizing Index Creation
  • Consider Creating Indexes with NOLOGGING
  • Understand When to Use Unusable or Invisible Indexes
  • 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
  • Creating a Large Index
  • Creating an Index Online
  • Creating a Function-Based Index
  • Creating a Key-Compressed Index
  • Creating an Unusable Index
  • Creating an Invisible Index
• Altering Indexes
  • Altering Storage Characteristics of an Index
  • Rebuilding an Existing Index
  • Making an Index Unusable
  • Making an Index Invisible
  • Renaming an Index
  • Monitoring Index Usage
• Monitoring Space Use of Indexes
• Dropping Indexes
• Indexes Data Dictionary Views

22 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

23 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

24 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

25 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

26 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

27 Managing Resources with Oracle Database Resource Manager

• About Oracle Database Resource Manager
  • What Solutions Does the Resource Manager Provide for Workload Management?
  • 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 Manager Administration Privileges
• Assigning Sessions to Resource Consumer Groups
  • Overview of Assigning Sessions to Resource Consumer Groups
  • Assigning an Initial Resource Consumer Group
  • Specifying Session-to–Consumer Group Mapping Rules
    • About Session-to–Consumer Group Mapping Rules
    • Creating Consumer Group Mapping Rules
    • Modifying and Deleting Consumer Group Mapping Rules
    • Creating Mapping Rule Priorities
  • Switching Resource Consumer Groups
    • Manually Switching Resource Consumer Groups
    • Enabling Users or Applications to Manually Switch Consumer Groups
  • Specifying Automatic Resource Consumer Group Switching
    • Specifying Automatic Switching with Mapping Rules
    • Specifying Automatic Switching by Setting Resource Limits
  • Granting and Revoking the Switch Privilege
    • Granting the Switch Privilege
    • Revoking Switch Privileges
• The Types of Resources Managed by the Resource Manager
  • CPU
  • Degree of Parallelism Limit
  • Parallel Target Percentage
    • Managing Parallel Statement Queuing Using Parallel Target Percentage
  • Parallel Queue Timeout
  • Active Session Pool with Queuing
  • Automatic Consumer Group Switching
  • Canceling SQL and Terminating Sessions
  • Execution Time Limit
  • Undo Pool
  • Idle Time Limit
• 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
• Enabling Oracle Database Resource Manager and Switching Plans
• Putting It All Together: Oracle Database Resource Manager Examples
  • Multilevel Plan Example
  • Examples of Using the Maximum Utilization Limit Attribute
  • Example of Using Several Resource Allocation Methods
  • Example of Managing Parallel Statements Using Directive Attributes
  • An Oracle-Supplied Mixed Workload Plan
• Managing Multiple Database Instances on a Single Server
  • About Instance Caging
  • Enabling Instance Caging
• 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
  • Predefined Consumer Group Mapping Rules
  • Resource Manager Data Dictionary Views

28 Oracle Scheduler Concepts

• Overview of Oracle Scheduler
• About Jobs and Supporting Scheduler Objects
  • Programs
  • Schedules
  • Jobs
  • Destinations
    • About Destinations and Scheduler Agents
  • File Watchers
  • Credentials
  • Chains
  • Job Classes
  • Windows
    • Overlapping Windows
  • Groups
    • Destination Groups
    • Window Groups
• More About Jobs
  • Job Categories
    • Database Jobs
    • External Jobs
    • Multiple-Destination Jobs
    • Chain Jobs
    • Detached Jobs
    • Lightweight Jobs
  • Job Instances
  • Job Arguments
  • How Programs, Jobs, and Schedules are Related
• Scheduler Architecture
  • The Job Table
  • The Job Coordinator
    • Job Coordinator Actions
    • Maximum Number of Scheduler Job Processes
  • How Jobs Execute
  • After Jobs Complete
  • Using the Scheduler in Real Application Clusters Environments
    • Service Affinity when Using the Scheduler
• Scheduler Support for Oracle Data Guard
• Oracle Scheduler and Editions

29 Scheduling Jobs with Oracle Scheduler

• About Scheduler Objects and Their Naming
• Creating, Running, and Managing Jobs
  • Job Tasks and Their Procedures
  • Creating Jobs
    • Overview of Creating Jobs
    • Specifying a Job Action and Job Schedule
    • Specifying Job Credentials and Job Destinations
    • Creating Multiple-Destination Jobs
    • Setting Job Arguments
    • Setting Additional Job Attributes
    • Creating Detached Jobs
    • Creating Multiple Jobs in a Single Transaction
    • Techniques for External Jobs
  • Altering Jobs
  • Running Jobs
  • Stopping Jobs
    • Stopping External Jobs
    • Stopping a Chain Job
  • Dropping Jobs
  • Disabling Jobs
  • Enabling Jobs
  • Copying Jobs
  • Viewing stdout and stderr for External Jobs
• Creating and Managing Programs to Define Jobs
  • Program Tasks and Their Procedures
  • Creating Programs
    • Defining Program Arguments
  • Altering Programs
  • Dropping Programs
  • Disabling Programs
  • Enabling Programs
• Creating and Managing Schedules to Define Jobs
  • 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 Events to Start Jobs
  • About Events
  • Starting Jobs with Events Raised by Your 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
  • Starting a Job When a File Arrives on a System
    • About File Watchers
    • Enabling File Arrival Events from Remote Systems
    • Creating File Watchers and File Watcher Jobs
    • File Arrival Example
    • Managing File Watchers
    • Viewing File Watcher Information
• Creating and Managing Job Chains
  • Chain Tasks and Their Procedures
  • Creating Chains
  • Defining Chain Steps
  • Adding Rules to a Chain
    • Setting an Evaluation Interval for Chain Rules
  • Enabling Chains
  • Creating Jobs for Chains
  • Dropping Chains
  • Running Chains
  • Dropping Chain Rules
  • Disabling Chains
  • Dropping Chain Steps
  • Stopping Chains
  • Stopping Individual Chain Steps
  • Pausing Chains
  • Skipping Chain Steps
  • Running Part of a Chain
  • Monitoring Running Chains
  • Handling Stalled Chains
• Prioritizing Jobs
  • Managing Job Priorities with Job Classes
    • Job Class Tasks and Their Procedures
    • Creating Job Classes
    • Altering Job Classes
    • Dropping Job Classes
  • Setting Relative Job Priorities Within a Job Class
  • Managing Job Scheduling and Job Priorities with Windows
    • Window Tasks and Their Procedures
    • Creating Windows
    • Altering Windows
    • Opening Windows
    • Closing Windows
    • Dropping Windows
    • Disabling Windows
    • Enabling Windows
  • Managing Job Scheduling and Job Priorities with Window Groups
    • Window Group Tasks and Their Procedures
    • Creating Window Groups
    • Dropping Window Groups
    • Adding a Member to a Window Group
    • Removing a Member from a Window Group
    • Enabling a Window Group
    • Disabling a Window Group
  • Allocating Resources Among Jobs Using Resource Manager
  • Example of Resource Allocation for Jobs
• Monitoring Jobs
  • Viewing the Job Log
    • Run Details
    • Precedence of Logging Levels in Jobs and Job Classes
  • Monitoring Multiple Destination Jobs
  • Monitoring Job State with Events Raised by the Scheduler
    • About Job State Events
    • Altering a Job to Raise Job State Events
    • Consuming Job State Events with your Application
  • Monitoring Job State with E-mail Notifications
    • About E-mail Notifications
    • Adding E-mail Notifications for a Job
    • Removing E-mail Notifications for a Job
    • Viewing Information About E-mail Notifications

30 Administering Oracle Scheduler

• Configuring Oracle Scheduler
  • Setting Oracle Scheduler Privileges
  • Setting Scheduler Preferences
  • Using the Oracle Scheduler Agent to Run Remote Jobs
    • Enabling and Disabling Databases for Remote Jobs
    • Installing and Configuring the Scheduler Agent on a Remote Host
    • Performing Tasks with the Scheduler Agent
• Monitoring and Managing the Scheduler
  • Viewing the Currently Active Window and Resource Plan
  • Finding Information About Currently Running Jobs
  • Monitoring and Managing Window and Job Logs
    • Job Log
    • Window Log
    • Purging Logs
  • Managing Scheduler Security
• Import/Export and the Scheduler
• Troubleshooting the Scheduler
  • A Job Does Not Run
    • About Job States
    • Viewing the Job Log
    • Troubleshooting Remote Jobs
    • About Job Recovery After a Failure
  • A Program Becomes Disabled
  • A Window Fails to Take Effect
• Examples of Using the Scheduler
  • Examples of Creating Job Classes
  • 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

31 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
    • Global Name as a Loopback Database Link
  • 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

32 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

33 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
    • Generating the Execution Plan
    • Viewing the Execution Plan
• Handling Errors in Remote Procedures

34 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

35 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 Appendixes

A Support for DBMS_JOB in Oracle Database 11g Release 2

• About DBMS_JOB
  • Configuring DBMS_JOB
  • Using Both DBMS_JOB and Oracle Scheduler
• Moving from DBMS_JOB to Oracle Scheduler
  • Creating a Job
  • Altering a Job
  • Removing a Job from the Job Queue

Index