Skip Headers

Oracle® Database Admi nistrator's Guide
10g Release 1 (10.1)
Part Number B10739-01

Home		 Go to Book List
Book List		 Go to Index
Index		 Go to Master Index
Mast er Index		 Go to Feedback page
Feedback
< hr />
Go to next page
Next
 View PDF

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Audience
Organization
Related Documentation
Convention s
Documentation Accessibility

What's New in the Oracle Database 10g Administrator's Guide?

Oracle Database 10g Release 1 (10.1) New Features

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
< dd>
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: Tu ne Database Performance
Identifying Your Oracle Database Software Release
Release Number Format
Major Database Relea se Number
Database Maintenance Release Number
Applica tion 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 Usernam es
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
< dd>Local Connections and Secure Remote Connections
Using Operating System Authentication
Preparing to Use Operating System Authentication
Connecting Using Operating System Authentication
OSDBA and OSOPER
Using Password File Authentication
Preparing to Use Password File Authentication
Connecting Using Password File A uthentication
Creating and Maintaining a Password File
Using ORAPWD
Setting REMOTE_LOGIN_ PASSWORDFILE
< dd>Adding Users to a Password File
Granting and Revoki ng SYSDBA and SYSOPER Privileges
Viewing Password File Members
Maintaining a Password File
Expanding the Number of Password File Users
Removing a Password File
Changing the P assword File State
Server Manageability
Automatic Manageability Features
Automatic Workload Repository< /a>
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 B efore 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: Cr eate the Initialization Parameter File
Step 4: Connect to the Instance
Step 5: Create a Server Parameter File (Recommended)
Step 6: St art the Instance
Step 7: Issue the CREATE DATABASE Statement
Step 8: Create Additional Tablespaces
Step 9: Run Scripts to Build Data D ictionary Views
Step 10: Run Scripts to Install Additional Options (Optional)
Step 11: Back Up the Database.
Under standing the CREATE DATABASE Statement
Protecting Your Database: Specifying Pas swords for Users SYS and SYSTEM
Creating a Locally Managed SYSTEM Tablespace
Creating the SYSAUX Tablespace
Using Automatic Undo Manag ement: Creating an Undo Tablespace
Creating a Default Permanent Tablespace
< a href="create.htm#sthref232">Creating a Default Temporary Tablespace
Specifying Oracle-M anaged 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 a nd Time Zone File
Specifying the Database Time Zone
Specifying the Database Time Zone File
Specifying FORCE LOGGIN G Mode
Using the FORCE LOGGING Clause
Performance Considerations of FORCE LOGGING Mode
Initialization Pa rameters and Database Creation
Determining the Global Database Name
DB_NAME Initialization Parameter
DB_DOMAIN Ini tialization Parameter
Specifying a Flash Recovery Area
Specifying Control Files
Specifying Database Block Sizes
DB_BLOCK_SIZE Initialization Parameter
Nonstand ard 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
Specifyin g the Method of Undo Space Management
UNDO_MANAGEMENT Initialization Parameter< /a>
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 Pa rameter File
What Is a Server Parameter File?
Migrating to a Server Parameter File
Creating a Server Parameter File
The SPFILE Initialization Parameter
Using ALTER SYSTE M to Change Initialization Parameter Values
Setting or Changing Initialization Parameter Values
Deleting Initialization Parameter Values
Exporting the Server Parameter File
Backing Up the Server Paramete r 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 Considera tions
Installing the Oracle Database Sample Schemas
Viewing Information About the Database

3 Starting Up and Shutting Down

Starti ng Up a Database
Options for Starting Up a Database
Starting Up a Database Using SQL*Plus
Starting Up a Database Usin g Recovery Manager
Starting Up a Database Using Oracle Enterprise Manager
Preparing to Start an Instance
Using SQL*Plus to Star t Up a Database
Starting an Instance: Scenarios
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< /a>
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
Quiescing a Data base
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
Initializati on Parameters for Shared Server
Enabling Shared Server
Determining a Value for SHARED_SERVERS
Decreasing the Number of Sh ared Server Processes
Limiting the Number of Shared Server Processes
Limiting the Number of Shared Server Sessions
Protecting Share d Memory
Configuring Dispatchers
DISPATCHERS Initialization Parameter Attributes
Determining the Number of Disp atchers
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 P arallel Execution Servers
Altering Parallel Execution for a Session
< dd>Disabling Parallel SQL Execution
Enabling Parallel SQ L Execution
Forcing Parallel SQL Execution
Managing Processes for External Procedures
Terminating Sessions< /dd>
Identifying Which Session to Terminate
Terminating an Active Session
Terminating an Inactive Session
< a href="manproc.htm#sthref641">Monitoring the Operation of Your Database
Serve r-Generated Alerts
Using APIs to Administer Server-Generated Alerts
< dd>Viewing Alert Data
Monitoring the Database Using Trace Files and the Alert File
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
Mon itoring Locks
Monitoring Wait Events
Proces s and Session Views

