Conversion of DDL Oracle to Postgres

Question

I have a very huge DDL script in Oracle of our existing Application, It has no Stored Procedures. Just Tables, Sequences and constraints.

What is the best way to convert it to Postgres?

Some people say its better to do it by hand, and some say there are free tools for it. Can anyone suggest me the best way to do it?

If it is by hand, please suggest me what changes have to be made.

Example of Oracle DDL is given below, Please notify the changes to be made while converting to Postgres for the below DDL.

-  DDL for Table ACTOR_ROLE_INFO
--------------------------------------------------------

  CREATE TABLE "PAYTM_RELEASE1"."ACTOR_ROLE_INFO" 
   (    "ACTOR_ROLE_ID" NUMBER, 
    "ACTOR_ID" NUMBER, 
    "ROLE_ID" NUMBER, 
    "STATUS" NUMBER, 
    "CREATED_BY" NUMBER, 
    "CREATED_ON" TIMESTAMP (6) WITH TIME ZONE, 
    "MODIFIED_BY" NUMBER, 
    "MODIFIED_ON" TIMESTAMP (6) WITH TIME ZONE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Table ACTOR_TYPES
--------------------------------------------------------

  CREATE TABLE "PAYTM_RELEASE1"."ACTOR_TYPES" 
   (    "ACTOR_TYPE_ID" NUMBER, 
    "ACTOR_TYPE" VARCHAR2(100 BYTE), 
    "ACTOR_DESCRIPTION" VARCHAR2(100 BYTE), 
    "CREATED_BY" NUMBER, 
    "CREATED_DATE" TIMESTAMP (6) WITH TIME ZONE, 
    "MODIFIED_BY" NUMBER, 
    "MODIFIED_DATE" TIMESTAMP (6) WITH TIME ZONE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;

Some quick thoughts: You need to drop all the table creation qualifiers except TABLESPACE as none of those exist for Pg. (It'd help if you explained what they're for and which ones, if any, are important for application function, ie you need an alternative). NUMBER becomes NUMERIC for arbitary precision values, or specific more efficient types like integer etc. You'll want to look into any compatibility differences between NUMBER and NUMERIC. VARCHAR2 would typically just become text with a constraint, or varchar(n). — Craig Ringer
– Craig Ringer, Commented Aug 3, 2012 at 8:43
If the DDL is that huge, maintaining it for two platforms will be a nightmare. Have you considered taking this opportunity to convert it into an abstract format from which DDL scripts for both Pg and Oracle can be generated? Some schema reverse engineering tools can help automate the process. — Craig Ringer
– Craig Ringer, Commented Aug 3, 2012 at 8:44
Yes it will be a night mare, But i am not deciding authority.. We are doing it by hand.... I have taken your type changes into consideration,....! Thank you soo much..! — bali208
– bali208, Commented Aug 5, 2012 at 12:45

Jim · Accepted Answer · 2012-08-03 13:14:55Z

1

Use Ora2PG http://sourceforge.net/projects/ora2pg/ to get started, but don't blindly use the resulting schema. As Craig suggested, take a look at the data types. The NUMBER to NUMERIC conversion while simple, leads to a much bigger footprint on disk. It also leads to much bigger index sizes which will slow the whole app down. Your resulting schema shouldn't contain any NUMERIC columns unless your app really needs the abritrary percision and it should be the exception, not the rule.

answered Aug 3, 2012 at 13:14

Jim

7714 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Conversion of DDL Oracle to Postgres

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related