Oracle9i Database Concepts

Contents......Page 3
Send Us Your Comments......Page 21
Preface......Page 23
Organization......Page 24
Related Documentation......Page 28
Conventions......Page 29
Documentation Accessibility......Page 31
Part I What Is Oracle?......Page 33
1 Introduction to the Oracle Server......Page 35
Introduction to Databases and Information Management......Page 36
Oracle9i Real Application Clusters: Multiple Instance Systems......Page 38
Tablespaces......Page 39
Schemas and Schema Objects......Page 40
Description of Data Blocks, Extents, and Segments......Page 41
Datafiles......Page 42
Redo Log Files......Page 43
Structured Query Language (SQL)......Page 44
Data Utilities......Page 45
Memory Structures......Page 46
System Global Area......Page 48
User (Client) Processes......Page 49
Background Processes......Page 50
An Example of How Oracle Works......Page 53
The Relational Model......Page 55
Type Inheritance......Page 56
Description of Views......Page 58
Materialized Views......Page 59
Synonyms......Page 60
Clusters and Hash Clusters......Page 61
Database Links......Page 64
Concurrency......Page 65
Read Consistency, Undo Records, and Transactions......Page 66
Locking Mechanisms......Page 67
Quiesce Database......Page 68
Startup and Shutdown Operations......Page 69
2 Oracle Server Features......Page 71
The Server......Page 72
Distributed Databases......Page 73
Two-Phase Commit......Page 74
Multitier Materialized Views......Page 75
Oracle and Oracle Net Services......Page 76
Heterogeneous Services......Page 77
Synchronous Communication......Page 78
Advantages of Oracle Advanced Queuing......Page 79
Data Warehousing Overview......Page 80
SQL Statements......Page 81
Transactions......Page 83
Commit and Roll Back Transactions......Page 84
PL/SQL—Procedural Language Extension to SQL......Page 85
Database Triggers and Information Management......Page 86
Data Integrity......Page 87
Keys......Page 88
Database Security Overview......Page 89
Security Mechanisms......Page 90
Privileges......Page 91
Roles......Page 92
Profiles and Resource Limits......Page 93
Selective Auditing of User Actions......Page 94
Types of Failures......Page 95
Structures Used for Recovery......Page 97
The Redo Log......Page 98
Undo Records......Page 99
Basic Recovery Steps......Page 100
Transparent Application Failover (TAF)......Page 101
Instance Recovery Tuning......Page 102
Use of SQL Statements to Initiate Checkpoints......Page 103
Oracle8 Automated Standby Database......Page 104
What Kind of High Availability: Fast Failover or Disaster Protection?......Page 105
Logical Standby Database......Page 107
Oracle9i LogMiner™......Page 108
How the Data Guard Components Work Together......Page 109
Disaster Recovery Server and DRMON......Page 111
Limiting Data Loss......Page 112
LogMiner SQL-Based Log Analyzer Overview......Page 113
Part II Database Structures......Page 115
3 Data Blocks, Extents, and Segments......Page 117
Introduction to Data Blocks, Extents, and Segments......Page 118
Data Blocks Overview......Page 119
Header (Common and Variable)......Page 120
Free Space......Page 121
Availability and Compression of Free Space in a Data Block......Page 122
Row Chaining and Migrating......Page 123
The PCTFREE Parameter......Page 124
The PCTUSED Parameter......Page 125
How PCTFREE and PCTUSED Work Together......Page 126
Determine the Number and Size of Extents......Page 129
How Extents Are Allocated......Page 130
Allocating Extents in Dictionary-Managed Tablespaces......Page 131
When Extents Are Deallocated......Page 132
Extents in Materialized Views and Their Logs......Page 133
Segments Overview......Page 134
Introduction to Data Segments......Page 135
Operations that Require Temporary Segments......Page 136
How Temporary Segments Are Allocated......Page 137
Undo Mode......Page 138
Undo Retention Control......Page 139
Introduction to Rollback Segments......Page 140
When Rollback Information Is Required......Page 141
Transactions and Rollback Segments......Page 142
How Extents Are Deallocated from a Rollback Segment......Page 146
Oracle Instances and Types of Rollback Segments......Page 147
Rollback Segment States......Page 149
Deferred Rollback Segments......Page 151
High Water Mark......Page 152
4 Tablespaces, Datafiles, and Control Files......Page 153
Introduction to Tablespaces, Datafiles, and Control Files......Page 154
Allocate More Space for a Database......Page 155
The Data Dictionary......Page 159
Creation of Undo Tablespaces......Page 160
Default Temporary Tablespace......Page 161
Multiple Tablespace Usage......Page 162
Dictionary-Managed Tablespaces......Page 163
Segment Space Management in Locally Managed Tablespaces......Page 164
Nonstandard Block Sizes......Page 165
When a Tablespace Goes Offline......Page 166
Read-Only Tablespaces......Page 167
Sort Segments......Page 168
Transport of Tablespaces between Databases......Page 169
Datafiles Overview......Page 170
Offline Datafiles......Page 171
Control File Contents......Page 172
Multiplexed Control Files......Page 174
5 The Data Dictionary......Page 175
Introduction to the Data Dictionary......Page 176
How the Data Dictionary Is Used......Page 177
Other Programs and the Data Dictionary......Page 178
Views with the Prefix USER......Page 179
The DUAL Table......Page 180
Database Object Metadata......Page 181
Part III The Oracle Instance......Page 183
6 Database and Instance StartupandShutdown......Page 185
Introduction to an Oracle Instance......Page 186
Connection with Administrator Privileges......Page 187
An Example of a Parameter File......Page 188
Globalization Support Parameters......Page 189
Forced Startup in Abnormal Situations......Page 190
How a Database Is Mounted with Oracle9i Real Application Clusters......Page 191
How a Clone Database Is Mounted......Page 192
Undo Space Acquisition and Management......Page 193
Open a Database in Read-Only Mode......Page 194
Unmount a Database......Page 195
Abnormal Instance Shutdown......Page 196
7 Distributed Processing......Page 197
Multitier Architecture......Page 198
Application Servers......Page 199
Distributed Processing Overview......Page 200
Internet Scalability......Page 204
The Listener......Page 205
Oracle Internet Directory......Page 206
8 Memory Architecture......Page 209
Introduction to Oracle Memory Structures......Page 210
System Global Area (SGA) Overview......Page 211
Dynamic SGA......Page 212
Organization of the Database Buffer Cache......Page 214
Size of the Database Buffer Cache......Page 215
Multiple Buffer Pools......Page 217
The Shared Pool......Page 218
PL/SQL Program Units and the Shared Pool......Page 219
Allocation and Reuse of Memory in the Shared Pool......Page 220
The Large Pool......Page 222
SGA Starting Address......Page 223
Private SQL Area......Page 224
SQL Work Areas......Page 226
PGA Memory Management for Dedicated Mode......Page 227
Dedicated and Shared Servers......Page 228
Software Code Areas......Page 229
9 Process Architecture......Page 231
Types of Processes......Page 232
Connections and Sessions......Page 234
Background Processes......Page 235
Database Writer Process (DBWn)......Page 238
Log Writer Process (LGWR)......Page 239
System Monitor Process (SMON)......Page 241
Job Queue Processes......Page 242
Archiver Processes (ARCn)......Page 243
Trace Files and the Alert Log......Page 244
Shared Server Architecture......Page 245
Dispatcher Request and Response Queues......Page 247
Shared Server Processes (Snnn)......Page 249
Restricted Operations of the Shared Server......Page 250
Dedicated Server Configuration......Page 251
The Program Interface......Page 252
Program Interface Drivers......Page 253
Communications Software for the Operating System......Page 254
10 Database Resource Management......Page 255
Introduction to the Database Resource Manager......Page 256
Database Resource Manager Terms......Page 257
Example of a Simple Resource Plan......Page 258
Example of Resource Control......Page 259
Database Integration......Page 260
Resource Plans and Resource Consumer Groups......Page 261
Dynamic......Page 262
Groups of Resource Plans......Page 263
CPU Method......Page 265
Automatic Consumer Group Switching......Page 266
CPU Resource Allocation......Page 267
CPU Allocation Rules......Page 269
Levels and Priorities......Page 270
Interaction with Operating-System Resource Control......Page 271
Dynamic Reconfiguration......Page 272
Part IV The Object-Relational DBMS......Page 273
11 Schema Objects......Page 275
Introduction to Schema Objects......Page 276
Tables......Page 278
How Table Data Is Stored......Page 279
Row Format and Size......Page 280
Column Order......Page 282
Default Values for Columns......Page 283
Default Value Insertion and Integrity Constraint Checking......Page 284
Partitioned Tables......Page 285
Temporary Tables......Page 286
Parent and Child Transactions......Page 287
Data Loading with External Tables......Page 288
Views......Page 289
How Views Are Used......Page 291
Use of Indexes Against Views......Page 292
Dependencies and Views......Page 293
Object Views in an Oracle Database......Page 294
Materialized Views......Page 295
Define Constraints on Views......Page 296
Materialized View Logs......Page 297
Dimensions......Page 298
The Sequence Generator......Page 299
Synonyms......Page 300
Indexes......Page 301
Composite Indexes......Page 303
Indexes and Nulls......Page 304
Uses of Function-Based Indexes......Page 305
Dependencies of Function-Based Indexes......Page 306
Format of Index Blocks......Page 307
The Internal Structure of Indexes......Page 308
Advantages of B-tree Structure......Page 310
Index Unique Scan......Page 311
Index Range Scan......Page 313
Index Range Scan Descending......Page 316
Prefix and Suffix Entries......Page 318
Uses of Key Compression......Page 319
Reverse Key Indexes......Page 320
Benefits for Data Warehousing Applications......Page 321
Cardinality......Page 322
Bitmap Index Example......Page 323
Bitmap Indexes and Nulls......Page 325
Bitmap Join Indexes......Page 326
Four Join Models......Page 327
Creation of a Bitmap Join Index......Page 330
Index-Organized Tables......Page 331
Benefits of Index-Organized Tables......Page 332
Index-Organized Tables with Row Overflow Area......Page 333
Mapping Table......Page 334
B-tree Indexes on UROWID Columns for Heap- and Index-Organized Tables......Page 335
Clusters......Page 336
Hash Clusters......Page 338
12 Partitioned Tables and Indexes......Page 341
Introduction to Partitioning......Page 342
Partitioned Tables......Page 344
Partitioning Methods......Page 345
Range Partitioning......Page 347
List Partitioning Example......Page 348
Hash Partitioning Example......Page 349
Composite Partitioning Example......Page 350
Partitioned Indexes......Page 351
Global Partitioned Indexes......Page 352
Maintenance of Global Partitioned Indexes......Page 353
Global Nonpartitioned Indexes......Page 354
Example of a Global Partitioned Index......Page 355
Using Partitioned Indexes in DSS Applications......Page 356
Partition Pruning......Page 357
Partition-wise Joins......Page 358
Parallel DML......Page 359
13 System-Provided Datatypes......Page 361
Introduction to Oracle Datatypes......Page 362
CHAR Datatype......Page 363
Length Semantics for Character Datatypes......Page 364
NVARCHAR2......Page 366
LONG Datatype......Page 367
NUMBER Datatype......Page 368
Internal Numeric Format......Page 369
DATE Datatype......Page 370
Use of Julian Dates......Page 371
Daylight Savings Support......Page 372
Example:......Page 373
LOB Datatypes......Page 374
CLOB and NCLOB Datatypes......Page 375
RAW and LONG RAW Datatypes......Page 376
The ROWID Pseudocolumn......Page 377
Extended Rowids......Page 378
Restricted Rowids......Page 379
Examples of Rowid Use......Page 380
How Rowids Are Used......Page 381
Comparison of Logical Rowids with Physical Rowids......Page 382
Guesses in Logical Rowids......Page 383
ANSI, DB2, and SQL/DS Datatypes......Page 384
XMLType with Structured Storage......Page 385
XMLIndex......Page 386
Data Conversion......Page 387
14 User-Defined Datatypes......Page 389
Complex Data Model Example......Page 390
User-Defined Datatype Categories......Page 391
Purchase Order Example......Page 392
Types of Methods......Page 393
Object Identifiers......Page 396
REFs......Page 397
VARRAYs......Page 399
Nested Tables Description......Page 400
Example of Creating a NOT FINAL Object Type......Page 401
Why Have User-Defined Aggregate Functions?......Page 402
How Do Aggregate Functions Work?......Page 403
Application Interfaces......Page 404
Pro*C/C++......Page 405
Dynamic Creation and Access of Type Descriptions......Page 406
OCI......Page 407
SQLJ......Page 408
Datatype Evolution......Page 409
15 Object Views......Page 411
Advantages of Object Views......Page 412
How Object Views Are Defined......Page 413
Use of Object Views......Page 414
Updates of Nested Table Columns in Views......Page 415
Example of Creating a View Hierarchy......Page 416
Part V Data Access......Page 419
16 SQL, PL/SQL, and Java......Page 421
Introduction to Structured Query Language......Page 422
Data Manipulation Language Statements Description......Page 423
Data Definition Language Statements Description......Page 424
Embedded SQL Statements Description......Page 425
Cursors Overview......Page 426
Shared SQL......Page 427
SQL Processing......Page 428
Overview of SQL Statement Execution......Page 429
DML Statement Processing......Page 431
Stage 2: Parse the Statement......Page 432
Stage 5: Bind Any Variables......Page 433
Stage 7: Execute the Statement......Page 434
Control of Transactions......Page 435
The Optimizer......Page 436
Editing Stored Outlines......Page 437
How PL/SQL Executes......Page 438
Cursors......Page 440
Stored Procedures......Page 441
PL/SQL Server Pages......Page 442
PL/SQL Program Units......Page 443
Stored Procedures and Functions......Page 444
Packages......Page 446
Security with Definer-Rights Procedures......Page 448
Memory Allocation......Page 449
Anonymous PL/SQL Blocks Compared with Stored Procedures......Page 450
External Procedures......Page 451
Parallel Execution of Table Functions......Page 452
Benefits of Packages......Page 453
Java Overview......Page 454
Attributes......Page 455
Interfaces......Page 456
The Java Virtual Machine (JVM)......Page 457
Package DBMS_JAVA......Page 458
17 Transaction Management......Page 461
Introduction to Transactions......Page 462
Statement Execution and Transaction Control......Page 463
Statement-Level Rollback......Page 464
Oracle and Transaction Management......Page 465
Commit Transactions......Page 466
Rollback of Transactions......Page 467
Savepoints In Transactions......Page 468
How Transactions Are Named......Page 469
The Two-Phase Commit Mechanism......Page 470
Discrete Transaction Management......Page 471
Autonomous PL/SQL Blocks......Page 472
Transaction Control Statements in Autonomous Blocks......Page 473
18 Triggers......Page 475
Introduction to Triggers......Page 476
Some Cautionary Notes about Triggers......Page 478
Triggers compared with Declarative Integrity Constraints......Page 479
Parts of a Trigger......Page 480
The Triggering Event or Statement......Page 481
Trigger Action......Page 482
Statement Triggers......Page 483
AFTER Triggers......Page 484
Trigger Type Combinations......Page 485
Sample Package and Trigger for SAL Table......Page 486
Modify Views......Page 487
Example of an INSTEAD OF Trigger......Page 488
Triggers on System Events and User Events......Page 490
Event Attributes......Page 491
User Events......Page 492
Trigger Execution......Page 493
The Execution Model for Triggers and Integrity Constraint Checking......Page 494
Data Access for Triggers......Page 496
Dependency Maintenance for Triggers......Page 498
19 Dependencies Among Schema Objects......Page 499
Introduction to Dependency Issues......Page 500
Resolution of Schema Object Dependencies......Page 502
Views and Base Tables......Page 503
Program Units and Referenced Objects......Page 504
Security Authorizations......Page 505
DETERMINISTIC Functions......Page 506
Dependency Management and Nonexistent Schema Objects......Page 507
Management of Local Dependencies......Page 509
Dependencies Among Local and Remote Database Procedures......Page 510
Dependencies Among Other Remote Schema Objects......Page 511
Dependencies of Applications......Page 512
Part VI Parallel SQL and Direct-Load INSERT......Page 513
20 Parallel Execution of SQL Statements......Page 515
When to Implement Parallel Execution......Page 516
When Not to Implement Parallel Execution......Page 517
How Parallel Execution Works......Page 518
Parallelized SQL Statements......Page 519
Parallelism Between Operations......Page 520
Degree of Parallelism......Page 522
Parallel Query Intra- and Inter-Operation Example......Page 523
DDL Statements that can be Parallelized......Page 526
SQL*Loader......Page 527
Parallel DML......Page 528
21 Direct-Path INSERT......Page 531
Advantages of Direct-Path INSERT......Page 532
Serial and Parallel Direct-Path INSERT......Page 533
Direct-Path INSERT and Logging Mode......Page 534
Index Maintenance......Page 535
Locking Considerations......Page 536
Part VII Data Protection......Page 537
22 Data Concurrency and Consistency......Page 539
Preventable Phenomena and Transaction Isolation Levels......Page 540
Overview of Locking Mechanisms......Page 541
Multiversion Concurrency Control......Page 542
Statement-Level Read Consistency......Page 543
Read Consistency with Oracle9i Real Application Clusters......Page 544
Set the Isolation Level......Page 545
Serializable Isolation......Page 546
Comparison of Read Committed and Serializable Isolation......Page 547
Transaction Set Consistency......Page 548
Referential Integrity......Page 549
Read Committed Isolation......Page 550
Serializable Isolation......Page 551
Quiesce Database......Page 553
How Oracle Locks Data......Page 554
Lock Duration......Page 555
Deadlocks......Page 556
Deadlock Detection......Page 557
Types of Locks......Page 558
Row Locks (TX)......Page 559
Table Locks (TM)......Page 560
DML Locks Automatically Acquired for DML Statements......Page 564
Share DDL Locks......Page 567
Latches and Internal Locks......Page 568
Explicit (Manual) Data Locking......Page 569
Examples of Concurrency under Explicit Locking......Page 570
Flashback Query......Page 578
Easy Manageability......Page 579
Account Balances......Page 580
Packaged Applications......Page 581
23 Data Integrity......Page 583
Introduction to Data Integrity......Page 584
Referential Integrity Rules......Page 585
Integrity Constraints Description......Page 586
Advantages of Integrity Constraints......Page 587
Superior Performance......Page 588
NOT NULL Integrity Constraints......Page 589
UNIQUE Key Integrity Constraints......Page 590
Unique Keys......Page 591
UNIQUE Key Constraints and Indexes......Page 592
PRIMARY KEY Integrity Constraints......Page 593
PRIMARY KEY Constraints and Indexes......Page 594
Referential Integrity Constraints......Page 595
Self-Referential Integrity Constraints......Page 596
Nulls and Foreign Keys......Page 597
Actions Defined by Referential Integrity Constraints......Page 598
Concurrency Control, Indexes, and Foreign Keys......Page 599
The Mechanisms of Constraint Checking......Page 603
Constraint Attributes......Page 606
Unique Constraints and Indexes......Page 607
Constraint States......Page 608
Constraint State Modification......Page 609
24 Controlling Database Access......Page 611
Schemas, Database Users, and Security Domains......Page 612
User Authentication......Page 613
Third Party-Based Authentication Technologies......Page 614
Public Key Infrastructure-Based Authentication......Page 615
Remote Authentication......Page 617
Account Locking......Page 618
Password Complexity Verification......Page 619
Clients, Application Servers, and Database Servers......Page 620
Authentication by the Secure Socket Layer Protocol......Page 623
Authentication of Database Administrators......Page 624
Tablespace Access and Quotas......Page 626
The User Group PUBLIC......Page 627
User Resource Limits and Profiles......Page 628
CPU Time......Page 629
Other Resources......Page 630
Determine Values for Resource Limits of a Profile......Page 631
Concurrent Usage Licensing......Page 632
Named User Licensing......Page 634
25 Privileges, Roles, and Security Policies......Page 635
System Privileges......Page 636
Schema Object Privileges......Page 637
Who Can Grant Schema Object Privileges?......Page 638
Data Definition Language Operations......Page 639
Privileges Required to Create Views......Page 640
Procedure Security......Page 641
Procedure Execution and Security Domains......Page 642
System Privileges Needed to Create or Alter a Procedure......Page 643
Packages and Package Objects......Page 644
Method Execution Model......Page 646
Privileges Required to Create Types and Tables Using Types Example......Page 647
Privileges on Type Access and Object Access......Page 649
Type Dependencies......Page 650
Introduction to Roles......Page 651
Common Uses for Roles......Page 652
The Mechanisms of Roles......Page 653
Who Can Grant or Revoke Roles?......Page 654
Anonymous Blocks with Invoker Rights......Page 655
Data Definition Language Statements and Roles......Page 656
Roles in a Distributed Environment......Page 657
Dynamic Predicates......Page 658
Security Policy Example......Page 659
Application Context......Page 660
Creation of Secure Application Roles......Page 661
26 Auditing......Page 663
Types of Auditing......Page 664
Audit Records and the Audit Trail......Page 665
When Are Audit Records Generated?......Page 666
Events Always Audited to the Operating System Audit Trail......Page 667
Audit to the Operating System Audit Trail......Page 668
Privilege Auditing......Page 669
Schema Object Audit Options for Views and Procedures......Page 670
Fine-Grained Auditing......Page 671
Successful and Unsuccessful Statement Executions Auditing......Page 673
BY SESSION......Page 674
BY ACCESS......Page 675
Audit in a Multitier Environment......Page 676
A Operating System Specific Information......Page 679
Glossary......Page 683
Index......Page 699