Part II Oracle Database Structure and Storage

5 Managing Control Files

What Is a Control File?
Guidelines for Control Files
Provide File names for the Control Files
Multiplex Control Files on Different Disks
Back Up Control Files
Manage the Size of Control Files
Creating Control Files
Creat ing Initial Control Files
Creating Additional Copies, Renaming, and Relocating Control F iles
Creating New Control Files
W hen to Create New Control Files
The CREATE CONTROLFILE Statement
Steps for Creating New Control Files
Troublesh ooting 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?
R edo Threads
Redo Log Contents
How Ora cle 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 File s
Responding to Redo Log Failure
Legal and Illegal Configurations
Placing Redo Log Members on Differen t Disks
Setting the Size of Redo Log Members
Choosing the Number of Redo Log Files
Controlling Archive Lag
< dl>
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 an d Renaming Redo Log Members
Dropping Redo Log Groups and Members
< dd>Dropping Log Groups
Dropping Redo Log Members
Forcing Log Switches
Verifyi ng Blocks in Redo Log Files
Clearing a Redo Log File
Viewing Redo Log Information

7 Managing Archived Redo Logs

What Is the A rchived 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 D atabase Archiving Mode
Performing Manual Archiving
Adjusting the Number of Archiver Processes
Specifying the Archive D estination
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 D estination Status
Specifying the Mode of Log Transmission
Normal Transmission Mode
Standby Transmission M ode
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 Perf ormance Views
The ARCHIVE LOG LIST Command

8 Managing Tablespaces

Guidelines for Managing Tablespaces
Using M ultiple 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 Loca lly Managed Tablespace
Bigfile Tablespaces
Creating a Bigfile Tablespace
Altering a Bigfile Tablespace
Identifying a Bigfile Tablespace
Dic tionary-Managed Tablespaces
Creating a Dictionary-Managed Tablespace
Specifying Tablespace Default Storage Parameters
Altering a Dictionary-Managed Tablespace
Coalescing Free Space in Dictionary-Managed Ta blespaces
Temporary Tablespaces
Creating a Locally Managed Temporary Tablespace
Creating a Bigfile Temporary Tablespace
Altering a Locally Managed Temporary Tablespace
Creating a Dictionary-Managed Temporary Tablespace
Altering a Dictio nary-Managed Temporary Tablespace
Multiple Temporary Tablespaces: Using Table space Groups
Creating a Tablespace Group
Changing Members of a Tablespace Group
Assigning a Tablespace Group as the De fault Temporary Tablespace
Specifying Nonstandard Block Sizes for T ablespaces
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 SYSA UX 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 Considera tions for Transportable Tablespaces
Transporting Tablespaces Between Databases: A Proce dure and Example
Step 1: Determine if Platforms are Supported and Endianness< /a>
Step 2: Pick a Self-Contained Set of Tablespaces
Step 3: Generate a Transportable Tablespace Set
Step 4: Transport the Tablespac e Set
Step 5: Plug In the Tablespace Set
Using Transportable Tablespaces: Scenarios
Transporting and Attachi ng 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 Tablesp aces to Perform TSPITR
Moving Databases Across Platforms Using Transportable Tablespaces
Viewing Tablespace Information
Example 1: Listing Tablespaces and Default Storage Parameters
Exa mple 2: Listing the Datafiles and Associated Tablespaces of a Database
Example 3: Displ aying Statistics for Free Space (Extents) of Each Tablespace

9 Managing Datafiles and Tempfiles

