https://aws.amazon.com/cn/blogs/database/validating-database-objects-after-migration-using-aws-sct-and-aws-dms/
Step 1 - Validate packages
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 1 - Oracle source database:
SELECT object_name AS package_name
FROM all_objects
WHERE object_type = 'PACKAGE'
AND owner = upper('your_schema')
ORDER BY upper(object_name);
QUERY 2 - Amazon Aurora PostgreSQL:
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);
Step 2 - Validate tables
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 3 - Oracle:
SELECT count(1) AS tables_cnt
FROM all_tables
WHERE owner = upper('your_schema');
QUERY 4 - Amazon Aurora PostgreSQL:
SELECT count(1) AS tables_cnt
FROM pg_tables
WHERE schemaname = lower('your_schema');
Step 3 - Validate views:
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 5 - Oracle:
SELECT count(1) AS views_cnt
FROM all_views
WHERE owner = upper('your_schema');
QUERY 6 - Amazon Aurora PostgreSQL:
SELECT count(1) AS views_cnt
FROM pg_views
WHERE schemaname = lower('your_schema');
Step 4 - Validate sequences:
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 7 - Oracle:
SELECT count(1) AS sequence_cnt
FROM all_sequences
WHERE sequence_owner = upper('your_schema');
QUERY 8 - Amazon Aurora PostgreSQL:
SELECT count(1) AS sequence_cnt
FROM information_schema.sequences
WHERE sequence_schema = lower('your_schema');
Step 5 - Validate triggers:
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 9 - Oracle:
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;
QUERY 10 - Amazon Aurora PostgreSQL:
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);
Step 6 - Validate primary keys:
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 11 - Oracle:
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';
QUERY 12 - Amazon Aurora PostgreSQL:
SELECT upper(n.nspname) AS schema_name,
trim(upper(split_part(conrelid::regclass::varchar, '.', 2)), '"') 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')
Step 7 - Validate indexes:
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 13 - Oracle:
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'') '
||' union all SELECT null, null, null, null FROM dual '
)
) 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,
replace('CREATE'|| decode(cols.uniqueness, 'UNIQUE', ' '||cols.uniqueness) || ' INDEX ' || cols.index_name || ' ON your_schema.' || 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;
QUERY 14 - Amazon Aurora PostgreSQL:
SELECT upper(schemaname) AS schema_name,
upper(tablename) AS table_name,
upper(indexname) AS object_name,
'INDEX' AS object_type,
upper(replace(indexdef, '"', '')) AS condition_column
FROM pg_indexes
WHERE schemaname = lower('your_schema');
Step 8 - Validate check contraints:
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 15 - Oracle:
WITH ref AS (
SELECT extractValue(xs.object_value, '/ROW/OWNER') AS schema_name
, extractValue(xs.object_value, '/ROW/TABLE_NAME') AS table_name
, extractValue(xs.object_value, '/ROW/CONSTRAINT_NAME') AS object_name
, extractValue(xs.object_value, '/ROW/SEARCH_CONDITION') AS condition_column
, extractValue(xs.object_value, '/ROW/COLUMN_NAME') AS column_name
FROM (
SELECT XMLTYPE(
DBMS_XMLGEN.GETXML('SELECT cons.owner, cons.table_name, cons.constraint_name, cons.search_condition, cols.column_name
FROM ALL_CONSTRAINTS cons, ALL_CONS_COLUMNS cols
WHERE cons.owner = cols.owner AND cons.table_name = cols.table_name AND cons.constraint_name = cols.constraint_name
AND cons.owner = upper(''your_schema'') AND cons.constraint_type = ''C'' '
)
) AS xml FROM DUAL
) x
, TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs
)
SELECT schema_name||'.'||table_name AS table_name,
object_name,
constraint_type,
trim(upper(replace(check_condition, '"', ''))) AS check_condition
FROM (
SELECT
schema_name,
table_name,
object_name,
'CHECK' AS constraint_type,
condition_column AS check_condition
FROM ref
UNION
SELECT
owner AS schema_name,
table_name,
'SYS_C0000'||column_id AS object_name,
'CHECK' AS constraint_type,
'"'||column_name||'" IS NOT NULL' AS check_condition
FROM all_tab_columns tcols where owner = upper('your_schema') and nullable = 'N'
AND NOT EXISTS ( SELECT 1 FROM ref WHERE ref.table_name = tcols.table_name
AND ref.schema_name = tcols.owner
AND ref.column_name = tcols.column_name
AND ref.condition_column = '"'||tcols.column_name||'" IS NOT NULL')
/* ALL_TAB_COLUMNS contains Tables and Views. Add below to exclude Views NOT NULL constraints */
AND NOT EXISTS ( SELECT 1 FROM ALL_VIEWS vw WHERE vw.view_name = tcols.table_name
AND vw.owner = tcols.owner
)
);
QUERY 16 - Amazon Aurora PostgreSQL:
SELECT
table_name,
object_name,
constraint_type,
upper(trim(replace(replace(replace(replace(check_condition, '"', ''), '(', ''), ')', ''), 'CHECK', ''))) check_condition
FROM (
SELECT
upper(n.nspname)||'.'||upper(c.relname) AS table_name,
'SYS_C0000'||attnum AS object_name,
'CHECK' AS constraint_type,
a.attname||' IS NOT NULL' AS check_condition
FROM pg_attribute a, pg_class c, pg_namespace n
WHERE a.attrelid = c.oid and c.relnamespace = n.oid AND n.nspname = lower('your_schema')
AND attnotnull AND attstattarget <> 0
UNION
SELECT
upper(conrelid::regclass::varchar) AS table_name,
upper(conname::varchar) AS object_name,
'CHECK' AS constraint_type,
pg_get_constraintdef(c.oid) AS check_condition
FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
WHERE contype in ('c')
AND conrelid::regclass::varchar <> '-'
AND n.nspname = lower('your_schema')
) a;
Step 9 - Validate foreign keys:
Run the following queries on Oracle and Amazon Aurora PostgreSQL databases, and compare your results:
QUERY 17 - Oracle:
SELECT
c.child_tab_owner AS schema_name,
c.table_name,
c.constraint_name AS object_name,
'FOREIGN KEY' AS object_type,
'FOREIGN KEY ('|| cc.fk_column || ') REFERENCES ' || p.parent_tab_owner || '.' || p.table_name || '('|| pc.ref_column ||') NOT VALID' AS condition_column
FROM ( SELECT owner child_tab_owner, table_name, constraint_name, r_constraint_name FROM ALL_CONSTRAINTS WHERE owner = upper('your_schema') AND constraint_type = 'R') c,
( SELECT owner parent_tab_owner, table_name, constraint_name FROM ALL_CONSTRAINTS WHERE owner = upper('your_schema') AND constraint_type IN('P', 'U') ) p,
( SELECT owner, table_name, constraint_name, listagg(column_name, ', ') WITHIN group(ORDER BY position) fk_column
FROM ALL_CONS_COLUMNS WHERE owner = upper('your_schema') GROUP BY owner, table_name, constraint_name ) cc,
( SELECT owner, table_name, constraint_name, listagg(column_name, ', ') WITHIN group(ORDER BY position) ref_column
FROM ALL_CONS_COLUMNS WHERE owner = upper('your_schema') GROUP BY owner, table_name, constraint_name ) pc
WHERE c.r_constraint_name = p.constraint_name
AND c.table_name = cc.table_name AND c.constraint_name = cc.constraint_name AND c.child_tab_owner = cc.owner
AND p.table_name = pc.table_name AND p.constraint_name = pc.constraint_name AND p.parent_tab_owner = pc.owner;
QUERY 18 - Amazon Aurora PostgreSQL:
SELECT upper(n.nspname::text) AS schema_name,
trim(upper(split_part(conrelid::regclass::varchar, '.', 2)), '"') AS table_name, upper(conname::varchar) AS object_name,
'FOREIGN KEY' AS object_type,
CASE contype WHEN 'f' THEN upper(pg_get_constraintdef(c.oid)) END AS condition_column
FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
WHERE contype in ('f')
AND n.nspname::text = lower('your_schema');
``
