Oracle is extremely powerful object-relational DBMS designed to manage huge enterprise scale databases. However, it is expensive to acquire the ownership and comes with a strict licensing policy. These reasons force organizations and companies to migrate their databases from Oracle to yet another DBMS.
Reasons to Consider PostgreSQL?
When migrating to new system it is reasonable to ensure that new system offers similar features and capabilities compared to the original DBMS. None of relational database management systems is equal to Oracle due to its unique features such as:
- extensive backup
- multi-level compression
- flexible storage customization
Being a robust standard-complied database management system providing both object-oriented and relational database features, PostgreSQL gets a lot closer to Oracle than DBMS. Below is the brief list of PostgreSQL benefits:
- asynchronous replication
- multi-version concurrency control
- nested transactions
- point-in-time recovery
- sophisticated locking mechanism
With those advanced features PostgreSQL perfectly fits complex database driven projects demanding for high performance, security and data integrity. Or another words, it is the best alternative to Oracle database management system.
Oracle to PostgreSQL Migration
The procedure of migrating from Oracle to Postgres comprises of the following instructions:
- extract definitions of Oracle table in form of “CREATE TABLE” statements
- make these SQL-instructions conformed with PostgreSQL format and load to the target server
- export Oracle data from the source tables into comma separate values files
- if it is necessary, process those temporary stored data according to target format and import in PostgreSQL database
- extract database logic entries such as views, triggers, stored procedures and functions from Oracle database in form of SQL statements and plain text source code
- convert these statements and code into PostgreSQL equivalents and import into the target database
Table classifications
We take into account that SQL*Plus is used as default Oracle client application anywhere below. The following command line is used to connect to Oracle database from SQL*Plus:
sqlplus username/password@database
First, it is necessary to get list of all tables:
SQL> select table_name from user_tables;
Oracle table definition can be extracted using the statements below (‘set long’ command customized max length of text output and the related value should be configured according to the number of table columns):
SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,'<TABLE NAME>'[,’SCHEMA’]) from DUAL
It is required to modify the resulting script according to PostgreSQL syntax before loading into the destination database:
- Oracle specific statements at the end of table DDL must be removed
- All data types must be converted into PostgreSQL equivalents
Data
Export of Oracle data into comma separate values format is implemented using the statements below:
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 provides the “COPY” command to import CSV file into existing table:
COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;
Indexes
Run this query to get list of all indexes that belong to Oracle table “mytable”:
SQL> select * from all_indexes where table_name = ‘<TABLE NAME>’;
It is important to remember that all Oracle database object names are case insensitive by default unless enclosed in quotes.
To get definition of the particular index, use the following statements:
SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;
As before, value of ‘set long’ command should be configured according to the length of index definition.
Migration Tools
The steps explored above demonstrate that manual migration from Oracle to PostgreSQL requires a lot of work and it can also cause loss of data or corruption due to human factor. Therefore, it is justifiable to take some special tools to automate this migration with only a few clicks of the mouse. One of such tools is Oracle to PostgreSQL converter provided by one of the leading companies in database migration and synchronization field, Intelligent Converters. It can migrate the following database objects:
- Table definitions
- Data
- Indexes and constraints
- Foreign keys
- Views
