Help Center> Data Warehouse Service (DWS)> Best Practices> Data Migration> Migrating Data From Oracle to GaussDB(DWS)> Migrating Table Definitions> Migrating Table Definitions and Syntax
Updated on 2024-04-25 GMT+08:00
View PDF

Migrating Table Definitions and Syntax

  1. Log in to the PL/SQL Developer use an account with the sysdba permission. In this example, the account db_user01 is used.

    The following uses the PL/SQL Developer Trial Version as an example.

  1. On the menu bar, choose Tools > Export User Objects.
  2. Select the logged-in user db_user01, select the table object APEX2_DYNAMIC_ADD_REMAIN_TEST of the user, select the path to the output file (name the output SQL file as test), and click Export.

    The exported DDL file is as follows:

  3. Place the exported DDL file in the input directory of the decompressed DSC folder.

  4. In the directory of runDSC.bat, press Shift and right-click. Choose Open PowerShell window here and perform the conversion. Replace D:\DSC\DSC\input, D:\DSC\DSC\output, and D:\DSC\DSC\log with the actual DSC paths.

    1
    		 
    .\runDSC.bat --source-db Oracle --input-folder D:\DSC\DSC\input --output-folder D:\DSC\DSC\output --log-folder D:\DSC\DSC\log --application-lang SQL --conversion-type bulk --target-db gaussdbA

  5. After the conversion is complete, the converted DDL file is automatically generated in the output directory of DSC.

  6. The table definition structure of GaussDB(DWS) is different from that of Oracle. You need to manually modify the converted table definition.

    Comment out \echo in the file (if you use gsql to import table definitions, you do not need to do this) and manually change the distribution column of the specified table.
    • Before the change:

    • After the change:

    The distribution column in a hash table must meet the following requirements, which are ranked by priority in descending order:

    1. The values of the distribution key should be discrete so that data can be evenly distributed on each DN. You can select the primary key of the table as the distribution key. For example, for a person information table, choose the ID number column as the distribution key.
    2. Do not select the column where a constant filter exists. For example, if a constant constraint (for example, zqdh= '000001') exists on the zqdh column in some queries on the dwcjk table, you are not advised to use zqdh as the distribution key.
    3. Select the join condition as the distribution column, so that join tasks can be pushed down to DNs to execute, reducing the amount of data transferred between the DNs.

  7. Create a GaussDB(dws) cluster. For details, see Creating a Cluster.
  8. Connect to the GaussDB(DWS) cluster as the system administrator dbadmin. For details, see Using the Data Studio GUI Client to Connect to a Cluster. By default, the first connection is to the default database gaussdb.
  9. Create a new target database test, and then switch to it.

    1
    		 
    CREATE DATABASE test WITH ENCODING 'UTF-8' DBCOMPATIBILITY 'ORA' TEMPLATE template0;

  10. Create a schema and switch to it. The schema name must be the same as the Oracle user name (db_user01 in this example).

    1
2
    		 
    CREATE SCHEMA db_user01;
SET CURRENT_SCHEMA = db_user01;

  11. Copy the converted DDL statements in 7 to Data Studio for execution.
  12. If the APEX2_DYNAMIC_ADD_REMAIN_TEST table can be found in the schema in the test database of the GaussDB(DWS) cluster, the table definition is migrated.

    1
    		 
    SELECT COUNT(*) FORM db_user01.APEX2_DYNAMIC_ADD_REMAIN_TEST;