1. How to Create a Migration Repository in Oracle SQL Developer
2. Migration Wizard - Capturing Source Database
3. Migration Wizard - Convert Captured Model to Oracle
4. Migration Wizard - Translating SQL Objects
5. Migration Wizard - Generating Scripts to Create Target Database
6. Move Data from Third Party Database to Oracle

Create Migration Repository

1. Create user, and assign permissions

CREATE USER MIGRATIONREPO IDENTIFIED BY oracle;

ALTER USER MIGRATIONREPO QUOTA UNLIMITED ON USERS;

GRANT CONNECT,RESOURCE,CREATE VIEW ,CREATE MATERIALIZED VIEW to MIGRATIONREPO;

2. Associate Repository

NOTE: Should go smothly, without erros

1 - Setup migration

First we will translate the current mysql to oracle.

1. Choose the database connection and select "Migrate to Oracle"

2. Select repository:
   1. select the previous created repository
   2. And select "Truncate" option
   3. 
   4. Click "Next"
3. Project
   1. Give a project a name
   2. Select the output directory
      1. This will be used to output logs, schemas, ect..
   3. 
   4. Click "Next"
4. Select Source database connection
   1. You must select the connection where the database you want to migrate is located:
   2. 
   3. Click "Next"
5. Capture
   1. Select all the databases you want to migrate
   2. 
   3. Click "Next"
6. Convert
   1. Associate data types from mysql and the corresponding in oracle, eg:
      • mysql -> text | oracle -> clob
   2. The defaults are fine for
   3. 
   4. Click "Next"
7. Translate
   1. Select all SQL Objects we want to translate
   2. Select "Proceed to Summary Page"
   3. Click "Finish"
   4. This can take some time, ~15-20m
      1. If is successful:
      2. 
   5. 

2 - Update columns name

As part of the translation process oracle renamed columns with reserved keywords as name, eg: "order" to "order_"

So we need first fix that.

On Converted Database Objects » Columns.

1. Search for: TARGET_COLUMN_NAME like '%!_' escape '!'
2. Replace all "TARGET_COLUMN_NAME" with the column name inside double quotes
   1. eg: order_ to "ORDER"
3. Commit Changes

IMPORTANT COLUMN NAMES MUST BE UPPERCASE

Resize Varchar 8000 to 4000

Oracle limits Varchar2 to 4000 characters by default, this can be change but we may not have permissions to do that.

In "Columns" tabs search for:

SOURCE_COLUMN_PRECISION like '8000'

And change column "TARGET_COLUMN_PRECISION" value to 4000

Commit the changes

3 - Generate target

Once the translation is successful we will start, make some changes

1. Generate target
   1. 
2. Select the database connection
   1. 
   2. Click "Next"
3. Move data
   1. Choose the source and target connections, in our case is:
      1. Source: mysql
      2. Target: oracle connection
   2. Select "Truncate data"
   3. 
   4. Click "Next"
4. Finish
   1. Click "Finish"
   2. This is a lot quicker than the translate it should take about ~1m
      1. If is successful:
      2. 

4 - Unlock User

Last we need to unlock the user and reset the passord:

ALTER USER sgiv10_cmvm_test ACCOUNT UNLOCK;

ALTER USER sgiv10_cmvm_test IDENTIFIED BY sgiv10_cmvm_test;

5 - Identify Clob columns

SELECT DISTINCT table_name, column_name FROM all_tab_columns WHERE DATA_TYPE LIKE 'CLOB' and table_name like 'T%';

SOURCE_COLUMN_PRECISION like '8000'

TARGET_COLUMN_NAME like '"NUMBER"'

DECLARE
  CURSOR c_product
  IS
    SELECT DISTINCT
        table_name
    FROM 
        all_tables 
    WHERE
        (REGEXP_LIKE(table_name, '^T[[:digit:]]+')
        OR table_name IN ('FAILED_JOBS', 'ACTIVITY_LOG', 'MIGRATIONS', 'SYSTEM_DEPLOYMENTS'))
        AND OWNER = 'SGIV10_CMVM_TEST'
    ORDER BY 
        table_name ASC;
BEGIN
  FOR r_product IN c_product
  LOOP
    dbms_output.put_line( r_product.table_name );
  END LOOP;
END;

Errors and fixs

Error classic Capture

Error:

Classic Capture ORA-01950: no privileges on tablespace 'USERS'

Fix:

ALTER USER migrations quota unlimited on users;

Extra

List all columns that were modified and ends in _

TARGET_COLUMN_NAME like '%!_' escape '!' 

LINks

https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm https://docs.oracle.com/cd/E25259_01/appdev.31/e24285/migration.htm#RPTUG45388