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

Description

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