Oracle9i for Dummies

About This Book
Conventions Used in This Book
What You're Not to Read
Foolish Assumptions
How This Book Is Organized

Part I: Road Map
Part II: Getting Started
Part III: Putting Oracle9i to Work
Part IV: Tuning Up and Turbocharging
Part V: The Part of Tens

Icons Used in This Book

Chapter 1: A Quick Tour of Oracle9i

Discovering Oracle9i: The Program That Runs It All

Oracle9i's core package
Oracle9i's newest star: XML Developer's Kit
The new look of Enterprise Manager
Console packs four all-star components

Looking Around with Enterprise Manager Console

The Object and Object contents windows
The other Schema Manager elements

Getting Acquainted with SQL: The Messenger Priestess of Oracle9i

Starting and stopping SQL*Plus on a desktop
Using SQL*Plus in command-line mode

Getting Help: Let Your Mouse Do the Walking

Chapter 2: Data Whaaat? A Database Primer

Dataspeak Definitions for the Techno-Impaired
Relational Database Concepts

Users and roles
Tablespaces and datafiles
Tables
Columns and rows
Relationships

Object-Relational Database Concepts

An object defined
The scoop on object types
The connection of relational tables with objects
Object reference
Methods to their madness
Nested tables
Arrays

Database Things That You Can Do with Oracle9i

Keeping track of a fishbowl (the easy example)
Running a pet shop (the medium example)
Tracking endangered species globally (the hard example)

Chapter 3: SQL Nuts and Bolts

Starting SQL*Plus
Asking a Question in SQL

The basic SQL query
Some sample queries
Some tips to help you write good queries

Using an Editor While Running SQL*Plus
Pulling Out Data without Breaking Anything
Combining Tables: The Meat and Potatoes of SQL

Basic join query structure
Examples of join queries

Using the Built-in Functions of Oracle9i

Reformatting a date with TO_CHAR
Concatenating columns with concatenate (||)

Grouping and Summarizing Data
Making Power Changes in Data

Modifying data with the UPDATE command
Inserting new rows with grace and style
Deleting rows with distinction

Fixing Mistakes, or Where's the Brake?

The COMMIT and ROLLBACK commands
Commands that can't be undone, even with ROLLBACK

Traveling Back in Time with Flashback Queries

Chapter 4: Object SQL: The New Stuff

Starting SQL*Plus
Querying Object Tables in SQL

The basic object-oriented SQL query
Queries using a nested table
Queries using an array

Making Changes to Object Table Data

Updating an object
Updating one row in a nested table
Inserting rows into an object table
Inserting rows into an array
Inserting rows into a nested table
Deleting rows from an object table
Deleting a row from a nested table

Chapter 5: Using Enterprise Manager's DBA Tools

Introducing Enterprise Manager
Exploring Four Console Tools

Storage Manager: The external viewpoint
Security Manager: The keeper of the gate
Schema Manager: The builder of tables
Instance Manager: The activity monitor

Chapter 6: The Relational Model and You

Redundant Relational Database Redundancy
Keys Rule

Considering types of keys
Importing foreign keys

The Key, the Whole Key, and Nothing but the Key

Old MacDonald's farm
Porridge for everyone
Back on the farm

One Too Many: The Bread and Butter of Relational Databases
Objects and the Oracle9i Database

Defining an object
Connecting relational tables with objects

Chapter 7: Getting Familiar with Data-Dictionary Views

Using Data-Dictionary Views
Looking at Data-Dictionary Views with SQL*Plus

Chapter 8: Oracle's User

Playing a Role

What kinds of users are there?
What kind of user are you?

Creating a New User from Scratch
Changing Your Password

Chapter 9: Defining Tables, Tablespaces, and Columns

Tablespace: The Final Frontier

Oracle Managed Files: The real world meets the virtual world
Locally managed versus dictionary managed tablespaces

A Word or Two about Columns

Defining columns in Oracle9i
Allowing nulls or not

Console's Spreadsheet for Tables

Step 1: Defining the columns
Step 2: Defining the constraints
Step 3: Defining the storage

A Table of Your Own with SQL

