Oracle 9i. Database Administrator's Guide

Contents......Page 3 Send Us Your Comments......Page 29 Preface......Page 31 Organization......Page 32 Related Documentation......Page 36 Conventions......Page 38 Documentation Accessibility......Page 40 What’s New in Oracle9i?......Page 43 Oracle9i Release 1 (9.0.1) New Features......Page 44 Part I Basic Database Administration......Page 55 1 The Oracle Database Administrator......Page 57 Database Administrators......Page 58 Application Developers......Page 59 Tasks of a Database Administrator......Page 60 Task 3: Plan the Database......Page 61 Task 4: Create and Open the Database......Page 62 Task 8: Back Up the Fully Functional Database......Page 63 Release Number Format......Page 64 Database Administrator Security and Privileges......Page 65 Database Administrator Usernames......Page 66 Administrative Privileges......Page 68 Selecting an Authentication Method......Page 70 Using Operating System (OS) Authentication......Page 72 Using Password File Authentication......Page 73 Password File Administration......Page 74 Using ORAPWD......Page 75 Setting REMOTE_LOGIN_ PASSWORDFILE......Page 76 Adding Users to a Password File......Page 77 Maintaining a Password File......Page 79 Export and Import......Page 80 2 Creating an Oracle Database......Page 81 Planning for Database Creation......Page 82 Deciding How to Create an Oracle Database......Page 84 The Oracle Database Configuration Assistant......Page 85 Creating a Database......Page 86 Managing Templates......Page 90 Manually Creating an Oracle Database......Page 91 Step 2: Establish the Database Administrator Authentication Method......Page 92 Step 3: Create the Initialization Parameter File.......Page 93 Step 5: Start the Instance.......Page 95 Step 6: Issue the CREATE DATABASE Statement......Page 96 Step 7: Create Additional Tablespaces......Page 98 Step 10: Create a Server Parameter File (Recommended)......Page 99 Creating an Undo Tablespace......Page 100 Creating a Default Temporary Tablespace......Page 101 Using Oracle-Managed Files......Page 102 Setting and Managing the Time Zone......Page 103 Considerations After Creating a Database......Page 104 Some Security Considerations......Page 105 Installing Oracle’s Sample Schemas......Page 106 Determining the Global Database Name......Page 108 Specifying Control Files......Page 109 Specifying Database Block Sizes......Page 110 Setting Initialization Parameters that Affect the Size of the SGA......Page 111 Specifying the Method of Undo Space Management......Page 114 Setting License Parameters......Page 115 Managing Initialization Parameters Using a Server Parameter File......Page 116 What is a Server Parameter File?......Page 117 Creating a Server Parameter File......Page 118 The SPFILE Initialization Parameter......Page 119 Using ALTER SYSTEM to Change Initialization Parameter Values......Page 120 Exporting the Server Parameter File......Page 122 Viewing Parameters Settings......Page 123 3 Using Oracle-Managed Files......Page 125 Who Can Use Oracle-Managed Files?......Page 126 Benefits of Using Oracle-Managed Files......Page 127 Enabling the Creation and Use of Oracle-Managed Files......Page 128 Setting the DB_CREATE_FILE_DEST Initialization Parameter......Page 129 Creating Oracle-Managed Files......Page 130 How Oracle-Managed Files are Named......Page 131 Creating Oracle-Managed Files at Database Creation......Page 132 Creating Datafiles for Tablespaces......Page 137 Creating Tempfiles for Temporary Tablespaces......Page 140 Creating Control Files......Page 141 Creating Online Redo Log Files......Page 143 Behavior of Oracle-Managed Files......Page 144 Renaming Files......Page 145 Scenario 1: Create and Manage a Database with Multiplexed Online Redo Logs......Page 146 Scenario 2: Add Oracle-Managed Files to an Existing Database......Page 151 4 Starting Up and Shutting Down......Page 153 Options for Starting Up a Database......Page 154 Using SQL*Plus to Start Up a Database......Page 155 Starting an Instance: Scenarios......Page 157 Opening a Closed Database......Page 161 Restricting Access to an Open Database......Page 162 Shutting Down with the NORMAL Option......Page 163 Shutting Down with the TRANSACTIONAL Option......Page 164 Quiescing a Database......Page 165 Placing a Database into a Quiesced State......Page 166 Suspending and Resuming a Database......Page 168 Part II Oracle Server Processes and Storage Structure......Page 171 5 Managing Oracle Processes......Page 173 Dedicated Server Processes......Page 174 Shared Server Processes......Page 175 Initialization Parameters for Shared Server......Page 177 Setting the Initial Number of Dispatchers (DISPATCHERS)......Page 178 Modifying Dispatcher and Server Processes......Page 180 About Oracle Background Processes......Page 183 Process and Session Views......Page 186 Trace Files and the Alert File......Page 187 Managing the Parallel Execution Servers......Page 190 Altering Parallel Execution for a Session......Page 191 Managing Processes for External Procedures......Page 192 Setting up an Environment for Calling External Procedures......Page 193 Terminating Sessions......Page 194 Identifying Which Session to Terminate......Page 195 Terminating an Inactive Session......Page 196 6 Managing Control Files......Page 199 Provide Filenames for the Control Files......Page 200 Back Up Control Files......Page 201 Creating Initial Control Files......Page 202 Creating New Control Files......Page 203 Checking for Missing or Extra Files......Page 207 Recovering from Control File Corruption Using a Control File Copy......Page 208 Dropping Control Files......Page 209 Displaying Control File Information......Page 210 7 Managing the Online Redo Log......Page 211 Online Redo Log Contents......Page 212 How Oracle Writes to the Online Redo Log......Page 213 Multiplexing Online Redo Log Files......Page 215 Setting the Size of Online Redo Log Members......Page 219 Controlling Archive Lag......Page 220 Creating Online Redo Log Groups and Members......Page 222 Creating Online Redo Log Members......Page 223 Relocating and Renaming Online Redo Log Members......Page 224 Dropping Log Groups......Page 226 Dropping Online Redo Log Members......Page 227 Verifying Blocks in Redo Log Files......Page 228 Clearing an Online Redo Log File......Page 229 Viewing Online Redo Log Information......Page 230 8 Managing Archived Redo Logs......Page 233 Running a Database in NOARCHIVELOG Mode......Page 234 Running a Database in ARCHIVELOG Mode......Page 235 Controlling the Archiving Mode......Page 236 Changing the Database Archiving Mode......Page 237 Enabling Automatic Archiving......Page 238 Disabling Automatic Archiving......Page 240 Specifying Archive Destinations......Page 241 Understanding Archive Destination Status......Page 244 Specifying the Mode of Log Transmission......Page 245 Standby Transmission Mode......Page 246 Managing Archive Destination Failure......Page 247 Specifying the Minimum Number of Successful Destinations......Page 248 Re-Archiving to a Failed Destination......Page 250 Tuning Archive Performance by Specifying Multiple ARCn Processes......Page 251 Controlling Trace Output Generated by the Archivelog Process......Page 253 Viewing Information About the Archived Redo Log......Page 254 Fixed Views......Page 255 The ARCHIVE LOG LIST Command......Page 256 9 Using LogMiner to Analyze Redo Log Files......Page 257 Things to Know Before You Begin......Page 258 Redo Log Files......Page 259 Dictionary Options......Page 260 Tracking of DDL Statements......Page 261 Extracting Data Values from Redo Log Files......Page 262 LogMiner Views......Page 263 Using LogMiner......Page 264 Extracting a Dictionary......Page 265 Specifying Redo Log Files for Analysis......Page 268 Starting LogMiner......Page 269 Analyzing Output from V$LOGMNR_CONTENTS......Page 273 Ending a LogMiner Session......Page 274 Example: Tracking Changes Made By a Specific User......Page 275 Example: Calculating Table Access Statistics......Page 277 10 Managing Job Queues......Page 279 Enabling Processes Used for Executing Jobs......Page 280 The DBMS_JOB Package......Page 281 Submitting a Job to the Job Queue......Page 282 How Jobs Execute......Page 287 Altering a Job......Page 288 Broken Jobs......Page 290 Terminating a Job......Page 291 Displaying Information About Running Jobs......Page 292 11 Managing Tablespaces......Page 295 Use Multiple Tablespaces......Page 296 Assign Tablespace Quotas to Users......Page 297 Creating Tablespaces......Page 298 Locally Managed Tablespaces......Page 299 Dictionary-Managed Tablespaces......Page 303 Temporary Tablespaces......Page 305 Storage Parameters in Locally Managed Tablespaces......Page 308 Storage Parameters for Dictionary-Managed Tablespaces......Page 309 Coalescing Free Space in Dictionary-Managed Tablespaces......Page 310 Taking Tablespaces Offline......Page 313 Altering the Availability of Datafiles or Tempfiles......Page 315 Using Read-Only Tablespaces......Page 316 Making a Tablespace Read-Only......Page 317 Creating a Read-Only Tablespace on a WORM Device......Page 319 Delaying the Opening of Datafiles in Read Only Tablespaces......Page 320 Dropping Tablespaces......Page 321 Troubleshooting Tablespace Problems with DBMS_SPACE_ADMIN......Page 322 Scenario 1: Fixing Bitmap When Allocated Blocks are Marked Free (No Overlap)......Page 323 Scenario 4: Correcting Media Corruption of Bitmap Blocks......Page 324 Transporting Tablespaces Between Databases......Page 325 Introduction to Transportable Tablespaces......Page 326 Compatibility Considerations for Transportable Tablespaces......Page 327 Transporting Tablespaces Between Databases: A Procedure......Page 328 Object Behaviors......Page 334 Using Transportable Tablespaces......Page 336 Viewing Tablespace Information......Page 340 Listing Tablespaces and Default Storage Parameters: Example......Page 341 Displaying Statistics for Free Space (Extents) of Each Tablespace: Example......Page 342 12 Managing Datafiles......Page 345 Determine the Number of Datafiles......Page 346 Store Datafiles Separate from Redo Log Files......Page 348 Creating Datafiles and Adding Datafiles to a Tablespace......Page 349 Enabling and Disabling Automatic Extension for a Datafile......Page 350 Manually Resizing a Datafile......Page 351 Altering Datafile Availability......Page 352 Altering the Availability of All Datafiles or Tempfiles in a Tablespace......Page 353 Renaming and Relocating Datafiles......Page 354 Renaming and Relocating Datafiles for a Single Tablespace......Page 355 Renaming and Relocating Datafiles for Multiple Tablespaces......Page 357 Viewing Datafile Information......Page 358 13 Managing Undo Space......Page 361 What is Undo?......Page 362 Starting an Instance in Automatic Undo Management Mode......Page 363 Starting an Instance in Manual Undo Management Mode......Page 364 Managing Undo Tablespaces......Page 365 Creating an Undo Tablespace......Page 366 Dropping an Undo Tablespace......Page 367 Switching Undo Tablespaces......Page 368 Setting the Retention Period for Undo Information......Page 369 Viewing Information About Undo Space......Page 371 Guidelines for Managing Rollback Segments......Page 373 Creating Rollback Segments......Page 378 Altering Rollback Segments......Page 381 Explicitly Assigning a Transaction to a Rollback Segment......Page 384 Viewing Rollback Segment Information......Page 385 Part III Schema Objects......Page 391 14 Managing Space for Schema Objects......Page 393 Specifying the PCTFREE Parameter......Page 394 Specifying the PCTUSED Parameter......Page 397 Selecting Associated PCTUSED and PCTFREE Values......Page 399 Specifying the Transaction Entry Parameters: INITRANS and MAXTRANS......Page 400 Identifying the Storage Parameters......Page 401 Setting Storage Parameters for Index Segments......Page 405 Understanding Precedence in Storage Parameters......Page 406 Example of How Storage Parameters Effect Space Allocation......Page 407 Managing Resumable Space Allocation......Page 408 Resumable Space Allocation Overview......Page 409 Enabling and Disabling Resumable Space Allocation......Page 413 Detecting Suspended Statements......Page 414 Resumable Space Allocation Example: Registering an AFTER SUSPEND Trigger......Page 416 Deallocating Space......Page 418 Issuing Space Deallocation Statements......Page 419 Examples of Deallocating Space......Page 420 Understanding Space Use of Datatypes......Page 423 15 Managing Tables......Page 425 Specify How Data Block Space Is to Be Used......Page 426 Consider Parallelizing Table Creation......Page 427 Estimate Table Size and Set Storage Parameters......Page 428 Plan for Large Tables......Page 429 Creating a Table......Page 430 Creating a Temporary Table......Page 431 Parallelizing Table Creation......Page 432 Altering Tables......Page 433 Dropping Columns......Page 436 Redefining Tables Online......Page 438 Steps for Online Redefinition of Tables......Page 439 Example of Online Table Redefinition......Page 441 Restrictions......Page 442 Dropping Tables......Page 443 Managing Index-Organized Tables......Page 444 What are Index-Organized Tables......Page 445 Creating Index-Organized Tables......Page 446 Maintaining Index-Organized Tables......Page 450 Analyzing Index-Organized Tables......Page 452 Converting Index-Organized Tables to Regular Tables......Page 453 Managing External Tables......Page 454 Creating External Tables......Page 455 Altering External Tables......Page 458 Viewing Information About Tables......Page 459 16 Managing Indexes......Page 463 Guidelines for Managing Indexes......Page 464 Index the Correct Tables and Columns......Page 465 Specify Index Block Space Use......Page 467 Specify the Tablespace for Each Index......Page 468 Consider Creating Indexes with NOLOGGING......Page 469 Consider Costs and Benefits of Coalescing or Rebuilding Indexes......Page 470 Creating Indexes......Page 471 Creating an Index Explicitly......Page 472 Creating an Index Associated with a Constraint......Page 473 Creating an Index Online......Page 475 Creating a Function-Based Index......Page 476 Creating a Key-Compressed Index......Page 480 Altering Indexes......Page 481 Rebuilding an Existing Index......Page 482 Monitoring Space Use of Indexes......Page 483 Dropping Indexes......Page 484 Viewing Index Information......Page 485 17 Managing Partitioned Tables and Indexes......Page 487 What Are Partitioned Tables and Indexes?......Page 488 Partitioning Methods......Page 489 When to Use the Range Partitioning Method......Page 490 When to Use the List Partitioning Method......Page 491 When to Use the Composite Partitioning Method......Page 493 Creating Partitioned Tables......Page 494 Creating Range-Partitioned Tables......Page 495 Creating Hash-Partitioned Tables......Page 496 Creating List-Partitioned Tables......Page 497 Creating Composite Partitioned Tables......Page 498 Creating Partitioned Index-Organized Tables......Page 499 Partitioning Restrictions for Multiple Block Sizes......Page 501 Maintaining Partitioned Tables......Page 502 Updating Global Indexes Automatically......Page 505 Adding Partitions......Page 506 Coalescing Partitions......Page 509 Dropping Partitions......Page 510 Exchanging Partitions......Page 513 Merging Partitions......Page 515 Modifying Default Attributes......Page 518 Modifying Real Attributes of Partitions......Page 519 Modifying List Partitions: Adding or Dropping Values......Page 520 Moving Partitions......Page 522 Rebuilding Index Partitions......Page 523 Splitting Partitions......Page 525 Truncating Partitions......Page 528 Moving the Time Window in a Historical Table......Page 531 Converting a Partition View into a Partitioned Table......Page 532 Viewing Information About Partitioned Tables and Indexes......Page 533 18 Managing Clusters......Page 537 Guidelines for Managing Clusters......Page 538 Choose Appropriate Columns for the Cluster Key......Page 540 Specify the Space Required by an Average Cluster Key and Its Associated Rows......Page 541 Creating Clusters......Page 542 Creating Clustered Tables......Page 543 Altering Clusters......Page 544 Altering Clustered Tables......Page 545 Dropping Clusters......Page 546 Viewing Information About Clusters......Page 547 19 Managing Hash Clusters......Page 549 When to Use Hash Clusters......Page 550 Situations Where Hashing Is Not Advantageous......Page 551 Creating Hash Clusters......Page 552 Controlling Space Use Within a Hash Cluster......Page 553 Estimating Size Required by Hash Clusters......Page 556 Viewing Information About Hash Clusters......Page 557 20 Managing Views, Sequences, and Synonyms......Page 559 Creating Views......Page 560 Updating a Join View......Page 563 Replacing Views......Page 568 Managing Sequences......Page 569 Creating Sequences......Page 570 Managing Synonyms......Page 571 Dropping Synonyms......Page 572 Viewing Information About Views, Synonyms, and Sequences......Page 573 21 General Management of Schema Objects......Page 575 Creating Multiple Tables and Views in a Single Operation......Page 576 Analyzing Tables, Indexes, and Clusters......Page 577 Using Statistics for Tables, Indexes, and Clusters......Page 578 Validating Tables, Indexes, Clusters, and Materialized Views......Page 583 Listing Chained Rows of Tables and Clusters......Page 584 Using DELETE......Page 586 Using TRUNCATE......Page 587 Enabling and Disabling Triggers......Page 588 Disabling Triggers......Page 590 Integrity Constraint States......Page 591 Setting Integrity Constraints Upon Definition......Page 594 Modifying or Dropping Existing Integrity Constraints......Page 595 Deferring Constraint Checks......Page 596 Reporting Constraint Exceptions......Page 597 Managing Object Dependencies......Page 599 Manually Recompiling Packages......Page 601 Changing Storage Parameters for the Data Dictionary......Page 602 Structures in the Data Dictionary......Page 603 Using PL/SQL Packages to Display Information About Schema Objects......Page 605 Using Views to Display Information About Schema Objects......Page 607 22 Detecting and Repairing Data Block Corruption......Page 613 DBMS_REPAIR Procedures......Page 614 Using the DBMS_REPAIR Package......Page 615 Task 1: Detect and Report Corruptions......Page 616 Task 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR......Page 617 Task 4: Repair Corruptions and Rebuild Lost Data......Page 619 DBMS_REPAIR Examples......Page 620 Using ADMIN_TABLES to Build a Repair Table or Orphan Key Table......Page 621 Using the CHECK_OBJECT Procedure to Detect Corruption......Page 622 Fixing Corrupt Blocks with the FIX_CORRUPT_BLOCKS Procedure......Page 624 Rebuilding Free Lists Using the REBUILD_FREELISTS Procedure......Page 625 Enabling or Disabling the Skipping of Corrupt Blocks: SKIP_CORRUPT_BLOCKS......Page 626 Part IV Database Security......Page 629 23 Establishing Security Policies......Page 631 User Authentication......Page 632 Data Security Policy......Page 633 General User Security......Page 634 End-User Security......Page 636 Administrator Security......Page 638 Application Developer Security......Page 640 Password Management Policy......Page 642 Account Locking......Page 643 Password Aging and Expiration......Page 644 Password History......Page 645 Password Complexity Verification......Page 646 A Security Checklist......Page 650 24 Managing Users and Resources......Page 661 Concurrent Usage Licensing......Page 662 Named User Limits......Page 665 Viewing Licensing Limits and Current Values......Page 666 User Authentication Methods......Page 667 Database Authentication......Page 668 External Authentication......Page 669 Global Authentication and Authorization......Page 671 Proxy Authentication and Authorization......Page 673 Creating Users......Page 676 Altering Users......Page 680 Dropping Users......Page 681 Managing Resources with Profiles......Page 682 Enabling and Disabling Resource Limits......Page 683 Creating Profiles......Page 684 Using Composite Limits......Page 685 Viewing Information About Database Users and Profiles......Page 687 Listing All Tablespace Quotas......Page 689 Listing All Profiles and Assigned Limits......Page 690 Viewing Memory Use for Each User Session......Page 691 25 Managing User Privileges and Roles......Page 693 System Privileges......Page 694 Managing User Roles......Page 696 Predefined Roles......Page 697 Creating a Role......Page 699 Specifying the Type of Role Authorization......Page 700 Dropping Roles......Page 702 Granting System Privileges and Roles......Page 703 Granting Object Privileges and Roles......Page 704 Granting Privileges on Columns......Page 705 Revoking Object Privileges and Roles......Page 706 Cascading Effects of Revoking Privileges......Page 708 When Do Grants and Revokes Take Effect?......Page 709 Specifying Default Roles......Page 710 Granting Roles Using the Operating System or Network......Page 711 Using Operating System Role Identification......Page 712 Using Network Connections with Operating System Role Management......Page 714 Viewing Privilege and Role Information......Page 715 Listing Object Privileges Granted to a User......Page 717 Listing the Current Privilege Domain of Your Session......Page 718 Listing Information About the Privilege Domains of Roles......Page 719 26 Auditing Database Use......Page 721 Keep Audited Information Manageable......Page 722 Guidelines for Auditing Suspicious Database Activity......Page 723 What Information is Contained in the Audit Trail?......Page 724 Events Audited by Default......Page 725 Setting Auditing Options......Page 726 Turning Off Audit Options......Page 730 Enabling and Disabling Database Auditing......Page 732 Controlling the Growth and Size of the Audit Trail......Page 733 Protecting the Audit Trail......Page 735 Fine-Grained Auditing......Page 736 Creating the Audit Trail Views......Page 737 Using Audit Trail Views to Investigate Suspicious Activities......Page 738 Part V Database Resource Management......Page 743 27 Using the Database Resource Manager......Page 745 How Does the Database Resource Manager Address These Problems?......Page 746 What are the Elements of the Database Resource Manager?......Page 747 Understanding Resource Plans......Page 748 Administering the Database Resource Manager......Page 752 Creating a Simple Resource Plan......Page 754 Creating Complex Resource Plans......Page 755 Using the Pending Area for Creating Plan Schemas......Page 756 Creating Resource Plans......Page 758 Creating Resource Consumer Groups......Page 760 Specifying Resource Plan Directives......Page 761 Managing Resource Consumer Groups......Page 764 Changing Resource Consumer Groups......Page 765 Managing the Switch Privilege......Page 766 Enabling the Database Resource Manager......Page 768 Multilevel Schema Example......Page 769 Example of Using Several Resource Allocation Methods......Page 771 An Oracle Supplied Plan......Page 772 Creating the Environment......Page 773 Why Is This Necessary to Produce Expected Results?......Page 774 Viewing Database Resource Manager Information......Page 775 Viewing Consumer Groups Granted to Users or Roles......Page 776 Viewing Current Consumer Groups for Sessions......Page 777 Viewing the Currently Active Plans......Page 778 Part VI Distributed Database Management......Page 779 28 Distributed Database Concepts......Page 781 Homogenous Distributed Database Systems......Page 782 Heterogeneous Distributed Database Systems......Page 785 Client/Server Database Architecture......Page 786 What Are Database Links?......Page 788 What Are Shared Database Links?......Page 790 Why Use Database Links?......Page 791 Global Database Names in Database Links......Page 792 Names for Database Links......Page 794 Types of Database Links......Page 795 Users of Database Links......Page 796 Creation of Database Links: Examples......Page 799 Schema Objects and Database Links......Page 800 Database Link Restrictions......Page 802 Site Autonomy......Page 803 Distributed Database Security......Page 804 Administration Tools......Page 811 Remote SQL Statements......Page 813 Shared SQL for Remote and Distributed Statements......Page 814 Two-Phase Commit Mechanism......Page 815 Database Link Name Resolution......Page 816 Schema Object Name Resolution......Page 819 Global Name Resolution in Views, Synonyms, and Procedures......Page 822 Transparency in a Distributed Database System......Page 824 Remote Procedure Calls (RPCs)......Page 826 Character Set Support......Page 827 Homogeneous Distributed Environment......Page 829 Heterogeneous Distributed Environment......Page 830 29 Managing a Distributed Database......Page 833 Understanding How Global Database Names Are Formed......Page 834 Determining Whether Global Naming Is Enforced......Page 835 Changing the Domain in a Global Database Name......Page 836 Changing a Global Database Name: Scenario......Page 837 Obtaining Privileges Necessary for Creating Database Links......Page 840 Specifying Link Types......Page 841 Specifying Link Users......Page 843 Using Connection Qualifiers to Specify Service Names Within Link Names......Page 845 Determining Whether to Use Shared Database Links......Page 846 Creating Shared Database Links......Page 847 Configuring Shared Database Links......Page 848 Managing Database Links......Page 850 Dropping Database Links......Page 851 Limiting the Number of Active Database Link Connections......Page 852 Determining Which Links Are in the Database......Page 853 Determining Which Link Connections Are Open......Page 856 Using Views to Create Location Transparency......Page 858 Using Synonyms to Create Location Transparency......Page 860 Using Procedures to Create Location Transparency......Page 862 Managing Statement Transparency......Page 864 Creating a Public Fixed User Database Link......Page 866 Creating a Public Fixed User Shared Database Link......Page 867 Creating a Public Connected User Shared Database Link......Page 868 Creating a Public Current User Database Link......Page 869 30 Developing Applications for a Distributed Database System......Page 871 Controlling Connections Established by Database Links......Page 872 Tuning Distributed Queries......Page 873 Using Collocated Inline Views......Page 874 Using Cost-Based Optimization......Page 875 Using Hints......Page 878 Analyzing the Execution Plan......Page 880 Handling Errors in Remote Procedures......Page 882 31 Distributed Transactions Concepts......Page 885 What Are Distributed Transactions?......Page 886 Session Trees for Distributed Transactions......Page 888 Local Coordinators......Page 890 Commit Point Site......Page 891 Two-Phase Commit Mechanism......Page 895 Prepare Phase......Page 896 Commit Phase......Page 899 Forget Phase......Page 900 Automatic Resolution of In-Doubt Transactions......Page 901 Relevance of System Change Numbers for In-Doubt Transactions......Page 904 Stage 1: Client Application Issues DML Statements......Page 905 Stage 3: Global Coordinator Sends Prepare Response......Page 907 Stage 4: Commit Point Site Commits......Page 908 Stage 6: Global and Local Coordinators Tell All Nodes to Commit......Page 909 Stage 7: Global Coordinator and Commit Point Site Complete the Commit......Page 910 32 Managing Distributed Transactions......Page 911 Limiting the Number of Distributed Transactions......Page 912 Specifying the Commit Point Strength of a Node......Page 913 Transaction Naming......Page 914 Determining the ID Number and Status of Prepared Transactions......Page 915 Tracing the Session Tree of In-Doubt Transactions......Page 917 Discovering Problems with a Two-Phase Commit......Page 919 Determining Whether to Perform a Manual Override......Page 920 Analyzing the Transaction Data......Page 921 Manually Committing an In-Doubt Transaction......Page 922 Purging Pending Rows from the Data Dictionary......Page 924 Determining When to Use DBMS_TRANSACTION......Page 925 Manually Committing an In-Doubt Transaction: Example......Page 926 Step 2: Query DBA_2PC_PENDING......Page 928 Step 3: Query DBA_2PC_NEIGHBORS on Local Node......Page 930 Step 4: Querying Data Dictionary Views on All Nodes......Page 931 Step 6: Check for Mixed Outcome Using DBA_2PC_PENDING......Page 934 Transaction Timeouts......Page 935 Simulating Distributed Transaction Failure......Page 936 Managing Read Consistency......Page 937 Index......Page 939 Readers of this guide are assumed to be familiar with relational database concepts. They are also assumed to be familiar with the operating system environment under which they are running Oracle.Administrators frequently participate in installing the Oracle server software and migrating existing Oracle databases to newer formats (for example, version 8 databases to OracIe 9i format). This guide is not an installation or migration manual.If your primary interest is installation, see your operating system specific Oracleinstallation guide.If your primary interest is database or application migration, see the Oracle 9i Database Migration manual.In addition to administrators, experienced users of Oracle and advanced database application designers might aiso find information in this guide useful.However, database application developers should also see the Oracle 9i Application Developer's Guide - Fundamentals and the documentation for the tool or language product they are using to develop Oracle database applications.