Validating database objects after migration using AWS SCT and AWS DMS

AWS provides several tools and services that provide a pre-migration checklist and migration assessments. 

You can use the AWS Schema Conversion Tool (AWS SCT) to convert your existing database schema from one database engine to another. 

AWS Database Migration Service (AWS DMS) makes it easy to migrate relational databases, data warehouses, NoSQL databases, and other types of data stores. 

ORACLE	MS SQL SERVER	PostgreSQL
Package		Package
SELECT object_name AS package_name  FROM all_objects WHERE object_type = 'PACKAGE' AND owner = upper('your_schema') ORDER BY upper(object_name);	dsdssdsdsddddsdsdssddddds	SELECT upper(schema_name) AS package_name FROM information_schema.schemata WHERE schema_name not in ('pg_catalog','information_schema', lower('your_schema')) ORDER BY upper(schema_name);
Validate Table		Validate Table
SELECT count(1) AS tables_cnt FROM all_tables WHERE owner = upper('your_schema');		SELECT count(1) AS tables_cnt FROM pg_tables WHERE schemaname = lower('your_schema');
Validate View		Validate View
SELECT count(1) AS views_cnt FROM all_views WHERE owner = upper('your_schema');		SELECT count(1) AS views_cnt FROM pg_views WHERE schemaname = lower('your_schema');
Validate sequences		Validate sequences
SELECT count(1) AS sequence_cnt FROM all_sequences WHERE sequence_owner = upper('your_schema');		SELECT count(1) AS sequence_cnt FROM information_schema.sequences WHERE sequence_schema = lower('your_schema');
Validate triggers		Validate triggers
SELECT owner AS schema_name, trigger_name, table_name, triggering_event, trigger_type FROM ALL_TRIGGERS WHERE owner = upper('your_schema') ORDER BY trigger_name;		SELECT upper(trigger_schema) AS schema_name, upper(trigger_name) AS trigger_name, upper(event_object_table) AS table_name, string_agg(upper(event_manipulation), ' OR ' ORDER BY CASE WHEN event_manipulation = 'INSERT' THEN 1 WHEN event_manipulation = 'UPDATE' THEN 2 ELSE 3 END) AS triggering_event, upper(action_timing) || ' ' || CASE WHEN action_orientation = 'ROW' THEN 'EACH ROW' ELSE action_orientation END AS trigger_type FROM information_schema.triggers WHERE trigger_schema = lower('your_schema') GROUP BY trigger_schema, trigger_name, event_object_table, action_timing, action_orientation ORDER BY upper(trigger_name);
Validate primary keys		Validate primary keys
SELECT owner AS schema_name, table_name, constraint_name AS object_name, 'PRIMARY KEY' AS object_type FROM all_constraints WHERE owner = upper('your_schema') AND constraint_type = 'P';		SELECT upper(n.nspname) AS schema_name, trim(upper(conrelid::regclass::varchar), '"') AS table_name, upper(conname::varchar) AS object_name, 'PRIMARY KEY' AS object_type FROM pg_constraint c JOIN pg_namespace n ON n.oid = c.connamespace WHERE contype in ('p') AND n.nspname = lower('your_schema');
Validate indexes
WITH cols AS ( SELECT idx.owner AS schema_name, idx.table_name, idx.index_name, cols.column_name, cols.column_position, idx.uniqueness, decode(cols.descend, 'ASC', '', ' '||cols.descend) descend FROM ALL_INDEXES idx, ALL_IND_COLUMNS cols WHERE idx.owner = cols.index_owner AND idx.table_name = cols.table_name AND idx.index_name = cols.index_name AND idx.owner = upper('your_schema') ), expr AS ( SELECT extractValue(xs.object_value, '/ROW/TABLE_NAME')AS table_name , extractValue(xs.object_value, '/ROW/INDEX_NAME') AS index_name , extractValue(xs.object_value, '/ROW/COLUMN_EXPRESSION') AS column_expression , extractValue(xs.object_value, '/ROW/COLUMN_POSITION') AS column_position FROM ( SELECT XMLTYPE( DBMS_XMLGEN.GETXML ('SELECT table_name, index_name, column_expression, column_position FROM ALL_IND_EXPRESSIONS WHERE index_owner = upper(''your_schema'') ' ) ) AS xml FROM DUAL ) x , TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs ) SELECT cols.schema_name, cols.table_name, cols.index_name AS object_name, 'INDEX' AS object_type, 'CREATE'|| decode(cols.uniqueness, 'UNIQUE', ' '||cols.uniqueness) || ' INDEX ' || cols.index_name || ' ON VESTEK.' || cols.table_name || ' USING BTREE (' || listagg(CASE WHEN cols.column_name LIKE 'SYS_N%' THEN expr.column_expression || cols.descend ELSE cols.column_name || cols.descend END, ', ') within group (order by cols.column_position) || ')' AS condition_column FROM cols LEFT OUTER JOIN expr ON cols.table_name = expr.table_name AND cols.index_name = expr.index_name AND cols.column_position = expr.column_position GROUP BY cols.schema_name, cols.table_name, cols.index_name, cols.uniqueness;