Oracle Database Administrators Guide, 10g Release 2 (10.2) b14231

Contents......Page 3
Send Us Your Comments......Page 25
Documentation Accessibility......Page 27
Structure......Page 28
Related Documents......Page 31
Conventions......Page 32
Oracle Database 10g Release 2 (10.2) New Features in the Administrator's Guide......Page 37
Oracle Database 10g Release 1 (10.1) New Features in the Administrator's Guide......Page 41
Part I Basic Database Administration......Page 47
Types of Oracle Database Users......Page 49
Application Developers......Page 50
Tasks of a Database Administrator......Page 51
Task 3: Plan the Database......Page 52
Task 8: Back Up the Fully Functional Database......Page 53
Task 11: Roll Out to Additional Hosts......Page 54
Identifying Your Oracle Database Software Release......Page 55
Checking Your Current Release Number......Page 56
Database Administrator Usernames......Page 57
The DBA Role......Page 58
SYSDBA and SYSOPER......Page 59
Connecting with Administrative Privileges: Example......Page 60
Nonsecure Remote Connections......Page 61
Preparing to Use Operating System Authentication......Page 62
Connecting Using Password File Authentication......Page 63
Using ORAPWD......Page 64
Adding Users to a Password File......Page 66
Maintaining a Password File......Page 67
Automatic Manageability Features......Page 68
Advisors......Page 69
Export and Import Utilities......Page 70
Deciding How to Create an Oracle Database......Page 71
Planning for Database Creation......Page 72
Creating the Database......Page 74
Step 2: Establish the Database Administrator Authentication Method......Page 75
Step 5: Create a Server Parameter File (Recommended)......Page 76
Step 7: Issue the CREATE DATABASE Statement......Page 77
Step 10: Run Scripts to Install Additional Options (Optional)......Page 79
Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM......Page 80
Creating a Locally Managed SYSTEM Tablespace......Page 81
Creating the SYSAUX Tablespace......Page 82
Using Automatic Undo Management: Creating an Undo Tablespace......Page 83
Creating a Default Temporary Tablespace......Page 84
Specifying Oracle-Managed Files at Database Creation......Page 85
Specifying the Default Tablespace Type......Page 86
Specifying the Database Time Zone File......Page 87
Using the FORCE LOGGING Clause......Page 88
Understanding Initialization Parameters......Page 89
DB_NAME Initialization Parameter......Page 91
Specifying Control Files......Page 92
DB_BLOCK_SIZE Initialization Parameter......Page 93
Components and Granules in the SGA......Page 94
Using Automatic Shared Memory Management......Page 95
Enabling Automatic Shared Memory Management......Page 96
Setting Minimums for Automatically Sized SGA Components......Page 97
Dynamic Modification of SGA_TARGET......Page 98
Modifying Parameters for Automatically Managed Components......Page 99
Setting the Buffer Cache Initialization Parameters......Page 100
Specifying the Shared Pool Size......Page 101
Viewing Information About the SGA......Page 102
UNDO_TABLESPACE Initialization Parameter......Page 103
Setting the License Parameter......Page 104
Managing Initialization Parameters Using a Server Parameter File......Page 105
Migrating to a Server Parameter File......Page 106
Creating a Server Parameter File......Page 107
Setting or Changing Initialization Parameter Values......Page 108
Exporting the Server Parameter File......Page 109
Viewing Parameter Settings......Page 110
Defining Application Services for Oracle Database 10g......Page 111
Configuring Services......Page 112
Server-Side Use......Page 113
Some Security Considerations......Page 114
Enabling Transparent Data Encryption......Page 115
Viewing Information About the Database......Page 116
Starting Up a Database Using SQL*Plus......Page 119
Understanding Initialization Parameter Files......Page 120
Starting Up an Instance......Page 122
Restricting Access to an Instance at Startup......Page 123
Forcing an Instance to Start......Page 124
Opening a Closed Database......Page 125
Restricting Access to an Open Database......Page 126
Shutting Down with the IMMEDIATE Clause......Page 127
Shutting Down with the ABORT Clause......Page 128
Quiescing a Database......Page 129
Restoring the System to Normal Operation......Page 130
Suspending and Resuming a Database......Page 131
Dedicated Server Processes......Page 133
Shared Server Processes......Page 134
Initialization Parameters for Shared Server......Page 136
Determining a Value for SHARED_SERVERS......Page 137
Limiting the Number of Shared Server Processes......Page 138
Configuring Dispatchers......Page 139
DISPATCHERS Initialization Parameter Attributes......Page 140
Setting the Initial Number of Dispatchers......Page 141
Altering the Number of Dispatchers......Page 142
Shutting Down Specific Dispatcher Processes......Page 143
Monitoring Shared Server......Page 144
About Oracle Database Background Processes......Page 145
Managing Processes for Parallel SQL Execution......Page 146
Forcing Parallel SQL Execution......Page 147
Terminating Sessions......Page 148
Terminating an Active Session......Page 149
Server-Generated Alerts......Page 150
Setting Threshold Levels......Page 151
Additional APIs to Manage Server-Generated Alerts......Page 152
Monitoring the Database Using Trace Files and the Alert Log......Page 153
Controlling When Oracle Database Writes to Trace Files......Page 154
Monitoring Locks......Page 155
Process and Session Views......Page 156
Part II Oracle Database Structure and Storage......Page 159
What Is a Control File?......Page 161
Multiplex Control Files on Different Disks......Page 162
Creating Initial Control Files......Page 163
When to Create New Control Files......Page 164
Steps for Creating New Control Files......Page 165
Backing Up Control Files......Page 167
Recovering from Permanent Media Failure Using a Control File Copy......Page 168
Displaying Control File Information......Page 169
Redo Threads......Page 171
How Oracle Database Writes to the Redo Log......Page 172
Log Switches and Log Sequence Numbers......Page 173
Multiplexing Redo Log Files......Page 174
Legal and Illegal Configurations......Page 175
Placing Redo Log Members on Different Disks......Page 176
Choosing the Number of Redo Log Files......Page 177
Setting the ARCHIVE_LAG_TARGET Initialization Parameter......Page 178
Creating Redo Log Groups......Page 179
Relocating and Renaming Redo Log Members......Page 180
Dropping Redo Log Members......Page 182
Verifying Blocks in Redo Log Files......Page 183
Clearing a Redo Log File......Page 184
Viewing Redo Log Information......Page 185
What Is the Archived Redo Log?......Page 187
Running a Database in NOARCHIVELOG Mode......Page 188
Running a Database in ARCHIVELOG Mode......Page 189
Changing the Database Archiving Mode......Page 190
Adjusting the Number of Archiver Processes......Page 191
Method 1: Using the LOG_ARCHIVE_DEST_n Parameter......Page 192
Understanding Archive Destination Status......Page 194
Normal Transmission Mode......Page 195
Standby Transmission Mode......Page 196
Specifying Mandatory and Optional Destinations......Page 197
Scenario for Archiving to Both Mandatory and Optional Destinations......Page 198
Controlling Trace Output Generated by the Archivelog Process......Page 199
Dynamic Performance Views......Page 200
The ARCHIVE LOG LIST Command......Page 201
Using Multiple Tablespaces......Page 203
Creating Tablespaces......Page 204
Locally Managed Tablespaces......Page 205
Creating a Locally Managed Tablespace......Page 206
Specifying Segment Space Management in Locally Managed Tablespaces......Page 207
Bigfile Tablespaces......Page 208
Creating a Bigfile Tablespace......Page 209
Temporary Tablespaces......Page 210
Creating a Bigfile Temporary Tablespace......Page 211
Altering a Locally Managed Temporary Tablespace......Page 212
Changing Members of a Tablespace Group......Page 213
Specifying Nonstandard Block Sizes for Tablespaces......Page 214
Taking Tablespaces Offline......Page 215
Using Read-Only Tablespaces......Page 217
Making a Tablespace Read-Only......Page 218
Making a Read-Only Tablespace Writable......Page 219
Delaying the Opening of Datafiles in Read-Only Tablespaces......Page 220
Renaming Tablespaces......Page 221
Managing the SYSAUX Tablespace......Page 222
Controlling the Size of the SYSAUX Tablespace......Page 223
Diagnosing and Repairing Locally Managed Tablespace Problems......Page 224
Scenario 3: Fixing Bitmap Where Overlap is Reported......Page 225
Migrating the SYSTEM Tablespace to a Locally Managed Tablespace......Page 226
Introduction to Transportable Tablespaces......Page 227
About Transporting Tablespaces Across Platforms......Page 228
Limitations on Transportable Tablespace Use......Page 229
Compatibility Considerations for Transportable Tablespaces......Page 230
Transporting Tablespaces Between Databases: A Procedure and Example......Page 231
Step 2: Pick a Self-Contained Set of Tablespaces......Page 232
Step 3: Generate a Transportable Tablespace Set......Page 234
Step 4: Transport the Tablespace Set......Page 236
Step 5: Import the Tablespace Set......Page 238
Transporting and Attaching Partitions for Data Warehousing......Page 239
Publishing Structured Data on CDs......Page 240
Mounting the Same Tablespace Read-Only on Multiple Databases......Page 241
Viewing Tablespace Information......Page 242
Example 2: Listing the Datafiles and Associated Tablespaces of a Database......Page 243
Example 3: Displaying Statistics for Free Space (Extents) of Each Tablespace......Page 244
Guidelines for Managing Datafiles......Page 245
Determine a Value for the DB_FILES Initialization Parameter......Page 246
Determine the Size of Datafiles......Page 247
Creating Datafiles and Adding Datafiles to a Tablespace......Page 248
Enabling and Disabling Automatic Extension for a Datafile......Page 249
Altering Datafile Availability......Page 250
Taking Datafiles Offline in NOARCHIVELOG Mode......Page 251
Renaming and Relocating Datafiles......Page 252
Procedure for Relocating Datafiles in a Single Tablespace......Page 253
Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces......Page 254
Dropping Datafiles......Page 255
Copying Files Using the Database Server......Page 256
Copying a File on a Local File System......Page 257
File Transfer and the DBMS_SCHEDULER Package......Page 258
Mapping Files to Physical Devices......Page 259
Components of File Mapping......Page 260
External Process (FMPUTL)......Page 261
Mapping Structures......Page 262
Configuration ID......Page 263
Enabling File Mapping......Page 264
Using the DBMS_STORAGE_MAP Package......Page 265
Obtaining Information from the File Mapping Views......Page 266
Example 2: Map a File into Its Corresponding Devices......Page 267
Example 3: Map a Database Object......Page 268
Viewing Datafile Information......Page 269
What Is Undo?......Page 271
Overview of Automatic Undo Management......Page 272
Retention Guarantee......Page 273
Automatic Tuning of Undo Retention......Page 274
Sizing Fixed-Size Undo Tablespaces......Page 275
Managing Undo Tablespaces......Page 276
Using the CREATE UNDO TABLESPACE Statement......Page 277
Dropping an Undo Tablespace......Page 278
Establishing User Quotas for Undo Space......Page 279
Viewing Information About Undo......Page 280
Part III Automated File and Storage Management......Page 283
What Are Oracle-Managed Files?......Page 285
What Is a File System?......Page 286
Enabling the Creation and Use of Oracle-Managed Files......Page 287
Setting the DB_CREATE_FILE_DEST Initialization Parameter......Page 288
Creating Oracle-Managed Files......Page 289
How Oracle-Managed Files Are Named......Page 290
Specifying Control Files at Database Creation......Page 291
Specifying Redo Log Files at Database Creation......Page 292
Specifying the Undo Tablespace Datafile at Database Creation......Page 293
CREATE DATABASE Statement Using Oracle-Managed Files: Examples......Page 294
CREATE TABLESPACE: Examples......Page 296
Creating Tempfiles for Temporary Tablespaces Using Oracle-Managed Files......Page 297
Creating Control Files Using Oracle-Managed Files......Page 298
CREATE CONTROLFILE Using RESETLOGS Keyword: Example......Page 299
Using the ALTER DATABASE ADD LOGFILE Statement......Page 300
Behavior of Oracle-Managed Files......Page 301
Scenarios for Using Oracle-Managed Files......Page 302
Scenario 1: Create and Manage a Database with Multiplexed Redo Logs......Page 303
Scenario 2: Create and Manage a Database with Database and Flash Recovery Areas......Page 306
Scenario 3: Adding Oracle-Managed Files to an Existing Database......Page 307
What Is Automatic Storage Management?......Page 309
Overview of the Components of Automatic Storage Management......Page 311
Administering an Automatic Storage Management Instance......Page 312
Installing ASM......Page 313
Operating System Authentication for ASM......Page 314
Setting Initialization Parameters for an ASM Instance......Page 315
Initialization Parameters for ASM Instances......Page 316
Improving Disk Discovery Time......Page 317
Starting Up an ASM Instance......Page 318
CSS Daemon on the Windows Platform......Page 319
Disk Discovery......Page 320
Shutting Down an ASM Instance......Page 321
Determining the Number of Disk Groups......Page 322
How ASM Handles Disk Failures......Page 323
Failure Groups and Mirroring......Page 324
Managing Capacity in Disk Groups......Page 325
Scalability......Page 326
Creating a Disk Group......Page 327
Altering the Disk Membership of a Disk Group......Page 328
Adding Disks to a Disk Group......Page 329
Dropping Disks from Disk Groups......Page 331
Manually Rebalancing a Disk Group......Page 332
Mounting and Dismounting Disk Groups......Page 333
Managing Disk Group Directories......Page 334
Dropping a Directory......Page 335
Dropping an Alias Name for an ASM Filename......Page 336
Managing Disk Group Templates......Page 337
Adding Templates to a Disk Group......Page 339
Using Automatic Storage Management in the Database......Page 340
What Types of Files Does ASM Support?......Page 341
About ASM Filenames......Page 342
Fully Qualified ASM Filename......Page 344
Alias ASM Filenames......Page 345
Incomplete ASM Filename......Page 346
Starting the ASM and Database Instances......Page 347
Creating ASM Files Using a Default Disk Group Specification......Page 348
Using ASM Filenames in SQL Statements......Page 349
Creating Tablespaces in ASM......Page 350
Creating Redo Logs in ASM......Page 351
Creating a Control File in ASM......Page 352
Recovery Manager (RMAN) and ASM......Page 353
Accessing Automatic Storage Management Files with the XML DB Virtual Folder......Page 354
Restrictions......Page 355
Viewing Information About Automatic Storage Management......Page 356
Part IV Schema Objects......Page 359
Creating Multiple Tables and Views in a Single Operation......Page 361
Using DBMS_STATS to Collect Table and Index Statistics......Page 362
Validating Tables, Indexes, Clusters, and Materialized Views......Page 363
Eliminating Migrated or Chained Rows in a Table......Page 364
Truncating Tables and Clusters......Page 365
Using TRUNCATE......Page 366
Enabling and Disabling Triggers......Page 367
Disabling Triggers......Page 368
Integrity Constraint States......Page 369
Enable Novalidate Constraint State......Page 370
Enabling Constraints Upon Definition......Page 371
Disabling Enabled Constraints......Page 372
Deferring Constraint Checks......Page 373
Reporting Constraint Exceptions......Page 374
Viewing Constraint Information......Page 375
Managing Object Dependencies......Page 376
About Dependencies Among Schema Objects......Page 377
Manually Recompiling Views......Page 378
Managing Object Name Resolution......Page 379
Using a PL/SQL Package to Display Information About Schema Objects......Page 381
Example 1: Displaying Schema Objects By Type......Page 382
Example 2: Displaying Dependencies of Views and Synonyms......Page 383
Managing Tablespace Alerts......Page 385
Setting Alert Thresholds......Page 386
Limitations......Page 387
Specifying the INITRANS Parameter......Page 388
Identifying the Storage Parameters......Page 389
Setting Storage Parameters for Index Segments......Page 390
Understanding Precedence in Storage Parameters......Page 391
How Resumable Space Allocation Works......Page 392
What Operations are Resumable?......Page 393
Enabling and Disabling Resumable Space Allocation......Page 394
Specifying a Timeout Interval......Page 395
Notifying Users: The AFTER SUSPEND System Event and Trigger......Page 396
Using the DBMS_RESUMABLE Package......Page 397
Resumable Space Allocation Example: Registering an AFTER SUSPEND Trigger......Page 398
Reclaiming Wasted Space......Page 399
Using the Segment Advisor......Page 400
Running the Segment Advisor Manually......Page 401
Running the Segment Advisor Manually with Enterprise Manager......Page 402
Running the Segment Advisor Manually with PL/SQL......Page 403
Viewing Segment Advisor Results......Page 405
Viewing Segment Advisor Results with Enterprise Manager......Page 406
Viewing Segment Advisor Results by Querying the DBA_ADVISOR_* Views......Page 409
Viewing Segment Advisor Results with DBMS_SPACE.ASA_RECOMMENDATIONS......Page 410
Configuring the Automatic Segment Advisor Job......Page 411
Viewing Automatic Segment Advisor Information......Page 412
Shrinking Database Segments Online......Page 413
Using PL/SQL Packages to Display Information About Schema Object Space Usage......Page 415
Using Views to Display Information About Space Usage in Schema Objects......Page 416
Example 2: Displaying Extent Information......Page 417
Example 4: Displaying Segments that Cannot Allocate Additional Extents......Page 418
Capacity Planning for Database Objects......Page 419
Obtaining Object Growth Trends......Page 420
About Tables......Page 423
Design Tables Before Creating Them......Page 424
Specify the Location of Each Table......Page 425
Consider Using Table Compression when Creating Tables......Page 426
Restrictions to Consider When Creating Tables......Page 427
Creating a Temporary Table......Page 428
Loading Tables......Page 430
Inserting Data with DML Error Logging......Page 431
Error Logging Table Format......Page 432
Creating an Error Logging Table Manually......Page 433
Inserting Data Into Tables Using Direct-Path INSERT......Page 434
Enabling Direct-Path INSERT......Page 435
Specifying the Logging Mode for Direct-Path INSERT......Page 436
Locking Considerations with Direct-Path INSERT......Page 437
Altering Tables......Page 438
Altering Physical Attributes of a Table......Page 439
Manually Allocating Storage for a Table......Page 440
Renaming Table Columns......Page 441
Marking Columns Unused......Page 442
Redefining Tables Online......Page 443
Performing Online Redefinition with DBMS_REDEFINITION......Page 444
Constructing a Column Mapping String......Page 446
Creating Dependent Objects Automatically......Page 447
Results of the Redefinition Process......Page 448
Restrictions for Online Redefinition of Tables......Page 449
Online Redefinition of a Single Partition......Page 450
Rules for Online Redefinition of a Single Partition......Page 451
Online Table Redefinition Examples......Page 452
Privileges Required for the DBMS_REDEFINITION Package......Page 457
Recovering Tables Using the Flashback Table Feature......Page 458
Dropping Tables......Page 459
What Is the Recycle Bin?......Page 460
Enabling and Disabling the Recycle Bin......Page 461
Purging Objects in the Recycle Bin......Page 462
Restoring Tables from the Recycle Bin......Page 463
Managing Index-Organized Tables......Page 464
What Are Index-Organized Tables?......Page 465
Creating Index-Organized Tables that Contain Object Types......Page 466
Choosing and Monitoring a Threshold Value......Page 467
Using the INCLUDING Clause......Page 468
Using Key Compression......Page 469
Moving (Rebuilding) Index-Organized Tables......Page 470
Creating a Secondary Index on an Index-Organized Table......Page 471
Collecting Optimizer Statistics for Index-Organized Tables......Page 472
Converting Index-Organized Tables to Regular Tables......Page 473
Managing External Tables......Page 474
Creating External Tables......Page 475
Altering External Tables......Page 477
System and Object Privileges for External Tables......Page 478
Viewing Information About Tables......Page 479
About Indexes......Page 483
Create Indexes After Inserting Table Data......Page 484
Limit the Number of Indexes for Each Table......Page 485
Specify the Tablespace for Each Index......Page 486
Consider Costs and Benefits of Coalescing or Rebuilding Indexes......Page 487
Creating Indexes......Page 488
Creating an Index Associated with a Constraint......Page 489
Specifying the Index Associated with a Constraint......Page 490
Creating an Index Online......Page 491
Creating a Function-Based Index......Page 492
Altering Indexes......Page 493
Rebuilding an Existing Index......Page 494
Monitoring Space Use of Indexes......Page 495
Dropping Indexes......Page 496
Viewing Index Information......Page 497
About Partitioned Tables and Indexes......Page 499
Partitioning Methods......Page 500
When to Use Range Partitioning......Page 501
When to Use List Partitioning......Page 502
When to Use Composite Range-Hash Partitioning......Page 503
When to Use Composite Range-List Partitioning......Page 504
Creating Partitioned Tables......Page 505
Creating a Range Partitioned Table......Page 506
Creating Hash-Partitioned Tables and Global Indexes......Page 507
Creating List-Partitioned Tables......Page 508
Creating Composite Range-Hash Partitioned Tables......Page 509
Creating Composite Range-List Partitioned Tables......Page 510
Specifying a Subpartition Template for a Range-Hash Partitioned Table......Page 511
Specifying a Subpartition Template for a Range-List Partitioned Table......Page 512
Using Multicolumn Partitioning Keys......Page 513
Creating Partitioned Index-Organized Tables......Page 516
Creating Range-Partitioned Index-Organized Tables......Page 517
Partitioning Restrictions for Multiple Block Sizes......Page 518
Maintaining Partitioned Tables......Page 519
Updating Indexes Automatically......Page 522
Adding a Partition to a Hash-Partitioned Table......Page 524
Adding a Subpartition to a Range-Hash Partitioned Table......Page 525
Adding Index Partitions......Page 526
Coalescing a Partition in a Hash-Partitioned Table......Page 527
Dropping a Partition from a Table that Contains Data and Global Indexes......Page 528
Dropping a Partition Containing Data and Referential Integrity Constraints......Page 529
Exchanging Partitions......Page 530
Exchanging a Hash-Partitioned Table with a Range-Hash Partition......Page 531
Merging Partitions......Page 532
Merging Range Partitions......Page 533
Merging Range-Hash Partitions......Page 534
Merging Subpartitions in a Range-List Partitioned Table......Page 535
Modifying Real Attributes for a Range or List Partition......Page 536
Modifying Real Attributes of Index Partitions......Page 537
Dropping Values from a List Partition......Page 538
Modifying a Subpartition Template......Page 539
Moving Table Partitions......Page 540
Rebuilding Local Index Partitions......Page 541
Renaming an Index Partition......Page 542
Splitting a Partition of a Range-Partitioned Table......Page 543
Splitting a Range-Hash Partition......Page 544
Splitting a Range-List Partition......Page 545
Splitting a Range-List Subpartition......Page 546
Optimizing SPLIT PARTITION and SPLIT SUBPARTITION Operations......Page 547
Truncating Table Partitions Containing Data and Global Indexes......Page 548
Truncating a Subpartition......Page 549
Partitioned Tables and Indexes Example......Page 550
Viewing Information About Partitioned Tables and Indexes......Page 551
About Clusters......Page 555
Guidelines for Managing Clusters......Page 556
Specify the Space Required by an Average Cluster Key and Its Associated Rows......Page 557
Creating Clusters......Page 558
Creating Cluster Indexes......Page 559
Altering Clustered Tables......Page 560
Dropping Clusters......Page 561
Viewing Information About Clusters......Page 562
About Hash Clusters......Page 563
Creating Hash Clusters......Page 564
Creating a Sorted Hash Cluster......Page 565
Choosing the Key......Page 566
Setting SIZE......Page 567
Controlling Space in Hash Clusters: Example 2......Page 568
Dropping Hash Clusters......Page 569
Viewing Information About Hash Clusters......Page 570
Creating Views......Page 571
Join Views......Page 572
Replacing Views......Page 573
Using Views in Queries......Page 574
Updating a Join View......Page 575
UPDATE Statements......Page 577
DELETE Statements......Page 578
Updating Views That Involve Outer Joins......Page 579
Using the UPDATABLE_ COLUMNS Views......Page 581
About Sequences......Page 582
Altering Sequences......Page 583
Generating Sequence Numbers with NEXTVAL......Page 584
Caching Sequence Numbers......Page 585
Dropping Sequences......Page 586
Creating Synonyms......Page 587
Viewing Information About Views, Synonyms, and Sequences......Page 588
About the DBMS_REPAIR Package......Page 591
Using the DBMS_REPAIR Package......Page 592
DB_BLOCK_CHECKING Initialization Parameter......Page 593
Task 3: Make Objects Usable......Page 594
DBMS_REPAIR Examples......Page 595
Example: Creating an Orphan Key Table......Page 596
Example: Detecting Corruption......Page 597
Example: Fixing Corrupt Blocks......Page 598
Example: Skipping Corrupt Blocks......Page 599
Part V Database Security......Page 601
Managing Users and Resources......Page 603
Auditing Database Use......Page 604
Part VI Database Resource Management and Task Scheduling......Page 605
Maintenance Windows......Page 607
Automatic Segment Advisor Job......Page 608
Resource Management for Automatic System Tasks......Page 609
What Problems Does the Database Resource Manager Address?......Page 611
What Are the Elements of the Database Resource Manager?......Page 612
A Simple Resource Plan......Page 613
A Resource Plan with Subplans......Page 614
Degree of Parallelism Limit......Page 615
Idle Time Limit......Page 616
Administering the Database Resource Manager......Page 617
Creating a Simple Resource Plan......Page 618
Creating Complex Resource Plans......Page 619
Validating Changes......Page 620
Clearing the Pending Area......Page 621
Deleting a Plan......Page 622
Creating Resource Consumer Groups......Page 623
Specifying Resource Plan Directives......Page 624
Updating Resource Plan Directives......Page 626
Managing Resource Consumer Groups......Page 627
Switching Sessions for a User......Page 628
Using the DBMS_SESSION Package to Switch Consumer Group......Page 629
Revoking Switch Privileges......Page 630
Creating Consumer Group Mappings......Page 631
Creating Attribute Mapping Priorities......Page 632
Automatic Group Switching......Page 633
Putting It All Together: Database Resource Manager Examples......Page 634
Multilevel Schema Example......Page 635
Example of Using Several Resource Allocation Methods......Page 636
Monitoring and Tuning the Database Resource Manager......Page 637
Why Is This Necessary to Produce Expected Results?......Page 638
Monitoring Results......Page 639
Guidelines for Using Operating-System Resource Control......Page 640
Viewing Database Resource Manager Information......Page 641
Viewing Consumer Groups Granted to Users or Roles......Page 642
Viewing the Currently Active Plans......Page 643
Creating a Job......Page 645
Removing a Job from the Job Queue......Page 646
Overview of the Scheduler......Page 647
What Can the Scheduler Do?......Page 648
Schedules......Page 649
Jobs......Page 650
Events......Page 651
How Programs, Jobs, and Schedules are Related......Page 652
Job Classes......Page 653
Windows......Page 654
Window Groups......Page 655
The Job Table......Page 656
Job Slaves......Page 657
Service Affinity when Using the Scheduler......Page 658
Scheduler Objects and Their Naming......Page 661
Creating Jobs......Page 662
Setting Job Arguments......Page 663
Creating Jobs Using a Named Schedule......Page 664
Running Jobs......Page 665
Running External Jobs......Page 666
Dropping Jobs......Page 667
Enabling Jobs......Page 668
Creating Programs......Page 669
Defining Program Arguments......Page 670
Dropping Programs......Page 671
Using Schedules......Page 672
Altering Schedules......Page 673
Using the Scheduler Calendaring Syntax......Page 674
Differences Between PL/SQL Expression and Calendaring Syntax Behavior......Page 677
Job Class Tasks and Their Procedures......Page 678
Using Windows......Page 679
Window Tasks and Their Procedures......Page 680
Creating Windows......Page 681
Opening Windows......Page 682
Dropping Windows......Page 683
Enabling Windows......Page 684
Examples of Overlapping Windows......Page 685
Window Group Tasks and Their Procedures......Page 687
Adding a Member to a Window Group......Page 688
Disabling a Window Group......Page 689
Using Events Raised by the Scheduler......Page 690
Consuming Scheduler-Raised Events with your Application......Page 691
Using Events Raised by an Application......Page 693
Specifying Event Information in an Event Schedule......Page 694
Altering an Event Schedule......Page 695
Passing Event Messages into an Event-Based Job......Page 696
Chain Tasks and Their Procedures......Page 697
Defining Chain Steps......Page 698
Adding Rules to a Chain......Page 699
Enabling Chains......Page 700
Dropping Chains......Page 701
Disabling Chains......Page 702
Altering Chain Steps......Page 703
Allocating Resources Among Jobs Using Resource Manager......Page 704
Example of Resource Allocation for Jobs......Page 705
Configuring the Scheduler......Page 707
Monitoring and Managing the Scheduler......Page 712
How to View Scheduler Information......Page 713
How to View Scheduler Privileges......Page 714
How to Find Information About Currently Running Jobs......Page 715
How to Monitor and Manage Window and Job Logs......Page 716
Controlling Job Logging......Page 717
Window Logs......Page 718
Purging Logs......Page 719
How to Manage Scheduler Privileges......Page 720
Types of Privileges and Their Descriptions......Page 721
How to Drop a Running Job......Page 722
Broken Jobs......Page 723
How to Change Job Priorities......Page 724
How the Scheduler Guarantees Availability......Page 725
Examples of Using the Scheduler......Page 726
Examples of Creating Job Classes......Page 727
Examples of Creating Programs......Page 728
Examples of Creating Windows......Page 729
Examples of Setting Attributes......Page 730
Examples of Creating Chains......Page 732
Examples of Creating Jobs and Schedules Based on Events......Page 733
Part VII Distributed Database Management......Page 735
Homogenous Distributed Database Systems......Page 737
Distributed Databases Versus Distributed Processing......Page 738
Heterogeneous Distributed Database Systems......Page 739
Client/Server Database Architecture......Page 740
Database Links......Page 741
What Are Database Links?......Page 742
What Are Shared Database Links?......Page 743
Global Database Names in Database Links......Page 744
Names for Database Links......Page 745
Types of Database Links......Page 746
Users of Database Links......Page 747
Current User Database Links......Page 748
Creation of Database Links: Examples......Page 749
Naming of Schema Objects Using Database Links......Page 750
Schema Object Name Resolution......Page 751
Site Autonomy......Page 752
Authentication Through Database Links......Page 753
Supporting User Accounts and Roles......Page 754
Schema-Independent Global Users......Page 755
Auditing Database Links......Page 757
SNMP Support......Page 758
Remote SQL Statements......Page 759
Distributed Transactions......Page 760
Database Link Name Resolution......Page 761
Terminating the Search for Name Resolution......Page 762
Example of Global Object Name Resolution: Complete Object Name......Page 763
Example of Global Object Name Resolution: Partial Object Name......Page 764
Global Name Resolution in Views, Synonyms, and Procedures......Page 765
Scenario 2: One Database Changes Names......Page 766
Location Transparency......Page 767
SQL and COMMIT Transparency......Page 768
Character Set Support for Distributed Environments......Page 769
Homogeneous Distributed Environment......Page 770
Heterogeneous Distributed Environment......Page 771
Understanding How Global Database Names Are Formed......Page 773
Determining Whether Global Naming Is Enforced......Page 774
Changing a Global Database Name: Scenario......Page 775
Obtaining Privileges Necessary for Creating Database Links......Page 778
Creating Public Database Links......Page 779
Creating Fixed User Database Links......Page 780
Creating a Current User Database Link......Page 781
Using Shared Database Links......Page 782
Determining Whether to Use Shared Database Links......Page 783
Creating Shared Links to Dedicated Servers......Page 784
Creating Shared Links to Shared Servers......Page 785
Closing Database Links......Page 786
Procedure for Dropping a Public Database Link......Page 787
Determining Which Links Are in the Database......Page 788
Determining Which Link Connections Are Open......Page 789
Creating Location Transparency......Page 790
Using Views to Create Location Transparency......Page 791
Creating Synonyms......Page 792
Using Local Procedures to Reference Remote Data......Page 793
Using Local Synonyms to Reference Remote Procedures......Page 794
Managing Statement Transparency......Page 795
Managing a Distributed Database: Examples......Page 796
Example 3: Creating a Public Connected User Database Link......Page 797
Example 5: Creating a Public Current User Database Link......Page 798
Controlling Connections Established by Database Links......Page 801
Maintaining Referential Integrity in a Distributed System......Page 802
Using Collocated Inline Views......Page 803
How Does Cost-Based Optimization Work?......Page 804
Analyzing Tables......Page 805
Using the NO_MERGE Hint......Page 806
Generating the Execution Plan......Page 807
Handling Errors in Remote Procedures......Page 808
What Are Distributed Transactions?......Page 811
Transaction Control Statements......Page 812
Session Trees for Distributed Transactions......Page 813
Global Coordinator......Page 814
How a Distributed Transaction Commits......Page 815
Commit Point Strength......Page 816
Two-Phase Commit Mechanism......Page 817
Prepared Response......Page 818
Steps in the Prepare Phase......Page 819
Guaranteeing Global Database Consistency......Page 820
In-Doubt Transactions......Page 821
Failure During the Commit Phase......Page 822
Manual Resolution of In-Doubt Transactions......Page 823
Stage 1: Client Application Issues DML Statements......Page 824
Stage 2: Oracle Database Determines Commit Point Site......Page 825
Stage 3: Global Coordinator Sends Prepare Response......Page 826
Stage 6: Global and Local Coordinators Tell All Nodes to Commit......Page 827
Stage 7: Global Coordinator and Commit Point Site Complete the Commit......Page 828
Specifying the Commit Point Strength of a Node......Page 829
Determining the ID Number and Status of Prepared Transactions......Page 830
Tracing the Session Tree of In-Doubt Transactions......Page 832
Deciding How to Handle In-Doubt Transactions......Page 833
Determining Whether to Perform a Manual Override......Page 834
Look for Transaction Advice......Page 835
Committing Using an SCN......Page 836
Purging Pending Rows from the Data Dictionary......Page 837
Determining When to Use DBMS_TRANSACTION......Page 838
Step 2: Query DBA_2PC_PENDING......Page 839
Looking for Comments or Advice......Page 840
Obtaining Database Role and Database Link Information......Page 841
Determining the Coordinators and Commit Point Site at sales......Page 842
Checking the Status of Pending Transactions at HQ......Page 843
Step 6: Check for Mixed Outcome Using DBA_2PC_PENDING......Page 844
Simulating Distributed Transaction Failure......Page 845
Disabling and Enabling RECO......Page 846
Managing Read Consistency......Page 847
Index......Page 849