Hands-On Oracle Database 10g Express Edition for Windows

Name: Hands-On Oracle Database 10g Express Edition for Windows
Brand: McGraw-Hill Education
Price: 115.69 EUR
Availability: OnlineOnly

Steve Bobrowski(Autor*in)

McGraw-Hill Education (Verlag)

1. Auflage

Erschienen am 26. Juli 2006

509 Seiten

E-Book

ePUB mit Adobe-DRM

Systemvoraussetzungen

978-0-07-149144-0 (ISBN)

115,69 €inkl. 7% MwSt.

Systemvoraussetzungen

für ePUB mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

Weitere Details

Weitere Ausgaben

Inhalt

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

Systemvoraussetzungen

Als PDF speichern Als Link merken