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:
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:
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:
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:
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:
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.
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:
Nov 30, 2018 0
Jul 01, 2017 0
Jul 28, 2018 0
Sep 12, 2017 0
Mar 31, 2022 0
Nov 10, 2021 0Theopen-source rapid application development platform is...
Nov 10, 2021 0The majority of seniors who are just beginning to get...
Oct 18, 2021 0There are so many reasons to know so that you can...
Oct 12, 2021 0In a world with so much information on site and other...
Oct 07, 2021 0Low-code is a software development method that permits the...
Nov 10, 2021 0
Oct 12, 2021 0