Guidelines for Managing Datafiles
Determine the Number of Dataf iles
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 Tab lespace
Changing Datafile Size
En abling 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 Off line in NOARCHIVELOG Mode
Altering the Availability of All Datafiles or Tempfiles in a T ablespace
Renaming and Relocating Datafiles
Procedures for Renaming and Relocating Datafiles in a Single Tablespace
Procedure for Renaming Datafiles in a Single Tablespace
Procedure for Rel ocating 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< /dd>
Advanced File Transfer Mechanisms
Map ping Files to Physical Devices
Overview of Oracle Database File Mapping Interf ace
How the Oracle Database File Mapping Interface Works
Components of File Mapping
Mapping Structures
Example of Mapping Structures
Configuration ID
< /dd>
Using the Oracle Database File Mapping Interface
Enabling File Mapping
Using the DBMS_STORAGE_MAP Package
Obtaining Information from the File Mapping Views
Fi le 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 Un do Tablespace

What Is Undo?
Introdu ction to Automatic Undo Management
Overview of Automatic Undo Management
Undo Retention
Automatic Tuning of Undo Rete ntion
Setting the UNDO_RETENTION Initialization Parameter
Retention Guarantee
Sizing the Undo Tablespace
Using Auto-Extensible Tablespaces
Sizing Fixed-Si ze Undo Tablespaces
The Undo Advisor PL/SQL Interface
Calculating the Space Requirements For Undo Retention
Managing Undo Tablespaces
Creating an Undo Tablespace
Using CREATE DATABASE to Create an Undo Tablespace
Using the C REATE UNDO TABLESPACE Statement
Altering an Undo Tablespace
Dropping an Undo Tablespace
Switching Undo Tablespaces
Establishing User Quotas for Undo Space
Monitorin g the Undo Tablespace
Flashback Features and Undo Space
Flashback Query
Flashback Versions Query
Flashback Transaction Query
Flashback Table
Migration to Automatic Undo Management
Best Practices
Part III Automated File and Storage Management

11 Using Oracle-Managed Files< /font>

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
Sett ing the DB_CREATE_FILE_DEST Initialization Parameter
Setting the DB_RECOVERY_FILE_DEST Para meter
Setting the DB_CREATE_ONLINE_LOG_DEST_n Initialization Parameter
< dd>Creating Oracle-Managed Files
How Oracle-Managed Files Are Named
Creating Oracle-Managed Files at Database Creation
< a href="omf.htm#sthref1488">Specifying Control Files at Database Creation
Specifying Redo L og Files at Database Creation
Specifying the SYSTEM and SYSAUX Tablespace Datafiles at Data base 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< /a>
CREATE UNDO TABLESPACE: Example
ALTER TABLESPACE: Example
Creating Tempfiles for Temporary Tablespaces Using Oracle-Managed Files< /a>
CREATE TEMPORARY TABLESPACE: Example
AL TER TABLESPACE ... ADD TEMPFILE: Example
Creating Control Files Using Oracle-Mana ged Files
CREATE CONTROLFILE Using NORESETLOGS Keyword: Example
CREATE CONTROLFILE Using RESETLOGS Keyword: Example
Crea ting Redo Log Files Using Oracle-Managed Files
Using the ALTER DATABASE ADD LOGFI LE Statement
Using the ALTER DATABASE OPEN RESETLOGS Statement
Creating Archived Logs Using Oracle-Managed Files
Behavio r 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 Area and Flash Recovery Area
Scenario 3: Adding Oracle-Managed Files to an Existing Database

12 Using Automatic Storage Management

What Is Automatic Storage Management?
Over view of the Components of Automatic Storage Management
Administering an ASM Instance
Installation of ASM
Authentica tion for Accessing an ASM Instance
Setting Initialization Parameters for an ASM Instan ce
Initialization Parameters for ASM Instances
Tuning Rebalance Operations
Improving Disk Discovery Time
< dd>Behavior of Database Initialization Parameters in an ASM Instance
Starting Up an ASM Instance
ASM Instance Memory R equirements
Disk Discovery
Disk Group R ecovery
Shutting Down an ASM Instance
Disk Group Fixed Tables
Configuring the Components of Automatic Sto rage Management
Considerations and Guidelines for Configuring an ASM Instanc e
Determining the Number of Disk Groups
Storage Arrays and Automatic Storage Management
Consider Performance Charac teristics when Grouping Disks
Effects of Adding and Dropping Disks from a Disk Group
Failure Groups and Mirroring
Scalability
Creating a Disk Group
Alteri ng 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 Grou ps
Managing Disk Group Templates
Adding Templates to a Disk Group
Modifying a Disk Group Template
Dropping Templates from a Disk Group
Managing Disk Group Directories
Creating a New Directory
Renaming a Directory
Dropping a Directory
Managing Alias Names for ASM Filenames
Addin g 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
Checking Internal Consi stency of Disk Group Metadata
Dropping Disk Groups
Using Automatic Storage Management in the Database
Wha t Types of Files Does Automatic Storage Management Support?
About ASM Filenames
Fully Qualified ASM Filename
Numeri c ASM Filename
Alias ASM Filenames
Alia s ASM Filename with Template
Incomplete ASM Filename
Incomplete ASM Filename with Template
Starting the ASM and Dat abase 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 Using Automatic Storage Management
Creating Tablespaces Using Automatic Storage Management
Creating Redo Logs Using Automatic Storage Management
Creating a Control File Using Automatic Storage Management
Creating Arch ive Log Files Using Automatic Storage Management
Recovery Manager (RMAN) and Automatic Storage Management
Viewing Information About Automatic Storage Management

