Data Definition Language (DDL) Migration

This section focuses on the migration of the databases and tables from Oracle to Exasol. It includes information on how databases, data types, and constraints are mapped to Exasol.

This migration guide is based on the premise of using the Oracle Database Migration Script to execute the DDL migration (schema, tables) and map all data types to Exasol data types. While it is possible to execute certain steps manually outside of the script, using the script lets you automate tasks for converting schemas and table definitions as much as possible.

Prerequisite

Before you begin with the migration, as a prerequisite you need to first upload Oracle JDBC Driver or Oracle OCI Driver to Exasol. To do this, you can follow the steps below: 

Using JDBC Driver

  1. You can download the JDBC driver (for example, ojdbc8.jar) from Oracle JDBC Driver download site. Make sure you download the correct version of the JDBC driver matching the version of the Oracle database.
  2. Next, you need to upload this JDBC driver to EXAoperation, Exasol's management interface.
    1. Log in to the EXAoperation user interface as an administrator user.
    2. Select Configuration > Software and click the JDBC Drivers tab. Click Add to add the JDBC driver details.
    3. Enter the following details for the JDBC properties:
      • Driver Name: Oracle
      • Main Class: oracle.jdbc.driver.OracleDriver
      • Prefix: jdbc:oracle:thin:
      • Disable Security Manager: Do not select this option. This is an optional field. This allows the JDBC Driver to access the certificate and additional information.
      • Comment: This is an optional field.
    4. Click Add to save the settings.
    5. Select the radio button next to the driver from the list of JDBC drivers.
    6. Click Choose File to locate the downloaded driver and click Upload to upload the JDBC driver.
  3. Open your preferred Exasol SQL Client (for example, DBvisualizer or DBeaver) and create the connection to Oracle: 
    create or replace connection oracle_jdbc    
    to 'jdbc:oracle:thin:@192.168.56.106:1521/orcl' 
    user 'stage' identified by 'stage';
  4. Check the connection to your Oracle database from Exasol by performing a small dummy import, as shown below: 
    import from JDBC at oracle_jdbc 
    statement 'select ''Connection works'' from dual';

Using OCI Driver

  1. Log in to the EXAoperation user interface as an administrator user.
  2. Select Configuration > Software and click the Versions tab. The screen shows you which exact driver version you need to download to enable native Oracle connections (for example, instantclient-basic-linux.x64-12.1.0.2.0.zip).
  3. Download the exact OCI driver from Oracle’s Instant Client Download site (for example, instantclient-basic-linux.x64-12.1.0.2.0.zip).

  4. Upload the downloaded OCI driver on Configuration > Software > Versions.

    1. Click Choose File to select the downloaded file (for example, instantclient-basic-linux.x64-12.1.0.2.0.zip).
    2. Click Submit to upload the OCI driver.
  5. Open your preferred Exasol SQL Client (for example, DBvisualizer or DBeaver) and create a connection to Oracle over OCI with the following statement: 
    create or replace connection oracle_oci 
    to '192.168.56.106:1521/orcl' 
    user 'stage' identified by 'stage';
  6. Check the ORA connection to Oracle by performing a small dummy import, as shown below: 
    import from ORA at oracle_oci 
    statement 'select ''Connection works'' from dual';

    Using sys as sysdba as the user for the OCI connection might result in an ORA-01017 error. Use another user with the necessary privileges instead.

Next Steps

The next steps in the DDL migration are: