Being the unique powerful solution to maintain huge databases, Oracle is a high-priced database management system with strict licensing policy. Nowadays many companies are shifting their databases to open-source systems in order to reduce total cost of the ownership.
PostgreSQL is an open-source object-relational DBMS that is great alternative to Oracle because these systems are very close to each other in the scope of capabilities and range of advanced tools for administration and development. While Oracle is superior in multi-level compression, extensive backup, and flexible storage customization, PostgreSQL provides such advantages as sophisticated locking and recovery techniques, nested transactions, multi-level concurrency control and asynchronous replication.
Straight forward approach to Oracle to PostgreSQL database migration includes the following steps according to extract-transform-load model:
- export schemas, indexes and constraints from the source Oracle database in form of SQL CREATE- statements; convert it into PostgreSQL format and import to the target database
- export data from Oracle database into CSV files (one file per table), transform it according to the target format if necessary and load to the PostgreSQL database
- extract triggers, views, and stored procedures from the source Oracle database in form of SQL statements and PL/SQL source code
- transform those queries and code according to PostgreSQL syntax and then load it to the destination database
First step is to get list of all tables, here is Oracle query for this purpose:
SQL> select table_name from user_tables;
To get definition of the particular table, the following Oracle statements may be used:
SQL> set long 2000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,'<TABLE NAME>’) from DUAL
The resulting ‘CREATE TABLE’ script must be transformed according to PostgreSQL syntax before loading into database:
- Oracle specific keywords at the end of ‘CREATE TABLE’ statement must be removed (starting from “USING INDEX PCTFREE…”)
- Column types must be converted into PostgreSQL equivalents
The next step is to export of Oracle data in CSV format, the following statements may be used for this purpose:
SQL> set heading off
SQL> spool file.csv
SQL> select column1 || ‘,’ || column2 || … from table1;
SQL> set colsep ‘,’
SQL> select * from table1;
SQL> spool off;
PostgreSQL provides the following command to import CSV file into previously created table:
COPY <table_name> FROM <csv_file> DELIMITER ‘,’ CSV;
In order to extract all Oracle indexes for some table, the following query may be used:
SQL> select * from all_indexes where table_name = ‘<TABLE NAME>’;
Remember that all names of database objects are case-sensitive in Oracle. These statements are used to extract definition of the particular index:
SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;
Oracle allows to extract foreign keys through this query:
user_constraints a, user_cons_columns b
a.constraint_name=b.constraint_name and a.constraint_type=’R’
PostgreSQL uses the same syntax of creating indexes and foreign keys, so all related Oracle queries may be run in the target DBMS without any changes.
Migration of views, stored procedures and triggers requires strong skills in database administration and it is laid outside of this article.
Solutions to automate migration from Oracle to PostgreSQL
The steps specified above confirm that database migration between such sophisticated systems as Oracle and PostgreSQL is a tedious time-consuming procedure demanding for a lot of efforts when doing it manually. It would be smart to find a dedicated tool for automating the migration process.
One of such tools is provided by Intelligent Converters company and is known as Oracle to PostgreSQL converter. It makes the migration as easy as a few clicks of mouse buttons. The product has user-friendly interface to simplify conversion of tables, data, indexes, constraints and views.
Find more details about Oracle to PostgreSQL converter at https://www.convert-in.com/ora2pgs.htm