Part IV Schema Objects

13 Managing Space for Schema Objects

Managing Space in Data Blocks
Specifying the PCTFREE Parameter
Effects of Specifying a Smaller PCT FREE
Effects of Specifying a Larger PCTFREE
PCTFREE for Nonclustered Tables
PCTFREE for Clustered Tables
PCTFREE for Indexes
Specifying the PCTUSED Parameter
Effects of Specifying a Smaller PCTUSED
Effects of Specifying a Larger PCTUSED
Selecting Associated PCTUSED and PCTFR EE Values
Specifying the INITRANS Parameter
Managing Space in Tablespaces
Managing Storage Parameters
Identifying the Storage Parameters
Setting Default S torage Parameters for Objects Created in a Tablespace
Specifying Storage Parameters at O bject 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< /dd>
Changing Values of Storage Parameters
Understan ding Precedence in Storage Parameters
Example of How Storage Parameters Effect Space All ocation
Managing Resumable Space Allocation
Resumable Space Allocation Overview
How Resumable Space A llocation Works
What Operations are Resumable?
What Errors are Correctable?
Resumable Space Allocation Limitations for Dictionary-M anaged Tablespaces
Resumable Space Allocation and Distributed Operations
Parallel Execution and Resumable Space Allocation
Enabling and Disabling Resumable Space Allocation
Setting the RESUMABLE_TIME OUT Initialization Parameter
Using ALTER SESSION to Enable and Disable Resumable Space A llocation
Using a LOGON Trigger to Set Default Resumable Mode
Detecting Suspended Statements
Notifying Users: T he AFTER SUSPEND System Event and Trigger
Using Views to Obtain Information About Suspen ded Statements
Using the DBMS_RESUMABLE Package
Operation-Suspended Alert
Resumable Space Allocation Example: Registering an AFTER SUSPEND Trigger
Reclaiming Unused Space
Segment Advisor
Shrinking Database Segments
Deallocating Unused Space
Understanding Space Usag e of Datatypes
Displaying Information About Space Usage for Schema Objects
Using PL/SQL Packages to Display Information About Schema Object Space Usage
< a href="schema.htm#sthref1805">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

14 Managing Tables

About Tables
Guidelines for Managing Tables
Design Tables Before Creating Them
Consider Your Options for the Type of Ta ble to Create
Specify How Data Block Space Is to Be Used
Specify the Location of Each Table
Consider Parallelizing Table Creation
Consider Using NOLOGGING When Creating Tables
Consider Using Table Compression when Creating Tables
Estimate Table Size and Plan Acc ordingly
Restrictions to Consider When Creating Tables
Creating Tables
Creating a Table
Creating a Temporary Table
Parallelizing Table Creation
Inserting Data Into Tables Using Direct-Path INSERT
Advantages of Using Direct-Path INSERT
Enabling Direct-Path INSERT
How Direct-Path INSERT Works
Serial Direct-Path INSERT into Partitioned or Nonpartitioned Tables
Parallel Direct-Path INSER T into Partitioned Tables
Parallel Direct-Path INSERT into Nonpartitioned Tables
Specifying the Logging Mode for Direct-Path INSERT
Direct-Path INSERT with Logging
Direct-Path INSERT without Logging< /a>
Additional Considerations for Direct-Path INSERT
Index Maintenance with Direct-Path INSERT
Space Considerations with Direct-Path INSERT
Locking Considerations with 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 Column s
Renaming Table Columns
Dropping Table Col umns
Removing Columns from Tables
Marking Columns Unused
Removing Unused Columns
Redefining Tables Online
Features of Online Table Red efinition
The DBMS_REDEFINITION Package
Ste ps for Online Redefinition of Tables
Intermediate Synchronization
Terminate and Clean Up After Errors
Example of Online Table Redef inition
Restrictions for Online Redefinition of Tables
Auditing Table Changes Using Flashback Transaction Query
Recoverin g Tables Using the Flashback Table Feature
Dropping Tables
Using Flashback Drop and Managing the Recycle Bin
What Is the Recycle Bin?
Renaming Objects in 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< /dd>
Using the Overflow Clause
Choosing and Monitori ng a Threshold Value
Using the INCLUDING Clause
Parallelizing Index-Organized Table Creation
Using Key Compression
Maintaining Index-Organized Tables
Alte ring 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
< /dd>
Using the ORDER BY Clause with Index-Organized Tables
Converting Index-Organized Tables to Regular Tables
Managing External T ables
Creating External Tables
Al tering External Tables
Dropping External Tables
System and Object Privileges for External Tables
Viewing Information Abou t Tables

