While Oracle is highly significant to companies that maintain huge databases, this object-relational DBMS is high-priced when it comes to acquisition of ownership. Its strict licensing policy is another drawback to be considered. These are the reasons why some companies are shifting their focus onto better DBMS.

PostgreSQL is one of the DBMS that are great replacements for Oracle because of the similarities of both database systems. Oracle database system boasts of features such as multi-level compression, extensive backup, and flexible storage customization. But PostgreSQL is a superior improvement to Oracle beacsye it has both relational database and object-oriented features. Some of the features are sophisticated locking mechanism, nested transactions, multi-level concurrency control, asynchronous replication, and timely recovery. For projects which require high data integrity and dependability, PostgreSQL is a great option.

When migrating from Oracle to PostgreSQL, there are some steps that must be considered. These are:

• In ‘CREATE TABLE’ statements, export Oracle table definitions
• Convert SQL-statements into PostgreSQL format, and load into target server
• Using CSV as an intermediate storage, export Oracle data
• If necessary, convert into target format and import in PostgreSQL database
• In SQL statements and plain text source code, export Oracle triggers, views, and stored procedures.
• According to PostgreSQL syntax, transform statements and code, then load to target server

For table definitions, there are basic steps that must be followed.

sqlplus username/password@database

First step is to get full list of Oracle tables that can be done using the SELECT-query below:

SQL> select table_name from user_tables;

Now let us extract definition of particular Oracle table through these statements:

SQL> set long 2000

SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,'<TABLE NAME>'[,’SCHEMA’]) from DUAL

The resulting script must be post-processed before importing into the destination DBMS as follows:

• all Oracle specific statements must be removed at the end of table DDL (starting from “USING INDEX PCTFREE…”)
• According to a certain table that can be found online, equivalents of PostgreSQL must be converted.

Data

The following step is the export of Oracle data in CSV file format according to the following:

SQL> set heading off

SQL> spool filename.csv

SQL> select column1 || ‘,’ || column2 || … from mytable;

SQL> set colsep ‘,’

SQL> select * from my_table;

SQL> spool off;

PostgreSQL allows to import CSV file into a table via the following statement:

COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;

In case of “Permission denied” error after running this statement, try to replace COPY command  by “\COPY” as a workaround.

Indexes

From ‘mytable’, get the list of indexes.

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

However, all database objects are stored in upper case in Oracle. Lower case is used as specified. To extract definition of particular indexes, this command is followed:

SQL> set long 1000

SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;

Solutions to automate Oracle to PostgreSQL migration

The above-listed processes may seem simple but are somewhat sophisticated and time-consuming. It is more tasking when it involves a manual migration. As usual, manual migration is born of human factor which is not only time-wasting and monotonous, it leads to data loss or corruption. This is why it is important to search for a special tool which makes the conversion process less daunting.

Intelligent Converters, a software company specializing in database migration and synchronization, has created Oracle to PostgreSQL converter to make the conversion easy in a few clicks. Also, the tool doesn’t also reduce sophistication, it automates migration of table definitions, views, data, foreign keys, and indexes & constraints.