Oracle Database 11g: Administer a Data Warehouse

Course:  ORDWA
Duration:  5 Days
Level:  II
Course Summary

This course details the tools available to the Oracle database administrator/warehouse developers to develop and administer a data warehouse consisting of staging area, object tables, and data marts (dimensional models). The course will describe the best tools and strategies to use for extracting data from OLTP systems, why you should use partitioning, how to use partitioning, when to use parallelism, and a wealth of other tools. The course will also cover data cleansing strategies. The student will also have the opportunity to actually build a data warehouse consisting of a star schema and several object tables. This exercise can and has been used as a template for future development by student DBA/developers.

« Hide The Details
Topics Covered In This Course


  • Development Tools
  • Oracle SQL Developer
  • Enterprise Manager
  • Oracle Warehouse Builder
  • Oracle Data Integrator
  • Practice schemas

Data Warehouse Design: Overview

  • What is a Data Warehouse?
  • Overview of Data Warehouse Terms
  • Characteristics of a Data Warehouse
  • Comparing OLTP and Data Warehouses
  • Data Warehouse Architectures
  • Data Warehouse Design
  • Data Warehouse objects
  • Data Warehouse Schemas
  • Star Transformation
  • Techniques For Successfully Designing Dimension/Data Marts

Oracle Database Architecture

  • Memory Structures and Recommended Settings
  • Data, Temp, and Undo tablespaces
  • Fixing the ORA-01555 Error
  • Determine Whether Sorts Are Performed on Disk or Memory

Partitioning Basics

  • Partitioned Tables and Indexes
  • Partitioning Methods
  • Partitioning Types
  • Partition Pruning and Star queries

Parallelism Concepts

  • Operations That Can Be Parallelized
  • How Parallel Execution Works
  • Degree of Parallelism
  • Parallel execution plan
  • Parallel Operations in Data Warehouses
  • Parallel Query
  • Parallel DDL ??? Reindexing and table creation
  • Parallel DML
  • Tuning Parameters for Parallel Execution
  • Which Data Warehouse Objects That Should Be Parallelized

ETL: Extraction and Transportation

  • Extraction Methods
  • Capturing Data With Change Data Capture
  • Sources and Modes of Change Data Capture
  • Publish and Subscribe Model: The Publisher and the Subscriber
  • Synchronous and Asynchronous CDC
  • Asynchronous AutoLog Mode and Asynchronous HotLog Mode
  • Transportation in a Data Warehouse
  • Transportable Tablespaces

ETL: Loading

  • Loading Mechanisms
  • Applications of External Tables
  • Defining external tables with SQL*Loader
  • Populating external tables with Data Pump
  • Other Loading Methods

ETL: Transformation

  • Data transformation
  • Transformation Mechanisms
  • Transformation Using SQL
  • Table Functions
  • DML error logging
  • Merge statement
  • Insert All statement
  • Grouping sets, cube, and rollup options
  • Data cleansing strategies

Materialized Views

  • The Need for Summary Management
  • Types of Materialized Views
  • Using Materialized Views for Summary Management
  • Fast vs Complete Refresh
  • Using Refresh Groups
  • Other Materialized View Refresh Options
  • Materialized View Logs
  • When not to use materialized views

Working With Dimensions, Fact Tables, and Cubes

  • What Are Dimensions, Fact Tables, Cubes, Star Schemas, and Snow Flaked Star Schema
  • Creating Dimensions and Hierarchies
  • Dimensions and Privileges
  • Dimension Restrictions
  • Verifying Relationships in a Dimension
  • Dimension Invalidation

Query Rewrite

  • Query Rewrite: Overview
  • What Can be Rewritten
  • Conditions Required for Oracle to Rewrite a Query
  • Query Rewrite guidelines
  • Setting Initialization Parameters for Query Rewrite
  • Query Rewrite Methods
  • Partition Change Tracking (PCT) and Query Rewrite
  • Query Rewrite Enhancement to Support Queries Containing Inline Views

Tuning and Performance Options

  • Types of indexes
  • Identifying Long Running Queries
  • Compression and percent free
  • End user tuning methodologies
  • Benefit of ANSI SQL 99 in tuning queries


  • Global Temporary Tables
  • Referential Integrity and Data Marts
  • How a Data Warehouse Can Be the One Source of Truth
  • Oracle Text
  • Analytic Functions
What You Can Expect

At the end of this course the student should understand the data warehouse stages, how to populate the data warehouse, and manage its day-to-day operation. In addition, the student will have a template that can be used for developing actual data marts.

Who Should Take This Course

Data warehouse DBA's, data administrators, and developers

Recommended Prerequisites

Students should have a basic understanding of OLTP and OLAP Database Design, and experience with Oracle's products.

Training Style

Instructor led with 60% lecture and 40% lab.

« Hide The Details
Related Courses
Code Course Title Duration Level
Oracle Physical Data Warehouse Design and Implementation
4 Days
5 Days
Oracle OLTP and OLAP Database Design
5 Days
Oracle Data Integrator 11g
4 Days
Oracle BI Enterprise Edition (OBIEE) 11g: Reports and Dashboards
4 Days
Data Warehousing Fundamentals
3 Days
Oracle BI Enterprise Edition (OBIEE) 11g: Boot Camp
5 Days
DB2 Data Warehousing Performance And Tuning Workshop
4 Days
Oracle BI Enterprise Edition (OBIEE) 11g: Building Repositories
5 Days
Advanced PL/SQL (and SQL) for Data Warehousing ETL
4 Days
Oracle Database 11g: Data Warehousing and Oracle Warehouse Builder
5 Days

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.
Notify me the next time this course is confirmed!
Can't find the course you want?
Call us at 800.533.3893, or
email us at [email protected]