Oracle is an efficient object-relational DBMS ideal for managing huge enterprise scale databases. Then again, it is expensive to acquire the ownership. It also comes with a strict licensing policy. This downside pushes some businesses and companies to move their databases from Oracle to yet another DBMS.

Reasons to Consider PostgreSQL?

Database administrator or other person in charge of database movement should look forward to that new system that offers equivalent set of services when compared to the primary DBMS. When speaking about Oracle database, it is apparent that none of the other relational database management systems is so packed with powerful properties such as:

• extensive backup
• multi-level compression
• flexible storage customization

As a robust and standard-certified RDBMS that integrates object-oriented and relational database features, PostgreSQL gets a lot closer to Oracle than other programs. Here is the brief list of its advanced features:

• asynchronous replication
• multi-version concurrency control
• nested transactions
• point-in-time recovery
• sophisticated locking mechanism

Those advantages make PostgreSQL an exceptional solution for complex projects requiring high dependability and data integrity i.e. the best option to Oracle database.

Oracle to PostgreSQL Migration

The procedure of converting Oracle to Postgres comprises of the following instructions:

• export Oracle table definitions into “CREATE TABLE” statements
• make these SQL-instructionsconformed with PostgreSQL format and load to the target server
• export Oracle data into intermediate storage such as CSV files
• convert it into the target format (if necessary) and import in PostgreSQL database
• export Oracle views, triggers, stored procedures and functions into SQL statements and plain text source code
• transform these statements and code according to PostgreSQL syntax and load to the target server

Table classifications

Underneath this category, we take into account the basic procedures require to carry out this process. Anywhere below SQL*Plus is used as default Oracle client application. This is the command line to connect to the database via SQL*Plus:

sqlplus username/password@database

First, it is necessary to get list of all tables:

SQL> select table_name from user_tables;

And here is the way to extract definition of particular Oracle table:

SQL> set long 1000

SQL> set pagesize 0

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

The resulting script must be corrected before loading to PostgreSQL as follows:

• remove Oracle specific statements at the end of table DDL (starting from “USING INDEX PCTFREE…”)
• convert all data types into PostgreSQL equivalents according to this table

Data

Next step is to export Oracle data into CSV format. It can be done via the following sequence of commands:

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;

The resulting CSV file can be imported into PostgreSQL table via “COPY” command:

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

If there is “Permission denied” error after running this statement, try to use “\COPY” command instead.

Indexes

Let’s get list of all indexes that belong to table “mytable”:

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

Note, that Oracle stores all database object names as upper case by default unless lower case was specified directly by enclosing table name in quotes in “CREATE TABLE” statement.

And this is how definition of particular indexes can be extracted:

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 methods explained above demonstrate that moving Oracle database to PostgreSQL is a complex process. Manual migration requires a lot of work and it can also cause loss of data or corruption due to human factor. However, It is justifiable to take some special migration tools into account to aid the transfer of database from Oracle to PostgreSQL with only a few clicks of the mouse. Intelligent Converters also created related tool – Oracle to PostgreSQL converter. It can automate movement of the following database objects:

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