Oracle8i
New Features for Developers
This
course will give participants a hands-on introduction to the key
features and enhancements of Oracle8. Class exercises will challenge
participants to build on their knowledge and experience using
Oracle7 as they implement Oracle8 features, including object-oriented
technology.
Audience:
Application developers, technical support professionals, and designers.
Prerequisites:
Oracle for Application Developers
Introduction to
Partitioning
Oracle Versioning
Large Tables
Oracle8.0 - The Object-Relational Release
Oracle8 Size Ceilings
Oracle 8i - The Internet Release
Oracle8 Data Storage
Creating a Range Partitioned Table
The Partition Key
Partition Syntax
Partition Features
Advantages of Partitioning
Accessing a Partitioned Table
Data Dictionary Views
Range Partitioning by Date Values
Date Partitioned Example
Using MAXVALUE
Partitioning Rules to Follow
Partition Extended Table Name Syntax
Restrictions on Partition Extended Name
Multi-Column Partition Keys
Inserting into a Multi-Column Partition Key
Modifying Partition Key Values
Setting Physical Attributes for Partitions
Hash Partitioning
Defining Tablespaces for Hash Partitions
Composite Partitioning
Creating a Composite Partitioned Table
Partition-Wise Joins
Information about Composite Objects
Enhancements to the Plan Table
New Plan Table Columns
Analyzing Execution Plans
Scan All Partitions
Perform a Partition Scan
Scan Multiple Partitions
Operations on Partitions
8i Indexes
Different Types of Indexes
Partitioned Indexes
GLOBAL Prefixed Indexes
Create a Global Prefixed Index
Local Indexes
Local Prefixed Indexes
Create a Local Prefixed Index
Local Non-Prefixed Indexes
Composite Partitioned Indexes
Reverse Key Indexes
Compressing B*Tree Indexes
Bitmap Indexes (Review)
Improvements to Bitmap Indexes
Function-based Indexes
Working with Function-based Indexes
Descending Indexes
Domain Indexes
Index Options and Maintenance
Features of Index Rebuilds ONLINE
Rebuilding Indexes Online
Rebuilding the Index
The ALTER INDEX COMPUTE STATISTICS Option
Coalescing the Index
Non-Unique Indexes on Primary and Unique Keys
Optimizer Enhancements
Cost-based Optimizer Enhancements
Generating Statistics
DBMS_STATS
Copying Statistics to Different Databases
Monitoring Tables
Computing Statistics (Review)
Enhanced Cost-based Optimizer Features
Creating Stored Outlines
OUTLN_PKG Package
New Data Storage
Features
Index-Organized Tables and Syntax
Restrictions and Issues with IOTs
Getting Information on IOTs
Partitioning Index-Organized Tables
Global Temporary Tables
Marking a Column as Unused
The DROP Option
DROP COLUMN Examples
Moving Tables
Constraints can be Deferred
Deferred Constraint Example
Setting Deferred Constraints
Setting the RELY Flag
Enabling and Disabling Constraints
Locally Managed Tablespaces
Dictionary Versus Locally Managed Tablespaces
Read-Only Tablespaces
Converting Dictionary and Local Tablespaces
Transportable Tablespaces
Steps to Transport a Tablespace
Introduction to Object Technology
Software Development
Issues with 3GL Development
Issues with Relational Database Development
Object-Relational Development
Goals of Object-Relational Development
Functional Versus Object Hierarchies
Software Evolution
What is a Type?
Type and Class Definitions
What is an Object?
More on Objects
Creating a User Defined Type
Rules When Working with User Defined Types
Defining an Abstract Data Type
Definitions
Inheritance
Single Versus Multiple Inheritance
Polymorphism
With and Without Polymorphism
Different Object Methodologies
What is UML?
Visual Models
Attributes
Different Types of Methods
Object-Relational Concepts
Object-Relational Databases
New Object-Relational Features
New Oracle Data Types
Abstract Data Types
Data Dictionary Views
CREATE TYPE Command
Object Tables
Type Methods
The Constructor Method
Working with User-Defined Datatypes
Modifying Data in Object Tables using User-Defined Datatypes
Working with Object Tables
Object Identifiers
Defining a REF
INSERTING REF Values
Updating and Deleting REF Values
Dangling REFS
The DEREF Operator
ROWIDs
DBMS_ROWID Package
Different Types of Rowids
LOBS
Object Methods
Object Methods
Employee Type Example
Create Type Body Command
Executing Object Methods
Different Types of Methods
MAP Versus ORDER Method
Static Methods
Differences Between Packages and Objects
DDL Type Commands
Objects and Nulls
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"
The TABLE Operator
Managing Large Objects
LONG and LONG RAW Datatypes
LOB Datatypes
Components of a LOB
LOB Syntax and Examples
LOB Segments and Features
Working with LOB Data
Character LOB Data
Binary LOB Data
Directory Database Object
Creating Directory Objects
The DBMS_LOB Package
The Oracle Server Image Cartridge
Working with Internal LOBS
Working with BFILES
Initializing BFILE Values
Deleting LOBS
Writing to a LOB
Reading LOB Data
Temporary LOBS
Advanced Queuing
Fundamentals
Why Advanced Queuing?
The Advanced Queuing Option
Features of Advanced Queuing
Queuing Components
Oracle Advanced Queuing
How AQ Works?
Initialization Parameters Impacting AQ
The DBMS_AQ Package
Initial Setup
Granting Permissions
Defining the Message Data Structure
Creating Datatypes
Starting the Queue
Adding Subscribers
Putting A Message on the Queue
Queue Data Types
MESSAGE_PROPERTIES_T Data Type
Option and Messsage Properties
Defining Procedures for Queuing
Dequeuing a Message
Setting Dequeuing Options
Create a Queue Table (Java Example)
Enqueue a Message (Java Example)
The Oracle Java Messaging Service (JMS)
Using JMS and Oracle
Oracle8i Instance Features
Multiple DB Cache Buffer Pools
Defining the DB Buffer Pools
Buffer Pool Initialization Parameters
The Buffer Pool Parameter
Defining Storage for Buffer Pools
Caching Tables
Sharing Cursors
Large Pool Area
Setting the Large Pool
Sort Enhancements
Tuning Parallel Query
Parallel DML
Parallel DML Restrictions
Oracle8i's JVM Architecture (EJE)
The Move to Java
Java Virtual Machines
Oracle's Enterprise Java Engine
300% Java
Features of Oracle's EJE
Oracle's EJE and MTS
Aurora
Java Database Connectivity
Oracle Java and Object Based Products
Oracle JDeveloper
Releases of JDeveloper
JDeveloper File Types and Environments
The Internet File System
Different Types of Java Applications
Different Types of Java Programs
Java Applets
Java Standalone Applications
Java Database Programs
JavaBeans and Java Servlets
Remote Invocation Method
JavaServer Pages
Benefits of JavaServer Pages
Enterprise Java Beans and CORBA
What is BC4J?
The Business Components Framework
Business Component Wizards
Data-Aware Controls
Java Stored Procedures
Java Stored Procedures
Java and PL/SQL
Java Database Programs
Running a Java Stored Procedure
Using JDBC for Database Connectivity
Oracle JDBC Drivers
What JDBC Driver to Use?
JDBC Driver Manager
Connecting to a Database
Steps for Executing a Query
JDBC Example
JDBC Versus SQLJ
Loading Java Programs into Oracle
Loadjava
Library Units and the Resolver Spec
Publishing Java to SQL
Creating a Java Database Trigger
Integration and Extensibility
Changes in the Internet
Oracle AppWizard
Integrating the MS Transaction Server
Development Languages
External Routines
The Call Specification
External Body Clause
External Routine Example
Processing External Routines
Callbacks
Java Stored Procedures
Data Cartridges
Index Interfaces
COM Cartridge
Additional Cartridge
Custom Indexes
Oracle8i Security Features
Password Management
Setting Up Password Management
Password Complexity Verification
Defining a Profile with Password Limits
Defining a Profile for a User
Information on Password Management
Data Encryption
Security Policies Overview
More on Security Policies
Application Context Overview
Implementing a Security Policy
Defining Packages for Enforcing Security
Policies
Implementing an Application Context
Security Package (HR_SEC)
Defining a CONTEXT
How it All Works
Unique Schemas
Shared Schema
Enterprise User Management
Oracle Wallets
Radius Authentication Dial-In User Service
Oracle Security Management
N-tier Authentication/Authorization
Oracle8i PL/SQL Enhancements
Working with Object Types and Collections
Autonomous Transactions
Defining an Autonomous Block
Executing Autonomous Transactions
Advantages and Restrictions
Autonomous Triggers and PL/SQL Blocks
Definer's and Invoker's Rights
Using the AUTHID Clause
Resolving External References
Database Triggers
Event Functions
Examples of System Events
Database Trigger Event Attributes
Client Events
Schema Triggers
INSTEAD-OF Triggers
Oracle Utilities and Managing Data
Oracle Utilities
Loadjava and Dropjava
Using Multiple Export Dump Files
Using a Query Filter on Export
Exporting Outlines
Transportable Tablespaces
O8i Data Warehousing Features
What is a Materialized View?
Creating a Materialized View
Dimensions
Create Dimension Syntax
Hierarchies
Create a DIMENSION with Hierarchies
Attributes
Normalized Dimensions
Managing Dimensions
Alter Dimension Syntax
Considerations for Building Dimensions and
Hierarchies
Query Rewrites
Enabling Query Rewrites
Creating the Materialized View
Materialized View Options
REFRESH Options
CREATE MATERIALIZED VIEW
Materialized View Log
Create Materialized View Log Syntax
Materialized View Log Setup
Create Materialized View Logs
Create Final Materialized View Log
New ROLLUP Operators
The GROUPING Function
Analytical Functions
Top-N Analysis
Sample Clause
Full List of SQL Functions
