Table of Contents
8. Managing Large Objects
8.1.3 DBMS_LOB Nonprogram Elements 392
8.1.4 About the Examples 392
395
8.2 LOB Concepts 395
8.2.1 LOB Datatypes 395
8.2.2 BFILE Considerations 398
8.2.3 Internal LOB Considerations 399
402
8.3 DBMS_LOB Interface 402
8.3.1 Working with BFILEs 402
8.3.2 Reading and Examining LOBs 415
8.3.3 Updating BLOBs, CLOBs, and NCLOBs 424
433
9. Datatype Packages 434
9.1 DBMS_ROWID: Working with the ROWID Pseudo−Column (Oracle8 only) 434
9.1.1 Getting Started with DBMS_ROWID 434
9.1.2 ROWID Concepts 436
9.1.3 The DBMS_ROWID Interface 437
444
9.2 UTL_RAW: Manipulating Raw Data 444
9.2.1 Getting Started with UTL_RAW 444
9.2.2 Raw Data Manipulation Concepts 446
9.2.3 The UTL_RAW Interface 447
9.2.4 UTL_REF: Referencing Objects (Oracle8.0.4) 463
9.2.5 UTL_REF Interface 465
9.2.6 UTL_REF Example 468
471
10. Miscellaneous Packages 472
10.1 DBMS_UTILITY: Performing Miscellaneous Operations 472

10.1.1 Getting Started with DBMS_UTILITY 472
10.1.2 The DBMS_UTILITY Interface 475
493
10.2 DBMS_DESCRIBE: Describing PL/SQL Program Headers 493
10.2.1 Getting Started with DBMS_DESCRIBE 493
10.2.2 Explaining DBMS_DESCRIBE Results 496
10.2.3 DBMS_DESCRIBE Example 499
504
10.3 DBMS_DDL: Compiling and Analyzing Objects 504
10.3.1 Getting Started with DBMS_DDL 504
10.3.2 Compiling PL/SQL Objects 505
10.3.3 Computing Statistics for an Object 508
10.3.4 Setting Referenceability of Tables 510
514
10.4 DBMS_RANDOM: Generating Random Numbers (Oracle8 Only) 514
10.4.1 Getting Started with DBMS_RANDOM 514
10.4.2 DBMS_RANDOM Interface 515
517
[Appendix A] What's on the Companion Disk?
v
Table of Contents
11. Managing Session Information 518
11.1 DBMS_SESSION: Managing Session Information 518
11.1.1 Getting Started with DBMS_SESSION 518
11.1.2 Modifying Session Settings 519
11.1.3 Obtaining Session Information 523
11.1.4 Managing Session Resources 525
11.1.5 DBMS_SESSION Examples 530
540
11.2 DBMS_System: Setting Events for Debugging 540

11.2.1 Getting Started with DBMS_SYSTEM 540
11.2.2 DBMS_SYSTEM Interface 541
11.2.3 DBMS_SYSTEM Examples 543
547
12. Managing Server Resources 548
12.1 DBMS_SPACE: Obtaining Space Information 548
12.1.1 Getting Started with DBMS_SPACE 548
12.1.2 The DBMS_SPACE Interface 549
12.1.3 DBMS_SPACE Examples 553
563
12.2 DBMS_SHARED_POOL: Pinning Objects 563
12.2.1 Getting Started with DBMS_SHARED_POOL 563
12.2.2 Pinning and Unpinning Objects 564
12.2.3 Monitoring and Modifying Shared Pool Behavior 566
12.2.4 DBMS_SHARED_POOL Examples 568
573
13. Job Scheduling in the Database 574
13.1 Getting Started with DBMS_ JOB 574
13.1.1 DBMS_JOB Programs 574
13.1.2 Job Definition Parameters 575
13.2.1 INIT.ORA Parameters and Background Processes 578
13.2.2 Job Execution and the Job Execution Environment 578
13.2.3 Miscellaneous Notes 578
13.2.4 DBMS_JOB Interface 579
13.2.5 Submitting Jobs to the Job Queue 580
13.2.6 Modifying Job Characteristics 580
13.2.7 Removing Jobs and Changing Job Execution Status 581
13.2.8 Transferring Jobs 583
586
13.2 Job Queue Architecture 588

591
13.3 Tips on Using DBMS_JOB 591
13.3.1 Job Intervals and Date Arithmetic 591
13.3.2 Viewing Job Information in the Data Dictionary 592
13.3.3 DBMS_IJOB: Managing Other Users' Jobs 595
597
13.4 DBMS_JOB Examples 597
13.4.1 Tracking Space in Tablespaces 597
13.4.2 Fixing Broken Jobs Automatically 598
13.4.3 Self−Modifying and Self−Aware Jobs 599
602
[Appendix A] What's on the Companion Disk?
vi
Table of Contents
14. Snapshots 603
14.1 DBMS_SNAPSHOT: Managing Snapshots 603
14.1.1 Getting Started with DBMS_SNAPSHOT 603
14.1.2 Using the I_AM_A_REFRESH Package State Variable 604
14.1.3 Refreshing Snapshots 606
14.1.4 Purging the Snapshot Log 610
14.1.5 Reorganizing Tables 611
14.1.6 Registering Snapshots 613
14.2.1 Getting Started with DBMS_REFRESH 617
14.2.2 Creating and Destroying Snapshot Groups 617
14.2.3 Adding and Subtracting Snapshots from Snapshot Groups 618
14.2.4 Altering Properties of a Snapshot Group 619
14.2.5 Manually Refreshing Snapshot Groups 623
624
14.2 DBMS_REFRESH: Managing Snapshot Groups 624
626