15 Ma naging Indexes

About Indexes
Guidelines for Managing Indexes
Create Indexes After Inserting Table Data
Index the Correct Tables and Columns
Order I ndex Columns for Performance
Limit the Number of Indexes for Each Table
Drop Indexes That Are No Longer Required
Specify Index B lock Space Use
Estimate Index Size and Set Storage Parameters
Specify the Tablespace for Each Index
Consider Parallelizing Index Creation
Consider Creating Indexes with NOLOGGING
Consider Costs and Benefits of Coalescing or Rebuilding Indexes
Consider Cost Before Disabling or Dropping Constraints
Creating Indexes
Creating an Index Explicitly
Creating a Unique In dex 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 Inc idental Statistics when Creating an Index
Creating a Large Index
Creating an Index Online
Creating a Function-Based Index
Creating a Key-Compressed Index
Alter ing Indexes
Altering Storage Characteristics of an Index
Rebuilding an Existing Index
Monitoring Index Usage
< /dl>
Monitoring Space Use of Indexes
Dropping Indexes
Viewing Index Information

16 Managing Partitioned Tables and Indexes

About Partitioned Tables and Indexes
Partitioning Me thods
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 Comp osite Range-List Partitioning
Creating Partitioned Tables
Creating Range-Partitioned Tables and Global Indexes
Creating a Range Partitioned Table
Creating a Range-Partitioned Global Ind ex
Creating Hash-Partitioned Tables and Global Indexes
Creating a Hash Partitioned Table
Creating a Hash-Par titioned Global Index
Creating List-Partitioned Tables
Creating Composite Range-Hash Partitioned Tables
Creating Compo site Range-List Partitioned Tables
Using Subpartition Templates to Describe Composite P artitioned Tables
Specifying a Subpartition Template for a Range-Hash Partiti oned Table
Specifying a Subpartition Template for a Range-List Partitioned Table
Using Multicolumn Partitioning Keys
U sing Table Compression with Partitioned Tables
Using Key Compression with Partitioned I ndexes
Creating Partitioned Index-Organized Tables
Creating Range-Partitioned Index-Organized Tables
Creating List-Par titioned Index-Organized Tables
Creating Hash-Partitioned Index-Organized Tables
Partitioning Restrictions for Multiple Block Sizes
Maintaining Partitioned Tables
Updating Indexes Autom atically
Adding Partitions
Addi ng a Partition to a Range-Partitioned Table
Adding a Partition to a Hash-Partitioned Ta ble
Adding a Partition to a List-Partitioned Table
Adding Partitions to a Range-Hash Composite-Partitioned Table
Adding Partitio ns 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 a Table Partition
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 wit h a Range-List Partition
Exchanging a Subpartition of a Range-List Partitioned Table
Merging Partitions
Merg ing Range Partitions
Merging List Partitions
Merging Range-Hash Partitions
Merging Range-List Partitions
Modifying Default Attributes
Modifying Defau lt 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 Att ributes of Index Partitions
Modifying List Partitions: Adding Values
Adding Values for a List Partition
Addi ng Values for a List Subpartition
Modifying List Partitions: Dropping Values< /a>
Dropping Values from a List Partition
Dropping Values from a List Subpartition
Modifying a Subpartition Temp late
Moving Partitions
Moving T able Partitions
Moving Subpartitions
Movi ng Index Partitions
Rebuilding Index Partitions
Rebuilding Global Index Partitions
Rebuilding Local Index Pa rtitions
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
Partitioned Tables and Indexes Example
Viewing Information About Partitioned Tables and Indexes

17 Managing Clusters

About Clusters
Guidelines for Managing Clusters
Choose Appropriate Tables for the Cluster
Choose Appropriate Columns for the Cluster Key
Specify Data Block Space Use
Specify the Space Required by an Average Cluster Key and Its Associated Rows
Sp ecify 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 Tab les
Altering Cluster Indexes
Dr opping Clusters
Dropping Clustered Tables
Dropping Cluster Indexes
Viewing Information About Clusters

18 Managing Hash Cluste rs

About Hash Clusters
When to Use Hash Clusters
Situations Where Hashing Is Useful
Situations Where Hashing Is Not Advantageous
Creating Hash Clusters< /a>
Creating a Sorted Hash Cluster
Creati ng 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 Cluste rs
Altering Hash Clusters
Dropping Ha sh Clusters
Viewing Information About Hash Clusters

19 Managing Views, Sequences, and Synonyms< /h3>
Managing Views
About Views
Creating Views
Join Views
Expansion of Defining Queries at View Creation Time
Creating Views with Errors
Replacing Views
U sing 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< /a>
Altering Views
Dropping Views
Managing Sequences
About Seque nces
Creating Sequences
Altering Sequences
Using Sequences
Referencing a Sequenc e
Caching Sequence Numbers
Dropping Sequences
Managing Synonyms
About Synonyms
Creating Synonyms
Using Sy nonyms in DML Statements
Dropping Synonyms
Viewing Information About Views, Synonyms, and Sequences

20 General Management of Schema Objects

Creating Multiple Tables and Views in a Single Operation
Analyzing Tabl es, Indexes, and Clusters
Using DBMS_STATS to Collect Table and Index Statist ics
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
Usin g TRUNCATE
Enabling and Disabling Triggers
Enabling Triggers
Disabling Triggers
Managing Integrity Constraints
Integrity Constrai nt States
Disabling Constraints
Enabling Constraints
Enable Novalidate Constraint State
Efficient Use of Integrity Constraints: A Procedure
Setting I ntegrity 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
Manually Recompiling Vie ws
Manually Recompiling Procedures and Functions
Manually Recompiling Packages
Managing Object Name Resolution
Switching to a Different Schema
Displaying Infor mation About Schema Objects
Using a PL/SQL Package to Display Information Abo ut 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

21 Detecting and Repairing Data Block Corruption

Options for Repairing Data Block Corruption
About th e 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: Perfo rming an Offline Database Check
ANALYZE: Corruption Reporting
DB_BLOCK_CHECKING (Block Checking Initialization Parameter)
Task 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR
Task 3: Make Objects Usabl e
Corruption Repair: Using the FIX_CORRUPT_BLOCKS and SKIP_CORRUPT_BLOCKS Proc edures
Implications when Skipping Corrupt Blocks
Task 4: Repair Corruptions and Rebuild Lost Data
Recover Data Using the DUMP_ORPHAN_KEYS Procedures
Repair Free Lists Using the REBUILD_FREELISTS Proc edure
Fix Segment Bitmaps Using the SEGMENT_FIX_STATUS Procedure
DBMS_REPAIR Examples
Using ADMIN_TAB LES to Build a Repair Table or Orphan Key Table
Creating a Repair Table
Creating an Orphan Key Table
Using the CHECK_OBJECT Procedure to Detect Corruption
Fixing Corrupt Blocks with the FIX_CORRUPT_B LOCKS Procedure
Finding Index Entries Pointing into Corrupt Data Blocks: DUMP_ORPHAN_KEY S
Rebuilding Free Lists Using the REBUILD_FREELISTS Procedure
Enabling or Disabling the Skipping of Corrupt Blocks: SKIP_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
Automatic Statistics C ollection Job
Resource Management

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 Thes e Problems?
What Are the Elements of the Database Resource Manager?
Understanding Resource Plans
A Single-Level Resource Plan
A Multilevel Resource Plan
Resource Consumer Gro ups
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
< dd>Creating a Plan
Updating a Plan
Deleting a Plan
Using the Ratio Policy
Creating Resource Consumer Groups
Creating a Consumer Group< /a>
Updating a Consumer Group
Deleting a Consumer G roup
Specifying Resource Plan Directives
Creating a Resource Plan Directive
Updating Resource Plan Directives
Deleting Resource Plan Directives
How Resource Plan Di rectives Interact
Managing Resource Consumer Groups
Assigning an Initial Resource Consumer Group
Changing R esource Consumer Groups
Switching a Session
Switching Sessions for a User
Using the DBMS_SESSION Package to Switch Consumer Gro up
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
Explicit Session Switching
Automatic Group Switching
Enabling the Database Resource Manager
Putting It All Together: Database Resource Manager Examples
Multilevel Sch ema Example
Example of Using Several Resource Allocation Methods
An Oracle-Supplied Plan
Monitoring and Tuning the Database Res ource 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 Inform ation
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 Queu e

26 Overview of Scheduler Concepts

Overview of the Scheduler
What Can the Scheduler Do?
Bas ic Scheduler Concepts
General Rules for all Database Objects
< a href="schedover.htm#sthref3234">Programs
Schedules
Jobs
Job Instances
How Programs, Jobs, and Schedules are Related
Advanced Sc heduler Concepts
Job Classes
Windows
Overlapping Windows
Window Groups
Example of Window Group
< /dd>
Scheduler Architecture
The Job Table
The Job Coordinator
How Jobs Ex ecute
Job Slaves
Using the Scheduler in RAC Environments
Service Affinity when Using the Scheduler

27 Usi ng the Scheduler

Scheduler Objects and Their Naming
Administering Jobs
Job Tasks and Their Procedures
Creating Jobs
Job Attributes
Setting Job Arguments
Ways of Creating J obs
Copying Jobs
Pr ivileges Required for Copying a Job
Altering Jobs
Running Jobs
Running Jobs Asynchronously
< a href="scheduse.htm#sthref3280">Running Jobs Synchronously
Job Run Environment
Running External Jobs
Stopping Jobs
Dropping Jobs
Disabling Jobs
Enabling Jobs
Administering Programs
Program Tasks and Their Procedures
Creating Programs
Defining Program Arguments
Altering Programs
Dropping Programs
Disabling Programs
Enabling Programs
Administering Schedules
Schedule Tasks a nd Their Procedures
Creating Schedules
Altering Schedules
Dropping Schedules
S etting the Repeat Interval
Using the Scheduler Calendaring Syntax
< dd>Using a PL/SQL Expression
Differences Between PL/ SQL Expression and Calendaring Syntax Behavior
Repeat Intervals and Daylight Savings
Administering Job Classes
Job Class Tasks and Their Procedures
Creating Job Classes
Altering Job Classes
Dropping Job Classes
< /dl>
Administering Windows
Window Tasks and Their Procedures
Creating Windows
Creating Windows Using a Saved Schedule
Altering Windows
Opening Windows
Closing Windows
< dd>Dropping Windows
Disabling Windows
< a href="scheduse.htm#sthref3324">Enabling Windows
Overlapping Windows
Changing Resource Plans
Examples of Overlappi ng Windows
Window Logging
Administering Window Groups
Window Group Tasks and Their Procedures
Creating Window Groups
Dropping Window G roups
Adding a Member to a Window Group
Dropping a Member from a Window Group
Enabling a Window Group
Disabling a Window Group
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 S cheduler
How to View Scheduler Information
How to View the Currently Active Window and Resource Plan
How to V iew Scheduler Privileges
How to Find Information About Currently Running Jobs
How the Job Coordinator Works
J ob Coordinator and Real Application Clusters
Using DBMS_SCHEDULER and DBMS_JOB at th e Same Time
Scheduler Attribute max_job_slave_processes
How to Monitor and Manage Window and Job Logs
Job Logs
Job Run Details
Control ling Job Logging
Window Logs
Purgin g 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 Not Run?
Failed Jobs
Broken Jobs
Disabled Jobs
Completed Jobs
How to Change Job Priorities
How the Scheduler Guarantees Availability
How to Handle Sc heduler 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
Examp le of Creating Window Groups
Examples of Setting Attributes

Part VII Distributed Database Management

29 Distributed Database Co ncepts
Distributed Database Architecture
Homogenous Distributed Database Systems
Dis tributed Databases Versus Distributed Processing
Distributed Databases Versus Repli cated Databases
Heterogeneous Distributed Database Systems
Heterogeneous Services
Transparent Ga teway 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< /dd>
Types of Database Links
Users of Data base 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 Datab ase Links
Authorization for Accessing Remote Schema Objects
Synonyms for Schema Objects
Schema Object Name Resolutio n
Database Link Restrictions
Distributed Database Administration
Site Autonomy
Distributed Database Security
Authentication Through Database Links
Authentication Without Passwords
Supporting User Accounts and Roles
Centralize d User and Privilege Management
Data Encryption
Auditing Database Links
Administration Tools
Enterprise Manager
Third-Party Admin istration 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 Re solution When the Global Database Name Is Partial
Name Resolution When No Global Da tabase 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 Vie ws, 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 Transparenc y
Remote Procedure Calls (RPCs)
Distributed Query Optimization
Character Set Support for Distrib uted Environments
Client/Server Environment
Homogeneous Distributed Environment
Heterogeneous Distributed Environment

