Oracle8i New Features for Database Administrators

Course Length: 5 days

This course will give Oracle7 DBAs a comprehensive overview of the changes in Oracle8. You will learn the newest features and functionality, as well as migration issues.

Audience: Database administrators.

Prerequisites: Oracle for Application Developers

Introduction to Partitioning

Oracle Versioning
Managing Large Tables with Oracle7
Oracle8.0 - The Object-Relational Release
Oracle8 Size Ceilings
Oracle8i - The Internet Release
Oracle8 Data Storage
What is Partitioning?
Creating a Range Partitioned Table
The Partition Key
Partition Syntax
Range Partitioning
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
Inserting into a Multi-Column Partition Key
Setting Physical Attributes for Partitions
Hash Partitioning
Defining Tablespaces for Hash Partitions
Composite Partitioning
Partition Pruning
Partition Wise Joins
Information on 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
Function-Based Indexes
Domain Indexes
Index Options and Maintenance
Features of Index Rebuilds ONLINE
Indexes on Primary Keys

Managing Partitioned Objects

New DDL Commands for Partition Management
Privileges for Working with Partitions
Unusable Indexes
New Plan Table
Adding, Merging and Dropping Partitions
Renaming and Truncating Partitions
Modifying Default and Logical Attributes
Adding PRIMARY or UNIQUE Keys
Moving and Splitting Partitions
Exchanging Partitions
Making Local Indexes Unusable
Rebuilding and Skipping Unusable Indexes
DDL Operations on Composite Partitioned Tables
Subpartition Operations
SQL*Loader and Partitioned Objects
Export, Import and Partitioned Objects
Partition Data Dictionary Views

New Data Storage Features

Index-Organized Tables
Partitioning Index-Organized Tables
Temporary Tables
Marking a Column as Unused
The DROP Option
Moving Tables
Constraints can be Deferred
Setting the RELY Flag
Enabling and Disabling Constraints
Review of Dictionary-Managed Tablespaces
Dictionary vs Locally-Managed Tablespaces
Read-Only Tablespaces
Converting Dictionary and Local Tablespaces
Transportable Tablespaces

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
Calculating the Hit Ratio for Buffer Pools
Calculating Cache Usage of Schema Objects
Calculating Buffer Pool Sizes
Latch Contention
Caching Tables
Sharing Cursors
Large Pool Area
Sort Enhancements
Tuning Parallel Query
Parallel DML

Object-Relational Concepts

Object-Relational Databases
New Oracle8 Object-Relational Features
New Oracle Data Types
Abstract Data Types
Data dictionary Views
Review: CREATE TYPE Command
Object Tables
Type Methods
The Constructor Method
Working with User-Defined Data Types
Modifying Data in Object Tables
Object Identifiers
Defining and Updating REF Values
INSERTING REF Values
Dangling REFS
The DEREF Operator
ROWIDs
DBMS_ROWID Package
Different Types of Rowids
LOBS

Object Methods

Object Methods
Create Type Body Command
Different Types of Methods
MAP and ORDER Methods
STATIC Methods
Differences Between Packages and Objects
DDL Type Commands
Objects and Nulls

Collections

Records
Creating a PL/SQL Record
PL/SQL2 Tables
Table Attributes
PL/SQL8 Tables
Instantiating PL/SQL Tables
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, Segments and Features
Working with LOB Data
Character and Binary LOB Data
Directory Database Object
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
The Advanced Queuing Option
Queuing Components
Oracle Advanced Queuing
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 Message Properties
Defining Procedures for Qeuing
Dequeuing Options
Create a Queue Table
Enqueue a Message
The Oracle Java Messaging Service (JMS)
Using JMS with Oracle

Database Resource Management

Features of Resource Management
Setting up Resource Management
DBMS_RESOURCE_MANAGER Package
Defining Work Areas
Defining Resource Plans
DBA_RSRC_PLANS
Default Consumer Groups
Allocation of Resources

Oracle8i's JVM Architecture (EJE)

The Move to Java
Java Virtual Machines
Oracle's Enterprise Java Engine
300% Java
Oracle's EJE and MTS
Aurora
Java Database Connectivity
Oracle Java and Object Based Products
Oracle Jdeveloper
The Internet File System

Different Types of Java Applications

Java Applets
Java Standalone Applications
Java Database Programs
JavaBeans
Java Servlets
Remote Invocation Method
JavaServer Pages
Enterprise JavaBeans and CORBA
What is BC4J?
The Business Components Framework
Business Component Wizards
Data-Aware Controls

Java Stored Procedures

Java and PL/SQL
Java Database Programs
Running a Java Stored Procedure
Using JDBC for Database Connectivity
Oracle JDBC Drivers
JDBC Driver Manager
Connecting to a Database
Steps for Executing a Query
JDBC versus SQLJ
Loading Java Programs into Oracle
Loadjava
Library Units and the Resolver Spec
Publishing Java to SQL
Creating a Java Database Trigger

Migration and Installation

New SQL*Plus Features in Oracle8i
Oracle Universal Installer
The Database Configuration Assistant
How to Migrate
Migration Issues
What to Backup?
Setting up Migration

Diagnostics and Troubleshooting

Detecting Software Corrupt Blocks
DBMS_REPAIR
Issues of Skipping Corrupt Blocks
The Log Miner Utility
V$LOGMNR_CONTENTS
Creating a LogMiner Data Dictionary File
Using LogMiner to Analyze Log Files
START_LOGMNR and END_LOGMNR
Recovery Manager
Issues With Managing Backups
Advantages and Disadvantages of RMAN
Recovery Manager Components
The Recovery Catalog
Recovery Manager Architecture
Creating a Catalog Database
Connecting to a Catalog Database
Resynching the Catalog and Target Databases
Automating Resynchronization
Summary of 8.1.5 Features

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

Creating a Materialized View
Dimensions
Create Dimension Syntax
Hierarchies
Create a DIMENSION with Hierarchies
Normalized Dimensions
Managing Dimensions
Alter Dimension Syntax
Query Rewrites
Enabling Query Rewrites
Creating the Materialized View
Materialized View Options
REFRESH Options
CREATE MATERIALIZED VIEW
Materialized View Log Setup
New ROLLUP Operators
Analytical Functions
Top-N Analysis
Sample Clause
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

Oracle8i Security Features

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
Application Context Overview
Implementing a Security Policy
Defining Packages for Enforcing Security
Implementing an Application Context
Security Package (HR_SEC)
Defining a CONTEXT
Unique and Shared Schemas
Enterprise User Management
Oracle Wallets
Radius Authentication Dial-In User Service
Oracle Security Management
N-tier Authentication/Authorization

Optimizer Enhancements

Cost-Based Optimizer Enhancements
Generating Statistics and DBMS_STATS
Copying Statistics to Different Databases
Monitoring Tables
Enhanced Cost-Based Optimizer Features:
Stored Outlines
OUTLN_PKG Package

Oracle8i PL/SQL Enhancements

Working with Object Types and Collections
Autonomous Transactions
Defining an Autonomous Block
Advantages and Restrictions
Autonomous Triggers
Autonomous PL/SQL Blocks
Definer's and Invoker's Rights
Using the AUTHID Clause
Resolving External References
DatabaseTriggers
Event Functions
Database Trigger Event Attributes
Client Events
Schema Triggers
INSTEAD-OF Triggers
Statspack
Installing Statspack
Database Space Requirements
Statspack Parameter Settings
Generating Statspack Snapshots
Exporting Statspack Data
UTLBSTAT / UTLESTAT and STATSPACK
Statistics Gathering
Scheduling Statspack Jobs
Removing Statspack