Validating database objects after migration from Oracle to PostreSQL

Managing databases to run at scale with high availability and reliability is difficult, time consuming and expensive. This is why, many companies are moving their databases to cloud to explore fully managed, high performance, and cost effective databases. Besides that, there exists a lot of reasons why organizations are showing interest to migrate their databases.

Data migration projects are inherently complex and brings a lot of challenges. There are many reasons why data migration projects run into trouble, one of which could be validating schema objects. Defining an effective and efficient testing strategy is critical to a project’s overall success.

This article focuses on, how you can validate your database schema objects after database migration. Following are the Oracle database schema objects those requires proper validation in order to ensure your migration is successful.

AWS has rich set of tools and documentation that makes database migration process easier, faster and effective. One of the AWS blog covers many scenarios of validating schema objects between Oracle(source) and PostgreSQL (target) after Oracle database schema objects are converted into PostgreSQL object types and migrated.

  1. Tables counts
  2. Views counts
  3. Materialized Views count
  4. Table/view columns count per table/view
  5. Table/view columns order and default values
  6. Materialized Views columns and structure
  7. Sequences count
  8. Indexes
  9. Primary Keys/Unique Keys
  10. Check constraints
  11. Foreign Keys
  12. Triggers
  13. Packages
  14. Procedures
  15. Functions
  16. User defined data types
  17. SYNONYM – Ignored or achieved through SET search_path

1. Validate tables counts

Covered in AWS Blog

2. Validate views counts

Covered in AWS Blog

3. Validate columns count per table

Oracle

SELECT cols.owner schema_name, cols.table_name object_name, obj.object_type, count(cols.column_name) cols_count
FROM dba_tab_columns cols, dba_objects obj
WHERE cols.owner = obj.owner
AND cols.table_name = obj.object_name
AND cols.owner = upper('your_schema')
GROUP BY obj.object_type, cols.owner, cols.table_name
ORDER BY obj.object_type, cols.owner, cols.table_name;

PostgreSQL

SELECT upper(s.nspname) schema_name, upper(t.relname) object_name, 
case t.relkind
when 'r' then 'TABLE'
when 'v' then 'VIEW'
when 'm' then 'MATERIALIZED VIEW'
end object_type,
count(a.attname) cols_count
FROM pg_attribute a
INNER JOIN pg_class t on a.attrelid = t.oid
INNER JOIN pg_namespace s on t.relnamespace = s.oid
WHERE a.attnum > 0
AND NOT a.attisdropped
AND s.nspname = lower('your_schema')
AND t.relkind in('r', 'v', 'm')
GROUP BY s.nspname, t.relkind, t.relname
ORDER BY s.nspname, t.relkind, t.relname;

3. Validate columns count per table/view/materialized view

Oracle
PostgreSQL
Oracle

SELECT cols.owner schema_name, cols.table_name object_name, obj.object_type, count(cols.column_name) cols_count
FROM dba_tab_columns cols, dba_objects obj
WHERE cols.owner = obj.owner
AND cols.table_name = obj.object_name
AND cols.owner = upper('your_schema')
GROUP BY obj.object_type, cols.owner, cols.table_name
ORDER BY obj.object_type, cols.owner, cols.table_name;

PostgreSQL

SELECT upper(s.nspname) schema_name, upper(t.relname) object_name, 
case t.relkind
when 'r' then 'TABLE'
when 'v' then 'VIEW'
when 'm' then 'MATERIALIZED VIEW'
end object_type,
count(a.attname) cols_count
FROM pg_attribute a
INNER JOIN pg_class t on a.attrelid = t.oid
INNER JOIN pg_namespace s on t.relnamespace = s.oid
WHERE a.attnum > 0
AND NOT a.attisdropped
AND s.nspname = lower('your_schema')
AND t.relkind in('r', 'v', 'm')
GROUP BY s.nspname, t.relkind, t.relname
ORDER BY s.nspname, t.relkind, t.relname;

4. Validate table columns order and default values

Oracle
PostgreSQL
Oracle

SELECT owner table_schema, table_name, column_name, column_id col_position, data_default column_default
FROM dba_tab_columns
WHERE owner = upper('your_schema')
ORDER BY owner, table_name, column_id;

PostgreSQL

SELECT upper(table_schema) table_schema, upper(table_name) table_name, upper(column_name) column_name, ordinal_position col_position, upper(column_default) column_default
FROM information_schema.columns
WHERE table_schema = lower('your_schema')
ORDER BY table_schema, table_name, ordinal_position;

developer

I'm a committed code lover
Subscribe
Notify of
2 Comments
newest
oldest most voted
Inline Feedbacks
View all comments
chris Oxley
9 months ago

Where is says “Covered in AWS Blog”, can you please send me the details of this blog?