Introduction to Oracle8i

Course Length: 5 days

This class will develop database-programming skills using SQL and PL/SQL. Students will learn the Oracle architecture, relational design concepts, simple and complex query skills, PL/SQL programming skills, proper software engineering techniques for database programming, Oracle problem solving, and performance tuning skills.

Audience: This class is intended for analysts, developers, designers, administrators, and managers new to Oracle or database programming.

Prerequisites: A background in data processing and programming is strongly recommended.

Oracle8i Server Architecture

What is an Oracle Server?
Oracle8i Architecture
Oracle Instance Components
The Oracle System Global Area
Oracle Background Processes
Database Files
Logical Storage Structures
Different Types of Segments
Tablespaces
Logical Structural Design
Control Files
Redo Log Files
System Change Numbers
Operating System Environment
Important Subdirectories in ORACLE_HOME
The Multi-Threaded Server
JServer
JServer Scalability and Performance
PL/SQL versus Java
Oracle's Object-Relational Database
Oracle Architectures
Database Tools
Oracle Versioning

SQL Programming

What is SQL?
SQL Statements and Standards
SQL (Oracle 7) Data Types
New Oracle8 Data Types
Using the SELECT Command
SELECT Syntax
Constants and Data Conversion
Pseudo Columns
Expressions and Aliases
DISTINCT
WHERE Clause
Arithmetic Operators
Logical Connectives
Predicates
ORDER BY Clause
GROUP BY Clause
Sales Order Entry System
Data Model

Using the Data Dictionary

Metadata and User Data
What is the Oracle Data Dictionary?
Base Tables and Static Views in the Data Dictionary
Dynamic Views in the Data Dictionary
ALL_Views, DBA_Views
USER Views, V$ Views
Useful Database Data Dictionary Views
Using Oracle's SQL*PLUS

What is SQL*PLUS?

Starting an Oracle Session
Logging into Oracle Through SQL*Plus
SQL*PLUS Commands
The DESCRIBE Command
Using the SHOW Command
Modifying Session Variables with SET
The SQL Buffer
Editing System Files with SQL*PLUS
Executing SQL*PLUS Commands
Tracing Statements in SQL*Plus
Setting Up a Site or User Profile
SQL*PLUS Reports
Modifying Column Formats
Using Compute Functions
Working with Report Titles
HELP Command
Creating Web Reports Using SQL*Plus
HTML Encoding

Learning the Data Manipulation Language

The Data Manipulation Language
How to Insert Data
Multi-row Insert
The Insert Command
Modifying Data
Update Command
Deleting Data
Transaction Processing
The COMMIT Command
The ROLLBACK Command
Savepoints
Locks
Using Oracle's SQL Loader

Using SQL Functions

Using SQL Functions
Types of SQL Functions
NUMERIC Functions
ROUND and TRUNC Function
Char Functions
Data Conversion Functions
Cast Examples
DATE Functions
Miscellaneous Functions
DECODE Function
Group Functions
New ROLLUP Operators
The GROUPING Function
Analytical Functions
Full List of SQL Functions

Advanced SQL Concepts

Working with Joins
Types of Joins
Equi-Join
Outer Joins
Left and Right Outer Joins
Subqueries
Subquery Rules
Comparison Tests for Subqueries
Using IN and EXISTS Operators in Subqueries
Using the ANY and ALL Operators in Subqueries
DML Commands Using Single-Row Subqueries
Top-N Analysis
Using Set Operators
NULLS and Logic

Understanding Database Objects

Schemas
The Data Definition Language
Creating Tables
Defining DEFAULT Values
The ALTER TABLE Command
Using Indexes
Query Optimization
Creating Indexes
Working with Logical Tables
More on Views
Creating Views
Creating and Accessing Sequences
Creating Synonyms
Dropping Database Objects
Truncating Tables
Constraints
Different Types of Constraints
Building Constraints
Primary Key Constraints
Foreign Key Constraints
Naming Constraints
Unique Constraints
Not Null Constraints
Check Constraints
Constraints can be Deferred
Enabling and Disabling Constraints
Index-Organized Table
Temporary Tables

Introduction to Partitioning

