book

Oracle Essentials, 3rd Edition

by Rick Greenwald, Robert Stackowiak, Jonathan Stern

February 2004

Intermediate to advanced

394 pages

12h 20m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Dedication
Preface
Goals of This BookAudience for This BookAbout the Third Edition (Oracle Database 10g)Structure of This BookConventions Used in This BookHow to Contact UsAcknowledgments
1. Introducing Oracle
1.1. The Evolution of the Relational Database1.1.1. Relational Basics1.1.2. How Oracle Grew1.2. The Oracle Family1.2.1. Oracle Standard Edition1.2.2. Oracle Enterprise Edition1.2.3. Oracle Personal Edition1.2.4. Oracle Lite1.3. Summary of Oracle Features1.4. Database Application Development Features1.4.1. Database Programming1.4.1.1. SQL1.4.1.2. PL/SQL1.4.1.3. Java features and options1.4.1.4. Large objects1.4.1.5. Object-oriented programming1.4.1.6. Third-generation languages (3GLs)1.4.1.7. Database drivers1.4.1.8. The Oracle Call Interface1.4.1.9. National Language Support1.4.1.10. Availability1.4.2. Database Extensibility1.4.2.1. Oracle Text and interMedia1.4.2.2. Oracle Spatial Option1.4.2.3. XML1.5. Database Connection Features1.5.1. Database Networking1.5.1.1. Oracle Net1.5.1.2. Oracle Names1.5.1.3. Oracle Internet Directory1.5.1.4. Oracle Connection Manager1.5.1.5. Advanced Security Option1.5.1.6. Label Security Option1.5.1.7. Availability1.5.2. Oracle Application Server1.5.2.1. Availability1.6. Distributed Database Features1.6.1. Distributed Queries and Transactions1.6.2. Heterogeneous Services1.6.3. Availability1.7. Data Movement Features1.7.1. Basic Replication1.7.2. Advanced Replication1.7.3. Transportable Tablespaces1.7.4. Advanced Queuing and Streams1.7.5. Extraction, Transformation, Loading1.7.6. Availability1.8. Performance Features1.8.1. Database Parallelization1.8.1.1. Availability1.8.2. Data Warehousing and Business Intelligence1.8.2.1. Bitmap indexes1.8.2.2. Star query optimization1.8.2.3. Materialized views1.8.2.4. Analytic functions1.8.2.5. OLAP Option1.8.2.6. Data Mining Option1.8.2.7. Availability1.9. Database Management Features1.9.1. Oracle Enterprise Manager1.9.1.1. EM2Go1.9.2. Availability1.9.3. Backup and Recovery1.9.3.1. Recovery Manager1.9.3.2. Incremental backup and recovery1.9.3.3. Oracle Storage Manager and Automated Disk Based Backup and Recovery1.9.3.4. Availability1.9.4. Database Availability1.9.4.1. Partitioning option1.9.4.2. Data Guard1.9.4.3. Fail Safe1.9.4.4. Oracle Real Application Clusters1.9.4.5. Data Guard and RAC1.9.4.6. Automated Storage Management1.9.4.7. Availability1.10. Oracle Developer Suite1.10.1. Oracle Forms Developer1.10.1.1. Oracle Reports Developer1.10.1.2. Oracle JDeveloper1.10.1.3. Oracle Designer1.10.1.4. Oracle Discoverer Administration Edition1.10.1.5. Oracle Portal1.10.1.6. Availability1.11. Oracle Lite
2. Oracle Architecture
2.1. Instances and Databases2.2. The Components of a Database2.2.1. Tablespaces2.2.2. Physical Files in an Oracle Database2.2.3. Control Files2.2.3.1. Control file parameters2.2.3.2. Multiple control files2.2.4. Datafiles2.2.4.1. Datafile structure2.2.4.2. Extents and segments2.2.5. Redo Log Files2.2.5.1. Multiplexing redo log files2.2.5.2. How Oracle uses the redo logs2.2.5.3. Naming conventions for redo logs2.2.5.4. Archived redo logs2.2.5.5. ARCHIVELOG mode and automatic archiving2.3. The Components of an Instance2.3.1. Memory Structures for an Instance2.3.1.1. Database buffer cache2.3.1.2. Shared pool2.3.1.3. Redo log buffer2.3.2. Background Processes for an Instance2.4. The Data Dictionary
3. Installing and Running Oracle
3.1. Installing Oracle3.1.1. Optimal Flexible Architecture (OFA)3.1.2. Supporting Multiple Oracle Versions on a Machine3.1.3. Upgrading an Oracle Database3.2. Creating a Database3.2.1. Planning the Database3.2.2. The Value of Estimating3.2.3. Tools for Creating Databases3.3. Configuring Oracle Net3.3.1. Resolving Oracle Net Service Names3.3.2. Oracle Net Manager3.3.3. Auto-Discovery and Agents3.3.4. Oracle Net Configuration Files3.4. Starting Up the Database3.5. Shutting Down the Database3.6. Accessing a Database3.6.1. Server Processes and Clients3.6.1.1. Server process3.6.1.2. Client process3.6.2. Application Servers and Web Servers as Clients3.6.3. Oracle Net and Establishing Network Connections3.6.4. The Shared Server/Multi-Threaded Server3.6.5. Session Memory for Shared Server Processes Versus Dedicated Server Processes3.6.6. Data Dictionary Information About the Shared Server3.7. Oracle at Work3.7.1. Oracle and Transactions3.7.2. A Transaction, Step by Step
4. Data Structures
4.1. Datatypes4.1.1. Character Datatypes4.1.2. Numeric Datatype4.1.3. Date Datatype4.1.4. Other Datatypes4.1.5. Type Conversion4.1.6. Concatenation and Comparisons4.1.7. NULLs4.2. Basic Data Structures4.2.1. Tables4.2.2. Views4.2.3. Indexes4.2.3.1. B*-tree indexes4.2.3.2. Reverse key indexes4.2.3.3. Bitmap indexes4.2.3.4. Function-based indexes4.3. Additional Data Structures4.3.1. Sequences4.3.2. Synonyms4.3.3. Clusters4.3.4. Hash Clusters4.4. Data Design4.5. Constraints4.6. Triggers4.7. Query Optimization4.7.1. Rule-Based Optimization4.7.2. Cost-Based Optimization4.7.2.1. Statistics4.7.2.2. Influencing the cost-based optimizer4.7.3. Choosing a Mode4.7.3.1. Why choose the cost-based optimizer?4.7.3.2. Why choose the rule-based optimizer?4.7.4. Saving the Optimization4.8. Understanding the Execution Plan4.9. SQL Tuning Advisor4.10. Data Dictionary Tables
5. Managing Oracle
5.1. Management and Enterprise Manager5.1.1. Oracle Enterprise Manager Architecture5.1.2. Oracle Enterprise Manager Consoles5.1.3. The HTML Console Interface5.1.4. EM2Go5.2. Oracle Database 10g Self-Tuning and Management5.3. Implementing Security5.3.1. Usernames, Privileges, Groups, and Roles5.3.2. Security Privileges5.3.3. The DBA Role, SYSDBA, and SYSOPER5.3.4. Auditing Security, Policies, and Policy Violations5.3.5. View-Based Security5.3.6. Fine-Grained Access Control5.3.7. Label Security Option5.3.8. Security and Application Roles and Privileges5.3.9. Distributed Database and Multi-Tier Security5.3.10. Advanced Security Option5.4. Fragmentation and Reorganization5.4.1. Resolving Fragmentation5.5. Backup and Recovery5.5.1. Types of Backup and Recovery Options5.5.2. Additional Backup Capabilities5.6. Working with Oracle Support5.6.1. Reporting Problems5.6.2. Automated Patching in Oracle Database 10g
6. Oracle Performance
6.1. Performance Tuning Basics6.1.1. Defining Performance Problems6.1.2. Oracle Server Performance6.1.3. Machine Resource Usage6.1.4. When All Else Fails6.1.5. A Final Note on Performance6.2. Oracle and Disk I/O Resources6.2.1. I/O Planning Principles for an Oracle Database6.2.2. Using RAID Disk Array Technology6.2.2.1. Host-based software6.2.2.2. Dedicated storage subsystems6.2.2.3. Combined host-based and I/O subsystem volume management6.2.3. Flexibility, Manageability, and Disk Arrays6.2.4. How Oracle I/O and Striped Arrays Interact6.3. Oracle and Parallelism6.3.1. Block-Range Parallelism6.3.2. Parallelism for Tables and Partitions of Tables6.3.3. What Can Be Parallelized?6.3.4. Controlling Oracle’s Parallel Resource Usage6.3.4.1. Setting the degree of parallelism6.3.4.2. Intra-operation parallelism6.3.5. Adaptive Parallelism6.3.6. Partition-Based Parallelism6.3.6.1. Parallelism for partitions and subpartitions of a table6.3.6.2. Fast full index scans for nonpartitioned tables6.3.6.3. Parallel insert for nonpartitioned and partitioned tables6.4. Oracle and Memory Resources6.4.1. How Oracle Uses the System Global Area6.4.1.1. The database buffer cache6.4.1.2. The shared pool6.4.1.3. The redo log buffer6.4.1.4. Automatic sizing for the SGA6.4.2. How Oracle Uses the Program Global Area6.4.2.1. Memory for SQL statements6.4.2.2. Memory for sorting within the PGA6.4.2.3. Automatic sizing for the PGA6.5. Oracle and CPU Resources6.6. Database Resource Manager6.7. Monitoring6.7.1. Dynamic Performance Views6.7.2. Enterprise Manager6.7.3. Automatic Workload Repository and Diagnostics6.8. Real Application Clusters and Performance
7. Multiuser Concurrency
7.1. Basics of Concurrent Access7.1.1. Transactions7.1.2. Locks7.1.3. Concurrency and Contention7.1.4. Integrity Problems7.1.5. Serialization7.2. Oracle and Concurrent User Access7.3. Oracle’s Isolation Levels7.4. Oracle Concurrency Features7.5. How Oracle Handles Locking7.5.1. A Simple Write Operation7.5.2. A Conflicting Write Operation7.5.3. A Read Operation7.6. Concurrent Access and Performance7.7. Workspaces
8. Oracle and Transaction Processing
8.1. OLTP Basics8.1.1. What Is a Transaction?8.1.2. What Does OLTP Mean?8.1.2.1. General characteristics8.1.2.2. Online versus batch8.1.3. OLTP Versus Decision Support8.2. Oracle’s OLTP Heritage8.3. Architectures for OLTP8.3.1. Traditional Two-Tier Client/Server8.3.2. Stored Procedures8.3.3. Three-Tier Systems8.3.4. Application Servers and Web Servers8.3.5. The Grid8.4. Oracle Features for OLTP8.4.1. General Concurrency and Performance8.4.2. Scalability8.4.2.1. Multi-Threaded Server/shared server8.4.2.2. Database Resource Manager8.4.3. Real Application Clusters8.5. High Availability8.6. Oracle Advanced Queuing and Streams8.6.1. Advanced Queuing for System Interfaces8.6.2. Oracle and Publish-Subscribe Technology8.7. Object Technologies and Distributed Components

