You are previewing Oracle Database 10g: A Beginner’s Guide.
O'Reilly logo
Oracle Database 10g: A Beginner’s Guide

Book Description

Provides beginning DBAs and developers with a solid foundation in the database administration and programming basics needed to embark on an Oracle career. The focus is on Oracle Database 10g, but you’ll get the fundamentals applicable to all Oracle database releases.

Table of Contents

  1. Cover Page
  2. Oracle Database 10g: A Beginner’s Guide
  3. Copyright Page
  4. About the Authors
  5. About the Contributing Authors
  6. Contents
  7. Acknowledgments
  8. Introduction
  9. 1 Database Fundamentals
    1. Critical Skill 1.1 Define a Database
    2. Critical Skill 1.2 Learn the Oracle Database 10g Architecture
      1. The Control Files
      2. The Online Redo Logs
      3. The SYSTEM Tablespace
      4. The SYSAUX Tablespace
      5. Default Temporary Tablespace
      6. Undo Tablespace
      7. The System Parameter File
      8. Background Processes
      9. Project 1-1 Reviewing the Oracle Database 10g Architecture
      10. The Database Administrator
    3. Critical Skill 1.3 Learn the Basic Oracle Database 10g Data Types
      1. varchar2
      2. number
      3. date
      4. timestamp
      5. clob
      6. blob
    4. Critical Skill 1.4 Work with Tables
      1. Tables Related to part_master
    5. Critical Skill 1.5 Work with Stored Objects
      1. Views
      2. Triggers
      3. Procedures
      4. Functions
      5. Packages
    6. Critical Skill 1.6 Become Familiar with Other Important Items in the Oracle Database 10g
      1. Indexes
      2. Users
      3. Tablespace Quotas
      4. Synonyms
      5. Roles
      6. Default User Environments
    7. Critical Skill 1.7 Work with Object and System Privileges
      1. Select
      2. Insert
      3. Update
      4. Delete
      5. System Privileges
    8. Critical Skill 1.8 Introduce Yourself to the Grid
    9. Critical Skill 1.9 Tie It All Together
    10. Chapter 1 Mastery Check
  10. 2 SQL: Structured Query Language
    1. Critical Skill 2.1 Learn the SQL Statement Components
      1. DDL
      2. DML
    2. Critical Skill 2.2 Use Basic insert and select Statements
      1. insert
      2. select
    3. Critical Skill 2.3 Use Simple where Clauses
      1. The where Clause with NOT
      2. The where Clause with a Range Search
      3. The where Clause with a Search List
      4. The where Clause with a Pattern Search
      5. The where Clause: Common Operators
    4. Critical Skill 2.4 Use Basic update and delete Statements
      1. update
      2. delete
    5. Critical Skill 2.5 Order Data
    6. Critical Skill 2.6 Employ Functions: String, Numeric, Aggregate (No Grouping)
      1. String Functions
      2. Numeric Functions
      3. Aggregate Functions
    7. Critical Skill 2.7 Use Dates and Data Functions (Formatting and Chronological)
      1. Date Functions
      2. Special Formats with the Date Data Type
      3. Nested Functions
    8. Critical Skill 2.8 Employ Joins (ANSI vs. Oracle): Inner, Outer, Self
      1. Inner Joins
        1. Outer Joins
      2. Project 2-1 Joining Data Using Inner and Outer Joins
      3. Project 2-2 Joining Data Using ANSI SQL Joins
        1. Self-Joins
      4. Critical Skill 2.9 Learn the group by and having Clauses
        1. group by
        2. having
        3. Project 2-3 Grouping Data in Your select Statements
      5. Critical Skill 2.10 Learn Subqueries: Simple and Correlated Comparison with Joins
        1. Simple Subquery
        2. Correlated Subqueries with Joins
      6. Critical Skill 2.11 Use Set Operators: Union, Intersect, Minus
        1. union
        2. union all
        3. intersect
        4. minus
        5. Project 2-4 Using the union Function in Your SQL
      7. Critical Skill 2.12 Use Views
      8. Critical Skill 2.13 Learn Sequences: Just Simple Stuff
      9. Critical Skill 2.14 Employ Constraints: Linkage to Entity Models, Types, Deferred, Enforced, Gathering Exceptions
        1. Linkage to Entity Models
        2. Types
        3. Deferred
      10. Critical Skill 2.15 Format Your Output with SQL*Plus
        1. Page and Line Size
        2. Page Titles
        3. Page Footers
        4. Formatting Columns
        5. Project 2-5 Formatting Your SQL Output
        6. Writing SQL*Plus Output to a File
      11. Chapter 2 Mastery Check
  11. 3 The Database Administrator
    1. Critical Skill 3.1 Learn the Job of the DBA
    2. Critical Skill 3.2 Understand the Oracle Database 10g DBA Skill Set
    3. Critical Skill 3.3 Perform Day-to-Day Operations
      1. Architecture and Design
      2. Capacity Planning
      3. Backup and Recovery
      4. Security
      5. Performance and Tuning
      6. Managing Database Objects
      7. Storage Management
      8. Change Management
      9. Schedule Jobs
      10. Network Management
      11. Troubleshooting
    4. Critical Skill 3.4 Understand the Oracle Database 10g Infrastructure
      1. Schemas
      2. Storage Structures
    5. Critical Skill 3.5 Operate Modes of an Oracle Database 10g
      1. Modes of Operation
      2. Database and Instance Shutdown
    6. Critical Skill 3.6 Get Started with Oracle Enterprise Manager
      1. Instance Configuration
      2. User Sessions
      3. Resource Consumer Groups
      4. Schema, Security, and Storage Management
      5. Distributed Management
      6. Warehouse Features
      7. Other Tools
    7. Critical Skill 3.7 Manage Database Objects
      1. Controlfiles
      2. Redo Logs
      3. Undo Management
      4. Schema Objects
    8. Critical Skill 3.8 Manage Space
      1. Archive Logs
      2. Tablespaces and Datafiles
    9. Critical Skill 3.9 Manage Users
      1. Create a User
      2. Edit Users
    10. Critical Skill 3.10 Manage Privileges for Database Users
      1. Grant Authority
      2. Roles
      3. Profiles
      4. In Conclusion
      5. Project 3-1 Creating Essential Objects
    11. Chapter 3 Mastery Check
  12. 4 Networking
    1. Critical Skill 4.1 Use Oracle Net Services
      1. Network Protocols
      2. Optimize Network Bandwidth
      3. Character Sets
      4. Connections
      5. Maintain Connections
      6. Define a Location
    2. Critical Skill 4.2 Learn the Difference Between Dedicated and Shared Server Architectures
      1. Dedicated Server
      2. Shared Server
      3. Set Dispatchers
      4. Views to Monitor the Shared Server
    3. Critical Skill 4.3 Define Connections
      1. A Connect Descriptor
      2. Define a Connect Descriptor
      3. The Oracle Connection Manager
      4. Session Multiplexing
      5. Firewall Access Control
    4. Critical Skill 4.4 Use the Oracle Net Listener
      1. Password Authentication
      2. Multiple Listeners
      3. Connection Pooling
    5. Critical Skill 4.5 Learn Naming Methods
      1. Directory Naming Method
      2. Directory Information Trees
      3. Distinguished Names
      4. How to Find the Directory Naming Information
      5. Net Service Alias Entries
      6. The Local Naming Method
      7. The Easy Naming Method
      8. The External Naming Method
      9. Which Naming Method to Use
    6. Critical Skill 4.6 Use Oracle Configuration Files
      1. Syntax for Configuration Files
    7. Critical Skill 4.7 Use Administration Tools
      1. The Oracle Enterprise Manager
      2. The Oracle Net Manager
      3. The OEM Console
      4. The OEM Components
      5. The Oracle Net Configuration Assistant
      6. The Oracle Internet Directory Configuration Assistant
      7. Command-Line Utilities
      8. The Oracle Advanced Security Option
      9. Dispatchers
      10. Project 4-1 Testing a Connection
    8. Critical Skill 4.8 Use Profiles
      1. Control Access
    9. Critical Skill 4.9 Network in a Multitiered Environment
    10. Chapter 4 Mastery Check
  13. 5 Backup and Recovery
    1. Critical Skill 5.1 Oracle Backup and Recovery Fundamentals
      1. Where Do I Start?
      2. Backup Architecture
      3. Oracle Binaries
      4. The Parameter Files
      5. Control Files
      6. Redo Logs
      7. Undo Segments
      8. Checkpoints
      9. Archive Logs
      10. Datafiles, Tablespaces, Segments, Extents, and Blocks
      11. Trace Files
    2. Critical Skill 5.2 Learn about Oracle User-Managed Backup and Recovery
      1. Types of User-Managed Backups
      2. Cold Backups
      3. Hot Backups
      4. Recovery from a Cold Backup
      5. Recovery from a Hot Backup
      6. Seven Steps to Recovery
      7. Recovery Using Backup Control Files
    3. Critical Skill 5.3 Write a Database Backup
    4. Critical Skill 5.4 Back Up Archived Redo Logs
    5. Critical Skill 5.5 Get Started with Oracle Data Pump
    6. Critical Skill 5.6 Use Oracle Data Pump Export
      1. Some Export Examples
    7. Critical Skill 5.7 Work with Oracle Data Pump Import
      1. A Data Pump Export and Import Example
    8. Critical Skill 5.8 Use Traditional Export and Import
      1. How to Run the Original Utilities
      2. Examples Using Original Export and Import
    9. Critical Skill 5.9 Get Started with Recovery Manager
      1. RMAN Architecture
      2. Set Up a Recovery Catalog and Target Database
      3. Key RMAN Features
      4. Backups
      5. Performing Backups
      6. Restore and Recovery
      7. Project 5-1 RMAN End-to-End
    10. Chapter 5 Mastery Check
  14. 6 PL/SQL
    1. Critical Skill 6.1 Define PL/SQL and Why We Use It
    2. Critical Skill 6.2 Describe the Basic PL/SQL Program Structure
    3. Critical Skill 6.3 Define PL/SQL Data Types
      1. The PL/SQL Character Set
    4. Critical Skill 6.4 Write PL/SQL Programs in SQL*Plus
      1. Project 6-1 Creating a PL/SQL Program
      2. SQL in Your PL/SQL Programs
      3. The Cursor FOR Loop
    5. Critical Skill 6.5 Handle Error Conditions in PL/SQL
      1. Error Handling Oracle-Supplied Variables
    6. Critical Skill 6.6 Include Conditions in Your Programs
      1. Program Control
      2. Project 6-2 Using Conditions and Loops in PL/SQL
    7. Critical Skill 6.7 Create Stored Procedures—How and Why
    8. Critical Skill 6.8 Create and Use Functions
      1. Project 6-3 Creating and Using a Function
    9. Critical Skill 6.9 Call PL/SQL Programs
    10. Chapter 6 Mastery Check
  15. 7 Java
    1. Java Server Fundamentals
    2. Critical Skill 7.1 What Does Java Mean to an Oracle DBA?
    3. Critical Skill 7.2 Overview of Java
      1. Platform Independence
      2. Java in All Three Tiers
      3. Java 2 Platform, Standard Edition
      4. Java 2 Platform, Enterprise Edition
      5. The J2EE Server
      6. Different Types of Java Programs
      7. Standalone Applications
      8. Applets
      9. JavaBeans
      10. JavaServer Pages and Servlets
      11. Enterprise JavaBeans
      12. Advantages of N-Tiered Architectures
      13. Java, XML, Web Services, and Oracle 10g
      14. Opportunities for Oracle DBAs
    4. Critical Skill 7.3 Configure Java for Oracle
      1. Java Initialization Parameters
      2. Environmental Variables
    5. Critical Skill 7.4 Java in Oracle
      1. Native Compilation
      2. Garbage Collection
      3. Things to Watch Out For
      4. Main Components of the JVM
    6. Critical Skill 7.5 JDBC Drivers
      1. JDBC Thin Driver
      2. JDBC Thick Driver
      3. JDBC Server-Side Driver
      4. Use the Proper JDBC Driver
    7. Critical Skill 7.6 Use JDBC
      1. How to Write Database Programs
      2. Project 7-1 Accessing the Database with Java
    8. Critical Skill 7.7 Use SQLJ
      1. SQLJ Translator
      2. Sample SQLJ Code
      3. SQLJ Directions
    9. Critical Skill 7.8 Java-Stored Procedures
      1. Java Utilities for DBAs
      2. loadjava and dropjava Examples
      3. ojvmjava Examples
      4. Privileges
      5. Resolver Specifications
      6. Project 7-2 Creating a Java-Stored Procedure
    10. Critical Skill 7.9 Create Java Objects in Oracle
      1. create java class
      2. create java source
      3. create java resource
    11. Critical Skill 7.10 Understand Oracle Java Products
      1. Oracle Application Server 10g
      2. Oracle JDeveloper 10g
    12. Chapter 7 Mastery Check
  16. 8 XML
    1. Critical Skill 8.1 Understand XML
    2. Critical Skill 8.2 XML DB: Use XML in the Database
    3. Critical Skill 8.3 SQLX: Create XML from Data Stored in Oracle
      1. The SQL/XML Standard
      2. xmlelement ()
      3. xmlattributes ()
      4. xmlforest ()
      5. xmlagg ()
      6. Project 8-1 Creating an XML Listing
    4. Critical Skill 8.4 Store XML in Oracle XML DB
      1. The Native XMLType
      2. The XML DB Repository
      3. Registering an XML Schema
      4. Loading XML Data
      5. Project 8-2 Storing XML
      6. Use the RESOURCE_VIEW
      7. Path-Based Access
      8. Update the XML Document
    5. Critical Skill 8.5 Use Simple Queries
      1. Project 8-3 Using Simple Queries
    6. Critical Skill 8.6 Create a Relational View from XML
    7. Critical Skill 8.7 Learn Programmatic Access Using XSLT
    8. Chapter 8 Mastery Check
  17. 9 Large Database Features
    1. Critical Skill 9.1 What Is a Large Database?
    2. Critical Skill 9.2 Why and How to Use Data Partitioning
      1. Why Use Data Partitioning
      2. Implement Data Partitioning
      3. Project 9-1 Creating a Range-Partitioned Table and a Local Partitioned Index
    3. Critical Skill 9.3 Compress Your Data
      1. Data Compression
      2. Index Key Compression
    4. Critical Skill 9.4 Use Parallel Processing to Improve Performance
      1. Parallel Processing Database Components
      2. Parallel Processing Configuration
      3. Invoke Parallel Execution
    5. Critical Skill 9.5 Use Materialized Views
      1. Uses for Materialized Views
      2. Query Rewrite
      3. When to Create Materialized Views
      4. Create Materialized Views
    6. Critical Skill 9.6 Real Application Clusters: A Primer
      1. RAC Architecture
    7. Critical Skill 9.7 Automatic Storage Management: Another Primer
      1. ASM Architecture
    8. Critical Skill 9.8 Grid Computing: The “g “in Oracle Database 10g
      1. Self-Managing Databases
    9. Critical Skill 9.9 Use SQL Aggregate and Analysis Functions
      1. Aggregation Functions
      2. Analysis Functions
      3. Other Functions
    10. Critical Skill 9.10 Create SQL Models
    11. Project 9-2 Using Analytic SQL Functions and Models
    12. Chapter 9 Mastery Check
  18. A Mastery Check Answers
  19. Index