There are a few one-off setup steps that you need to complete before you can use Oracle SQL Developer to browse or migrate from other databases. Before you can create a connection to a database such as MySQL, you need to set up the required database driver for the database. Once you have the drivers installed, you can create the database connections as you do for an Oracle connection. If you plan to migrate to Oracle, you'll also need to set up a migrations repository. In this section, we'll review the options available for the setting up of the additional database drivers, creating the database connections, and managing the migration repository.
Due to additional licensing requirements, Oracle SQL Developer does not ship the JDBC drivers required to connect to IBM DB2, Microsoft SQL Server, MySQL, Sybase, and Teradata. To connect to any of these databases, whether for browsing or migration purposes, you need to download additional drivers and set up SQL Developer in order to access the drivers. This setup is not required for Microsoft Access as it uses the JDBC/ODBC bridge. For a detailed explanation on downloading and setting up the required third-party drivers, refer to Chapter 8, Database Connections and JDBC Drivers.
Every downloaded driver will have a driver binary JAR file to a location on your machine. The driver binary JAR file is typically a separate JAR file located inside the downloaded archive file. The extracted JAR file should be used to import the extension into our SQL Developer environment:
Once you have set up the drivers, the connection dialog changes to include the additional tabs for the database drivers that you have added, as shown in the following screenshot:
You are now ready to create connections. Each of the connection tabs requires slightly different details. For example, in SQL Server, once you have entered Hostname and Port, click on Retrieve database to connect to and access the list of available databases:
By contrast, the Microsoft Access connection requires only the MDB file access. Setting up connections for supported third-party databases is discussed in Chapter 8, Database Connections and JDBC Drivers.
For each database, create the connection, save the password (optional), and test the connection. The Save and Connect buttons save the created connection, while Connect closes the dialog and makes the connection.
You can browse any of the non-Oracle databases in the same way that you can browse any Oracle database connection. The features that are available differ depending on the database you connect to. You can browse the objects and their details and review the data. The context menus for these objects are migration-specific and do not support any DDL.
In the preceding example, we created a database connection to Microsoft SQL Server, as shown in the previous screenshot, using the Microsoft SQL Server database.
Select and expand the database connection. Expand the Tables node and select a table in the list. The details for Columns, Keys (for Primary and Foreign keys), Indexes, Constraints, and Triggers are all displayed. There is also a data display editor that's used to browse the instance data:
This level of detail differs for each database type. For example, when connecting to MySQL, the set of display editors includes the column, index information, and constraints. All third-party connections display data in tables, and some support the ability to export data to various formats. The main difference between non-Oracle database connections and Oracle connections is that non-Oracle connections are read-only views of the database objects and data, while Oracle connections provide context menus to execute DDL and DML.
SQL Developer Migrations uses a database repository to collect, store, and transform the metadata during the migration process. The repository is used only during the migration process, which can be discarded once complete.
Using either the SQL worksheet or the Create User… context menu for other users, create a new Oracle database user with role and system privileges, which include the following privileges:
- Resources
- Creating a session
- Creating a view
Once you have the new user set up, create a new database connection for that user.
By navigating to Tools | Migration, you can manage all aspects of your repository, including creating, truncating, and deleting. Initially, select Create Repository…:
This builds an Oracle database repository with the following components:
Creating the repository is not a long process, with the progress tracked in a pop-up dialog that displays the current activity.
At any stage, in order to connect to a previously created repository, select Associate Migration Repository from the context menu. This Associate Migration Repository menu option performs a number of activities:
- Associating the user with an existing repository set up for that user
- Updating a repository created in an earlier release of SQL Developer
- Creating a new repository for the user if one does not exist yet
If you have a number of migration repositories, you can select and set the current repository using Select Current Repository….
As soon as you have a user connected or associated with the repository, two new windows open. These windows are as follows:
- Captured models
- Converted models
If the windows do not open, you can open them manually from the View menu. These windows will display a list of the captured or converted models you have previously worked with unless you delete or truncate the repository between migrations.
Before you start the migration, you should set up two additional database connections: one for the source database and another for the target database. These connections are no different from the connections you have created previously, but you should give some thought to the database schemas you are going to use.
The database you are migrating from is known as the source database. You need to create a connection to this database. SQL Developer Migrations uses Least Privilege Migration. This means that any user can migrate database objects and data to an Oracle database. However, the migration only reads and migrates those objects that you have access to depending on your privilege level.
Create a database connection, as described in Chapter 8, Database Connections and JDBC Drivers, using a schema that owns the objects you want to migrate.
When migrating to Oracle, you need to create a database connection to a schema that will hold the new database objects. This can be an existing or new schema. If you use an existing schema, you should ensure that no conflicts will arise due to the objects already in the schema. If you create a new schema, you need to ensure that the schema has the following privileges:
- Resources
- Creating a session
- Creating a view
Moreover, if you're performing a multischema migration, this is the main Oracle schema that is used to create the additional target schemas, and so it needs the following additional privileges: