Although Oracle is an amazing object-relational DBMS that can handle huge enterprise data warehouses, it is costly to acquire the ownership. It also includes a tough licensing policy. This drawback forces some companies and businesses to transfer their databases from Oracle to yet another DBMS. 

Why you should Consider PostgreSQL?

Database administrator as well as other person in charge of database movement need to look forward to that new system that provides equivalent set of services when compared to the primary DBMS. When talking about Oracle database, it’s apparent that no other relational database management systems is so filled with powerful properties including: 

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

As a powerful and standard-certified RDBMS that incorporates object-oriented and relational database functions, PostgreSQL becomes a lot closer to Oracle than other programs. Here is a short list of its advanced features: 

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

Those advantages make PostgreSQL a fantastic solution for complex projects demanding high dependability and data integrity i.e. the best option to Oracle database. 

Oracle to PostgreSQL Migration

The process of Oracle to PostgreSQL migration includes all of the following instructions: 

• export Oracle table definitions into “CREATE TABLE” statements
• make these SQL-instructions conformed with PostgreSQL format and load to the target server
• Oracle data must be exported into temporary CSV files
• transform that data according to PostgreSQL format and import into the target database
• extract views, triggers, stored procedures and functions from Oracle database in form of SQL statements and source code
• redevelop these statements and code with respect to PostgreSQL syntax and load into the database

Table classifications 

This section explores basic steps to get information about table definitions. All examples below use SQL*Plus as default Oracle client application, it can be accessed from command line as follows: 

sqlplus username/password@database

Run the query below to list all available tables: 

SQL> select table_name from user_tables;

Here is the query to extract definition (DDL) of the particular Oracle table: 

SQL> set long 1000

SQL> set pagesize 0

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

Data 

Next step is to get Oracle data and store it into CSV files. The following sequence of commands can be used for this purpose: 

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;

Indexes 

Run the following statement in order to get list of all indexes that belong to table “mytable”: 

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

It is important to remember that by default Oracle treats all object names as upper case unless lower case was specified directly by enclosing table name in quotes in “CREATE TABLE” statement. 

After listing all available indexes, it is possible to get definition of the particular index as follows: 

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 techniques described above indicate that moving Oracle database to PostgreSQL is a complex process. Manual migration needs a great deal of work and can also cause data loss or corruption as a result of human factor. Nevertheless, It is justifiable to take some special migration tools into consideration to compliment the migration of database from Oracle to PostgreSQL with only a few clicks of the mouse. Intelligent Converters made the tool for same purpose – Oracle to PostgreSQL converter. This program automate migrates the following database entries: 

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

Oracle to PostgreSQL converter can store conversion settings into profile to simplify next migration. Also, it supports command line to automate the procedure and allow scripting and scheduling.