14.3 DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot Instantiation 626
14.3.1 Getting Started with DBMS_OFFLINE_SNAPSHOT 626
14.3.2 DBMS_OFFLINE_SNAPSHOT Interface 627
629
14.4 DBMS_REPCAT: Managing Snapshot Replication Groups 629
14.4.1 Getting Started with DBMS_REPCAT 629
14.4.2 Creating and Dropping Snapshot Replication Groups 630
14.4.3 Adding and Removing Snapshot Replication Group Objects 633
14.4.4 Altering a Snapshot Replication Group's Propagation Mode 635
14.4.5 Manually Refreshing a Snapshot Replication Group 637
14.4.6 Switching the Master of a Snapshot Replication Group 638
641
15. Advanced Replication 642
15.1 DBMS_REPCAT_AUTH: Setting Up Administrative Accounts 642
15.1.1 Getting Started with DBMS_REPCAT_AUTH 642
15.1.2 Granting and Revoking Surrogate SYS Accounts 643
15.1.3 Granting and Revoking Propagator Accounts (Oracle8) 644
645
15.2 DBMS_REPCAT_ADMIN: Setting Up More Administrator Accounts 645
15.2.1 Getting Started with DBMS_REPCAT_ADMIN 645
15.2.2 Creating and Dropping Replication Administrator Accounts 646
649
15.3 DBMS_REPCAT: Replication Environment Administration 649
15.3.1 Getting Started with DBMS_REPCAT 649
15.3.2 Replication Groups with DBMS_REPCAT 651
15.3.3 Replicated Objects with DBMS_REPCAT 656
15.3.4 Replication Support with DBMS_REPCAT 665
15.3.5 Adding and Removing Master Sites with DBMS_REPCAT 676
15.3.6 Maintaining the Repcatlog Queue with DBMS_REPCAT 682
15.3.7 Quiescence with DBMS_REPCAT 686

15.3.8 Miscellaneous DBMS_REPCAT Procedures 688
693
15.4 DBMS_OFFLINE_OG: Performing Site Instantiation 693
15.4.1 Getting Started with DBMS_OFFLINE_OG 693
15.4.2 DBMS_OFFLINE_OG Interface 694
699
[Appendix A] What's on the Companion Disk?
vii
Table of Contents
15.5 DBMS_RECTIFIER_DIFF: Comparing Replicated Tables 699
15.5.1 Getting Started with DBMS_RECTIFIER_DIFF 699
15.5.2 DBMS_RECTIFIER_DIFF Interface 699
705
15.6 DBMS_REPUTIL: Enabling and Disabling Replication 705
15.6.1 Getting Started with DBMS_REPUTIL 705
15.6.2 DBMS_REPUTIL Interface 705
707
16. Conflict Resolution 708
16.1 Getting Started with DBMS_REPCAT 708
16.1.1 DBMS_REPCAT Programs 708
16.1.2 DBMS−REPCAT Exceptions 710
16.1.3 DBMS−REPCAT Nonprogram Elements 711
16.1.4 Data Dictionary Views 711
716
16.2 Column Groups with DBMS_REPCAT 716
16.2.1 About Column Groups 716
16.2.2 Creating and Dropping Column Groups 717
16.2.3 Modifying Existing Column Groups 720
724
16.3 Priority Groups with DBMS_REPCAT 724

16.3.1 About Priority Groups 724
16.3.2 Creating, Maintaining, and Dropping Priority Groups 725
16.3.3 Creating and Maintaining Priorities Within a Priority Group 729
16.3.4 Dropping Priorities from a Priority Group 734
738
16.4 Site Priority Groups with DBMS_REPCAT 738
16.4.1 About Site Priority Groups 738
16.4.2 Creating, Maintaining, and Dropping Site Priorities 738
16.4.3 Maintaining Site Priorities 741
749
16.5 Assigning Resolution Methods with DBMS_REPCAT 749
16.5.1 About Resolution Methods 749
759
16.6 Monitoring Conflict Resolution with DBMS_REPCAT 759
16.6.1 About Monitoring 759
762
17. Deferred Transactions and Remote Procedure Calls 763
17.1 About Deferred Transactions and RPCs 763
17.1.1 About Remote Destinations 763
17.1.2 Data Dictionary Views 764
767
17.2 DBMS_DEFER_SYS: Managing Deferred Transactions 767
17.2.1 Getting Started with DBMS_DEFER_SYS 767
17.2.2 Adding and Deleting Default Destinations 769
17.2.3 Copying Deferred Transactions to New Destinations 770
17.2.4 Maintenance Procedures 771
17.2.5 Propagating Deferred RPCs 775
17.2.6 Scheduling Propagation (Oracle8 only) 779
786
[Appendix A] What's on the Companion Disk?

viii
Table of Contents
17.3 DBMS_DEFER: Building Deferred Calls 786
17.3.1 Getting Started with DBMS_DEFER 786
17.3.2 Basic RPCs 788
17.3.3 Parameterized RPCs 793
798
17.4 DBMS_DEFER_QUERY: Performing Diagnostics and Maintenance 798
17.4.1 Getting Started with DBMS_DEFER_QUERY 798
Index 805
Table of Contents 805
Part I: Overview 805
Part II: Application Development Packages 805
Part III: Server Management Packages 805
Part IV: Distributed Database Packages 805
805
807
Part I: Overview 808
809
Part II: Application Development Packages 810
812
Part III: Server Management Packages 813
814
Part IV: Distributed Database Packages 815
816
Dedication 817
818
Preface 819
Structure of This Book 820
822

Conventions Used in This Book 822
824
Versions of Oracle 824
825
About the Disk 825
826
About PL/Vision 826
827
About Q 827
828
How to Contact Us 828
829
Acknowledgments 829
Steven 829
From Charles 830
From John 831
[Appendix A] What's on the Companion Disk?
ix