DMS_SAMPLE Schema Validation Report for Oracle to PostgreSQL Migration

Contents

  1. Overview
  2. Database Configuration
    1. Database
    2. Schema Size
    3. Character Encoding and Collation
  3. Database Objects
    1. Objects Summary
    2. Objects Mismatch
      1. Tables
      2. Tables Partition
      3. Indexes By Table
      4. Partition Indexes
      5. Indexes
      6. Table columns
      7. Sequence
      8. Sequence maxval
      9. Constraints
        1. Primary key
        2. Foreign Reference
        3. Unique
        4. Check
        5. Not Null
        6. Default
      10. Materialized View
      11. Type
      12. View
      13. Trigger
  4. Data Type
    1. Data Type Mapping Matrix
    2. Data Type Mapping on Primary and Foreign key columns
  5. Migration anomalies
    1. Partition column not part of Primary key
    2. Unique index as composite indexes with nullable constraint on column
    3. User Created Extended Statistics
    4. Default on NULLs
    5. Database Identifier length > 63
Schema Validation Report for Oracle to PostgreSQL Migration

Overview


  • This is the output from the Schema Validation script on Oracle to PostgreSQL Migration.

    • Current Database Time : 2022-02-21 14:43:07
    Oracle Database User : DMS_SAMPLE
    PostgreSQL Database Schema: dms_sample

    Database Configuration

    Database Details


  • Previous : Top : Next

    • Engine Database Name Version
    Oracle ORADEV Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    PostgreSQL dms_sample PostgreSQL 13.3 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 7.4.0, 64-bit


    Schema Size


  • Previous : Top : Next

    • schema Name Oracle Size(GB) PostgreSQL Size(GB)
    DMS_SAMPLE 106 0


    Character Encoding and Collation


  • Previous : Top : Next

    • Engine Characterset/Encoding/Collation
    Oracle AL32UTF8
    PostgreSQL Encoding = UTF8, Collation = en_US.UTF-8

    Database Objects

    Database Object Summary


  • Previous : Top : Next

    • This section shows the count of objects in Oracle and PostgreSQL Database with source Oracle as reference.

    Schema Name Object Type Oracle Object Count PostgreSQL Object Count
    DMS_SAMPLE TABLE 29 24
    DMS_SAMPLE INDEX 35 30
    DMS_SAMPLE SEQUENCE 6 7
    DMS_SAMPLE VIEW 3 2
    DMS_SAMPLE TRIGGER 4 3

    Database Schema Objects Mismatch

    Missing Target Database Table Details


  • Previous : Top : Next

    • This section provides the tables that exist in Oracle but are missing in PostgreSQL Database.

    Schema Name Table Name Partitioned
    DMS_SAMPLE AREA_EXCEPTIONS YES
    DMS_SAMPLE TBL_GRP NO
    DMS_SAMPLE TBL_STG NO
    DMS_SAMPLE TEST_DATA NO
    DMS_SAMPLE USERS YES

    Missing Target Database Table Partition Details


  • Previous : Top : Next

    • This section provides the table partitions that exist in Oracle but are missing in PostgreSQL Database.

    Schema Name Table Name Oracle Table Partition Count PostgreSQL Table Partition Count
    DMS_SAMPLE AREA_EXCEPTIONS 27 0
    DMS_SAMPLE USERS 2 0


    Schema Name Table Name Partition Name
    DMS_SAMPLE AREA_EXCEPTIONS AFE_2015_Q1
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P29459
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P31359
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P33239
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P35118
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P37439
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P40279
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P42926
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P45458
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P46663
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P49591
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P51158
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P51939
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P52759
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P53317
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P53617
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P54563
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P55363
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P56446
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P57246
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P58194
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P58902
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P59444
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P60298
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P60990
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P61806
    DMS_SAMPLE AREA_EXCEPTIONS SYS_P62555
    DMS_SAMPLE USERS USERS_0
    DMS_SAMPLE USERS USERS_1


    Database Indexes Comparison Based on Table


  • Previous : Top : Next

    • This section identifies those tables, where the number of indexes does not match between Oracle and PostgreSQL Database.

    Schema Name Table Name Oracle Index Count PostgreSQL Index Count
    DMS_SAMPLE TEST2 3 1
    DMS_SAMPLE AREA_EXCEPTIONS 2 0
    DMS_SAMPLE TEST_DATA 1 0


    Missing Target Database Indexes Details


  • Previous : Top : Next

    • This section identifies those indexes that exist in Oracle but are missing in PostgreSQL Database, for non-partitioned tables.

    Schema Name Table Name Index Name Oracle Index Type
    DMS_SAMPLE SPORTING_EVENT_TICKET SET_EV_ID_TKHOLDER_ID_IDX FUNCTION-BASED NORMAL
    DMS_SAMPLE TEST2 TEST2_UK1 NORMAL
    DMS_SAMPLE TEST2 TEST2_UK2 NORMAL
    DMS_SAMPLE TEST_DATA TEST_DATA_PK NORMAL


    Missing Table Partition Indexes


  • Previous : Top : Next

    • This section identifies those indexes that exist in Oracle but are missing in PostgreSQL Database, for Partitioned tables.

    Schema Name Table Name Index Name
    DMS_SAMPLE AREA_EXCEPTIONS AEX_RECNUMBFDDTKEY
    DMS_SAMPLE AREA_EXCEPTIONS AEX_RECORDNUMBERKEY


    Missing Target Database Table Column details


  • Previous : Top : Next

    • This section identifies the columns of a table that exist in Oracle but are missing in PostgreSQL Database.

    Schema Name Table Name Column Name
    DMS_SAMPLE TEST2 ID


    Missing Target Database Sequence


  • Previous : Top : Next

    • This section identifies the sequences that exist in Oracle but are missing in PostgreSQL Database.

    Schema Name Sequence Name Last Sequence Value
    DMS_SAMPLE ISEQ$$_73708 212201109


    Sequence MAXVAL Comparision


  • Previous : Top : Next

    • This section provides the last value of the sequence in Oracle Database when it is greater than the sequence value in PostgreSQL Database.

    Schema Name Sequence Name Oracle Last Sequence Value PostgreSQL Last Sequence Value

    Database Constraint Details

    Missing Target Database Primary key


  • Previous : Top : Next

    • This section identifies the Primary Key constraint that exist in Oracle but are missing in PostgreSQL Database.

    Schema Name Table Name Primary Key Constraint Column


    Missing Target Database Foreign Reference


  • Previous : Top : Next

    • This section identifies the Foreign Key constraint that exist in Oracle but are missing in PostgreSQL Database.

    Schema Name Table Name Foreign Key Constraint Column
    DMS_SAMPLE PLAYER SPORT_TEAM_FK {SPORT_TEAM_ID}
    DMS_SAMPLE SEAT SEAT_TYPE_FK {SEAT_TYPE}
    DMS_SAMPLE SEAT S_SPORT_LOCATION_FK {SPORT_LOCATION_ID}
    DMS_SAMPLE SPORT_DIVISION SD_SPORT_LEAGUE_FK {SPORT_LEAGUE_SHORT_NAME}
    DMS_SAMPLE SPORT_DIVISION SD_SPORT_TYPE_FK {SPORT_TYPE_NAME}
    DMS_SAMPLE SPORTING_EVENT SE_AWAY_TEAM_ID_FK {AWAY_TEAM_ID}
    DMS_SAMPLE SPORTING_EVENT SE_HOME_TEAM_ID_FK {HOME_TEAM_ID}
    DMS_SAMPLE SPORTING_EVENT SE_LOCATION_ID_FK {LOCATION_ID}
    DMS_SAMPLE SPORTING_EVENT SE_SPORT_TYPE_FK {SPORT_TYPE_NAME}
    DMS_SAMPLE SPORTING_EVENT_TICKET SET_PERSON_ID {TICKETHOLDER_ID}
    DMS_SAMPLE SPORTING_EVENT_TICKET SET_SEAT_FK {SPORT_LOCATION_ID,SEAT_LEVEL,SEAT_SECTION,SEAT_ROW,SEAT}
    DMS_SAMPLE SPORTING_EVENT_TICKET SET_SPORTING_EVENT_FK {SPORTING_EVENT_ID}
    DMS_SAMPLE SPORT_LEAGUE SL_SPORT_TYPE_FK {SPORT_TYPE_NAME}
    DMS_SAMPLE SPORT_TEAM HOME_FIELD_FK {HOME_FIELD_ID}
    DMS_SAMPLE SPORT_TEAM ST_SPORT_TYPE_FK {SPORT_TYPE_NAME}
    DMS_SAMPLE TEST2 TEST2_FK1 {COL1}
    DMS_SAMPLE TEST3 TEST3_FK1 {COL2}
    DMS_SAMPLE TICKET_PURCHASE_HIST TPH_SPORT_EVENT_TIC_ID {SPORTING_EVENT_TICKET_ID}
    DMS_SAMPLE TICKET_PURCHASE_HIST TPH_TICKETHOLDER_ID {PURCHASED_BY_ID}
    DMS_SAMPLE TICKET_PURCHASE_HIST TPH_TRANSFER_FROM_ID {TRANSFERRED_FROM_ID}


    Missing Target Database Unique Constraint


  • Previous : Top : Next

    • This section identifies the Unique Key constraint that exist in Oracle but are missing/mismatch in PostgreSQL Database.

    Schema Name Table Name Unique Key Constraint Column
    DMS_SAMPLE TEST2 TEST2_UK1 {COL2}
    DMS_SAMPLE TEST2 TEST2_UK2 {COL1}


    Missing Target Database Check Constraints


  • Previous : Top : Next

    • This section identifies the Foreign Key constraint that exist in Oracle but are missing/mismatch in PostgreSQL Database.

    Schema Name Table Name Oracle Check Constraint Count PostgreSQL Check Constraint Count
    DMS_SAMPLE TEST_CHECK_CONS 1 0


    Missing Nullable Constraints


  • Previous : Top : Next

    • This section identifies the Null Column constraint that exist in Oracle but are missing/mismatch in PostgreSQL Database.

    Schema Name Table Name Column Name Column Nullable
    DMS_SAMPLE TEST2 COL2 Yes
    DMS_SAMPLE TEST2 COL1 Yes


    Mismatch Default Constraints


  • Previous : Top : Next

    • This section identifies count of Columns with default constraint for a table, that exist in Oracle Database but do not match with the table in PostgreSQL Database.

    Schema Name Table Name Oracle Default Columns Count PostgreSQL Default Columns Count
    DMS_SAMPLE STADIUM_NOTES 1 0


    Materialized View


  • Previous : Top : Next

    • This section identifies Materilized Views in Oracle Database that do not exist/match in PostgreSQL Database.

    Schema Name MView Name Refresh Method


    Type


  • Previous : Top : Next

    • This section identifies Type in Oracle Database that do not exist/match in PostgreSQL Database.

    Schema Name Type Name Type Status


    View


  • Previous : Top : Next

    • This section identifies Views in Oracle Database that do not exist/match in PostgreSQL Database.

    Schema Name View Name View Status
    DMS_SAMPLE DBA_SEGMENTS VALID


    Trigger


  • Previous : Top : Next

    • This section identifies Triggers in Oracle Database that do not exist/match in PostgreSQL Database.

    Schema Name Trigger Name Trigger Status
    DMS_SAMPLE USERS_TRG VALID


    Oracle Package as PostreSQL Schema comparision


  • Previous : Top : Next

    • During Oracle to PostgreSQL conversion, Packages in Oracle are converted to Schemas in PostgreSQL Database.

    This section identifies those Packages in Oracle Database that do not have a schema in PostgreSQL Database.

    Schema Name Package Name

    Package body object comparision

    This section identifies public procedures and functions defined in Package specification of an Oracle Database that do not exist in the schema of a PostgreSQL Database.

    Schema Name Object Type Package Name Object Name


    Procedures and Functions


  • Previous : Top : Next

    • This section identifies procedures and functions in Oracle Database that do not exist in PostgreSQL Database.

    Schema Name Object Type Object Name Object Status

    Data Type

    Data Type Mapping Matrix


  • Previous : Top : Next

    • This section display of Column data type Matrix for Tables in Oracle and PostgreSQL Database.

    Oracle Data Type PostgreSQL Data Type
    BFILE character varying
    CLOB text
    DATE bigint
    DATE character varying
    DATE timestamp without time zone
    NUMBER bigint
    NUMBER double precision
    NUMBER integer
    NUMBER numeric
    NUMBER numeric(3,0)
    NUMBER(*,0) bigint
    NUMBER(*,0) character varying
    NUMBER(*,0) integer
    NUMBER(1,0) numeric(1,0)
    NUMBER(10,0) numeric(10,0)
    NUMBER(10,2) double precision
    NUMBER(10,2) numeric(10,2)
    NUMBER(2,0) numeric(2,0)
    NUMBER(3,0) numeric(3,0)
    NUMBER(4,0) numeric(4,0)
    NUMBER(7,0) numeric(7,0)
    NUMBER(8,2) numeric(8,2)
    VARCHAR2 character varying

    Data Type Mapping Matrix for Primary and Foreign key


  • Previous : Top : Next

    • This section shows the data type mismatch between Parent(Primary/Unique Key) and Child keys(Foreign Key) in PostgreSQL Database.

    Parent Schema Parent Table Name Parent Column name PostgreSQL Parent Data type Child Schema Child Table Name Child Column name PostgreSQL Child Data type
    DMS_SAMPLE TEST1 COL1 integer DMS_SAMPLE TEST2 COL1 bigint
    DMS_SAMPLE TEST2 COL1 bigint DMS_SAMPLE TEST3 COL2 character varying(10)

    Migration Anomalies

    Partition column not part of Primary key


  • Previous : Top : Next

    • This section identifies Primary keys that is missing Partition key column for Partitioned tables in Oracle Database.

    Schema Name Table Name Partition Column Primary Column
    DMS_SAMPLE AREA_EXCEPTIONS FEEDBACK_DATE RECORDNUMBER


    Unique on Composite column with nullable constraint on either


  • Previous : Top : Next

    • This section identifies Unique index as composite indexes with nullable constraint in Oracle Database.

    Schema Name Table Name Constraint Name Unique Columns Nullable Column
    DMS_SAMPLE TEST2 TEST2_UK1 COL2 COL2
    DMS_SAMPLE TEST2 TEST2_UK2 COL1 COL1


    User Created Extended Statistics


  • Previous : Top : Next

    • This section identifies those tables in Oracle Database that have extended statistics.

    Schema Name Table Name Statistics Extension Name Extended Stat Details


    Default on NULLs


  • Previous : Top : Next

    • This section identifies tables column that have default Values on Null Constraint in Oracle Database.

    Schema Name Table Name Column Name Default on Null Null Detail Data on Null
    DMS_SAMPLE STADIUM_NOTES ID YES N "DMS_SAMPLE"."ISEQ$$_73708".nextval


    Database Identifiers Greater length > 63


  • Previous : Top :

    • This section identifies Oracle Database objects, with identifier name length greater than 63 characters.

    Object Type Schema Name Object Name Table Name Identifier Length

    End Report