Oracle SQL Developer Migrations is an integrated feature of Oracle SQL Developer that's used to migrate from some of the popular non-Oracle databases to Oracle. It provides users with a basic migration wizard (for small, less complex migrations) and an interactive migration alternative, where the stages of the migration are separated, allowing users to make changes to the objects and code at each stage. SQL Developer Migrations supports the migration of most database objects depending on the database in question (including procedures, functions, and the data in the tables) to Oracle. In this chapter, we'll look at preparing the SQL Developer environment, preparing the required Oracle database repository, and the steps involved in the migration process. Refer to http://apex.oracle.com for more information.
Migrating a database requires careful planning and preparation. In this chapter, we'll review the tool support for a migration, while not discussing the additional work required when planning and preparing for a migration. We will not list the reasons for migrating from one database to another or the advantages of one database over another. We'll make the assumption that these decisions have been made.
Oracle SQL Developer's Migrations feature supports the conversion of tables, views, triggers, stored procedures, and other objects from non-Oracle (third-party) databases to Oracle database equivalents. Once the objects are converted to Oracle, the data can be copied from the source to the target database. The extent of what is converted depends on the third-party database in question. For example, not all migrations support the migration of procedures and functions.
SQL Developer offers users a choice when converting from a third-party database to Oracle by offering the following alternatives:
- A quick migration wizard
- Interactive migration
For both approaches, SQL Developer Migrations provides a phased approach to migrate from a third-party database to Oracle. Initially, the tool queries the source database, and the captured metadata is written to an Oracle repository. The next phase is the conversion of objects to Oracle metadata. Once converted, the Oracle metadata is used to generate the DDL for execution in the target database. The final phase is to copy the data from the source database to the target database.
If you use the migration wizard, the entire process flows from the beginning to completion with no chance to make adjustments before completion. This is typically used for smaller, less complex migrations. If you use the interactive approach, you can make adjustments (such as deleting or updating objects) after each phase is complete. We'll look at the details of these phases later in the chapter.
The quick migration wizard requires a direct connection to the database being migrated, while interactive migration allows you to make a direct connection to the source database or migrate from files. There may be a multitude of reasons for not wanting to directly access the database, and so, using files as a source can be very useful. Using files as a source can have the following results:
Interactive migration follows the same approach for a direct or offline migration, with the exception of how you start off the migration and load the data at the end.