Oracle vs PostgreSQL

Identifies high-water mark utilization across Oracle tablespaces and monitors active undo segment stealing during heavy OLTP transaction loads.

# 1. Audit Oracle tablespace utilization & autoextend headroom
sqlplus -s dbre_monitor/secret@oraprod as sysdba << 'EOF'
SET PAGESIZE 50 LINESIZE 120 FEEDBACK OFF;
SELECT 
  tablespace_name, 
  ROUND(used_space * 8192 / 1048576, 2) AS used_mb, 
  ROUND(tablespace_size * 8192 / 1048576, 2) AS total_mb, 
  ROUND(used_percent, 2) AS pct_used 
FROM dba_tablespace_usage_metrics 
WHERE used_percent > 80 
ORDER BY used_percent DESC;
EOF

# 2. Check active undo retention and transaction rollback contention
sqlplus -s dbre_monitor/secret@oraprod as sysdba << 'EOF'
SET PAGESIZE 50 LINESIZE 120 FEEDBACK OFF;
SELECT 
  begin_time, 
  undoblks, 
  txncount, 
  maxquerylen, 
  unxpstealcnt 
FROM v$undostat 
WHERE begin_time > SYSDATE - (1/24) 
ORDER BY begin_time DESC FETCH FIRST 5 ROWS ONLY;
EOF

Monitors database transaction ID age proximity to prevent autovacuum freeze lockouts and tracks replication slot WAL consumption.

# 1. Inspect transaction ID wraparound age across all databases
psql -h pg-primary -U dbre_admin -d postgres -c "
SELECT 
  datname, 
  age(datfrozenxid) AS xid_age, 
  2147483648 - age(datfrozenxid) AS xid_remaining, 
  ROUND(100.0 * age(datfrozenxid) / 2147483648, 2) AS pct_towards_freeze 
FROM pg_database 
ORDER BY xid_age DESC;"

# 2. Audit replication slot lag and WAL retention backlog
psql -h pg-primary -U dbre_admin -d postgres -c "
SELECT 
  slot_name, 
  plugin, 
  active, 
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_bytes 
FROM pg_replication_slots 
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;"