Oracle9i New Features for DBAs

Course Length: 5 days $149 per book

This course will give Oracle8i developers and DBAs a comprehensive overview of the new features in Oracle9i. Developers and DBAs will learn the new management and scalability features in Oracle9i. Students will also learn the enhancements made to the Oracle8i features.

Audience: Database administrators, application developers, and system administrators.

Prerequisites: A solid understanding of Oracle8i database administration, experience in Oracle tuning, backup and recovery and networking. SQL and PL/SQL programming experience is also required.

Introduction

Oracle9i Releases
Oracle9i New Features
A Few Changes to Start Out With
Oracle9i Desupported Features
Desupported and Deprecated Features
Desupported and Deprecated Initialization
Parameters
Oracle8 and Oracle8i Desupported and
Deprecated Features

Dynamic Memory Management

Setting up SQL Work Areas
Setting the PGA Target Memory Size
Defining Automatic PGA Memory
Management
Displaying Workarea Information
Using a PGA Workarea
Dynamic SGA
Granules
V$BUFFER_POOL
Changing the Database Buffer Cache
Changing the Shared Pool Size
Database Buffer Cache Parameters
Deprecated Parameters
Advisory Parameter
V$DB_CACHE_ADVICE

Performance Enhancements

Index Monitoring
The V$OBJECT_USAGE View
Skip Scans
Cursors
Cursor Sharing
The CURSOR_SHARING Parameter Setting CURSOR_SHARING To SIMILAR
Monitoring Execution Plans
The V$SQL View
V$SQL_PLAN
The PLAN_TABLE Columns
The V$SQL_PLAN View Columns
FIRST_ROWS Optimization
Gathering Statistic Estimates with
DBMS_STATS

Segment Management

Automatic Segment-Space Management
Creating Tablespaces Using Automatic Segment-Space Management
Space Management
Creating Objects in Automatic Segment-Space Managed Tablespaces
Space Deallocation With Automatic Segment-Space Management
The DBMS_SPACE Package
The SPACE_USAGE Parameters
Space Deallocation Statements
Viewing Space Usage Information
Bitmap Join Indexes
Multiple Bitmap Join Indexes
Bitmap Join Indexes - Restrictions

SQL Enhancements

ANSI Join Syntax
The JOIN Statement
Cross Join
Natural Join
The USING Clause
Outer Joins
CR_EMP_SALE_TABLES.SQL Script
Full Outer Join
CASE Expression
Simple CASE Statement
Searched CASE Statement
NULLIF CASE Statement
COALESCE CASE Statement
Scalar Subqueries
Using Explicit Defaults
The MERGE Statement
New Analytical Functions
WIDTH_BUCKET
GROUPING SETS
Grouping with Composite Columns
Using Concatenated Groupings
WITH Clause
FIRST and LAST Aggregate Sets

Enhancements to Schema Objects

Additional Constraint Features
Explicit Index Definition
USING INDEX Syntax
Dropping or Disabling Constraints and Indexes
Reducing Locking on Foreign Key Tables
Caching Primary Key Values
Constraints on Views
Index Scans and Function-Based Indexes
SELECT FOR UPDATE WAIT
Multitable INSERT Statement
Multitable INSERT Syntax
Migrating LONG to LOB Values
LOB Extensions and Restrictions
Common SQL Parser
List Partitioning in Oracle9i
Additional Oracle9i Partitioning Features
Reduced Global Index Maintenance in 9i

Oracle9i Security

Virtual Private Database (VPD)
Secure Application Role
Virtual Private Database (VPD) in Oracle9i
Encryption: Random Key Generation
Fine-Grained Access Control
Partitioning Fine-Grained Access Control
DBMS_RLS Procedures
Fine-Grained Auditing with DBMS_FGA
Defining an Event Handler
Oracle Login Server
Web Single Sign-On
Managing Global Application Context
Public Key Infrastructure (PKI)

External Tables

Extraction, Transformation, and Loading
External Tables
Applications of External Tables
Directory Objects and External Tables
Creating an External Table
External Table Syntax Explained
Querying External Tables
Sample Log File
External Table
Data Dictionary Views for External Tables
Creating External Table Scripts

