Programming

The Best Way To Transform Oracle Database To PostgreSQL

25 Jul , 2017  

Oracle is a productive question social DBMS perfect for overseeing tremendous venture scale databases. Of course, it is costly to obtain the proprietorship. It likewise accompanies a strict permitting arrangement. This drawback drives a few organizations and organizations to move their databases from Oracle to yet another DBMS.

Motivations to Consider PostgreSQL?

Database executive or another individual accountable for database development should anticipate that new framework that offers aproportionate arrangement of administrations when contrasted with the essential DBMS. When talking about Oracle database, it is clear that none of the other social database administration frameworks is so stuffed with effective properties, for example,

  • extensive reinforcement
  • multi-level pressure
  • flexible capacity customization

As a strong and standard-affirmed RDBMS that coordinates question arranged and social database highlights, PostgreSQL gets a ton nearer to Oracle than different projects. Here is the short rundown of its propelled highlights:

  • asynchronous replication
  • multi-form simultaneousness control
  • nested exchanges
  • point-in-time recuperation
  • sophisticated bolting instrument

Those points of interest make PostgreSQL an extraordinary answer for complex ventures requiring high reliability and information respectability i.e. the best choice to Oracle database.

Ora to Pgs Migration

The strategy of converting Oracle to Postgres contains the accompanying guidelines:

  • export Oracle table definitions into “Make TABLE” proclamations
  • make these SQL-guidelines accommodated with PostgreSQL configuration and load to the objective server
  • export Oracle information into moderate stockpiling, for example, CSV records
  • convert it into the objective configuration (if fundamental) and import in PostgreSQL database
  • export Oracle sees, triggers, put away systems and capacities into SQL proclamations and plain content source code
  • transform these announcements and code as indicated by PostgreSQL sentence structure and load to the objective server

Table characterizations

Underneath this classification, we consider the fundamental methods require to complete this procedure. Anyplace beneath SQL*Plus is utilized as default Oracle customer application. This is the summon line to associate with the database by means of SQL*Plus:

sqlplus username/password@database

Initially, it is important to get arundown of all tables:

SQL> select table_name from user_tables;

What’s more, here is the best approach to extricate meaning of specific Oracle table:

  • SQL> set long 1000
  • SQL> set pagesize 0
  • SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,'<TABLE NAME>'[,’SCHEMA’]) from DUAL

The subsequent script must be rectified before stacking to PostgreSQL as takes after:

  • Remove Oracle particular proclamations toward the finish of table DDL (beginning from “Utilizing INDEX PCTFREE…”)
  • convert all information sorts into PostgreSQL counterparts as per this table

Information

The subsequent stage is to send out Oracle information into CSV design. It should be possible by means of the accompanying grouping of orders:

  • SQL> set taking off
  • SQL> spool filename.CSV
  • SQL> select column1 || “,” || column2 || … from mytable;
  • SQL> set colsep “,”
  • SQL> select * from my_table;
  • SQL> spool off;

The subsequent CSV record can be foreign into PostgreSQL table through “Duplicate” summon:

Duplicate <table name> FROM <path to CSV file> DELIMITER “,” CSV;

When there is “Authorization denied” blunder subsequent to running this announcement, attempt to utilize “\COPY” charge.

Files

How about we get arundown of all files that have a place with table “mytable”:

  • SQL> select * from all_indexes where table_name = ‘<TABLE NAME>’;

Note, that Oracle stores all database protest names as capitalized as a matter of course unless thelower case was determined straightforwardly by encasing table name in quotes in “Make TABLE” explanation.

Also, this is the manner by which meaning of specific files can be extricated:

  • SQL> set long 1000
  • SQL> set pagesize 0
  • SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;
  • Oracle to PostgreSQL Transformation Tools

The strategies clarified above exhibit that moving Oracle database to PostgreSQL is a perplexing procedure. Manual migration requires a great deal of work and it can likewise cause loss of information or defilement because of thehuman factor. However, it is reasonable to consider some extraordinary migration instruments to convert Oracle to PostgreSQL with just a couple of snaps of the mouse. Wise Converters additionally made therelated device – Oracle to PostgreSQL converter. It can mechanize development of the accompanying database objects:

  • Table definitions
  • Data
  • Indexes and imperatives
  • Foreign keys
  • Views

,