PostgreSQL is a very popular open-source relational database system used widely across industries. Over time, it has inspired several forks and distributed databases that either extend PostgreSQL or maintain full compatibility with it. One of the most notable examples is YugabyteDB, a distributed SQL database designed to deliver high availability, horizontal scalability, and strong consistency – ideal for both OLTP and OLAP workloads. With PostgreSQL and YugabyteDB being a popular migration target for Oracle databases, we were curios to see if our database migration tool: HexaRocket works seamlessly. We choose to migrate an Oracle schema to YugabyteDB, and our aim was to migrate as if we are migrating to P…
PostgreSQL is a very popular open-source relational database system used widely across industries. Over time, it has inspired several forks and distributed databases that either extend PostgreSQL or maintain full compatibility with it. One of the most notable examples is YugabyteDB, a distributed SQL database designed to deliver high availability, horizontal scalability, and strong consistency – ideal for both OLTP and OLAP workloads. With PostgreSQL and YugabyteDB being a popular migration target for Oracle databases, we were curios to see if our database migration tool: HexaRocket works seamlessly. We choose to migrate an Oracle schema to YugabyteDB, and our aim was to migrate as if we are migrating to PostgreSQL. You can see our observations in this article.
Are Enterprises moving from Oracle to Open-Source ?
Oracle is a popular database for massive critical workloads and supports OLTP and OLAP workloads efficiently. While it is a commercial database, users may find it challenging to scale if it also scales up the cost of Infrastructure due to enterprise licensing. This is when users look for migrating to open-source databases like PostgreSQL or Postgres compatible databases like YugabyteDB.
Please visit HexaRocket at www.hexarocket.com
What is YugabyteDB?
YugabyteDB is an open-source, distributed SQL database built for global, internet-scale applications. It combines the best of both worlds – the flexibility and familiarity of PostgreSQL with the scalability and fault-tolerance of a distributed NoSQL system.
At its core, YugabyteDB is designed for cloud-native architectures, supporting:
Automatic sharding and rebalancing
Strong consistency and fault tolerance
Multi-region and multi-cloud deployments
ACID transactions across distributed clusters
Yugabyte’s storage engine, DocDB, ensures transactional consistency, while its YSQL API provides full PostgreSQL compatibility – making it a perfect candidate for Oracle to Yugabyte migration.
For a detailed overview, visit YugabyteDB Official Documentation.
PostgreSQL Compatibility
One of YugabyteDB’s biggest advantages is its deep PostgreSQL compatibility. Its YSQL layer is wire-compatible and syntax-compatible with PostgreSQL, which means:
Most PostgreSQL drivers, tools, and ORM frameworks (like Hibernate, SQLAlchemy, and Django ORM) work seamlessly with YugabyteDB.
SQL functions, DDL statements, and PL/pgSQL procedures can often be used without modification.
Applications that connect to PostgreSQL can connect to YugabyteDB using the same JDBC or libpq interfaces.
For detailed PostgreSQL compatibility notes, visit Yugabyte YSQL Compatibility Reference.
This makes migration from Oracle to YugabyteDB easier using tools already optimized for PostgreSQL compatibility – such as HexaRocket and YugabyteDB Voyager.
Migrating from Oracle to YugabyteDB
Oracle Database has long been the go-to choice for enterprise-scale transactional and analytical workloads. However, its licensing costs, scaling limitations, and vendor lock-in have led many organizations to explore open-source alternatives like PostgreSQL and PostgreSQL-compatible distributed databases such as YugabyteDB.
When choosing a migration approach, automation and accuracy are key. This is where HexaRocket plays an important role.
Tools Available to Simplify Migrations to YugabyteDB
We will limit this article to the 2 popular tools supporting end-to-end database migrations from Oracle to YugaByteDB.
1. Yugabyte Voyager
As proud creators and contributors of Ora2Pg, we are happy that Yugabyte Voyager is created on top of Ora2Pg. At the same time, Yugabyte team has put in a lot of efforts to provide the Voyager with several additional capabilities to simplify Online migrations and migrations from PostgreSQL to Yugabyte and logical replication from Yugabyte to other data sources.
2. HexaRocket
HexaRocket is a unified database migration and replication engine developed by HexaCluster to support multiple migration paths including –
Oracle → PostgreSQL
SQL Server → PostgreSQL
MySQL → PostgreSQL
MariaDB → PostgreSQL
SQL Server → BigQuery
Additionally, HexaRocket supports CDC (real-time replication) across –
Oracle → PostgreSQL
PostgreSQL → Oracle
Oracle → Oracle
MySQL → PostgreSQL
SQL Server → PostgreSQL
While HexaRocket was not built on top of Ora2Pg, we have incorporated a lot of lessons from Ora2Pg. Especially for code conversions from Oracle PL/SQL to PostgreSQL PL/pgSQL, we have designed highly effective code parsers and workflows with validations to support near to 100% automatic schema conversions.
Since Yugabyte is PostgreSQL-compatible, HexaRocket connects to it just like a standard PostgreSQL database. Our tests confirmed a 100% success rate using HexaRocket for schema migration from Oracle to Yugabyte in the first attempt – without any code modifications or manual adjustments.
What did we consider for our Testing ?
Our aim was to test schema migration involving Tables of almost all data types supported by Oracle, and some of the code objects like Stored Procedures, Functions and also Views.
Overall we have included 40 objects that cover a good amount of complexity.
- Tables: 14 Constraints and Indexes: 12
- Sequences: 2
- User Defined Types: 3
- Views: 6
- Stored Procedures: 5
- Functions: 10
Results
The result was expected to 100% from our confidence that YugaByteDB is 100% PostgreSQL comaptible. As expected, we see all objects migrated and also deployed to YugabyteDB without any adjustments.
You can find the Schema Migration Summary of HexaRocket below.
class="language-sql">
[yugabyte@yugabytedb$]bin/ysqlsh -h yugabyte_server -p 5439 -U yugabyte -d yugabyte
ysqlsh (15.12-YB-2025.1.0.1-b0)
Type "help" for help.
yugabyte=# \dn genericmini_cdc
List of schemas
Name | Owner
-----------------+----------
genericmini_cdc | yugabyte
(1 row)
yugabyte=# \dt genericmini_cdc.*
List of relations
Schema | Name | Type | Owner
-----------------+-------------------------------+-------+----------
genericmini_cdc | blob_data | table | yugabyte
genericmini_cdc | blob_data_clone | table | yugabyte
genericmini_cdc | blob_data_oracle | table | yugabyte
genericmini_cdc | boolean_non_boolean | table | yugabyte
genericmini_cdc | boolean_non_boolean_no_pk | table | yugabyte
genericmini_cdc | bytea_raw_with_pk | table | yugabyte
genericmini_cdc | bytea_raw_with_pk_not_null | table | yugabyte
genericmini_cdc | bytea_raw_without_pk | table | yugabyte
genericmini_cdc | bytea_raw_without_pk_not_null | table | yugabyte
genericmini_cdc | character_data | table | yugabyte
genericmini_cdc | character_data_07 | table | yugabyte
genericmini_cdc | clob_data | table | yugabyte
genericmini_cdc | datetime_and_intervals | table | yugabyte
genericmini_cdc | number_combinations | table | yugabyte
(14 rows)
yugabyte=# \dv genericmini_cdc.*
List of relations
Schema | Name | Type | Owner
-----------------+--------------------------------+------+----------
genericmini_cdc | blob_data_hexa | view | yugabyte
genericmini_cdc | boolean_non_boolean_no_pk_hexa | view | yugabyte
genericmini_cdc | character_data_hexa | view | yugabyte
genericmini_cdc | clob_data_hexa | view | yugabyte
genericmini_cdc | datetime_and_intervals_hexa | view | yugabyte
genericmini_cdc | number_combinations_hexa | view | yugabyte
(6 rows)
yugabyte=# select pp.proname from pg_proc pp
join pg_namespace pn on pn.oid=pp.pronamespace where pn.nspname = 'genericmini_cdc';
proname
-----------------------------
add_semesters_to_date
binary_float_subtype
bitwise_and_operation
boolean_to_string
calculate_bonus_percentage
calculate_discounted_price
check_employee_status
compare_binary_float_values
create_invoice_line
fetch_limit_rows
generate_random_integers
get_membership_level
initialize_test_scores
is_binary_float
last_day_leap_year
(15 rows)
Want to try HexaRocket ? Contact Us today.
Datatype Mapping Between Oracle and Yugabyte
Now, let us take a deeper look at the datatype mapping between Oracle and Yugabyte. Please note that the Yugabyte official documentation states that some of the datatypes are still being adopted. In case of any typos made or mistakes done during this draft, please notify us and we could correct the same.
| Oracle | YugabyteDB | Support Status |
|---|---|---|
| CHAR(n) | CHAR(n) | Supported |
| NCHAR(n) | CHAR(n) | Partially Supported |
| VARCHAR2(n) | VARCHAR(n) | Supported |
| NVARCHAR2(n) | VARCHAR(n) | Partially Supported |
| RAW | BYTEA | Supported |
| LONG RAW | BYTEA | Supported |
| DATE | TIMESTAMP | Supported |
| TIMESTAMP | TIMESTAMP | Supported |
| TIMESTAMP WITH TIME ZONE | TIMESTAMP WITH TIME ZONE | Supported |
| TIMESTAMP WITH LOCAL TIME ZONE | TIMESTAMP WITH TIME ZONE | Supported |
| INTERVAL YEAR TO MONTH | INTERVAL YEAR TO MONTH | Supported |
| INTERVAL DAY TO SECOND | INTERVAL DAY TO SECOND | Supported |
| LONG | TEXT | Supported |
| FLOAT | DOUBLE PRECISION | Supported |
| BINARY_FLOAT | DOUBLE PRECISION | Supported |
| BINARY_DOUBLE | DOUBLE PRECISION | Supported |
| NUMBER | NUMERIC | Supported |
| NUMBER(3) | SMALLINT | Supported |
| NUMBER(3,2) | NUMERIC(3,2) | Supported |
| NUMBER(2,7) | NUMERIC | Supported |
| NUMBER(6,-2) | NUMERIC(6,-2) | Unsupported |
| BLOB | BYTEA | Partially Supported |
| CLOB | TEXT | Partially Supported |
| NCLOB | TEXT | Partially Supported |
| BFILE | BYTEA | Unsupported |
| ROWID | OID | Partially Supported |
| UROWID[(size)] | OID | Partially Supported |
| SYS.AnyData | ANYDATA | Unsupported |
| SYS.AnyType | ANYTYPE | Unsupported |
| SYS.AnyDataSet | ANYDATASET | Unsupported |
| XMLType | XML | Partially Supported |
| URIType | URITYPE | Unsupported |
| Objects | — | Unsupported |
| REF | — | Unsupported |
| Nested tables | Composite type | Partially Supported |
| VARRAY | Composite type | Partially Supported |
| CHARACTER(n) | CHAR(n) | Supported |
| CHARACTER VARYING(n) | VARCHAR(n) | Supported |
| CHAR VARYING(n) | VARCHAR(n) | Supported |
| NATIONAL CHARACTER(n) | CHAR(n) | Supported |
| NATIONAL CHARACTER VARYING(n) | VARCHAR(n) | Supported |
| NATIONAL CHAR(n) | CHAR(n) | Supported |
| NATIONAL CHAR VARYING(n) | VARCHAR(n) | Supported |
| NCHAR VARYING(n) | VARCHAR(n) | Supported |
| NUMERIC[(p,s)] | REAL | Supported |
| DECIMAL[(p,s)] | REAL | Supported |
| INTEGER | NUMERIC(38) | Supported |
| INT | NUMERIC(38) | Supported |
| SMALLINT | NUMERIC(38) | Supported |
| DOUBLE PRECISION | DOUBLE PRECISION | Supported |
| REAL | DOUBLE PRECISION | Supported |
Reference: Data type mapping from Oracle to YugabyteDB
Conclusion
HexaRocket not only simplifies database migrations to PostgreSQL, but also to PostgreSQL Compatible databases. Be it PostgreSQL or YugabyteDB, you can simply migrate your Oracle, and other databases to YugabyteDB seamlessly.
In our next article, we will talk about our observations on data migration and CDC from Oracle to Yugabyte. And then, we shall present you with some interesting facts about SQL Server, MySQL and MariaDB to YugabyteDB. This is because, HexaRocket supports end-to-end database migrations from SQL Server, MySQL and MariaDB to PostgreSQL.
Explore our services at Database migrations to PostgreSQL.
The authors of this article are also the creators of HexaRocket schema migration engine that simplifies schema migration from any database to any database. Your inputs and feedback is greatly valuable and it directly reaches the authors.
Contact Us Today!
Anil is a Senior developer at HexaCluster. Anil worked on many critical development projects and gained huge accolades from his Customers each time. He is a full-stack developer with expertise in both front-end and backend technology stack. With strong expertise in database design, design patterns, Java, Golang, React and many other popular technologies, Anil has done great contributions to HexaRocket to simplify database migrations between multiple databases.
Babasai is a Senior Developer at HexaCluster with strong background in Backend and Front-end technology stack. Babasai has strong expertise in various technologies like Java, Golang, C, C++, and Databases like PostgreSQL, Oracle, SQL Server, MySQL and MariaDB. Babasai’s expertise has helped multiple customers modernize their Technology stack from legacy to an advanced Open-Source technology stack. Additionally, the expertise of Babasai has helped the customers of HexaRocket seamlessly migrate their schema between any databases.
Sai is an expert developer at HexaCluster with highest level of expertise in both Backend and Frontend technology stack. His ability to research and implementation at scale has not only helped customers transform their applications but also implement latest advanced technologies meeting enterprise standards. His ability to research and understanding of database internals, has enabled HexaRocket to achieve seamless schema migration capabilities between multiple databases. His expertise includes Java, Golang, C, C++, React, JavaScript, with deep understand of Oracle, PostgreSQL, SQL Server, MySQL, MariaDB and BigQuery internals.