Globalization

Global Database Support
Encodings
COMPOSE and DECOMPOSE Functions
Unicode Character Sets
JDBC Drivers
The JDBC Class Library
The JDBC OCI (Thick) Driver
The JDBC Thin Driver
The JDBC Server Driver
Identifying Conversion Issues
Scanning Modes
Starting the Character Set Scanner
The Character Set Scanner Parameters
Using the Character Set Scanner
Oracle Locale Builder Utility
Customizing a Language Definition
Customizing a Territory Definition
Building a Custom Character Set
Building a Custom Linguistic Sort
Multiple Linguistic Sorting
Linguistic Sorts
Implicit Type Conversions
Unicode Enhancements to SQL*Loader
Generating the NLB File

Globalization: Date and Time Features

Daylight Savings Time Boundaries
Formats
Named Regions
New Datatypes Related to Globalization
Globalization Parameters
TIMESTAMP Literals
Using Interval and Timestamp Datatypes
Date, Time, and Interval Arithmetic
New Date and Time Types
TIMESTAMP Formats
Datetime Functions
Datetime Conversion

PL/SQL Features

CASE Expressions and Statements
TABLE FUNCTION
Pipelined versus Non-pipelined
Returning Results from Table Functions
New SQL Datatypes
Performing DML Operations Inside Table Functions
Performing DML Operations on Table
Functions
Parallelizing Table Functions
Enhancements to Bulk Operations
ADVANCED QUEUEING - New Features
JMS Enhancements

Advanced PL/SQL Features

Object Support
Native Compilation of PL/SQL Code
Steps to Compile PL/SQL Code
Better Integration of LOB Datatypes
Multilevel Collections
New PL/SQL Supplied Packages
Cursor Subquery
XML Data Type
Accessing Metadata
The DBMS_METADATA Package
DBMS_METADATA Object Types
Customizing DDL Output
GET_XML AND GET_DDL

Server Parameter File

Definition of the Server Parameter File
(SPFILE)
Operation of the SPFILE
Steps for Creating an SPFILE
Creating a SPFILE
Changing Parameters in the SPFILE
Backing up the SPFILE
Gathering Information About Parameters
SPFILE FAQs

Oracle Managed Files

Database Temporary Tablespace
Creating a Default Temporary Tablespace
Why Use Oracle Managed Files?
The Mechanism of OMF
OMF Data Files
OMF Naming Conventions
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
Using OMF in Database Creation
OMF and Online Redo Log Files
Renaming OMF Data Files
OMF and Control Files

Tablespace Management

Automatic Undo Management
Creating an Undo Tablespace
Automatic Mode for Managing Undo Space
Automatic Undo Initialization Parameters
The UNDO_RETENTION Parameter
Creating an Undo Tablespace
Defining an Undo Tablespace When Creating
a Database
Determining the Size of the Undo Tablespace
Data File
Dropping an Undo Tablespace
Switching Undo Tablespaces
Data Dictionary Views for Undo Information
Multiple Block Size Support
Setting up Multiple Block Sizes
Information About Block Sizes

High Availability

MTTR and Checkpoint intervals
Methods for Controlling Check pointing
V$INSTANCE_RECOVERY View
Minimal I/O Recovery
Oracle Flashback Query
Oracle Flashback Query Applications
Setting up Oracle Flashback Query
The DBMS_FLASHBACK Package
DBMS_FLASHBACK - Error Messages
DBMS_FLASHBACK versus
DBMS_LOGMNR
Resumable Statements and
DBMS_RESUMABLE
DBMS_RESUMABLE - Procedures and
Functions
Resumable Operations With
ALTER SESSION
After Suspend Database Trigger
New 9i Features in Oracle Utilities
Exporting Statistics
Importing Statistics
New Parameters for Export and Import
Tablespace Level Exports

Resource Manager Enhancements

Resource Manager Overview
Resource Manager Terminology Review
The RESOURCE_MANAGER_PLAN
Parameter
Modifying Resource Management
Dynamically
Automatic Consumer Group Switching
Query Queuing and the Active Session Pool
Query Queuing - How it Works
Maximum Estimated Execution Time
Setting Undo (Rollback) Quotas
New Columns in Related Views
Oracle Supplied Consumer Plans
Creating a Resource Plan in One Step

Enhanced Online Features

Online Index Rebuilds
Index Organized Table High Availability
Features
Online Update of Logical ROWIDs
Online Table Redefinitions
DBMS_REDEFINITION Package
Redefining a Table
Validate Structure Online
Quiesce Database

Managing Workspaces

What is a Workspace?
Versioning a Table.
Enabling and Disabling Versioning
Creating Workspaces
Workspace Privileges
Granting and Revoking Workspace Privileges
Accessing a Workspace
Savepoint Definition
Savepoints Define Workspaces
Rolling Back Changes
Merging Workspaces
Conflict Resolution
Using the <table_name>_CONF Views
The Conflict Resolution Session
Locking Workspaces
Freezing and Unfreezing Workspaces
Automatically Freezing Workspaces
Refreshing Workspaces
Dropping Workspaces
Workspace Manager Views
Import and Export Issues

New LogMiner Features

LogMiner and the Data Dictionary
Building the LogMiner Dictionary
The DBMS_LOGMNR BUILD Procedure
Starting LogMiner Using the Online
Dictionary
Tracking DDL Statements

Refreshing the Dictionary

Skipping Log Corruptions

Viewing Primary Key Information
Viewing Only Committed Transactions
LogMiner Views
LogMiner tm GUI Interface
Additional Restrictions and Unsupported
Features

Data Guard

Overview of Oracle9i Data Guard
Oracle9i Data Guard Architecture
Data Divergence versus Data Loss
The No Data Loss Feature
The No Data Divergence Feature
Log Transport Services
Configuring Availability Modes
The Log Writing Process
SYNCH Network Transmission Mode
ASYNCH Network Transmission Mode
Disk Writing Methods AFFIRM &
NOAFFIRM
Redo Log Reception
Failure Resolution - PROTECTED Mode
Failure Resolution - UNPROTECTED Mode
Interrelationships Between Availability Mode Specifications
Standby Redo Logs
Storage
Creating Standby Redo Logs
Adding Standby Redo Log Group Members
Log Apply Services
Managed Recovery Control Options
Database Failover
Graceful Failover
Forced Failover
Database Switchover
Pre-Switchover Considerations
Switchover - Primary to Standby
Switchover - Standby to Primary
Automatic Archive Gap Resolution
Standby File Management
Additional Enhancements
Data Guard Broker

Oracle9i RMAN

Customizable Configuration Parameters
CONFIGURE - SHOW ALL
New SHOW Parameters
REPORT Enhancements
New LIST Parameters
Persistent Channel Settings
Types of RMAN Commands
Channel Allocation
Retention Policies
BACKUP Optimization
Backups of Archived Logs Needing Backup
Block Level Media Recovery
Block Level Media Recovery - Restrictions
Control File Auto backups
Performing Trial Recovery
Archive Log Features
Block Media Recovery
Oracle Enterprise Manager Enhancements
Additional Enhancements

Real Application Clusters

Real Application Clusters
Overview of Real Application Clusters
Benefits of Real Application Clusters
Global Cache Service Resource Modes
Benefits of Cache Fusion
Block Access Modes and Buffer States
Cache Fusion Block Transfers Example
Writing Blocks to Disk
Shared Initialization Parameter File
Shared Server-Side Parameter File
Administering RAC with SRVCTL
SRVCTL Commands
Real Application Clusters and OEM
Background Process Changes
Initialization Parameter Name Changes
New Views
High Availability Enhancements
Real Application Clusters Guard
Monitors and Failover

Migrating to Oracle9i

Preparing for Migration
Upgrade Options
Upgrade Paths
How to Migrate
Migration Issues

Additional Considerations

What to Backup?
Setting up Migration
Additional Oracle9i Features
Shared Server Architecture
Deprecated Parameters
Shared Server Parameters
New Features in 9iFS
New 9i Features in Replication
New Features in Materialized Views

Conclusion

Goals of Oracle9i
Summary of Oracle9i Release 1
Oracle9i Release 2
New features in Oracle9i Release 2