30 Managing a Distributed Database

Managing Global Names in a Dis tributed System
Understanding How Global Database Names Are Formed
Determining Whether Global Naming Is Enforced
Vi ewing 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 Databa se Links
Specifying Link Types
Creating Private Database Links
Creating Public Database Links
Creating Global Database Links
Specifying Link Use rs
Creating Fixed User Database Links
Creating Connected User and Current User Database Links
Using Conne ction Qualifiers to Specify Service Names Within Link Names
Using Shared Dat abase Links
Determining Whether to Use Shared Database Links
Creating Shared Database Links
Configuring Shared Data base Links
Creating Shared Links to Dedicated Servers
Creating Shared Links to Shared Servers
M anaging Database Links
Closing Database Links
Dropping Database Links
Procedure for Dropping a Private Da tabase Link
Procedure for Dropping a Public Database Link
Limiting the Number of Active Database Link Connections
Viewing Information About Database Links
Determining Which Links Ar e in the Database
Authorization for Viewing Password Information
Viewing Password Information
Viewing Authenticatio n Passwords
Determining Which Link Connections Are Open
< dd>Creating Location Transparency
Using Vi ews to Create Location Transparency
Using Synonyms to Create Location Transparency
Creating Synonyms
Managing Privi leges 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 Re ference Remote Procedures
Managing Procedures and Privileges
Managing Statement Transparency
Managing a D istributed Database: Examples
Example 1: Creating a Public Fixed User Databa se 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 Conn ections Established by Database Links
Maintaining Referential Integrity in a Distribu ted System
Tuning Distributed Queries
Using Collocated Inline Views
Using Cost-Based Optimization
How Does Cost-Based Optimization Work?
Se tting 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 Dis tributed Transactions?
DML and DDL Transactions
Transaction Control Statements
Session Trees for Distributed Transactions
Clients
Database Servers
Local Coordinators
Global Coordin ator
Commit Point Site
How a Di stributed 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 Consisten cy
Forget Phase
In-Do ubt Transactions
Automatic Resolution of In-Doubt Transactions
Failure During the Prepare Phase
Failure Duri ng the Commit Phase
Manual Resolution of In-Doubt Transactions
< a href="ds_txns.htm#sthref4033">Relevance of System Change Numbers for In-Doubt Transactions
Distributed Transaction Processing: Case Study
Stage 1: Clien t Application Issues DML Statements
Stage 2: Oracle Database Determines Commit Point Si te
Stage 3: Global Coordinator Sends Prepare Response
Stage 4: Commit Point Site Commits
Stage 5: Commit Point Site Informs Glob al Coordinator of Commit
Stage 6: Global and Local Coordinators Tell All Nodes to Commi t
Stage 7: Global Coordinator and Commit Point Site Complete the Commit

33 Managing Distr ibuted Transactions

Specifying the Commit Point Strength of a Node
Naming Transactions
Viewing Information Abo ut Distributed Transactions
Determining the ID Number and Status of Prepare d Transactions
Tracing the Session Tree of In-Doubt Transactions
< dd>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 C omments
Look for Transaction Advice
Manually Overriding In-Doubt Transactions
Manually Commi tting an In-Doubt Transaction
Committing Using Only the Transaction ID< /dd>
Committing Using an SCN
Manuall y Rolling Back an In-Doubt Transaction
Purging Pending Rows from the Data D ictionary
Executing the PURGE_LOST_DB_ENTRY Procedure
Determining When to Use DBMS_TRANSACTION
Manuall y Committing an In-Doubt Transaction: Example
Step 1: Record User Feedback< /a>
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 Coordi nators 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 Distribu ted Transaction Failure
Forcing a Distributed Transaction to Fail
Disabling and Enabling RECO
Managing Read Consistency

Index

Go to next page
Next
Oracle
Copyright © 2001, 2003 Oracle Corporation
All Rights Reserved.
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Feedback