How can I migrate data from Derby to Oracle?

To transfer a database from Derby to Oracle, follow the instructions below:

It is recommended to prepare a new PDB database for aSISt (asistPDB). If you have PDB database, you can use it. It is not necessary to create separate PDB to aSISt. Using the root database is not recommended.

To create asistPDB you can:

use sql:

<SQL> CREATE PLUGGABLE DATABASE asistpdb ADMIN USER asistdba identified by “asist” DEFAULT TABLESPACE USERS DATAFILE ‘D:\APP\ORACLE12\ORADATA\ASIST\ASIST\USERS01.DBF’ SIZE 1G AUTOEXTEND ON FILE_NAME_CONVERT=(‘D:\APP\ORACLE12\ORADATA\ORCL\PDBSEED\’,’D:\APP\ORACLE12\ORADATA\ASIST\ASIST\’);

<SQL> ALTER PLUGGABLE DATABASE asistpdb open read write;

<SQL> ALTER PLUGGABLE DATABASE asistpdb SAVE STATE;

Note: The path in the first SQL has to define the existing place and files.

use the instruction available on the Oracle website.

To create a new Oracle user can be used SQL:

<SQL> alter session set container = asistpdb;

<SQL> create user asist identified by asist default tablespace users temporary tablespace temp;

<SQL> grant connect,resource,create view to asist;

<SQL> GRANT UNLIMITED TABLESPACE TO asist;

SQL can be modified if you need to change the path or database name, user name.

Please connect with the new asist user.

Next, it is required to prepare backup of Derby database.

If you have the backup, you can start the migration.

Please run derby_2_oracle_migrator.bat which is available in aSISt5 directory:

In the wizard it is required to fill:

User: asist (migr in attached screenshots)

Password: asist (migr in attached screenshots)

Address: jdbc:oracle:thin:@//localhost:1522/asistpdb (format for Oracle 12, you have to fill the address for the new asist connection including the proper PDB database – orclpdb in my case)