Useful AWS DMS Postgres & Oracle SQL Queries

 When working with AWS Database Migration Service (DMS) and migrating between PostgreSQL and Oracle, there are key SQL queries you’ll want to use for monitoring, validating, and troubleshooting. Below is a categorized list of useful SQL queries for both PostgreSQL and Oracle, especially tailored to DMS contexts.

---

🔍 General Categories

1. Schema Comparison

2. Row Count Validation

3. Data Type Check

4. Change Data Capture (CDC) Monitoring

5. Latency and Lag Analysis

6. DMS-specific Tables & Metadata

7. Performance Troubleshooting

---

📘 PostgreSQL Useful Queries

1. List All Tables

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema');

2. Row Count per Table

SELECT schemaname, relname AS table_name, n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

3. Check Table Size

SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

4. Check Replication/CDC Lag

If logical replication is enabled:

SELECT pid, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

5. Get Indexes for a Table

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'your_table';

---

📕 Oracle Useful Queries

1. List All User Tables

SELECT owner, table_name
FROM all_tables
WHERE owner NOT IN ('SYS', 'SYSTEM');

2. Row Count per Table

SELECT owner, table_name, num_rows
FROM all_tables
WHERE owner NOT IN ('SYS', 'SYSTEM');

num_rows is based on stats — use ANALYZE TABLE or DBMS_STATS.GATHER_TABLE_STATS for current counts.

3. Actual Row Count

SELECT COUNT(*) FROM schema_name.table_name;

4. Table Size in MB

SELECT segment_name, segment_type, bytes / (1024*1024) AS size_mb
FROM dba_segments
WHERE segment_type='TABLE' AND owner='YOUR_SCHEMA';

5. Indexes for a Table

SELECT index_name, column_name
FROM all_ind_columns
WHERE table_name = 'YOUR_TABLE';

---

🛠️ DMS Metadata Tables (Query from Target Database)

If DMS is replicating these:

SELECT * FROM awsdms_apply_exceptions;
SELECT * FROM awsdms_heartbeat;

Use these to detect:

• Apply lag

• Last replicated change

• Failed rows or skipped rows

---

✅ Validation Queries

Compare Row Counts (PostgreSQL vs Oracle)

On PostgreSQL:

SELECT COUNT(*) FROM public.my_table;

On Oracle:

SELECT COUNT(*) FROM my_schema.my_table;

You can automate validation using EXCEPT logic or checksum approaches.

---

📈 Monitoring Query Example (DMS Task Monitoring)

Use AWS DMS CloudWatch Logs and ReplicationInstanceMetrics to monitor:

• CDCLatencySource

• CDCLatencyTarget

• FullLoadThroughputRowsSource

• FullLoadThroughputRowsTarget