Oracle8 Size Ceilings
Managing Large Tables with Oracle7
Oracle8 Data Storage
What is Partitioning?
The Partition Key
Partition Syntax
Creating a Range Partitioned Table
Range Partitioning
Partition Features
Advantages of Partitioning
Accessing a Partitioned Table
Data Dictionary Views
Using MAXVALUE
Partitioning Rules to Follow
Partition Extended Table Name Syntax
Multi-Column Partition Keys
Modifying Partition Key Values
Setting Physical Attributes for Partitions
Hash Partitioning
Defining Tablespaces for Hash Partitions
Composite Partitioning
Creating a Composite Partitioned Table
Getting Information about Composite Objects

8i Indexes

Different Types of Indexes
GLOBAL Prefixed Indexes
Local Prefixed Indexes
Local Non Prefixed Indexes
Composite Partitioned Indexes
Reverse Key Indexes
Bitmap Indexes
Improvements to Bitmap Indexes
Function-Based Indexes
Additional Index Features
Domain Indexes
Index Options and Maintenance
Features of Index Rebuilds ONLINE
Rebuilding Indexes Online
Rebuilding the Index
Indexes on Primary Keys

Introduction to Performance Tuning

Understanding Different Types of Applications
Application Tuning
The Rule-Based Optimizer
The Cost-Based Optimizer
Generating Statistics
The ANALYZE Command
Rule- Based versus Cost-Based Optimization
Setting the Optimizer Mode
Hints
EXPLAIN PLAN
AUTOTRACE
SQL Trace and TKPROF
TKPROF Data
DBMS_STATS
Generating and Removing Statistics
Exporting and Importing Statistics

Oracle Security

System Privileges
Roles
The Granting System Privileges
Viewing System Privileges
Granting Object Privileges
Privileges and the User Group PUBLIC
Revoking System Privileges and Roles
Object Privileges
Database Authentication
Predefined Roles
Setting up a Role
Working with Default Roles
Enabling and Disabling Roles
Guidelines and Standards for Roles

Introduction to PL/SQL Programming

What is PL/SQL?
Features of PL/SQL
The Block Structure
Language Rules
Using PL/SQL
Executing PL/SQL from SQL*Plus
Variables and Constants
Identifiers
Customizing SQL*Plus for PL/SQL Scripts
SELECT INTO Statement
Using Bind Variables
Interactive PL/SQL Programs
Oracle Datatypes are supported in PL/SQL
Numeric Datatypes
Datatype Conversion
Implicit and Explicit Conversion
%TYPE and %ROWTYPE
Displaying Dept Values
Subtypes
Subtype Declarations
Labels
Variable Scope and Visibility Rules
SQL and PL/SQL Reserved Words
Direction of PL/SQL
External Routines and Java Stored Procedures

Control Flow

Operators
Built-In Functions
Handling Exceptions
Working with Exceptions
SQLCODE and SQLERRM Functions
Predefined PL/SQL Exceptions
User-Defined Exceptions
The Procedure Raise_Application_Error
IF Statements
Generic LOOP
Working with NULL
LOOP Labels
FOR LOOP
WHILE LOOP
GOTO Statement

Working with Cursors

Working with Cursors
Implicit Cursors and Attributes
Explicit Cursors
Using Subqueries with Cursors
Using Cursor FOR LOOPs
Using %ROWTYPE with Cursors
Using Aliases and Subqueries
Using Bind Variables
Using Parameters with Explicit Cursors
Using Cursor Parameters
Modifying Data using Explicit Cursors
WHERE CURRENT OF
Managing Transactions in PL/SQL Programs

Procedures, Functions, and Packages

Working with Subprograms
Named Subprograms
Software Evolution
Stored Procedures
Parameters
Positional and Named Notation
Executing a Stored Procedure
Compiling and Debugging Stored Procedures
Stored Function Syntax
Executing a Function from SQL*Plus
The RETURN Statement
Packages
Managing Side Effects
Pragma RESTRICT_REFERENCES
Defining Subprograms Within Another Subprogram
Forward Declarations
Package Specification and Body

Collections

Records
Creating a PL/SQL Record
PL/SQL2 Tables
Defining PL/SQL2 Tables
Table Attributes
PL/SQL8 Tables
Instantiating PL/SQL Tables
Coding Examples
Create a Type Specification
CAST Operator
Operator "THE"
Queries on Nested Tables

Integration and Extensibility

Changes in the Internet
Oracle AppWizard
Integrating the MS Transaction Server
Development Languages
External Routines
The Call Specification
External Body Clause
Processing External Routines
Callbacks
Java Stored Procedures
Data Cartridges
COM Cartridge
Additional Cartridges
Custom Indexes