Cover image for Oracle9i instant PL/SQL scripts
Oracle9i instant PL/SQL scripts
Loney, Kevin.
Personal Author:
Publication Information:
New York : Osborne/McGraw-Hill, [2001]

Physical Description:
xviii, 598 pages : illustrations ; 24 cm + 1 computer optical disc (4 3/4 in.)
General Note:
Includes index.
Title Subject:


Format :


Call Number
Material Type
Home Location
Item Holds
QA76.9.D3 L665 2001 Book and Software Set Central Closed Stacks

On Order



This text provides readers with a comprehensive collection of SQL and PL/SQL scripts. It is organized into chapters by function and each script is fully documented with sample results shown. Readers can understand how the script works, learn to customize the script with confidence, and act properly on the script's results. Features include: scripts specifically for Oracle9i; a CD-ROM including major scripts from book; CD-icon with script name next to scripts that appear on the CD to allow for a quick search; a list of scripts and a brief description at the beginning of each chapter; and Programmer's Notes and Annotations that provide expert explanations on the scripts and how they can be customized.

Table of Contents

Acknowledgmentsp. xv
Introductionp. xvii
1 Oracle9i--Specific Scriptsp. 1
Database Propertiesp. 2
Determine the Database Properties in Effectp. 3
Server Parameter File (spfile)p. 5
Determine Parameters in the spfilep. 5
Native Compilation of PL/SQL Program Unitsp. 9
Determine Compilation Settings of Stored PL/SQL Unitsp. 10
Managing Undo Spacep. 13
Determine Undo Space Consumptionp. 14
Obtaining Advice About Database Cache Sizep. 15
Predicting the Physical Read Factorp. 15
Working With Nonstandard Block Sizesp. 17
Determine Non-Standard Block Sizes in Usep. 18
2 Scripts for Managing Performancep. 21
Looking Inside the SGAp. 24
Determine the SGA Sizep. 24
The Hit Ratio and the Miss Ratep. 26
Hit Ratio by Userp. 36
Buffer Usage Within the SGAp. 38
Shared SQL Area Hit Ratiop. 48
Shared Pool Structuresp. 50
Shared SQL Area User Statisticsp. 56
Estimate Space Usage by Shared SQL Objectsp. 61
SQL Statements that Use Shared Memory Improperlyp. 70
Large Pool Utilizationp. 71
Log Buffer Cache Sizep. 74
Multiblock Read Settingp. 75
Proper setting of DB_FILE_MULTIBLOCK_READ_COUNTp. 76
Enqueue Resourcesp. 77
Check Enqueue Resources Against Enqueue Waitsp. 77
Full Table Scans of Large Tablesp. 78
Determine Full Table Scans of Large Tablesp. 79
Sort Areap. 80
Determine In-Memory versus Disk Sortsp. 81
Parallel Query Server Processesp. 82
Determine Effectiveness of Parallel Query Settingp. 83
Effectiveness of Redo Copy Latchesp. 85
Contention for Redo Copy Latchesp. 85
Effectiveness of Multithreaded Serverp. 86
MultiThreaded Server Statisticsp. 86
Effectiveness of Database Writer Processp. 88
Compare DBWR to Data Block Waitsp. 88
Beyond the SGAp. 89
Datafile I/O Distributionp. 90
Parameter Settingsp. 92
General Statisticsp. 97
Generating Explain Plansp. 98
Using utlbstat/utlestatp. 106
Statspackp. 107
Autotuning with SPFILE.ORAp. 108
3 Transaction Managementp. 111
Rollback Segmentsp. 113
Monitor the Number of Rollback Segmentsp. 113
Monitor Rollback Segment Sizep. 116
Monitor Rollback Segment Extent Sizep. 118
Monitor Who Is Using Which Rollback Segmentp. 119
Obtain Transaction Information and Monitor the Size of Rollback Entries Per Transactionp. 121
Lockingp. 123
Show Sessions Involved in Lock Waitsp. 123
Generate Commands to Terminate Waiting Sessionsp. 128
Generate Commands to Terminate Sessions Holding Locksp. 131
Show the Tables Being Lockedp. 133
Show the SQL Associated with Locksp. 137
Online Redo Logsp. 142
4 Data Managementp. 147
Diagnostic Scriptsp. 148
Object Dependenciesp. 149
Foreign Key Columnsp. 152
Primary Key Columnsp. 154
Tables Without Primary Keysp. 155
Associate Primary Keys with the Corresponding Indexesp. 156
Which Objects Changed Yesterday?p. 157
All Constraints Defined for a User's Tablesp. 159
Make Sure Foreign Key Columns Are Indexedp. 161
Rows Per File (Striping Effectiveness of a Table)p. 169
Utilitiesp. 170
DELETE_COMMIT Procedurep. 171
Hexadecimal to Decimal Conversionp. 177
Count the Number of Business Days Between Two Given Datesp. 179
Generate Random Numbersp. 181
5 Object Managementp. 193
Diagnosticsp. 196
Fully Describe a Tablep. 196
List Submitted Jobsp. 213
List Running Jobsp. 217
List Invalid Objectsp. 219
Describe Tables That Use Abstract Datatypesp. 223
Utilitiesp. 231
Pinning Packagesp. 231
Revalidating Objectsp. 234
Generate Tablesp. 236
Display Data Distribution Across Table Partitionsp. 284
Generate Indexesp. 287
Display Data Distribution Across Index Partitionsp. 324
Generate Triggersp. 327
Generate Viewsp. 341
Generate Synonymsp. 352
Generate Proceduresp. 358
Generate Functionsp. 365
Generate Packagesp. 372
Generate Librariesp. 381
Generate Sequencesp. 384
Generate Database Linksp. 392
Generate Snapshots/Materialized Viewsp. 401
Generate Snapshots Logs/Materialized Views Logsp. 413
Generate Abstract Datatypesp. 422
Generate Object Grantsp. 433
6 Space Managementp. 449
Diagnosticsp. 451
Space Available in the Databasep. 451
Tablespace Fragmentationp. 456
Which Files Can Extendp. 471
Free Space Within the Databasep. 475
Space Usage Within the Database by Userp. 477
Default Storage for Objectsp. 486
Segment Space Usagep. 493
Extents Showing Increasing Extent Size and Locationp. 503
Temporary Segments Sizep. 506
Segments Not Set to maxextents UNLIMITEDp. 507
Database Block Mapp. 512
Utilitiesp. 520
Forcing a Coalesce of Free Space in a Tablespacep. 520
How Long Is a LONG?p. 523
How Long Is a LOB?p. 526
Measuring Row Lengthp. 528
Sizing a Tablep. 537
Estimating Index Sizep. 542
7 User Managementp. 547
Diagnostic Scriptsp. 548
Users Who Own Objectsp. 549
Types of Objects Ownedp. 549
Users' System and Role Privilegesp. 551
Users' Object Privilegesp. 555
Active Roles and Privilegesp. 558
How Many Users Have Logged In?p. 558
User Session Informationp. 560
User Resource Quotasp. 560
Utilitiesp. 562
Re-creating Usersp. 562
Generate Grant Commandsp. 566
Generate Rolesp. 567
Change the Default and Temporary Tablespacesp. 570
Lock and Unlock Database User Accountsp. 571
Indexp. 575