Defining the table name and the columns
Defining the primary-key constraint
Adding storage parameters and creating the table

Chapter 10: Creating Object Types, Objects, and References

An Overview of Creating Objects
Types Need No Space

Defining an object type
Making a table type
Creating an array type

Objects Still Sit inside Tablespaces

Making an object table
Making an object table with mixed columns
Creating constraints on object tables

Chapter 11: Security Options: Roles and Grants

Security Blanket Included
Roles Meet the Real World
Roles and Privileges with Security Manager

Creating a role and assigning object privileges
Assigning users to a role

Oracle Internet Directory

Looking at a virtual private database on the Web
Self-service data administration on the Web

Chapter 12: Views and Synonyms: Do You See What I See?

Views: Like a Table — Almost

Views that narrow
Views that tie everything together
Views with Console
Object views with Console
Materialized views — pros and cons

Synonyms: Nicknames for Tables and Views

Creating a synonym with Console
Combining a grant and a synonym

Chapter 13: Java and the Internet Database

Discovering Java Virtual Machine

Using Oracle9i's JVM — some benefits
Introducing SQLJ

Running a Java Applet that Queries the Database

Chapter 14: XML and Enterprise Java Beans

What Is XML?

A Sample XML XSQL servlet

Anyone for EJB?

Chapter 15: The Five Ws of Safeguarding Your Data

Why Back Up
What to Back Up
When to Back Up
How to Back Up

Data Manager
EXP and IMP commands
Oracle9i Data Guard can save you from downtime
Log Miner: Digging around to find pay dirt

Where to Hide Your Backup Files

Chapter 16: Special Tasks with Enterprise Manager

Starting Oracle9i

Starting the database with Instance Manager
Starting the database using SQL*Plus

Shutting Down Oracle9i

Shutting down Oracle9i on a desktop
Shutting down Oracle9i on a mainframe or a network

Initializing Enterprise Manager Console

Creating a repository for Enterprise Manager
Starting Intelligent Agent
Starting the Enterprise Management service
Setting a Web-based Console
Taking a quick tour of Enterprise Manager Console

Chapter 17: What's Slowing Down Your Query?

The Oracle Tuning Pack

Watch Index Tuning Wizard work its magic
Let SQL Analyze help tune queries

Helping Optimizer Do Its Job

Moods and modes of Optimizer
One command to analyze your entire schema
Personal attention: Analyzing one table at a time
A hint . . . you're getting warmer

Getting Ahead While Testing and Tuning

Chapter 18: Improving Performance with Indexes and Memory Buffers

Why Create an Index?
Creating Your Own Indexes

Adding an index
Removing an index

Using Indexes with Object Tables and Nested Tables

Creating an index on an object table
Creating an index on a nested table
Removing an index on an object table or a nested table

Enterprise Manager's Memory Manager

How Oracle9i uses memory
Getting advice from Enterprise Manager

Chapter 19: Correcting Flaws

Categories of Changes
Changing Columns

Baby Bear: Easy as porridge
Mama Bear: A few medium steps
Papa Bear: A lotta steps

Changing Tables

Schema Manager table changes
SQL*Plus table changes: Move or rename a table

Modifying Object Tables and Object Types

Modifying object tables
Modifying object types

The Oracle Change Management Pack — Something New to Try!

Chapter 20: Ten Tips for Good Design

Name Tables and Columns Creatively and Clearly
Look Before You Leap (Design Before You Build)
Go Ahead, Leap! (Build a Prototype)
Share — Don't Reinvent the Wheel
Remember That Primary Keys Are Your Friends

Small keys take up less room
Nonintelligent keys are easy to maintain

Use Caution When Modifying Table Definitions
Handle Derived Data Efficiently
Approach Internet Security in Practical Ways

Low security — application proxy
Medium security — application context
High security — SSL

Be Smart about Test Data
Talk to Non-Techie-Type Humans

Chapter 21: Ten Handy Oracle9i Internet Features

interMedia
iSQL*Plus
PL/SQL on the Web
Internet File System
JDeveloper
Web Single Sign-On
Java Stored Procedures
Web Assistant
Ultra Search
Oracle9i Application Server Wireless

Appendix A: Glossary