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