9. Oracle and Data Warehousing
9.1. Business Intelligence Basics9.1.1. The Evolution of Business Intelligence9.1.2. A Topology for Business Intelligence9.1.3. Data Marts9.1.4. Operational Data Store and Enterprise Warehouse9.2. Data Warehouse Design9.3. Query Optimization9.3.1. Bitmap Indexes9.3.2. Real Application Clusters9.3.3. Summary Tables9.3.4. Materialized Views9.4. Analytics, OLAP, and Data Mining in the Database9.4.1. Functions9.4.2. OLAP and Data Mining Options9.4.3. Other Capabilities9.4.4. Database Extensibility and the Data Warehouse9.4.4.1. interMedia9.4.4.2. Spatial option9.4.4.3. XML9.5. Managing the Data Warehouse9.6. Other Data Warehouse Software9.6.1. Extraction, Transformation, and Loading9.6.2. Reporting and Ad-Hoc Query Tools9.6.3. OLAP and OLAP Applications Building9.6.4. Data Mining9.6.5. Business Intelligence Applications9.7. The Metadata Challenge9.8. Best Practices9.8.1. Common Misconceptions9.8.2. Effective Strategy
10. Oracle and High Availability
10.1. What Is High Availability?10.1.1. Measuring and Planning Availability10.1.2. Causes of Unplanned Downtime10.1.3. System Availability Versus Component Availability10.2. System Crashes10.2.1. What Is Instance Recovery?10.2.2. Phases of Instance Recovery10.2.2.1. Rollforward10.2.2.2. Fast-start fault recovery and bounded recovery time10.2.2.3. Rollback10.2.2.4. Deferred rollback10.2.2.5. Fast-start rollback10.3. Protecting Against System Crashes10.3.1. Component Redundancy10.3.1.1. Disk redundancy10.3.2. Automatic Storage Management10.3.3. Simple Hardware Failover10.3.3.1. Outage duration for hardware failover10.3.3.2. Failover and operating system platform10.3.4. Real Application Clusters10.3.4.1. Real Application Clusters and hardware failover10.3.4.2. Node failure and Real Application Clusters10.3.4.3. Parallel Fail Safe / RACGuard10.3.5. Oracle Transparent Application Failover10.3.5.1. How TAF works10.3.5.2. TAF and various Oracle configurations10.4. Recovering from Disasters10.4.1. Developing a Backup and Recovery Strategy10.4.2. Taking Oracle Backups10.4.3. Using Backups to Recover10.4.4. Read-Only Tablespaces10.4.5. Point-in-Time Recovery10.4.6. Flashback10.4.7. Recovery Manager10.5. Complete Site Failure10.5.1. Oracle Data Guard: Standby Database for Redundancy10.5.1.1. Logical standby database10.5.1.2. Oracle Data Guard management10.5.2. Possible Causes of Lost Data with a Physical Standby Database10.5.2.1. Copying archived redo logs to a standby site10.5.2.2. Unarchived redo information and the role of geomirroring10.6. Data Redundancy Solutions10.6.1. Data Replication: Synchronous and Asynchronous10.6.2. Old-Fashioned Data Redundancy
11. Oracle and Hardware Architecture
11.1. System Basics11.2. Uniprocessor Systems11.3. Symmetric Multiprocessing Systems11.4. Clusters11.5. Massively Parallel Processing Systems11.6. Non-Uniform Memory Access Systems11.7. Grid Computing11.8. Disk Technology11.8.1. Disk Deployment Strategies11.9. Which Platform Deployment Solution?11.9.1. Platform Comparison11.9.2. Approaches to Choosing Platforms
12. Distributed Databases and Distributed Data
12.1. Accessing Multiple Databases as a Single Entity12.1.1. Distributed Data Access Across Multiple Oracle Databases12.1.2. Access to and from Non-Oracle Databases12.2. Two-Phase Commits12.2.1. Transaction Processing Monitors12.3. Moving Data Between Distributed Systems12.3.1. Advanced Replication12.3.1.1. Managing advanced replication12.3.2. Transportable Tablespaces12.4. Advanced Queuing12.4.1. Queue Creation and Management12.4.2. Publish-and-Subscribe Capabilities12.5. Oracle Streams12.5.1. Oracle Streams and Grid Computing
13. Extending Oracle Datatypes
13.1. Object-Oriented Development13.1.1. Object-Relational Features13.1.1.1. Objects in Oracle13.1.1.2. Other extensibility features13.1.2. Java’s Role13.1.3. Enterprise JavaBeans13.2. Extensibility Features and Options13.2.1. Oracle Time Series13.2.2. Oracle interMedia and Oracle Text13.2.3. Oracle Spatial Option13.3. Using the Extensibility Framework in Oracle
14. Network Deployment Models
14.1. The Impact of the Internet14.2. The Oracle Database and the Internet14.2.1. Oracle HTTP Server14.2.2. Standards Support14.2.2.1. Java in the database14.2.2.1.1. PL/SQL and Java14.2.2.1.2. JDBC and SQLJ: Java’s 3GL interfaces14.2.2.2. XML14.2.2.2.1. XML standards14.2.2.2.2. XML DB14.2.3. Simplified HTML Applications Development14.3. Oracle Application Server14.3.1. Editions14.3.2. Installation14.3.3. Components14.3.3.1. HTTP Server14.3.3.2. Containers for J2EE (OC4J)14.3.3.3. TopLink14.3.3.4. Development tools14.3.3.5. Development servers14.3.3.6. Portal14.3.3.7. Wireless14.3.3.8. Security14.3.3.9. Integration14.3.3.10. Business intelligence14.3.4. System Services14.3.4.1. Management14.3.4.2. Caching14.3.4.2.1. Web Cache14.3.4.2.2. Java Object Cache14.3.4.3. Clustering and load balancing14.4. Grid Computing14.4.1. Features for Grid Computing14.4.1.1. Provisioning14.4.1.2. Resilience14.4.1.3. Integration14.4.1.4. Management14.4.2. A Final Thought on Grid Computing
A. What’s New in This Book for Oracle Database10g
A.1. Chapter 1, Introducing OracleA.2. Chapter 2, Oracle ArchitectureA.3. Chapter 3, Installing and Running OracleA.4. Chapter 4, Data StructuresA.5. Chapter 5, Managing OracleA.6. Chapter 6, Oracle PerformanceA.7. Chapter 7, Multiuser ConcurrencyA.8. Chapter 8, Oracle and Transaction ProcessingA.9. Chapter 9, Oracle and Data WarehousingA.10. Chapter 10, Oracle and High AvailabilityA.11. Chapter 11, Oracle and Hardware ArchitectureA.12. Chapter 12, Distributed Databases and Distributed DataA.13. Chapter 13, Extending Oracle DatatypesA.14. Chapter 14, Network Deployment Models
B. Additional Resources
B.1. Web SitesB.2. Books and Oracle DocumentationB.2.1. Chapter 1, Introducing OracleB.2.2. Chapter 2, Oracle ArchitectureB.2.3. Chapter 3, Installing and Running OracleB.2.4. Chapter 4, Data StructuresB.2.5. Chapter 5, Managing OracleB.2.6. Chapter 6, Oracle PerformanceB.2.7. Chapter 7, Multiuser ConcurrencyB.2.8. Chapter 8, Oracle and Transaction ProcessingB.2.9. Chapter 9, Oracle and Data WarehousingB.2.10. Chapter 10, Oracle and High AvailabilityB.2.11. Chapter 11, Oracle and Hardware ArchitectureB.2.12. Chapter 12, Distributed Databases and Distributed DataB.2.13. Chapter 13, Extending Oracle DatatypesB.2.14. Chapter 14, Network Deployment Models
Index
About the Authors
Colophon
Copyright

Overview

An enormous system comprising myriad technologies, options, and releases, Oracle's complexities have spawned numerous areas of specialization. For each area of specialization there are equally specialized how-to books and manuals. O'Reilly's Oracle Essentials claims a unique place among these books. Rather than focusing on one area, the book explains the foundational concepts of the Oracle technology and the core technical and business aspects of using it.The new edition of this classic book, Oracle Essentials, 3rd Edition: Oracle Database 10g, distills a vast amount of knowledge into an easy-to-read volume covering every aspect of the Oracle database. Readers of all levels will learn about Oracle's features and technologies, including the product line, architecture, data structures, networking, concurrency, tuning, and much more.Featuring focused text, abundant illustrations, and helpful hints, the new edition offers a valuable overview of Oracle's Database 10g--the industry's first database to support grid computing. Recent releases such as Oracle 9i and 8i are also covered. The book contains chapters on:

Oracle products, options, and overall architecture for Oracle 10g and prior recent releases
Installing and running Oracle: how to configure, start up, and shut down the database, and various networking issues
Oracle data structures, datatypes, and ways of extending datatypes, with an introduction to Oracle objects (e.g., tables, views, indexes)
Managing Oracle: security, the Oracle Enterprise Manager, fragmentation and reorganization, and backup and recovery
Oracle performance: characteristics of disk, memory, and CPU tuning
Multi-user concurrency, online transaction processing (OLTP), and high availability
Hardware architectures (e.g., SMP, MPP, NUMA) and their impact on Oracle
Data warehousing and distributed databases
Network deployment: using Oracle as an Internet computing platform and for grid computing
What's new in Oracle 10g: a summary of the database changes described in the book

Oracle Essentials, 3rd Edition: Oracle Database 10g was written for anyone whose job involves managing or building systems using Oracle DBMS technology or working with staff that uses Oracle technology. This book is the perfect all-in-one source for understanding the complexities and capabilities of Oracle.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0596005857Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills