Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



ISBN 13:
Library of Congress Number:
400 pages
Perfect bind - 9x7
PD 0411
Shelving:  Databases/Oracle Oracle In-Focus Series

  Migrating to Oracle
Expert Secrets for Migrating from SQL Server and MySQL

Ben Prusinski              

 Retail Price $89.95 /  £74.95

Order now at 30% off and get access to the code depot! Only $62.99
(30% off)
Key Features About the Author Table of Contents
Index Reader Comments Errata



One of the biggest challenges that IT departments currently face is how to migrate their non-Oracle databases to Oracle platform with limited budget, staff, and time constraints.

In order to meet these needs, Oracle originally developed an excellent tool called the Oracle Migration Workbench (OMWB). As the latest and greatest enhancement to migration software, Oracle has provided the new version of migration tools along with a full development environment with the SQL Developer software with Oracle 10g and 11g database releases as the method of choice to accomplish these daunting tasks.

This comprehensive guide to these new Oracle migration tools will be valuable to all database professionals who are challenged with the task of migrating their non-Oracle databases to the Oracle 10g and 11g platforms. This text will provide the blueprint and implementation details as well as expert hands-on tips and techniques on how to use this new migration tool to quickly migrate non-Oracle databases to the Oracle 10g database environment.

Topics will include a detailed discussion of these tools including coverage of both SQL Developer and the Oracle Migration Workbench installation and configuration process, techniques on how to best use the editors for parsing non-Oracle SQL and stored procedures to map to the Oracle SQL and PL/SQL code, problem resolution and troubleshooting during the migration process, and testing procedures after the initial migration has been completed.

This book distills the very complex and technical challenges of how to best migrate your non-Oracle database into an easily digested format with clear step by step techniques that all database professionals can implement right away.

Migrate to
             Oracle with


Key Features

• Understand SQL Developer Migration tools and the Oracle Migration Workbench

• See real examples for SQL Server migration to Oracle

• See examples for migrating from MySQL to Oracle

• Understand how to convert T-SQL to PL/SQL

• Save hours of time by recognizing potential migration pitfalls

• Avoid tedious and risky manual migrations

SQL Developer and the Oracle 10g Migration Workbench are the premier  migration tools offered by Oracle with release 10g and 11g to perform rapid migrations of large non-Oracle databases to the Oracle 10g/11g platform without the need to purchase expensive conversion tools. It provides a free option to convert all non-Oracle database servers to Oracle 10g instead of manual conversion techniques which would require hundreds of painful hours of trial and error. Because of the powerful capabilities within both SQL Developer and the Migration Workbench tool, it is quick method to perform mass migrations of SQL Server and MySQL databases for example, to the Oracle 10g/11g platform. This tool provides many automation features as well.

This book has a very broad reader base encompassing almost all technical professionals who need to perform database migrations to Oracle 10g. This text will become indispensable to both technical and project management staff who wish to migrate from non-Oracle platforms on a narrow timetable and limited budget.

This book incorporates proven techniques for implementing and completing database migrations from non-Oracle databases to the Oracle 10g/11g platform.

All of the techniques in this book use both SQL Developer as well as the Oracle 10g Workbench Migration tool, so no additional third party tools are required to be used to perform the database migration to Oracle 10g/11g. This text will be comprehensive, covering every aspect of how to migrate your non-Oracle database to Oracle 10g and 11g using either the new SQL Developer tool or the Oracle Workbench Migration tool. This text also includes expert tips and techniques that have been developed by the author that will provide a timeless and invaluable reference for performing database migrations using SQL Developer and the Oracle 10g Workbench Migration tool.


About the Author:

Ben Prusinski

Ben Prusinski is an Oracle Certified Professional and Oracle ACE with more than 10 years of full-time experience as a database administrator and has written numerous articles and white papers on database management. Ben is also an active member of the IOUG, OAUG, and SROAUG user group communities. As a top Oracle expert, Ben is a frequent speaker and presenter at major technical conferences including Oracle OpenWorld, IOUG, and CLOUG. 

Ben has worked with databases including Oracle, Microsoft SQL Server, IBM DB2 UDB, and MySQL since 1996.  As a certified Oracle RAC expert, Ben has designed robust architectures for high availability with Oracle RAC, Streams, and Data Guard for Oracle E-Business Applications and data warehouse environments. Furthermore, he has accumulated over a decade of practical knowledge and experience with complex database migrations and support, focusing mainly on how to best achieve results with large database migrations to the Oracle platform. 

Technical Editor:

Paulo Ferreira Portugal

Paulo Ferreira Portugal is a DBA with a decade of experience in IT and has worked as a DBA for 8 years. He is an Oracle Certified Professional (9i and 10g); IBM DB2 Certified (8 and 9 “Viper”); and an Oracle 11i Applications Database Administrator Certified Professional.

Currently, Paulo works as Senior Applications DBA for a company named F2C Consultoria in Rio de Janeiro/Brazil. F2C serves one of Oracle E-Business’ largest clients in Brazil.

He has participated in the Oracle Beta Test 11i project using Data Guard, and is a specialist in High Availability tools like Oracle Data Guard, Oracle Streams and Oracle RAC. His clients in Brazil demand the most sophisticated services using tools like Oracle E-Business Suite 11i, Oracle OTM, Oracle Retail and Oracle BPEL with Oracle RAC 10g.

Paulo greatly enjoys what he does and is always improving his technical knowledge by attending events like Oracle Open World - San Francisco (2005 and 2006) and IBM Information on Demand – Los Angeles (2006).

Table of Contents:

Chapter 1:  Introduction to Oracle Migrations


Migrating to Oracle

Overview of Database Migration

Manual Procedures for Database Migration

Export and Import Method for Database Migration (Data Pump)

Server Level Copy and Cloning Method

Replication for Database Migrations to Oracle  

Schema Copy and DDL Generation          

Third Party Tools for Database Migration to Oracle 10g 

GoldenGate® Software 

Quest® Shareplex           

Swis® SQL           

The New Oracle 10g Database Workbench Migration (OMWB)  

Preparation for Database Migration to Oracle 10g           

Project Planning for Database Migration to Oracle 10g 

Staffing Requirements for Database Migration 

Database Architecture Differences and Oracle 10g         

Oracle 10g Database Architecture           

Microsoft SQL Server Architecture          

Introduction to the MySQL Database Architecture           

SQL Differences and Oracle SQL               

MySQL SQL Versus Oracle SQL Constructs            

Microsoft SQL Server 2000 SQL versus Oracle SQL            

Data Type Issues:  MySQL and Oracle 10g            

Data Type Issues with Microsoft SQL Server 2000 and Oracle 10g              

Stored Procedures and Oracle PL/SQL    

Microsoft Transact SQL and Oracle PL/SQL          

Migration Workbench: The Preferred Method for Database Migration  



Chapter 2:  Overview of the Oracle Migration Workbench


Introduction to Oracle Migration Workbench    

Oracle Migration Workbench and the APEX Migration Workbench          

Oracle 10g Migration Workbench Components 

The Oracle 10g Application Express (APEX) Migration Workbench           

Oracle 10g APEX and the Oracle 10g APEX Migration Workshop Suite     

Oracle 10g APEX Components   

A Closer Look at Oracle 10g APEX Migration Workshop 

Benefits of the Oracle 10g APEX Migration Workshop    

Oracle 10g APEX Migration Workshop Components        

Migrating MS Access Applications           

Oracle Application Express 3.0   

Oracle 10g SQL Developer Migration Workbench              

Oracle 10g Exporter Tool              

Oracle 10g SQL Developer Migration Workbench             

Oracle 10g Database Migration Verifier



Chapter 3:  Installation Planning and Configuration for the Oracle 10g Migration Workbench 


Introduction to Oracle 10g Workbench Migration Tools

Prerequisites: Oracle 10g Migration Workbench Installation      

Preparation for Oracle Migration Workbench Installation            

Required Software for the Oracle Migration Workbench               

Downloading the Oracle Migration Workbench Software             

Download the Required Plugin Software              

Downloading Third Party Drivers for the OMWB Environment    

Download Relevant Software for MySQL Platform          

Oracle 10g SQL Developer Migration Workbench             

Configuration Summary              

Preparing the Database Environment to Migrate to Oracle 10g 

Required Privileges for the New Oracle 10g Migration Workbench Schema           

Creating a Connection with SQL Developer 1.2 

Migration Repository Creation for SQL Developer           

Setup for MySQL, Access and SQL Server             

Migration Setup for Access         

Database Connections - MySQL and MS SQL Server 2000              

Configure JDBC Drivers 

Install JDBC driver files for MySQL and SQL Server            

Configure JDBC for SQL Developer           

Downloading and Installing the JDBC Drivers for MySQL               

Oracle 10g Application Express (APEX) Migration            

Installation Procedures for APEX 3.0 Migration Software              

Oracle 10g Database Verifier (DMV)       

Oracle 10g Migration Workbench (OMWB) Installation 

Install the Plugins for the Oracle Migration Workbench 

Installation Process of Oracle 10g Migration Workbench              

Verify Setup for Oracle 10g Migration Workbench           




Chapter 4:  Configuration Tasks for the Oracle 10g Migration Workbench 


Configuration - The Oracle Migration Workbench           

Building the Source and Target Models 

Source Database Capture           

Choosing between Online and Offline Data Capture     

Advantages and Disadvantages of Online Capture Method        

Advantages and Disadvantages of Offline Capture Method       




Chapter 5:  Online Capture Process for the Oracle 10g Migration Workbench 


Introduction to the Online Capture Process       

Online Capture with Oracle 10g Migration Workbench 

The Automation Process for Online Capture with the Oracle 10g Migration Workbench  

Beginning to Use the Migration Workbench Environment           

Using SQL*Plus for Examining New Repository Objects 

Using OMWB Capture Wizard    

Errors During Source Model Creation     

Online Capture for MySQL Database to Oracle 10g/11g 

Prepare the MySQL Test Environment    

Automation Process and Configuration Parameters for MySQL 

Oracle SQL Developer Migration Workbench                                         

Online Capture for MS SQL Server 2000 to Oracle 10g/11g with the SQL Developer Workbench    





Chapter 6:  Performing Offline Capture for the Oracle 10g Migration Workbench 


The Offline Capture Process      

Offline Capture with Oracle 10g Migration Workbench 

Scripts for Use with Microsoft SQL Server             

Microsoft SQL Server BCP Scripts - Offline Capture         

Files Created By Offline Capture for MS SQL Server          

Scripts for Use with Sybase Adaptive Server        

Scripts for Use with MySQL- Offline Capture      

Offline Capture with MySQL with SQL Developer               

Scripts for Offline Capture- Informix Database 




Chapter 7:  Mapping the Source Model to Target Database       



Building the Source Model Oracle 10g Migration Workbench     

Capturing the Source Database for Microsoft SQL Server              

Cleanup After the Migration Process      

Building the Source Model for MySQL and SQL Developer           

Migrating the MySQL Source Model to Oracle Using SQL Developer        

Overview of OMWB Tools: The Editors 

Tablespace Discovery Editor       

Code Parser Editor          

SQL Developer Editors and Tools             

Exporter for Migrating MS Access to Oracle        

Translation Scratch Editor for SQL Developer      

Troubleshooting Migration Issues: Common Problems and Solutions    

Problems with Source and Target Model Mapping          

Issues with SQL Developer Migration Tools        

Problems with Quick Migrate for SQL Developer 1.5        

Incorrect JDBC Driver Versions for SQL Developer              

Completing the Initial Migration Using OMWB 

Completing the Initial Migration Using SQL Developer 

Generate Data Move Scripts with SQL Developer              

Offline Capture Scripts for MySQL Using SQL Developer               

Generating Schema and DDL Creation Scripts    




Chapter 8:  Understanding the Oracle Database Migration Utilities  



OMWB Verification Tool             

Features of the Oracle Database Migration Verifier Tool (DMV)

Installation Process for OMWB Verification Tool             

Requirements for DMV Installation for Oracle    

Summary of Steps to Install and Configure DMV for Oracle         

Using the Oracle Database Migration Verifier                                     

Using OMWB Editors     

Log Window Tool in OMWB        

Migration Script Generation       

SQL Developer Migration Tools and Utilities      

SQL Developer Translation Scratch Editor             

Installation for Database Plugins for SQL Developer        

Oracle APEX Migration Tools     




Chapter 9:  Testing and Verification of Database Migration         


Data Verification Testing            

Performance Tuning     

User Acceptance Testing             

Deployment Testing       

Functional Testing          

Quality Assurance (QA) Testing




Chapter 10:  Conclusion of Oracle Database Migration  

Book Conclusion             

Lessons Learned From the Trenches      

Prepare, Prepare, Prepare!         

Call for Backup 

Standby to the Rescue  

Appendix and Bibliography       









Active Session History

Ada object oriented language

administration arena


ANSI-92 SQL Standard

APEX 3.0 directories

APEX 3.0 Migration Software

APEX Migration Workshop Components

Application Builder

Application Express (APEX) Migration 3.1

Application Express (APEX) Migration Workbench

archive log file

Archiver process (ARCH)

Associate Migration Repository



BCP script






Capture Schema232

Capture wizard

Captured Objects

cast function

character data types

Checkpoint Process (CKPT)

clustering standby technology

Code Parser Editor

Commit Count

Configure JDBC for SQL Developer

convert function





Dat files

Data Compare Testing

Data Guard standby database

Data Move Scripts

data type conversions

Data Type Mappings

Data verification testing

database API layer

database creation script

Database migration

Database Migration Verifier

database replication methods

database upgrade assistant

database writer process (DBWR)

datatype mapping editor




default offline capture scripts

delete mytemp_table

deployment testing

Disaster recovery testing

Discovered tablespaces



drop table #mytemp_table


DSN setting

DTS package



Err files

Export and Import Method



failed repository objects

functional testing



General tab

Generated tablespaces






global temporary table

GoldenGate Software

graphical interface



hot standby site

HP NonStop







Informix database

InnoDB storage engine

Insert Batch Size

interprocess communication



jar files

Java Database Connectivity

Java Development Kit

Java J2EE

JDBC Drivers

jTDS driver




kick-off meeting



local temporary table

Log Window Tool

log write (LGWR)




Mapped tablespaces


materialized views


Memory storage engine

Microsoft Access database

Microsoft Access Exporter

Microsoft Cluster Server

Microsoft Cluster Services

Microsoft Project

Migrate Data

migration plugins

Migration Script Generation

migration scripts

Migration Setup for Access

migration wizard

Migration Workbench tool

migrations feature

MS Access 2003

MS Access 97

MS SQL Server

msdb database

myISAM storage engine


MySQL database architecture

MySQL JDBC files

MySQL Test Environment








network configuration file

Northwind database





offline capture

Offline capture

OMWB installation








omwb_rep repository


on delete cascade

online capture

Online Parallel Data Movement

online redo log file

Online Source Model Load

operating system

Oracle 10g Database Architecture

Oracle 10g Exporter Tool

Oracle 10g Migration Workbench (OMWB) tool

Oracle 10g SQL Developer Migration Workbench

Oracle Application Express (APEX)

Oracle Application Express 3.0

Oracle Database Migration Verifier (DMV)

Oracle EnterpriseDB

Oracle Migration Workbench

Oracle Migration Workbench User’s Guide

Oracle Streams

Oracle System Global Area

Oracle Transparent Gateways

Oracle Workbench Repository



parameter file

parameter initialization (pfile)

password file

pilot migration

PMON (Process Monitor)

Program Global Area





Quality Assurance Testing

Quest Shareplex

Quest® Shareplex

quick migrate

Quick Migrate

Quick Migration Wizard



Real Application Clusters

Recovery manager

Recovery Manager


Repository Management

repository schema

RMAN hot and cold backup

Row Prefetch Size



sanity test process

SAP R/3 Basis

schema copying and DDL generation


Server Level Copy and Cloning

Source and Binaries file

source and target models

Source Database Capture

SQL Developer

SQL Developer 1.5

SQL Developer interface

SQL Performance Analyzer

SQL Server replication

SQL Workshop



staffing and development configuration file

static parameter file


stored procedures

Streams based replication

Stress testing



Sybase Adaptive Server

sysmaster directory

System Monitor (SMON)



Tablespace Discoverer

TEMP tablespace

tempdb database

third party database drivers



Transact SQL

transact SQL (T-SQL) scripts

transaction log file

Translation Diff Viewer

Translation Scratch Editor

Transportable tablespaces

Txt files



UDFs (Universal Disk Formats)

usability testing

User Acceptance Testing

Utilities interface







Validation Testing





From the series Editor:



Hit Counter


 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks