Oracle SQL Developer: Migrate Other Databases to Oracle

Database migration is the process of migrating data from the source database to another target using database migration tools. It helps developers plan, verify, and apply schemas change to the required environments. Oracle launched its database migration tool SQL Developer in 2006 to aid the conversion from other databases to Oracle.

What is Oracle SQL Developer?

Oracle SQL Developer is an integrated development environment (IDE) and a standalone graphical tool of SQL *Plus to perform fundamental tasks by Oracle. The Java-written tool released its latest edition 22.2.1 in 2023. You can get it free from OTN.

SQL Developer includes an extension module for the Apache web server named the Oracle Web Agent (OWA/MOD PLSQL) that helps with the setup of dynamic web pages from PL/SQL inside the tool. It is shipped with Oracle Database 12c and later.

Features:

• Run SQL queries and scripts;

• Modify and debug PL/SQL code;

• Operate and unload data;

• View and produce reports;

• Offer geospatial data viewer;

• Manage Oracle with a DBA console;

• Browse, create, change, and delete database objects;

• Generate Entity Relationship Diagrams with all features;

• Gather statistics for an Oracle instance and provide real-time display;

• Connect to desired Oracle schema and operate the database objects;

• Connect to the schemas of non-Oracle databases and see the database data;

• Migrate Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, PostgreSQL, and IBM DB2 to Oracle database.

How to Download and Install SQL Developer?

Notice:

• Download an unzip tool or skip the step if you have one.

• Don’t install SQL Developer into any current ORACLE_HOME or sqldeveloper folder;

• The official release kit should be unzipped in a different directory from the one used for the beta version if you keep using it;

• It (different from the unzipped edition) is accessible in the menu system of Oracle database 11 or higher version.

On Windows with JDK 8 or Later:

You can download and install the JDK 8-included SQL Developer kit for Windows 64-bit systems if one is available.

1.     Download the Java SE Development Kit 8 and follow the displayed instructions.

2.     If you don’t need the Java Kit, download SQL Developer from the Oracle website.

3.     Click Browse to locate the JDK for the complete pathname if asked.

4.     Unzip the downloaded file into a directory and check the User folder names when unzipping.

5.     Find <sqldeveloper_install>sqldeveloper and double-click sqldeveloper.exe to start the tool (enter sh sqldeveloper.sh on Linux and Mac OS X).

On Linux, Mac, and Windows without JDK 8 or Later:

1.     Install JDK 8/9 from here.

2.     Unzip the downloaded file into a directory and check the User folder names when unzipping.

3.     Find <sqldeveloper_install>sqldeveloper and double-click sqldeveloper.exe to start the tool (enter sh sqldeveloper.sh on Linux and Mac OS X).

4.     You can connect a database by navigating to the Connections navigator> right-click Connections> New Connection.

How to Migrate Other Databases to Oracle with SQL Developer?

You can either migrate the database to Oracle with the migration wizard or copy the desired tables to the database.

1.     In the SQL Developer, go to Tools> Preferences>Migration (or Translation) to set

migration preferences optionally.

2.     Create an Oracle database user for the migration with a specified tablespace. The user must have proper roles and privileges.

CREATE USER name IDENTIFIED BY <password>

DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp,

3.     Install and add respective JDBC drivers for the source databases.

Navigate to Tools> Preferences> Database> Third Party JDBC Drivers> Add Entry> select the driver file> OK.

4.     Connect to the source and target databases. Go to Connections> right-click the root node> New Connection> click the corresponding tab and enter connection details> Test>Save (the source) Connect (the target)> Next.

5.     Go to Tools> Migration> Migrate to launch the migration wizard.

6.     In the Repository tab, enter the database connection for the repository, click +>input the source details> Test> Save> Next. Create one repository if you don’t have any.

a.     Create a database connection named Migration Repository to the database user.

b.     Right-click the repository connection> Migration Repository> Associate Migration Repository.

7.     In the Project page, set up the migration project and click Next.

8.     In the Source Database page, select Online or Offline for the Mode

Online: add (+) the source database Connection, and see the source database list from Available Source Platforms.

Offline: Offline Capture Source File is a file created before through Tools> Migration> Create Database Capture Scripts.

9.     In the Capture page, select the desired database under Available Databases and click Next.

10.  In the Convert page, check and change the mapping rules. Specify identifier options in the Advanced Options, and click Next.

11.  In the Translate page, choose the items to be translated under Available SQL Objects and click Next.

12.  In the Target Database page, select Online or Offline for the Mode.

Connection: add (+) the Oracle connection, and select Drop Target Objects to make sure to wipe off any existing database objects in the target schema before migrating.

Specify the generation preferences in the Advanced Options. Click Next.

13.  In the Move Data page, define the options for moving tables from Mode> Online, if you don’t want to migrate the table data, choose Offline.

Click + to add the source and target connections for Connections for online data move.

Enable Truncate Data to delete the existing data in the Oracle with the same name as the source table will be cleared before the move. Click Next.

14.  On the Summary page, check the summary and click Finish.

15.  Validate the results.

Count the rows in the Oracle database and the source database;

The sum of the numerical columns in the Oracle database should be calculated and compared to those in the source database.

Forewarned is Forearmed

Database backup takes precautions against any data loss accidents during the data migration and is absolutely necessary for businesses from all walks of life. Otherwise, the company may end up losing critical data.

Vinchin Backup & Recovery delivers backup and recovery services for 6 databases (Oracle Database, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB) and 10+ platforms including VMware, XenServer, Hyper-V, XCP-ng, Red Hat Virtualization, oVirt, Sangfor HCI, OpenStack, Oracle OLVM, and more.

This backup solution reduces backup workload and improves efficiency with various strategies:

Scheduled backup: automates backup under repeatable schedules and email notifications in case of job interruption.

Different backup types: a simple click on the full, incremental, differential, archive log, and transaction log backup for databases respectively.

Save 50% of storage: reduce 50% of data to conserve backup space for newly written data and storage costs by enabling data deduplication and compression.

Data security matters: encrypt the data path to protect data during backup and automatically employ an IO monitor to identify and reject suspicious visitors in real time for all backups in the Vinchin server.

Integration with databases: provide advanced database built-in features like auto-check data consistency, delete archive log, database compression, multithread, etc.

Reasonable price: select from flexible license models (subscription and perpetual) at an affordable price for organizations.

Free trial of the Enterprise edition: get the 60-day full-featured free trial of Vinchin Backup & Recovery now to protect your business-critical data.

Overall

Oracle SQL Developer is a free database migration tool developed by Oracle Corporation that allows users to convert Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, PostgreSQL, and IBM DB2 to Oracle database.

This article explains what is SQL Developer and how it migrates other databases to Oracle. Also, remember to backup all databases involved to brace for the worst situations.