Hands-On Oracle Database 10g Express Edition for Windows

Hands-On Oracle Database 10g Express Edition for Windows

by Steve Bobrowski
Released July 2006
Publisher(s): McGraw Hill Computing
ISBN: 9780072263312

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

The fastest way to get up and running on Oracle Database XE Take full advantage of all the powerful features available in Oracle Database 10 Express Edition with help from this easy-to-follow Oracle Press guide.Hands-On Oracle Database 10 Express Edition for Windows shows you, step by step, how to set up, administer, tune, troubleshoot, and secure a robust database system. You will also learn to use PL/SQL and Oracle Application Express to build custom database applications. Get started today with the Oracle database that’s free to develop, deploy, and distribute. The ebook version does not provide access to the companion files.

Table of contents

  1. Cover Page
  2. Hands-OnOracleDatabase10gExpressEditionforWindows
  3. Copyright Page
  4. Contents
  5. Foreword
  6. Acknowledgments
  7. Introduction
  8. PART I Getting Started
    1. 1 Introduction to Databases and Oracle
      1. Information Management
        1. Databases
        2. Database Management Systems
        3. Database Applications
        4. Oracle Database 10g
      2. Oracle Fundamentals
        1. Databases and Instances
        2. Tables
        3. SQL and Data Access
        4. Database Users and Sessions
        5. SQL*Plus
        6. Oracle Application Express
      3. Chapter Summary
    2. 2 Install and Start Using Oracle Database 10g Express Edition
      1. Installation Overview
      2. Database Server System Requirements
        1. EXERCISE 2.1: Check System Requirements
      3. Additional Requirements for Application Development
      4. Oracle Database 10g Express Edition Software
      5. Installation
        1. EXERCISE 2.2: Establish a Microsoft Windows Administrator Session
        2. EXERCISE 2.3: Install Oracle XE Step-by-Step
      6. Post-Installation Exercises
        1. EXERCISE 2.4: Accessing the Documentation
        2. EXERCISE 2.5: Registering for the Discussion Forum
        3. EXERCISE 2.6: Accessing the Support Forum
      7. Basic Oracle XE Skills
        1. Oracle Startup and Shutdown
          1. EXERCISE 2.7: Check the Status of Oracle XE
          2. EXERCISE 2.8: Start Oracle Manually
          3. EXERCISE 2.9: Stop Oracle Manually
          4. EXERCISE 2.10: Automate Oracle Startup
      8. Database Connections
        1. EXERCISE 2.11: Connect to Oracle with SQL*Plus
        2. EXERCISE 2.12: Disconnect from Oracle and Exit SQL*Plus
        3. EXERCISE 2.13: Launch the Database Home Page
      9. Troubleshooting Tips
        1. Confirm Existence of Oracle XE Software and Database
        2. Check Oracle-Related Services
        3. Permit Network Access
        4. Web Browser Configuration
      10. Oracle Database 10g Express Edition Updates
      11. Using This Book's Support Files
      12. Chapter Summary
  9. PART II Fundamentals of Application Development
    1. 3 Access Database Data with SQL
      1. Chapter Prerequisites
      2. What Is SQL?
        1. Types of SQL Commands
        2. Application Portability and the ANSI/ISO SQL Standard
        3. Ad Hoc SQL Tools
      3. Retrieving Data with Queries
        1. The Structure of a Query
        2. Building Basic Queries
          1. EXERCISE 3.1: Retrieving All Columns and Rows
          2. EXERCISE 3.2: Retrieving Specific Columns
          3. EXERCISE 3.3: Using the SQL*Plus DESCRIBE Command
          4. EXERCISE 3.4: Specifying an Alias for a Column
        3. Building Expressions in a Query's SELECT Clause
          1. EXERCISE 3.5: Building SELECT Clause Expressions with the Concatenation String Operator
          2. EXERCISE 3.6: Building SELECT Clause Expressions with Arithmetic Operators
          3. EXERCISE 3.7: Building SELECT Clause Expressions with SQL Functions
          4. EXERCISE 3.8: Working with Nulls in SELECT Clause Expressions
          5. EXERCISE 3.9: Implementing Conditional Logic in SELECT Clause Expressions
        4. Retrieving Specific Rows from Tables
          1. EXERCISE 3.10: Building WHERE Clause Conditions with Relational Operators
          2. EXERCISE 3.11: Building WHERE Clause Conditions with Subqueries
          3. EXERCISE 3.12: Building Composite WHERE Clause Conditions with Logical Operators
        5. Grouping and Sorting Data Within a Query's Result Set
          1. EXERCISE 3.13: Grouping Records in a Query's Result Set
          2. EXERCISE 3.14: Rolling Up Groups into Supergroups
          3. EXERCISE 3.15: Ordering Records in a Query's Result Set
        6. Joining Data in Related Tables
          1. EXERCISE 3.16: Building an Inner Join of Two Tables
          2. EXERCISE 3.17: Building an Outer Join of Two Tables
      4. Inserting, Updating, and Deleting Rows in Tables
        1. EXERCISE 3.18: Inserting New Rows into a Table
        2. EXERCISE 3.19: Updating Rows in a Table
        3. EXERCISE 3.20: Deleting Rows from a Table
      5. Committing and Rolling Back Transactions
        1. EXERCISE 3.21: Committing and Rolling Back Transactions
      6. Transaction Design
        1. Units of Work
        2. Read-Write Transactions
        3. Read-Only Transactions
      7. Building SQL with Oracle Application Express
        1. Introducing Oracle Application Express's Query Builder
          1. EXERCISE 3.22: Using Query Builder
      8. Chapter Summary
    2. 4 Code Database Access Programs with PL/SQL
      1. Chapter Prerequisites
      2. What Is PL/SQL?
      3. PL/SQL Blocks
        1. Program Declarations
        2. The Program Body
        3. Exception Handlers
        4. Program Comments
      4. The Fundamentals of PL/SQL Coding
        1. Working with Program Variables
          1. EXERCISE 4.1: Declaring Variables and Constants with Basic Datatypes
          2. EXERCISE 4.2: Assigning Values to Variables
        2. Controlling Program Flow
          1. EXERCISE 4.3: Using PL/SQL Loops
          2. EXERCISE 4.4: Using the PL/SQL Command IF ... ELSIF ... ELSE
        3. Interacting with Databases
          1. EXERCISE 4.5: Manipulating Table Data with DML Statements
          2. EXERCISE 4.6: Assigning a Value to a Variable with a Query
        4. Declaring and Using Subprograms: Procedures and Functions
          1. EXERCISE 4.7: Declaring and Using a Procedure
          2. EXERCISE 4.8: Declaring and Using a Function
        5. Working with Record Types
          1. EXERCISE 4.9: Declaring and Using Record Types
        6. Using the %TYPE and %ROWTYPE Attributes
          1. EXERCISE 4.10: Using the %TYPE Attribute
          2. EXERCISE 4.11: Using the %ROWTYPE Attribute
        7. Working with Cursors
          1. EXERCISE 4.12: Declaring and Using a Cursor
          2. EXERCISE 4.13: Manipulating a Cursor's Current Row
        8. Working with Collections
          1. EXERCISE 4.14: Declaring and Initializing a Nested Table
          2. EXERCISE 4.15: Using Collection Methods with a Nested Table
        9. Handling Program Exceptions
          1. EXERCISE 4.16: Handling Predefined Exceptions
          2. EXERCISE 4.17: Declaring and Handling User-Defined Exceptions
      5. Types of PL/SQL Programs
        1. Anonymous PL/SQL Blocks
        2. Stored Procedures and Functions
          1. EXERCISE 4.18: Creating and Using Stored Procedures
          2. EXERCISE 4.19: Creating and Using Stored Functions
        3. Packages
          1. EXERCISE 4.20: Declaring and Using a Package
        4. Database Triggers
          1. EXERCISE 4.21: Creating and Using Database Triggers
      6. Chapter Summary
    3. 5 Build a Basic Relational Schema
      1. Chapter Prerequisites
      2. Schemas
        1. Schemas, an Entirely Logical Concept
        2. The Correlation of Schemas and Database User Accounts
      3. Database Tables
        1. Columns and Datatypes
        2. Data Integrity and Integrity Constraints
        3. Creating Tables and Integrity Constraints
          1. EXERCISE 5.1: Start the Object Browser
          2. EXERCISE 5.2: Create the PARTS Table
          3. EXERCISE 5.3: Create the CUSTOMERS Table
          4. EXERCISE 5.4: Modify a Column's Datatype
          5. EXERCISE 5.5: Add a Primary Key to a Table
          6. EXERCISE 5.6: Add a Composite Unique Key to a Table
          7. EXERCISE 5.7: Add a Not Null Constraint to a Table
          8. EXERCISE 5.8: Create the SALESREPS Table
          9. EXERCISE 5.9: Create the ORDERS Table
          10. EXERCISE 5.10: Create the ITEMS Table
          11. EXERCISE 5.11: Add a Column and Referential Integrity Constraint to a Table
          12. EXERCISE 5.12: Test Integrity Constraints
      4. Views
        1. Creating Views
          1. EXERCISE 5.13: Create and Use a Read-Only View
          2. EXERCISE 5.14: Create an Updateable View
          3. EXERCISE 5.15: Create an Updateable Join View
          4. EXERCISE 5.16: Create an INSTEAD OF Trigger for a View
      5. Sequences
        1. Creating and Using Sequences
          1. EXERCISE 5.17: Create a Sequence
          2. EXERCISE 5.18: Use and Reuse a Sequence Number
      6. Synonyms
        1. Private and Public Synonyms
        2. Creating Synonyms
          1. EXERCISE 5.19: Create a Private Synonym
          2. EXERCISE 5.20: Using a Synonym
      7. Indexes
        1. Normal (B*) Indexes
        2. Using Normal Indexes Appropriately
        3. Creating Normal Indexes
          1. EXERCISE 5.21: Create a Normal Index
      8. The Data Dictionary: A Unique Schema
        1. Categories of Data Dictionary Views
          1. EXERCISE 5.22: Query the Data Dictionary
      9. Exploring Other Object Browser Features
        1. EXERCISE 5.23: Reveal Object Dependencies
        2. EXERCISE 5.24: Gather Object Statistics
        3. EXERCISE 5.25: Generate Object DML
      10. Chapter Summary
    4. 6 Build an Application with Oracle Application Express
      1. Chapter Prerequisites
      2. Introducing the Application Development Lifecycle
        1. Analyzing Processes and Defining Requirements
        2. Designing the Application to Meet the Requirements
        3. Implementing the Application Using the Design
        4. Testing That the Application Meets the Requirements
        5. Deploying the Application
        6. Maintaining the Application
      3. Software Modeling and the Unified Modeling Language
        1. Use Case Diagrams
        2. Class Diagrams
      4. Building the Application Schema
        1. EXERCISE 6.1: Upload and Run a SQL Script
        2. EXERCISE 6.2: Set User Interface Defaults
      5. Loading Schema Data
        1. Loading Data Manually
          1. EXERCISE 6.3: Manually Enter Data
          2. EXERCISE 6.4: Create Triggers to Generate Primary Keys
        2. Loading Spreadsheet and Text File Data
          1. EXERCISE 6.5: Load Data from a Text File
        3. Loading XML Data
          1. EXERCISE 6.6: Load Data from an XML File
      6. Creating the SysMgmt Application
        1. EXERCISE 6.7: Create the SysMgmt Application with the Create Application Wizard
      7. Running and Testing the Application
        1. EXERCISE 6.8: Run and Test the SysMgmt Application
        2. EXERCISE 6.9: Navigate Application Report Pages
        3. EXERCISE 6.10: Understand Oracle Application Express URLs
        4. EXERCISE 6.11: Browse and Review Application Pages
      8. Refining the Application
        1. EXERCISE 6.12: Create Lists of Values (LOVs) for Forms
        2. EXERCISE 6.13: Add LOVs to Forms
        3. EXERCISE 6.14: Modify a Report's Query and Make New Columns Searchable
      9. Maintaining the Application
        1. EXERCISE 6.15: Create a New Table
        2. EXERCISE 6.16: Set UI Defaults for the New Table
        3. EXERCISE 6.17: Create a New Trigger
        4. EXERCISE 6.18: Add Rows to the DATABASE_SERVICE_REQUESTS Table
        5. EXERCISE 6.19: Add New Application Pages
        6. EXERCISE 6.20: Add a Navigation Menu
        7. EXERCISE 6.21: Copy Breadcrumbs to the New Report and Form Pages
        8. EXERCISE 6.22: Add Search and Display Controls to the New Report
        9. EXERCISE 6.23: Add an Analysis Page to the Application
      10. Deploying the Application
        1. EXERCISE 6.24: Modify Application Attributes for Deployment
        2. EXERCISE 6.25: Deploy the Application on Another Computer
      11. Managing Application Access and Application Users
        1. EXERCISE 6.26: Create an Application Express User
      12. Chapter Summary
  10. PART III Database Administration
    1. 7 Secure Database Access
      1. Chapter Prerequisites
      2. User Management
        1. Oracle Database Users and Oracle Application Express Users
        2. Default Database User Accounts
        3. Database User Properties
          1. EXERCISE 7.1: Display Information About Database Users
          2. EXERCISE 7.2: Create a Database User
          3. EXERCISE 7.3: Test the New Database User Account
          4. EXERCISE 7.4: Modify Your Own Password
          5. EXERCISE 7.5: Alter or Drop a Database User
      3. Privilege Management
        1. Types of Database Privileges
        2. Granting and Revoking Privileges
          1. EXERCISE 7.6: Grant System Privileges to a User
          2. EXERCISE 7.7: Revoke System Privileges from a User
          3. EXERCISE 7.8: Grant Object Privileges to a User
          4. EXERCISE 7.9: Revoke Object Privileges from a User
        3. Privilege Management with Roles
          1. EXERCISE 7.10: Create a Role
          2. EXERCISE 7.11: Grant System Privileges and Roles to a Role
          3. EXERCISE 7.12: Grant Object Privileges to a Role
          4. EXERCISE 7.13: Grant a Role to a User
          5. EXERCISE 7.14: Set a User's Default Role
          6. EXERCISE 7.15: Enable and Disable a Role
      4. Resource Limitation
        1. Tablespace Quotas
          1. EXERCISE 7.16: Providing Specific Tablespace Quotas for a User
        2. Resource Limit Profiles
        3. Managing Resource Limit Profiles
          1. EXERCISE 7.17: Enable Resource Limitation
          2. EXERCISE 7.18: Create a Profile
          3. EXERCISE 7.19: Alter Profile Settings
          4. EXERCISE 7.20: Manually Force a User's Password to Expire
          5. EXERCISE 7.21: Use Password Complexity Checking
          6. EXERCISE 7.22: Set a User's Profile
          7. EXERCISE 7.23: Experiment with Password Management Settings
          8. EXERCISE 7.24: Alter the Default Database Profile
      5. Displaying Security Information
        1. EXERCISE 7.25: Display Information About Users
        2. EXERCISE 7.26: Display Information About Roles
        3. EXERCISE 7.27: Display Information About Tablespace Quotas
        4. EXERCISE 7.28: Display Information About Profiles
      6. Chapter Summary
    2. 8 Manage Database Space
      1. Chapter Prerequisites
      2. Logical vs. Physical Storage Structures
      3. Tablespaces and Data Files
        1. Permanent, Temporary, and Undo Tablespaces
        2. The SYSTEM Tablespace
        3. The SYSAUX Tablespace
        4. Other Tablespaces
        5. Online and Offline Tablespaces
        6. Read-Only and Read-Write Tablespaces
        7. A Tablespace's Data Files
        8. Use of Data File Space
        9. Data File Sizes
        10. Online and Offline Data Files
        11. Creating and Managing Permanent Tablespaces and Data Files
          1. EXERCISE 8.1: Display Database Storage Utilization
          2. EXERCISE 8.2: Display Tablespace Information
          3. EXERCISE 8.3: Display Data File Information
          4. EXERCISE 8.4: Create a Permanent Tablespace
          5. EXERCISE 8.5: Modify the Storage Properties of a Data File
          6. EXERCISE 8.6: Control Tablespace Availability
      4. Segments, Extents, and Data Blocks
        1. Types of Segments in an Oracle Database
        2. Data Blocks
        3. Managing Storage for Tables and Indexes
          1. EXERCISE 8.7: Display the Data Block Size Information
          2. EXERCISE 8.8: Display the Segments in a Schema
          3. EXERCISE 8.9: Compact (Shrink) Segments Wasting Space
          4. EXERCISE 8.10: Create a Table in a Specific Tablespace
          5. EXERCISE 8.11: Reserve Space for Updates to Existing Table Rows
          6. EXERCISE 8.12: Manage Space for LOBs
          7. EXERCISE 8.13: Perform Miscellaneous Space Management Tasks
      5. Undo Tablespaces
        1. Undo Segments
        2. Undo Segments Are Multipurpose
        3. Managing Undo Tablespaces and Retention Times
          1. EXERCISE 8.14: Display and Modify Automatic Undo Management Parameters
      6. Temporary Segments, Tablespaces, and Groups
        1. Managing Temporary Tablespaces
          1. EXERCISE 8.15: Reveal a Database's Default Temporary Tablespace
          2. EXERCISE 8.16: Display Information About Temporary Tablespaces
          3. EXERCISE 8.17: Display Information About Temp Files
      7. Chapter Summary
    3. 9 Protect Your Oracle XE Database
      1. About This Chapter's Practice Exercises
      2. An Overview of Database Problems and Solutions
        1. Instance Crashes and Crash Recovery
        2. Media (Physical) Failures, Backups, and Recovery Manager
        3. Logical Errors and Oracle Flashback
        4. Devising a Database Protection Policy
      3. Oracle Database Backup and Recovery Overview
        1. The Flash Recovery Area
        2. The Transaction Redo Log
        3. The Database Control File
        4. Database Backups
        5. Recovery Manager
      4. Configuring Oracle XE Database Protection
        1. EXERCISE 9.1: Display Information About the Flash Recovery Area
        2. EXERCISE 9.2: Display Information About the Control File
        3. EXERCISE 9.3: Display Information About the Redo Log
        4. EXERCISE 9.4: Create a Cold Database Backup
        5. EXERCISE 9.5: Establish a Privileged Administrator Session with SQL*Plus
        6. EXERCISE 9.6: Move the Flash Recovery Area and Online Redo Logs
        7. EXERCISE 9.7: Enable Media Recovery
        8. EXERCISE 9.8: Create a Hot Database Backup
        9. EXERCISE 9.9: Confirm Successful Log Archiving
        10. EXERCISE 9.10: Create Log Groups with Multiple Members
        11. EXERCISE 9.11: Mirror the Database Control File
      5. Backing Up Databases with Recovery Manager
        1. EXERCISE 9.12: Start Recovery Manager
        2. EXERCISE 9.13: Connect to the Target Database
        3. EXERCISE 9.14: Display and Configure Persistent Recovery Manager Settings
        4. EXERCISE 9.15: Back Up a Database
        5. EXERCISE 9.16: Back Up an Individual Tablespace
        6. EXERCISE 9.17: Back Up Archived Log Files
        7. EXERCISE 9.18: List Information About Backup Sets
        8. EXERCISE 9.19: Report Important Status Information for a Database
        9. EXERCISE 9.20: Delete Obsolete Backup Sets
        10. EXERCISE 9.21: Understand Flash Recovery Area Space Usage
        11. EXERCISE 9.22: Build a New Backup Script
      6. Additional Database Backup Topics
        1. Backups of Other Database-Related Files
        2. Logical Database Backups
      7. More Database Recovery Options
        1. Complete Recovery
        2. Incomplete Recovery
        3. What About Damage to Log Groups and the Control File?
      8. Recovering Databases from Problems
        1. EXERCISE 9.23: Simulate a System Crash and Perform Crash Recovery
        2. EXERCISE 9.24: Simulate a Lost Data File and a Database Recovery
      9. Using Oracle Flashback Features
        1. EXERCISE 9.25: Restore a Dropped Table Using Oracle Flashback Drop
        2. EXERCISE 9.26: Restore Lost Data Using Oracle Flashback Query
      10. Chapter Summary
    4. 10 Tune Application and Database Instance Performance
      1. Chapter Prerequisites
      2. Oracle Tuning Concepts
        1. Measures of Oracle Database Application Performance
        2. Realistic and Unrealistic Performance Goals
        3. Tunable Components
        4. Tuning Methodologies
        5. The Cost of Work
      3. Tuning Statistics
        1. Commonly Referenced Tuning Statistics
        2. Instance Efficiency Ratios
        3. The V$ Views
        4. Statspack
      4. Application Tuning
        1. Complex SQL Statements
        2. Data Access Methods and Indexes
        3. Data Concurrency and Locking
        4. Application-Tuning Exercises
          1. EXERCISE 10.1: Install Statspack
          2. EXERCISE 10.2: Take a Default Snapshot
          3. EXERCISE 10.3: Simulate and Investigate the Problem
          4. EXERCISE 10.4: Take Another Default Snapshot
          5. EXERCISE 10.5: Review Statspack Snapshots
          6. EXERCISE 10.6: Create a Statspack Report
          7. EXERCISE 10.7: Analyze a Statspack Report
          8. EXERCISE 10.8: Create Indexes for Filter Predicate Columns
          9. EXERCISE 10.9: Explain the New Results
          10. EXERCISE 10.10: Discover and Display Unindexed Foreign Keys
          11. EXERCISE 10.11: Explain the New Execution Plan
          12. EXERCISE 10.12: Gather New Statistics
          13. EXERCISE 10.13: Analyze Other Application-Related Statistics
      5. Database Instance Tuning
        1. The System Global Area
        2. Program Global Areas
        3. Database Instance Tuning Exercises
          1. EXERCISE 10.14: Display Current Overall Memory Utilization
          2. EXERCISE 10.15: Display Information About the SGA
          3. EXERCISE 10.16: Modify the Size of the SGA
          4. EXERCISE 10.17: Display Information About PGA Memory
          5. EXERCISE 10.18: Modify the Allowable PGA Memory
      6. Chapter Summary
  11. Index

Product information

  • Title: Hands-On Oracle Database 10g Express Edition for Windows
  • Author(s): Steve Bobrowski
  • Release date: July 2006
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780072263312