Oracle Development Unleashed (3rd Edition)
Page 3
Overview
Introduction xlv
Part I Oracle Concepts
1 Oracle Company Overview 3
2 What Is an RDBMS? 25
3 Oracle, Client/Server, and Enterprise Computing 35
4 Overview of the Oracle Architecture 45
Part II Oracle Systems and Servers
5 Personal Oracle7, Oracle Lite 83
6 Oracle Workgroup Server 103
7 Oracle8 Server 123
8 Oracle7 Enterprise Server 157
9 Oracle Web Application Server 3.0 179
10 Electronic Commerce Server 203
Part III Oracle Tools and Utilities
11 SQL 223
12 PL/SQL ”A Procedural Language 257
13 SQL*Plus 289
14 Server Manager 317
15 Enterprise Manager 343
16 SQL*Loader 363
17 Import and Export 389
Part IV Database Administration
18 Installation 417
19 Managing the Database 427
20 Managing Disk Space 469
21 Managing Users 485
22 Backup and Recovery 509
23 Performance Tuning and Optimization 545
24 Database Security 587
Page 4
Part V Developing Applications
25 Designing a Database 623
26 Object-Oriented Programming with Packages 649
27 Transaction Processing 671
28 Enforcing Integrity 693
29 Application Security 717
Part VI Designer/2000
30 Introduction to and Installation of Designer/2000 737
31 Repository Administration 755
32 Systems Modeling and Design 775
33 Generating Applications 811
Part VII Developer/2000
34 Introduction to Developer/2000 831
35 Oracle Forms 839
36 Oracle Reports 877
37 Oracle Graphics 915
38 Developer/2000 Integration 937
39 A Preview of Developer/2000 Release 2.0 947
Part VIII Oracle Objects for OLE
40 Introduction to Oracle Objects for OLE 957
41 Oracle OLE Automation Server 963
42 The Oracle Data Control 983
Part IX Discoverer 3.0
43 Introducing Oracle Discoverer 991
44 Using Discoverer 3.0 1007
Part X Other Oracle Development Tools
45 Programmer/2000 1031
Page 5
Part XI Integrating Oracle and Third-Party Tools
46 Using PowerBuilder 1065
47 Using SQLWindows 1077
48 Using Visual Basic 1085
49 Using Delphi 1097
50 Using the AIS Web Developer Suite 1109
Part XII Oracle and the Web
51 Putting Information on the Web 1121
52 Oracle Web Programming with Java and Perl 1145
53 Web Database Connectivity 1183
54 Putting an Application on the Web 1217
Part XIII Advanced Topics
55 Networking 1243
56 Parallel Processing 1273
57 ODBC 1311
58 Data Warehouses and Data Marts 1335
Page 6
Contents
Introduction xlv
Part I Oracle Concepts
- Oracle Company Overview 3
Databases Before Oracle 4
The Hierarchical Database Model 4
The Network Database Model 5
Relational Databases, So Help Me Codd 5
The Birth of Oracle 5
The Relational Database Wars ”A Never-Ending Story 6
Oracle Today and Tomorrow 7
Oracle's New Baby: Network Computing Architecture 8
The Goals of the NCA 8
What Is the Difference Between the Network Computer and What We Have Today? 9
What Network Computers Offer 10
The Challenges Facing NCA 10
Network Computer, Inc. 11
America's Promise, Oracle's Promise 11
CORBA 11
Introducing the Cartridge 13
The Oracle Enterprise Manager 13
The Universal Application Server 13
Oracle Cartridge Solutions Network Fund 14
The Universal Data Server 14
The Oracle Alliance 14
Oracle Alliance Value Service 15
Oracle Support 16
The Support Olympics: Bronze, Silver, or Gold? 16
Customer Support Identification Number 17
Crossing the TAR Pits 17
Oracle's Automated Support 18
The Oracle Developer Programme 20
Back to School: Oracle Education 20
Traditional Classroom Setting 20
The Oracle Channel 21
The Oracle Masters Programs 21
Oracle Consulting 22
Oracle Sales 23
Oracle on the Web 23
Summary 24
Page 7
- What Is an RDBMS? 25
The Relational Database Model 26
Hierarchical and Network Models 26
Elements of the Relational Model 27
RDBMS Components 30
The RDBMS Kernel 30
The Data Dictionary 31
Nonprocedural Data Access (SQL) 32
Databases and the Web 33
Summary 34
- Oracle, Client/Server, and Enterprise Computing 35
An Overview of Client/Server Computing 36
Databases in a Client/Server Architecture 37
Oracle and Client/Server Computing 38
The Oracle8 RDBMS Server 38
Networking Products 40
Client/Server Development Tools 41
Oracle Directions 43
Summary 44
- Overview of the Oracle Architecture 45
Computer Architecture Fundamentals 46
Memory 46
Processes/Programs 46
File Systems 46
Network 46
Global View of the Oracle Architecture 47
What Is a Database? 47
Oracle Files 49
System and User Processes 49
Memory 49
Network Software and SQL*Net 50
Oracle Files 51
Database Files 52
Control Files 52
Redo Logs 52
Other Supporting Files 53
System and User Processes 53
Mandatory System Processes 53
Optional System Processes 54
User Processes 56
Page 8
- Personal Oracle7, Oracle Lite 83
Overview of Personal Oracle7 and Oracle Lite 84
Personal Oracle7 and Oracle Lite Components 85
System Requirements 90
Hardware Requirements for Personal Oracle7 90
Hardware Requirements for Personal Oracle Lite 91
Software Requirements 91
- Oracle Workgroup Server103
- Oracle8 Server123
- Oracle7 Enterprise Server 157
- Oracle Web Application Server 3.0 179
- Electronic Commerce Server 203
- SQL 223
- PL/SQL ”A Procedural Language 257
- SQL*Plus 289
- Server Manager 317
- Enterprise Manager 343
- SQL*Loader 363
- Import and Export 389
- Installation 417
- Managing the Database 427
- Managing Disk Space 469
- Managing Users 485
- Backup and Recovery 509
- Performance Tuning and Optimization 545
- Database Security 587
- Designing a Database 623
Requirements Definition 624
The Logical Model 627
The Normalization Process 631
The Physical Model 634
Column Attributes and DDL 634
Performance Considerations 638
Capacity Planning 647
Summary 648
- Object-Oriented Programming with Packages 649
The Package Specification 650
The Package Body 652
Package Variables and Initialization 652
Overloading 654
Retrieving Results 656
Exception Handling 657
Package Privileges 659
Accessing Oracle Packages from Client Applications 661
Object-Oriented Concepts 663
Summary 668
- Transaction Processing 671
Sessions Versus Transactions 672
Commits, Rollbacks, and Savepoints 674
Transaction Control Statements 676
Types of Transactions 676
Concurrent Transactions 676
Discreet Transactions 676
Distributed Transactions 677
In-Doubt Transactions 677
Normal Transactions 677
Read-Only Transactions 677
Remote Transactions 678
- Enforcing Integrity 693
- Application Security 717
- Introduction to and Installation of
Designer/2000 737
Business Process Reengineering 738
Information Engineering 740
Rapid Application Development 740
Legacy-Led Development 741
Installing Designer/2000 742
System Requirements 743
Client 743
Server 744
Back Up, Back Up, Back Up 744
Upgrading to Designer/2000 744
Oracle CASE 5.1 745
Designer/2000 6.0.0 to 6.0.4 745
Designer/2000 6.0.5 to 6.06 745
Client Installation 745
Complete Installation 747
Default Installation 749
Custom Installation/Deinstallation 750
Server Installation 750
Summary 753
- Repository Administration 755
The Repository Administration Utility 756
The Repository Management Screen 757
The Privileges Dialog Box 758
The Tablespace Analysis Screen 758
The Maintain Users Screen 759
The User Extensibility Screen 760
The User Extensions Section 762
The Matrix Diagrammer 762
The Repository Object Navigator 764
Startup 764
Navigating the Hierarchy Tree 765
The Property Sheet 766
The Toolbar 767
The RON Menus 768
Repository Reports 772
Summary 772
- Systems Modeling and Design 775
Creating, Opening, and Saving a Diagram 776
Starting Up 777
- Generating Applications 811
- Introduction to Developer/2000 831
What Is Developer/2000? 832
Oracle Forms 4.5 832
Oracle Reports 2.5 832
Oracle Graphics 2.5 833
Why Use Developer/2000? 833
Features of Developer/2000 834
Common Tool Set Features 835
The Object Navigator 835
Database Connectivity 837
Module Access 838
Summary 838
- Oracle Forms 839
The Oracle Forms Environment 840
The Object Navigator 840
Object Properties Sheets 843
The Layout Editor 844
Customizing the Forms Environment 846
Creating New Forms Modules 848
Building a Simple Default Form 848
Using Boilerplate Text and Graphics to Enhance Forms
Applications 850
Customizing Forms with Properties 853
Implementing Triggers and Program Units 854
Using Record Groups and Lists of Values for Data Validation 857
Using Relationships to Link Separate Blocks in a Form 860
Mouse Events, Timers, and Other Advanced Widgets 862
Working with the Mouse 862
Working with Alerts 865
Using Timers for Event Control 866
Considerations for Multiple Form Applications 869
Techniques for Dynamic Runtime Applications 870
Working with Menus 872
Libraries, Object Classes, and Visual Attributes 874
Quality Considerations for Oracle Forms 874
Summary 876
- Oracle Reports 877
The Oracle Reports 2.5 Environment 878
The Data Model Editor 878
The Layout Editor 879
Initializing the Reports Environment 882
- Oracle Graphics 915
- Developer/2000 Integration 937
- A Preview of Developer/2000 Release2.0 947
- Introduction to Oracle Objects for OLE 957
Components of OO4O 958
The In-Process Automation Server 958
Oracle C++ Class Library 960
Oracle Data Control 960
OO4O and the Open Database Connectivity API 960
Host Environments 961
Summary 961
- Oracle OLE Automation Server 963
Background 964
What Is an OLE Automation Server? 964
Getting Started 965
Accessing the Automation Server 966
Connecting to Oracle Database Servers 966
Executing SQL Statements and PL/SQL Blocks 966
The CreateDynaset Method 967
The CreatePlsqlDynaset Method 968
The ExecuteSQL and CreateSQL Methods 969
- The Oracle Data Control 983
- Introducing Oracle Discoverer 991
Multidimensional Database Primer 992
A Semi-Formal Definition of OLAP 997
Multidimensional Storage Strategies ”A Star Schema
Is Born 999
Discoverer as a Tool for Data Warehousing 1001
Discoverer 3.0 ”Features, Functions, and Benefits 1002
Summary 1005
- Using Discoverer 3.0 1007
Creating the Tutorial and Video Databases 1009
Creating the OLAP Database 1010
Creating the Sample Database 1011
Mapping Data From a Database into Discoverer:
The Load Wizard 1012
Moving Around in the Land of Folders and Items 1014
The Administration Tasklist: Don't Enter the
Dimensions Without It! 1014
Create Business Areas (Mandatory) 1015
Edit Item Properties 1016
Create Joins 1016
Create Calculated Items 1017
Create Conditions 1017
- Programmer/2000 1031
Oracle Precompilers 1032
Why Precompile Source Code? 1032
General Precompiler Features 1032
Precompiler Languages 1033
ANSI/IO Standards 1033
Precompiler Options 1034
How to Precompile a Program 1035
Conditional Precompiling 1036
Embedded SQL Host Programs 1037
Basics of a Host Program 1037
Naming Conventions 1037
Embedded SQL 1038
PL/SQL Blocks 1038
Host Program Requirements 1040
Data Declaration Area 1040
Data Manipulation Area 1044
Error Handling 1047
General Guidelines 1047
SQLCA Variables 1047
The WHENEVER Statement 1049
Indicator Variables 1050
Host Arrays 1051
Dynamic SQL 1052
Coding Methods for Dynamic SQL Statements 1055
Method One 1055
Method Two 1056
Method Three 1056
Method Four 1056
- Using PowerBuilder 1065
Connecting to the Database 1066 Communicating with the Database 1068 Summary 1074
- Using SQLWindows 1077
Connecting to the Database 1078 Communicating with the Database 1079 Summary 1083
- Using Visual Basic 1085
Third-Party Products That Decrease Development Time
and Add Value 1086 Immediate Benefits to RAF Users 1086 Rapid Application Development 1086 RAF Courses 1087 Connecting to the Database 1087 Summary 1096
- Using Delphi 1097
Configuring a Data Source 1098 Communicating with the Database 1100 Summary 1106
- Using the AIS Web Development Suite 1109
Common Problems When Building Java Database
Applications 1110 Application Architecture 1111 Architecture Building Blocks 1112 Features of the Architecture 1112 Tools 1113 Summary 1117
- Putting Information on the Web 1121
The Rocket That Launched the Internet 1122
The Birth of the Web 1122
Why Put Information on the Web ”Benefits and Pitfalls 1125
Benefits 1125
Pitfalls 1127
HTML 1127
The Basics 1127
Making Your HTML Fancier 1130
Creating Links 1130
HTML Editors 1132
HTML Style and Design Considerations 1133
The Medium Is the Message 1137
The Role of the Browser 1138
HTML and Oracle's Web Server 1139
Oracle's ConText Product 1141
VRML 1142
NCA and VRML 1144
Summary 1144
- Oracle Web Programming with Java
and Perl 1145
Java Basics 1146
The Java Object Model 1146
The Java Language 1148
Advantages of Using Java 1155
CORBA Fundamentals 1156
The CORBA Object Model 1156
The CORBA Architecture 1157
Inter-ORB Protocol 1159
Interface Definition Language (IDL) 1161
Developing Java-Based CORBA Services 1166
IDL-to-Java Mapping 1166
Generating Java Source from IDL 1170
Developing the Implementations 1171
Binding Server Objects in the Name Service 1171
Accessing Objects from Client Applications 1173
Design Considerations 1174
Communicating with Oracle 1176
CGI Programming with Perl 1176
What Are CGI Scripts? 1177
Request Methods 1178
- Web Database Connectivity 1183
- Putting an Application on the Web 1217
- Networking 1243
A Quick and Dirty Introduction to Networking 1245
An Overview of Oracle Networking 1248
Host/Terminal Connections 1249
Client/Server Connections 1250
Web/Internet Connections 1254
Database-to-Database Connections 1256
SQL*Net 1 1259
SQL*Net 2 1263
Net/3 1264
ODBC, JDBC, and OLE 1265
Other Middleware Vendors 1265
Sample Configurations 1267
Network Development Tips 1270
Summary 1272
- Parallel Processing 1273
Understanding the Requirements for Parallel Processing 1274
Operating System Impact in Parallel Processing 1275
New World Processing 1275
Leveraging Parallel Processing Platforms 1276
Comparing Parallel Processing to Mainframes 1279
Test Results 1279
Oracle Server Scalable Parallel Architecture for Open
Data Warehousing 1280
Parallel Hardware Systems 1280
The Shared-Nothing Approach 1281
The Shared-Disk Approach 1282
What the Real World Needs 1283
Oracle Parallel Architecture: An Overview 1284
Oracle Parallel Server Technology 1284
Oracle Parallel Query Technology 1285
Dynamic Parallel Execution: Key Elements 1285
- ODBC 1311
- Data Warehouses and Data Marts 1335
- Supports large user communities. It has realized the need to support large user communities via the World Wide Web. Connect time as well as memory requirements have drastically been reduced. Sessions can be multiplexed, allowing a single server to maintain thousands of simultaneous connections.
- Supports star queries. Bitmap indices and support for star queries have been enhanced.
- Allows for parallel enhancements, support for parallel insert, update, and delete. These new features add to the parallel query feature that was available in version 7.
- Provides table partitioning. Allows for striping data across multiple tablespaces based on a logical partition ranges. This feature can drastically reduce I/O and contention.
- Supports replication. Advanced asynchronous replication of data between two or more computers is available, ensuring that data can be distributed and updated automatically. Snapshots allow for simple copies of data to be refreshed from one system to another.
- Supports Java. The Web Application Server allows for server-side Java applications to be interpreted through a cartridge-based virtual machine.
- Supports Enterprise Manager. A graphical facility is now available to assist a developer/DBA with such tasks as security management, event monitoring, job scheduling, and replication monitoring.
Oracle Memory 58
System Global Area (SGA) 58
Process Global Area 60
The Oracle Programs 61
Storage 61
Tablespaces and Database Files 61
Segments 62
Extents 62
Oracle Blocks 62
ROWID in Oracle8 63
Free Space and Automatic Compaction 63
System Database Objects 64
The Data Dictionary 64
Rollback Segments 64
Temporary Segments 65
Bootstrap/Cache Segment 66
Protecting Your Data 66
Transactions, Commit, and Rollback 66
Data Integrity 67
System-Level Privileges 70
Object-Level Privileges 71
Users and Roles 71
Auditing 72
Backup and Recovery 72
Backup and Recovery Options 72
Multiplexing 76
Distributed Databases 77
National Language Support 77
Data Warehousing 78
Following a SQL Statement Through the Architecture 79
Summary 80
Part II Oracle Systems and Servers
Page 9
Database Administration Tools 92
Oracle Navigator for Windows 95, Windows NT, and OS/2 93
SQL*DBA 94
Server Manager (Line Mode) 94
Instance Manager 94
Database Manager 95
Database Password Manager for Windows 96
The User Manager 96
The Object Manager 97
The Session Manager 97
The Database Expander 97
Database Backup and Recovery Tools 97
The Backup Manager 97
The Recovery Manager 97
The Export, Import, and SQL*Loader Utilities 98
The Export and Import Utilities 98
SQL*Loader 98
SQL Products 98
Networking Software 99
Middleware 99
Oracle Objects for OLE 99
Oracle7 ODBC Driver 100
Oracle Call Interface 100
Personal Oracle7 Database Options 100
Distributed Option 101
Replication Option 101
When to Use Personal Oracle7 and Oracle Lite 101
Summary 101
Overview of the Oracle7 Workgroup Server 104
How Oracle7 Workgroup Server Differs from Other Oracle Packages 104
Oracle7 Workgroup Server Components 106
System Requirements 109
Server Hardware Requirements 110
Server Operating System Requirements 111
Client System Requirements 113
Oracle7 Workgroup Server Tools and Utilities 113
Oracle Enterprise Manager 113
Oracle Administrator Toolbar 116
Oracle WebServer Manager 116
Oracle7 Performance Monitor for Windows NT 116
Page 10
Oracle Intelligent Agent 116
Network Manager for Windows 118
Oracle7 Workgroup Server Distributed and Replication Features 119
Database Links 119
Distributed Queries and Joins 119
Distributed Updates with Two-Phase Commit 120
Read-Only Snapshots 120
XA Support 120
Connectivity to Non-Oracle Data via Oracle Open Gateway 120
When to Use Oracle7 Workgroup Server 120
Summary 122
Architectural Overview 124
Oracle SGA 125
New Features in Oracle8 128
Physical Storage Improvements 128
SQL Language Extensions 132
Deferred Constraint Checking 135
Object Types 136
Object Views 140
Nested Tables 143
New PL/SQL Extensions and Packages 145
Optimizer Improvements 150
Administration and Security Enhancements 152
Oracle8 OCI 154
Oracle Type Translator (OTT) 155
Summary 156
An Overview of Oracle7 Server 158
Oracle7 Server Components 158
Oracle7 Server and Oracle7 Universal Data Server 160
Oracle Video Server 160
Oracle WebServer 161
Oracle OLAP Option 161
Oracle Messaging Option 162
Oracle Spatial Data Option 162
Oracle ConText Option 162
System Requirements 162
Server Hardware Requirements 163
Server Operating System Requirements 164
Client System Requirements 165
Page 11
Oracle7 Database Tools and Utilities 166
Instance Manager for Windows NT 166
Oracle Instance Manager 166
Oracle Response File Generator 167
Server Manager 167
Screen-Mode Utilities for NetWare 168
Oracle7 Enterprise Backup Utility 169
Oracle Trace 171
SQL*Plus for NetWare 171
New Features in Oracle7 Server 7.3 172
Standby Database 172
Bitmap Index 172
Hash Joins 173
Partition Views 173
How Oracle7 Server Differs from Other
Oracle Packages 174
Operating System Integration Support 174
Oracle7 Server Options 175
When To Use Oracle7 Server 176
Summary 177
Architectural Overview 180
HTTP Server 180
Web Request Broker 181
Cartridges 182
Component Interaction 183
Installation and Configuration 187
Database Access 188
Using Supplied Cartridges 191
The PL/SQL Cartridge 191
The LiveHTML Cartridge 197
The Java Cartridge 198
Other Oracle Cartridges 200
Developing Cartridges with the WRB API 200
Summary 202
What Is Electronic Commerce? 204
The Architecture of Electronic Commerce 206
The Future of Electronic Commerce with the NC 207
Is Electronic Commerce a Solution for My Business? 208
Factors That Make Electronic Commerce Profitable 208
Factors That Could Make Electronic Commerce
Unprofitable 210
Page 12
The Architecture of Oracle's Internet Commerce Server 211
The Big Picture 211
The ICS Store Manager 213
The ICS Persistent Shopping Cart 213
Product Browsing and Searches with ConText 213
Customizable User Interface 213
User Preferences 213
Promotions and Discounts 214
Customer Accounts 214
Open Interface 214
A Peek at Some of the Third-Party Internet Commerce
Server Cartridges 214
Oracle Applications for the Web 215
Oracle Web Customers 215
Oracle Web Employees 215
Oracle Web Suppliers 216
Encryption and Electronic Commerce 216
Encryption and Decryption: The Spy and Walt Whitman 216
Web Encryption 218
Summary 220
Part III Oracle Tools and Utilities
Oracle's SQL: An Overview 224
SQL ”The Standard 224
Datatypes 224
Numeric 225
Date 225
Character 226
Binary 226
Others 227
The CREATE Statement 227
Tables 227
Indexes 230
Sequences 232
Other Objects 233
Writing Queries 234
Built-In Functions 236
Know Your Tables and Views 245
Joining Tables 245
The DECODE Statement 250
INSERTs, UPDATEs, and DELETEs 251
Page 13
Master/Detail or Parent/Child SQL 254
Additional Tips 255
Summary 256
Packaged Procedures 258
Package Creation 258
Creating Package Subprograms 260
Procedure Definition 260
Function Definition 261
Subprogram Parameter Modes 261
Subprogram Specifications 262
Default Procedure Parameters 262
Stand-Alone Procedures 262
Cursors 263
Declaring Cursors 263
Cursor Control 263
Explicit Cursor Attributes 263
Cursor Parameters 264
Creating Cursor Packages 264
Procedure Variables 265
Variable Declaration and Assignment 265
The DEFAULT Keyword 267
Variable and Constant Attributes 267
Scalar Datatypes 268
Boolean 268
Date/Time 268
Character 268
Number 269
Composite Datatypes 269
Array Processing 269
Record Processing 270
Processing Control 271
Loop Control 271
Iterative Control 273
Exception Handling 274
User-Defined Exceptions 274
System-Defined Exceptions 275
Comments 276
Stored Procedures 277
Referencing Stored Procedures 277
Stored Procedure States 278
Overloading 279
Page 14
Commits 279
The STANDARD Package 279
Referencing Internal Functions 280
Internal Functions 280
Additional Topics 280
The DECLARE Statement 280
Naming Conventions 281
Datatype Conversion 282
Database Triggers 282
More on Exceptions 284
More on Control Structures 286
Oracle8 Enhancements 287
National Language Support 287
Object Types 287
External Procedure Reference 288
Summary 288
A Brief History of SQL*Plus 290
Use and Limitations 290
Platforms 291
SQL*Plus Commands 292
Commands That Initiate the SQL*Plus Environment 292
Character-Mode Environments 293
Graphical-Mode Environments 295
SQL*Plus Execute Commands 295
SQL*Plus Editing Commands 296
SQL*Plus Formatting Commands 297
Miscellaneous Commands 299
Access Commands for Various Databases 301
SQL*Plus Reporting 301
Reporting Techniques 301
Advanced Reporting Techniques 303
SQL*Plus Additional Functionality 310
SQL Creating SQL 310
SQL Creating Command-Language Scripts 312
SQL*Plus Creating Database-Driven
Command-Language Scripts 313
Summary 316
Introduction to Server Manager 318
The Server Manager Command Set 318
Before You Start Server Manager 319
Page 15
Server Manager Operation Modes 319
Server Manager in Line Mode 320
Starting Server Manager in Line Mode 320
Startup and Shutdown with Server Manager in Line Mode 320
Server Manager in Command Mode 322
Automating the Startup and Shutdown Options 323
Server Manager Command Reference 326
! (the Exclamation Mark) 327
@ (the "at" Symbol) 327
ARCHIVE LOG 328
CONNECT 329
DESCRIBE 329
DISCONNECT 330
EXECUTE 330
EXIT 331
HOST (see !) 331
PRINT 332
RECOVER 332
REMARK 333
SET 334
SHOW 336
SHUTDOWN 338
SPOOL 339
STARTUP 340
VARIABLE 341
Summary 342
Installing Enterprise Manager 345
The Enterprise Manager Console 345
The Navigator Window 347
The Map Window 348
The Job Control Window 349
The Event Management Window 352
Backup Manager 353
Data Manager 354
Export 355
Import 355
Load 357
Instance Manager 357
Schema Manager 358
Security Manager 358
Page 16
Software Manager 359
SQL Worksheet 360
Storage Manager 361
Summary 362
Basic SQL*Loader Components 364
The Input Data 364
The Control File 366
The Log File 366
Discard and Bad Files 368
Physical Versus Logical Records 368
Concatenated Records 369
SQL*Loader Paths 369
The Conventional Path 369
The Direct Path 370
Parallel Data Loading 372
Control File Syntax 374
OPTIONS Clause 376
UNRECOVERABLE/RECOVERABLE Clause 377
LOAD DATA Clause 377
INFILE Clause 377
Table Loading Methods 379
CONCATENATION Clause 380
INTO TABLE Clause 381
Command-Line Options and Parameter Files 387
Summary 388
Export 390
Using a Parameter File 391
Table Mode 396
User Mode 396
Full Database Mode 396
Command Line Versus Interactive 396
Export Parameters That Affect Performance 398
Exporting Tablespaces 400
Types of Exports: Complete, Incremental, and Cumulative 402
A Backup Strategy 403
Import 403
Import Usage 403
User Mode 408
Table Mode 408
Full-Database Mode 409
Page 17
Interactive Versus Command Line 409
Import Parameters That Affect Performance 410
How Objects Import 411
Importing and Exporting with Personal Oracle7 411
Import and Export Hints and Tips 412
Create an Index File 412
Adjusting Storage Parameters 412
Reorganizing Data 413
Reducing Database Fragmentation 413
Importing Tables with LONG and LONG RAW Datatypes 413
Summary 414
Part IV Database Administration
Oracle Software Options 418
Base Product Options 418
Trusted Oracle Options 420
Installation Preparation 422
Installing the Software 422
Other Optional Components 423
Directory Structure 424
Operating System Specifics 424
UNIX 424
DEC VAX VMS 425
Microsoft Windows 425
Summary 425
The Parameter File: INIT.ORA 428
The Oracle SID 433
Creating a New Database 434
The First Objects Created 436
Startup and Shutdown 437
Startup Stages 437
Shutdown Stages 440
The Data Dictionary 441
Core System Tables 442
Data Dictionary Views 442
Dynamic Performance Tables 443
Other Data Dictionary Views 443
Redo Logs 444
Creating, Altering, and Dropping Redo Logs 444
Multiplexing Redo Logs 445
Page 18
Control Files 446
Multiplexing Control Files 446
Trace and Alert Files 447
Database Modes 448
NOARCHIVELOG Mode 448
Archive Log Mode 448
Changing the Mode of the Database 448
Useful Data Dictionary Views 449
Summary 468
Obtaining Object Storage 470
Oracle Blocks in the Database Files 470
Segments and Extents 471
Growth Patterns 472
Forcing Additional Extents 473
Using the ROWID in Oracle8 474
Using the Storage Clause 475
Understanding the Storage Clause 475
Using Other Storage Parameters 476
Using Rollback Segments 478
Creating Public and Private Rollback Segments 478
Creating Additional Rollback Segments 478
Enabling and Disabling Rollback Segments 479
Expanding and Monitoring Rollback Segments 479
Maintaining Rollback Segments 480
Using Tablespaces 480
Creating the First New Tablespace 480
Creating Additional Tablespaces 481
Adding Files to a Tablespace 481
Increasing the Size of Database Files 481
Dropping Tablespaces 482
Using Temporary Segments 482
Analyzing Storage 483
Estimating Storage for a Table 484
Summary 484
User Needs Analysis 486
What Does the User Want? 488
What Does the User Need to Accomplish the Job?M 488
Is Someone Currently Set Up with the Same
Configuration Requested by the User? 489
Page 19
What Is the Minimum Level of Access the User Requires to Do the Job? 489
What Is the Maximum Level of Access the User Should Reasonably Have? 490
What Constraints (Technical or Political) Exist When Setting Up the User? 490
User Authentication Methods 491
Password Authentication 491
Operating System Authentication 491
User Configuration Setup 492
Profiles 492
Default Tablespace 493
Temporary Tablespace 494
Resource Management 494
Using Profiles 495
Using Quotas 501
User Database Accounts 502
Creating User Accounts 502
Modifying User Accounts 504
Deleting User Accounts 504
Changing User Passwords 504
Working with INIT.ORA Parameters 505
Special Account Considerations 505
Setting Up a Generic Database Administrator 505
Setting Up a Generic Applications Administrator 506
Maintaining User Data 506
Designing User Data Tables 507
User Table Maintenance 507
Summary 508
Importance of Backups 510
Terminology 511
Redo Logs 512
Requirements for Backups 519
What Is a Database? 519
Control Files 521
Database Files 521
Redo Log Files 522
Types of Backups 522
Control File Backups 522
Redo Log File Backups 525
Cold Backups 528
Page 20
Hot Backups 530
Oracle Enterprise Manager 531
Alternative Backup Methods 532
Tablespace Offline Copy 532
Export 532
SQL*Loader Readable File 533
Types of Database Failure 534
Tablespace 534
Control File 535
Redo Logs 536
Archive Logs 537
Recovery Methods 537
Cold Restore 537
Full Database Recovery 538
Time-Based Recovery 538
Cancel-Based Recovery 539
Change-Based Recovery 540
Sample Database Backup Scripts 540
Cold Backup 541
Hot Backup 542
Summary 543
General Concepts in Database Tuning 546
Applications Tuning 546
Database Tuning 548
Operating System Tuning 549
Parallel Processing 553
Parallel Server Option 553
Parallel Query Option 553
Performance Tools 554
Viewing SGA and Parameter Settings 554
utlbstat and utlestat 555
EXPLAIN PLAN 556
SQL*Trace and TKPROF 559
Dynamic Performance (V$) Tables 562
Tuning Database SGA 564
Examining the Current SGA 565
Changing the SGA Size 566
Ramifications of SGA Changes 570
Contention Issues 572
I/O Contention and Load Balancing 572
Rollback Segment Contention 575
Page 21
Redo Log Contention 576
Checkpoints 577
Database Objects 577
Tables and Indexes 578
Partitioned Tables 582
Views 583
Triggers 583
Database Locking 583
Types and Classes of Locks 584
Unresolved Locking Issues 584
Checking Locking Situation 585
Summary 586
Authentication 588
Password Authentication 589
Operating System Authentication 590
Why Protect Passwords? 590
Control 591
Protection 591
Integrity 592
Privileged Accounts 592
Operating System Account: oracle 593
Operating System Account: root 593
SYS 593
SYSTEM 594
Operating System Group : oracle 594
Operating System Group: dba 594
Operating System Group: oper 595
Remote Passwords 595
Object Versus System Security 596
Object Security 596
System Security 600
Object Security Model 606
Protected Object Ownership Schema 606
Capacity Planning Requirements 606
Avoiding Tablespace Fragmentation Issues 607
Defining Database Roles 608
Creating Roles 609
Modifying Roles 609
Deleting Roles 610
Setting and Changing the Default Role 610
Password Protecting Roles 611
Page 22
Defining Roles at Operating System Level 611
System Privilege Roles 613
Database Auditing 614
Statement-Level Auditing 615
Object-Level Auditing 618
Audit Trail Location 619
Summary 620
Part V Developing Applications
Page 23
Read-Consistency 678
Steps to Processing a Transaction 679
Entering DML/DDL Statements 679
Assigning Rollback Segments 679
Using the Optimizer 681
Parsing Statements 684
Handling Locks 685
Generating Redo Logs 685
Stepping Through the Transaction 686
Processing a Remote or Distributed Transaction 687
Entering DDL/DML Statements 688
Assigning Rollback Segments 688
Breaking Down Statements 688
Optimizing Local Statements 688
Forwarding Remote Commands 688
Assigning Remote Rollback Segments and Writing
Redo Logs 689
Optimizing Remote Statements 689
Returning Data to the Local Database 689
Summarizing Remote and Distributed Transactions 689
The SET TRANSACTION Command Reference 690
READ ONLY Option 690
READ WRITE Option 690
USE ROLLBACK SEGMENT Option 690
Transaction Space 691
Summary 691
A Common Integrity Problem 694
Column Constraints 696
Table Constraints 702
Using Sequences 705
Using Triggers 707
Application and Performance Considerations 711
Summary 715
Reasons for Using Application Security 718
Using Application-Specific Database Objects 719
Table-Driven Application Security 723
Application and Performance Considerations 731
Summary 733
Page 24
Part VI Designer/2000
Page 25
Entity Relationship Diagrammer 778
Multiple Diagrams 782
Making a Diagram Easier to Follow 783
Functional Hierarchy Diagrammer 784
Dataflow Diagrammer 790
Creating a Diagram 791
Modifying Dataflow Structure 793
Multiple Diagrams 794
Systems Design 795
Database Design Wizard 795
Starting the Database Design Wizard 795
Continuing to Generate 798
The Data Diagrammer 799
Creating a Diagram 799
Multiple Diagrams 803
Generate the Tables Again 803
Retrofit 803
Generating DDL 803
The Application Design Wizard 804
Starting the Application Design Wizard 804
The Module Structure Diagrammer 805
Starting Up 806
Modifying a Diagram 809
Summary 810
Preferences Navigator 812
Where Are Preferences Used? 812
What Do Preferences Do? 813
Calling from the Module Data Diagrammer 814
What Do the Codes Mean? 814
Saving Your Preferences 815
Generating the Calling Module 815
Marking a Preference 815
Searching for Preferences 815
Starting the Module Data Diagrammer 816
Creating a Diagram 816
Saving the Diagram 823
Placing Screen Items of Different Windows or Canvases 824
Generating Screens 824
Summary 828
Page 26
Part VII Developer/2000
Page 27
Creating a Report 884
Constructing a Tabular Report 885
Creating a Master-Detail Report 888
Using Secondary Queries 888
Adding Report Break Levels 892
Customizing Reports with Boilerplate Text and Graphics 894
Working with Summary and Formula Columns 900
Formatting Reports 903
Exploring Complex Reports 906
The Mailing Label Report 906
The Form Letter Report 907
The Matrix Report 908
Creating Dynamic Reports 910
Using Dynamic Query Parameters 910
Defining Runtime Queries with Lexical Parameters 912
Ten Tips for Oracle Reports 2.5 913
Summary 914
The Oracle Graphics Environment 916
The Layout Editor 916
Initializing the Graphics Environment 917
Creating a New Display 919
Defining Displays 920
Creating a Pie Chart 920
Creating a Columnar Chart 922
Other Chart Types 923
Defining Parameters for Charts 924
Multiple Chart Applications 926
Creating a Drill-Down Chart 926
Creating a Multi-Layer Chart 927
Formatting Charts with Text and Breaks 930
Adding Text Items to Charts 930
Customizing the Chart Labels 930
Creating a Break Chart 932
Advanced Formatting Techniques 933
Using Format Triggers 933
Creating Data Filters 934
Summary 935
Displaying Oracle Graphics in Oracle Forms 938
Executing Oracle Reports from Oracle Forms 940
Displaying Oracle Graphics in Oracle Reports 941
Page 28
Interfacing to Non-Oracle Applications 942
Interfacing with the Windows API and
Other DLLs 944
Summary 945
New Terminology in Developer/2000 948
Tools Integration 948
New Features in Forms 5.0 948
Object Orientation 949
Database Integration and Scalability 949
Increased Productivity 950
Extended Windows Support 951
Support for PL/SQL Version 2.3 951
New Features in Reports 3.0 951
The Report Wizard and Live Previewer 951
Report Templates 952
New User-Interface Elements 952
Support for PL/SQL Version 2.3 952
Runtime Enhancements for Reports 3.0 952
Improvements in Graphics 3.0 953
Developer/2000 and the Web 953
Summary 953
Part VIII Oracle Objects for OLE
Page 29
Using Parameters 969
Parameter Arrays 971
Executing PL/SQL Blocks 972
The OraDynaset Edit, Update, and AddNew Methods 974
LONG and LONG RAW Column Support 975
Transactions 977
Error Handling 978
Performance Tuning 979
Minimizing the OLE COM Overhead 979
Minimizing the Network Roundtrips 980
Reusing Database Connections 981
Thread Safety 982
Summary 982
Using the ODC in Visual Basic 984
Using the ODC and OO4O Automation Server Objects 986
Summary 988
Part IX Discoverer 3.0
Page 30
Create Item and Date Hierarchies 1018
Create Item Classes 1019
Create New Folders 1019
Create Summary Folders 1020
Grant Business Area Access (Mandatory) 1022
The Discoverer 3.0 User Edition 1022
Multidimensional Databases: The Possibilities
Are Endless, Spock! 1024
OLAP of the Mind 1026
Summary 1026
Part X Other Oracle Development Tools
Page 31
User Exits 1057
Performance Tuning 1059
Poor Performance 1059
Improving Performance 1060
New Features in Version 1.8 1061
Oracle Call Interface (OCI) 1061
SQL*Module 1062
Productivity, Security, and Performance 1062
Summary 1062
Part XI Integrating Oracle and Third-Party Tools
Page 32
Part XII Oracle and the Web
Page 33
Output of CGI Scripts 1178
Security Issues with CGI 1178
Writing CGI Scripts in Perl 1179
The Perl Language 1180
Getting Data from the Database 1180
Summary 1181
Writing JDBC Applets and Applications 1185
Connecting to the Database 1186
Retrieving Results 1188
Applying DML Statements 1191
Accessing Functions and Procedures 1195
Using Output Parameters 1196
Handling JDBC Exceptions 1197
JDBC Debugging 1200
Deployment Issues 1201
Native Libraries and Supported Platforms 1201
Thin Client and Multitier Designs 1202
Using RMI 1204
Using JavaIDL 1205
Applets Versus Java Application and Security
considerations 1207
Advanced JDBC Application Design 1209
Performance Considerations and Multithreading 1209
Writing a JDBC Driver 1210
JDBC-Native Interface Mapping 1211
JDBC Conformance 1211
Using JNI 1212
The Future of JDBC 1214
Summary 1215
Planning Your Web Applications 1218
Defining the User Domain 1218
Designing Web Applications 1220
Oracle's Network Computing Architecture 1222
NCA Components: Cartridges 1222
Supported Languages for Cartridge Development 1225
The Cartridge Internals 1225
PL/SQL Web Applications 1226
PL/SQL Developer's Toolkit 1226
Page 34
Oracle Web Server Hypertext Procedures and
Hypertext Functions Packages 1226
PL/SQL Cartridge Samples 1228
Creating a Cartridge Using C 1230
Java 1235
Creating a Simple Java Application 1235
Summary 1239
Part XIII Advanced Topics
Page 35
Intraoperator and Interoperator Parallelism 1286
Flexible Parallelism 1288
Internode Parallelism 1290
The Oracle Advantage over Pure
Shared-Nothing Systems 1291
Static Data Partitioning and Parallelism 1291
Leverage of Processing Power 1292
Ease of Administration 1293
Robust Scalability 1294
Emerging Trends in Technology 1294
Parallel Processing Platform Hardware Configuration
Requirements 1295
Fail-Safe Redundant Hosts 1295
Cross or Remote Mounted Disk Drives 1295
Disk Drive Allocation 1296
Disk Partitioning 1296
Maximizing Parallel Platform Database and Disk
Performance Through Balancing the Oracle Processes 1296
Disk Optimization for Oracle and the Parallel
Server Option 1296
Disk Storage Devices 1297
RAID Versus the Speed of Disk Mirroring 1298
Parallel Processor Types, Smart Controller Cards,
and Bus I/O 1300
Making the Right Choice 1301
Transaction Processing Performance Council
(TPC) Benchmarks 1301
Parallel Processing Platform Selection Criteria 1303
Parallel Index Platform Design 1304
Routine Parallel Platform Server Maintenance 1307
Parallel Processing Database and Tool Upgrades 1307
Summary 1308
Components of ODBC 1312
Configuring an ODBC Data Source 1313
Connecting to an ODBC Data Source Using the
ODBC API 1317
Setting Connection Options 1320
Applying SQL Transactions 1320
Retrieving Result Sets 1324
Handling Errors 1328
Page 36
Calling Stored Procedures and Functions 1330
Disconnecting and Freeing Resources 1332
Debugging ODBC Applications 1333
Limitations of ODBC 1334
Summary 1334
An Introduction to Data Warehouses and
Data Marts 1336
Data Stores 1339
Programs and Processes 1341
Other Types of Databases 1343
Typical Uses of Data Warehouses 1344
Designing a Data Warehouse 1345
Determining the Information Needed 1345
Other Data for the Future 1346
Data Extraction from Online Systems 1347
Purge Processes 1349
Star Join Schema 1350
Tuning Data Warehouses in Oracle 1351
Oracle Data Marts 1351
OLAP Engines 1353
Summary 1353
Index1355
Page 45
Introduction
by Brett Mark, Advanced Information Systems, Inc.
This is a very exciting time for the information-processing industry. Companies like Oracle and Sun have led a technological revolution culminating in today's software advances. New languages such as Java, and advanced database servers such as Oracle8, provide a shift in paradigm for application developers. This new paradigm yields capabilities unparalleled in comparison to software systems of yesterday . At last we have the tools necessary to network the entire world to large database systems in a secure fashion.
A few years ago were the database wars. Several relational database vendors readied server-based systems that accommodated large user communities performed industry standard database operations. Relational and client/server became buzzwords that were served up within technical circles to add weight to otherwise weightless words. All the major database vendors adopted SQL (Structured Query Language) and the relational model. Market share was initially well-distributed among a handful of these vendors. There was no clear leader, only a race to fulfill the promise of a few important requirements. Two of these included performance ”measured in transactions per second and portability. Of these, Oracle has always lead the pack. Slowly but surely, due to these and other factors, Oracle gained on the competition to become the second largest software company and the leading database vendor of today.
I marvel at the progress that has been made in the last couple versions of the product. Version 7 was known as the parallel everything architecture. Oracle7 accommodated for parallel operations to be performed anywhere from loading data to querying the database to adding redundancy and horsepower to the central server.
With the advent of Oracle8, I would like to suggest that the war has been won, and I am not an Oracle fanatic; I have used many different relational databases. Version 8 of Oracle places databases in a whole new category. Oracle8 offers several very important enhancements that take relational databases another quantum leap forward. The most significant change from Oracle7 is the implementation of objects. In fact, the database is considered to be object-relational, as opposed to its purely relational predecessors. Another significant improvement is seen in Oracle's commitment to Web computing through the NCA (network computing architecture) and the Web Application Server. With these, as well as many other enhancements from the OCI (Oracle Call Interface), improved PL/SQL, and a potpourri of GUI administration and development tools, Oracle has a real winner with this product.
Page 46
The Object-Relational Model
Oracle8 offers a sophisticated approach to implementing objects within the database itself. Never before could a database designer create objects that closely resemble the way business is actually performed. Instead, a set of tables would be normalized to contain discrete pieces of a single concept from the business world. It is this capability to focus on modeling real-world business problems that makes Oracle8 a powerful tool. It takes us another step further from merely using the database as a storage and retrieval system, to helping us solve real problems. The relational model required that business data be broken apart in order to store it in the database. Now, object datatypes, as well as object tables, provide a powerful single layer of object orientation. Although no features such as inheritance is currently supported, future versions can provide this kind of powerful functionality.
Many new features provide an abundance of capabilities for designing these database objects. Chapter 7, "Oracle8 Server," goes into detail on what these are and how to use them. You will learn about the collection type, Zero or Many methods, and One or Many attributes. Examples of the new PL/SQL extensions show how to write database code to process within the object paradigm. Sophisticated techniques show how to nest tables. These and other new features will be illustrated , thus providing a comprehensive understanding of Oracle8's object relational model.
Oracle8 will continue to support code as well as data models created using previous versions. The SQL and PL/SQL interpreters have been extended to support objects, and a whole new OCI layer is required to program a host language to Oracle8. Old OCI code is completely supported, performing relational operations to the server.
Network Computing Architecture (NCA)
Oracle8 has been designed to accommodate the needs of today's business environment. Opening a database system to the general public via the World Wide Web is a daunting task. Possibly millions of hits a day might need to interface with Oracle8. Oracle's Web Application Server acts as a conduit to the database server. The Web Application Server incorporates a cartridge architecture that facilitates the creation of secure transactions over the Web. Several chapters discuss the architecture of the Web Server, as well as programming code in Java, C, and PL/SQL to create dynamic Web pages.
NCA provides a robust array of development options that exploit the Web Application Server architecture. Several programming languages are available to write responses to URL requests over the Web. These responses provide dynamic Web pages capable of filling computer screens all over the world with data from Oracle servers. Cartridges can be written by developers wishing to provide particular functionality, thereby extending the capabilities of the NCA.
Page 47
Advanced Features: Oracle8
Oracle8 Server has drastically increased the potential performance for large databases. Virtually no limitations stand in the way of businesses requiring large data stores, Oracle
All Aboard!
It is time to say goodbye to those other database products and buckle your seatbelt. You are about to experience the most comprehensive compilation of tips, techniques, and information on a wide range of Oracle8 Server features, as well as other related products. This book should serve as a learning tool and a reference manual for persons relatively new to Oracle and to those who are considered advanced database administrators. You will benefit from learning about the new features in security, networking, and advanced server options. Database designers will quickly assimilate the new object features and begin designing radically different data models. Programmers will find themselves knee-deep in source code and architecture for a variety of popular languages, including C, PL/SQL, and Java.
All aboard for the database tour of your life.