Oracle is a powerful object-relational DBMS that can handle huge enterprise scale databases, but it comes with a high cost of ownership and strict licensing policy. As a result, some companies and organizations may need to migrate their databases from Oracle to another DBMS. PostgreSQL is often chosen as an alternative to Oracle due to its advanced features and powerful standards-compliant RDBMS that combines object-oriented and relational database functionality.
When considering database migration, it is important to ensure that the new system provides similar features compared to the original DBMS. PostgreSQL offers features such as asynchronous replication, multi-version concurrency control, nested transactions, point-in-time recovery, and a sophisticated locking mechanism. These benefits make PostgreSQL an excellent solution for complicated projects that demand high reliability and data integrity.
The process of converting Oracle database to PostgreSQL involves exporting Oracle table definitions into “CREATE TABLE” statements, making these SQL statements compatible with PostgreSQL format, and loading them onto the target server. Oracle data can then be exported into an intermediate storage such as CSV files and converted into the target format (if necessary) and imported into PostgreSQL database. Oracle views, triggers, stored procedures, and functions can also be exported into SQL statements and plain text source code, transformed according to PostgreSQL syntax, and loaded onto the target server.
To extract the definition of a particular Oracle table, one can use the SQL*Plus command-line tool to connect to the database and select the table name from user_tables. The definition of the table can then be obtained using the DBMS_METADATA.GET_DDL function:
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 to remove Oracle-specific statements and convert data types into PostgreSQL equivalents.
Oracle data can be exported into CSV format using the SQL*Plus command-line tool:
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;
and then imported into PostgreSQL using the “COPY” command. Indexes can be extracted using SQL commands, and empty strings and NULL values must be handled differently in Oracle and PostgreSQL.
List of all indexes that belong to table “mytable” can be extracted via the following Oracle statement:
SQL> select * from all_indexes where table_name = ‘<TABLE NAME>’;
Note, that Oracle table names are uppercase by default unless it is enclosed in quotes in CREATE TABLE query. Use the following statement to get definition of the particular Oracle index:
SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;
Another bottleneck of Oracle to PostgreSQL database migration is that Oracle does not distinguish empty strings from NULL values for text data.
Due to all these challenges of migration it is reasonable to use special software to convert databases between two DBMS. One of such solutions is Oracle to PostgreSQL converter offered by Intelligent Converters software company. The product offers the following benefits:
- Migrates schemas, data, indexes, constraints, sequences and non-sophisticated views
- Fast migration engine (2GB data is migrated within less than 10 minutes on average modern system)
- All versions of Oracle and PostgreSQL (starting from 9.0) are supported as well as popular DBaaS variations (such as Amazon RDS, Azure for PostgreSQL and Heroku)
- Option to merge and sync Oracle data into and existing PostgreSQL database
- Supports command line for scripting and scheduling and scheduling the migration
- Oracle database can be migrated into PostgreSQL script file containing statement to create database objects and insert the data.