#!/usr/bin/env bash
# dp_migrate.sh - Oracle 19c Data Pump migration, DDL extractor, compare (shallow+deep), and report mailer
# Requirements: bash 4+, sqlplus, expdp, impdp in PATH. Run on a host with Oracle client.
set -euo pipefail
#------------------------ Bootstrap & Paths ------------------------------------
CONFIG_FILE="${1:-dp_migrate.conf}" # pass a different conf path as first arg
SCRIPT_NAME="$(basename "$0")"
RUN_ID="$(date +%Y%m%d_%H%M%S)"
WORK_DIR="${WORK_DIR:-/tmp/dp_migrate_${RUN_ID}}"
LOG_DIR="${LOG_DIR:-${WORK_DIR}/logs}"
PAR_DIR="${PAR_DIR:-${WORK_DIR}/parfiles}"
DDL_DIR="${DDL_DIR:-${WORK_DIR}/ddls}"
COMMON_DIR_NAME="${COMMON_DIR_NAME:-DP_DIR}" # DB DIRECTORY object name on both src/tgt
mkdir -p "$WORK_DIR" "$LOG_DIR" "$PAR_DIR" "$DDL_DIR"
#------------------------ Pretty print helpers --------------------------------
ce() { printf "%b\n" "$*"; }
ok() { ce "\e[32m✔ $*\e[0m"; }
warn() { ce "\e[33m! $*\e[0m"; }
err() { ce "\e[31m✘ $*\e[0m"; }
#------------------------ Load Config -----------------------------------------
[[ -f "$CONFIG_FILE" ]] || { err "Config file not found: $CONFIG_FILE"; exit 1; }
# shellcheck disable=SC1090
source "$CONFIG_FILE"
# Required variables
need_vars=( SRC_EZCONNECT TGT_EZCONNECT SYS_PASSWORD NAS_PATH DUMPFILE_PREFIX )
for v in "${need_vars[@]}"; do
[[ -n "${!v:-}" ]] || { err "Missing required config variable: $v"; exit 1; }
done
#------------------------ Defaults / Tunables ---------------------------------
PARALLEL="${PARALLEL:-4}"
COMPRESSION="${COMPRESSION:-METADATA_ONLY}" # ALL|DATA_ONLY|METADATA_ONLY|NONE
ENCRYPTION_PASSWORD="${ENCRYPTION_PASSWORD:-}"# optional dump encryption password
TABLE_EXISTS_ACTION="${TABLE_EXISTS_ACTION:-APPEND}" # impdp behavior
REMAP_SCHEMA="${REMAP_SCHEMA:-}"
REMAP_TABLESPACE="${REMAP_TABLESPACE:-}"
INCLUDE="${INCLUDE:-}"
EXCLUDE="${EXCLUDE:-}"
FLASHBACK_SCN="${FLASHBACK_SCN:-}"
FLASHBACK_TIME="${FLASHBACK_TIME:-}"
ESTIMATE_ONLY="${ESTIMATE_ONLY:-N}"
# Optional schema lists for user/conf option
SCHEMAS_LIST_EXP="${SCHEMAS_LIST_EXP:-}" # e.g., "HR,SCOTT"
SCHEMAS_LIST_IMP="${SCHEMAS_LIST_IMP:-}" # if empty, falls back to SCHEMAS_LIST_EXP
# Auto-discovery skip list (names or LIKE patterns; case-insensitive)
# Example: SKIP_SCHEMAS="APEX_%,ORDS%,SYS%,XDB,APP_%,%_STG"
SKIP_SCHEMAS="${SKIP_SCHEMAS:-}"
# For the “All DDLs” sweep (adjust as needed)
DDL_OBJECT_TYPES="${DDL_OBJECT_TYPES:-TABLE,INDEX,VIEW,SEQUENCE,TRIGGER,FUNCTION,PROCEDURE,PACKAGE,PACKAGE_BODY,MATERIALIZED_VIEW,TYPE,SYNONYM}"
# Tablespaces to skip in tablespace DDL export
SKIP_TABLESPACES="${SKIP_TABLESPACES:-SYSTEM,SYSAUX,TEMP,UNDOTBS1,UNDOTBS2}"
# Global dry-run switch for destructive IMPORT cleanup actions
DRY_RUN_ONLY="${DRY_RUN_ONLY:-N}" # Y=show preview reports only, do NOT execute
# ---------- Email / report knobs ----------
REPORT_EMAILS="${REPORT_EMAILS:-}" # e.g., "dba1@ex.com,dba2@ex.com"
MAIL_ENABLED="${MAIL_ENABLED:-Y}" # Y/N — if N, never send email
MAIL_FROM="${MAIL_FROM:-noreply@localhost}" # visible From:
MAIL_SUBJECT_PREFIX="${MAIL_SUBJECT_PREFIX:-[Oracle Compare]}"
# ---------- Compare (Shallow) knobs ----------
COMPARE_EXCLUDE_TYPES="${COMPARE_EXCLUDE_TYPES:-}"
COMPARE_INCLUDE_TYPES="${COMPARE_INCLUDE_TYPES:-}" # leave empty to include all
# ---------- Compare (Deep) knobs ----------
COMPARE_DEEP_INCLUDE_TYPES="${COMPARE_DEEP_INCLUDE_TYPES:-TABLE,INDEX,VIEW,SEQUENCE,TRIGGER,FUNCTION,PROCEDURE,PACKAGE,PACKAGE BODY,MATERIALIZED VIEW,SYNONYM,TYPE}"
COMPARE_DEEP_EXCLUDE_TYPES="${COMPARE_DEEP_EXCLUDE_TYPES:-}"
EXACT_ROWCOUNT="${EXACT_ROWCOUNT:-N}" # Y runs SELECT COUNT(*), N uses stats
# Comparison output directory
COMPARE_DIR="${COMPARE_DIR:-${WORK_DIR}/compare}"
mkdir -p "$COMPARE_DIR"
#------------------------ Pre-flight checks -----------------------------------
for b in sqlplus expdp impdp; do
command -v "$b" >/dev/null 2>&1 || { err "Missing required binary: $b"; exit 1; }
done
[[ -d "$NAS_PATH" ]] || { err "NAS mount path not found on this host: $NAS_PATH"; exit 1; }
ok "Using config: $CONFIG_FILE"
ok "Work: $WORK_DIR | Logs: $LOG_DIR | Parfiles: $PAR_DIR | DDLs: $DDL_DIR | Compare: $COMPARE_DIR"
#------------------------ Utility helpers -------------------------------------
mask_pwd() { sed 's#[^/"]\{1,\}@#***@#g' | sed 's#sys/[^@]*@#sys/****@#g'; }
run_sql() {
# $1=ezconnect, $2=tag, $3+=SQL
local ez="$1"; shift
local tag="${1:-sql}"; shift || true
local sql="$*"
local conn="sys/${SYS_PASSWORD}@${ez} as sysdba"
local logf="${LOG_DIR}/${tag}_${RUN_ID}.log"
sqlplus -s "$conn" <
"$logf" 2>&1
SET PAGES 0 FEEDBACK OFF LINES 32767 VERIFY OFF HEADING OFF ECHO OFF
${sql}
EXIT
SQL
if grep -qi "ORA-" "$logf"; then
err "SQL error: ${tag} (see $logf)"
tail -n 120 "$logf" | mask_pwd | sed 's/^/ /'
exit 1
fi
ok "SQL ok: ${tag}"
}
print_log() {
local tag="$1"
local logf="${LOG_DIR}/${tag}_${RUN_ID}.log"
[[ -f "$logf" ]] && { echo "----- ${tag} (dry-run) -----"; cat "$logf"; echo "----- end (${tag}) -----"; } || true
}
ensure_directory_object() {
local ez="$1" host_tag="$2" dir_name="${3:-$COMMON_DIR_NAME}" dir_path="$NAS_PATH"
run_sql "$ez" "create_dir_${host_tag}" "
DECLARE n INTEGER;
BEGIN
SELECT COUNT(*) INTO n FROM all_directories WHERE directory_name = '${dir_name}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ${dir_name} AS ''${dir_path}''';
BEGIN EXECUTE IMMEDIATE 'GRANT READ,WRITE ON DIRECTORY ${dir_name} TO PUBLIC'; EXCEPTION WHEN OTHERS THEN NULL; END;
END;
/
"
}
par_common() {
local mode="$1" tag="$2"
local pf="${PAR_DIR}/${tag}_${RUN_ID}.par"
{
echo "directory=${COMMON_DIR_NAME}"
echo "logfile=${DUMPFILE_PREFIX}_${tag}_${RUN_ID}.log"
echo "parallel=${PARALLEL}"
} > "$pf"
if [[ "$mode" == "expdp" ]]; then
echo "dumpfile=${DUMPFILE_PREFIX}_${tag}_${RUN_ID}_%U.dmp" >> "$pf"
echo "compression=${COMPRESSION}" >> "$pf"
[[ -n "$FLASHBACK_SCN" ]] && echo "flashback_scn=${FLASHBACK_SCN}" >> "$pf"
[[ -n "$FLASHBACK_TIME" ]] && echo "flashback_time=${FLASHBACK_TIME}" >> "$pf"
[[ -n "$INCLUDE" ]] && echo "include=${INCLUDE}" >> "$pf"
[[ -n "$EXCLUDE" ]] && echo "exclude=${EXCLUDE}" >> "$pf"
[[ "${ESTIMATE_ONLY^^}" == "Y" ]] && echo "estimate_only=Y" >> "$pf"
[[ -n "$ENCRYPTION_PASSWORD" ]] && { echo "encryption=encrypt_password"; echo "encryption_password=${ENCRYPTION_PASSWORD}"; } >> "$pf"
else
echo "table_exists_action=${TABLE_EXISTS_ACTION}" >> "$pf"
[[ -n "$REMAP_SCHEMA" ]] && echo "remap_schema=${REMAP_SCHEMA}" >> "$pf"
[[ -n "$REMAP_TABLESPACE" ]] && echo "remap_tablespace=${REMAP_TABLESPACE}" >> "$pf"
[[ -n "$INCLUDE" ]] && echo "include=${INCLUDE}" >> "$pf"
[[ -n "$EXCLUDE" ]] && echo "exclude=${EXCLUDE}" >> "$pf"
[[ -n "$ENCRYPTION_PASSWORD" ]] && echo "encryption_password=${ENCRYPTION_PASSWORD}" >> "$pf"
fi
echo "$pf"
}
#------------------------ Email & HTML helpers ---------------------------------
mail_send_html() {
# $1=subject $2=html_file $3=comma_sep_recipients
local subj="$1" html="$2" recipients_csv="$3"
[[ "${MAIL_ENABLED^^}" == "Y" && -n "$recipients_csv" && -s "$html" ]] || {
warn "Mail disabled or no recipients / html file missing. Saved: $html"
return 0
}
local to_csv="${recipients_csv// /}" to_space
IFS=',' read -r -a _arr <<< "$to_csv"
to_space="${_arr[*]}"
if command -v /usr/sbin/sendmail >/dev/null 2>&1; then
{
printf "From: %s\n" "$MAIL_FROM"
printf "To: %s\n" "$to_csv"
printf "Subject: %s\n" "$subj"
printf "MIME-Version: 1.0\n"
printf "Content-Type: text/html; charset=UTF-8\n"
printf "Content-Transfer-Encoding: 8bit\n\n"
cat "$html"
} | /usr/sbin/sendmail -t
ok "Mailed HTML report to: $to_csv"
elif command -v mailx >/dev/null 2>&1; then
if mailx -V 2>/dev/null | grep -qi heirloom\|s-nail\|mailutils; then
mailx -a "Content-Type: text/html; charset=UTF-8" -r "$MAIL_FROM" -s "$subj" $to_space < "$html"
else
mailx -s "$subj" $to_space < "$html"
fi
ok "Mailed HTML report to: $to_csv (via mailx)"
else
warn "No sendmail/mailx found. Report saved at: $html"
fi
}
html_begin() {
cat <
${1}
EOF
}
html_end(){ echo ""; }
#------------------------ Schema discovery & confirmation ----------------------
schemas_skip_predicate() {
local out="" item trimmed
IFS=',' read -r -a arr <<< "${SKIP_SCHEMAS}"
for item in "${arr[@]}"; do
trimmed="$(echo "$item" | awk '{$1=$1;print}')"
[[ -z "$trimmed" ]] && continue
trimmed="${trimmed//\'/\'\'}"
out+=" AND UPPER(username) NOT LIKE UPPER('${trimmed}')"${'\n'}
done
printf "%s" "$out"
}
get_nonmaintained_schemas() {
local tmp_pred; tmp_pred="$(schemas_skip_predicate)"
run_sql "$SRC_EZCONNECT" "list_nonmaint_users" "
SET PAGES 0 FEEDBACK OFF HEADING OFF
WITH base AS (
SELECT username
FROM dba_users
WHERE oracle_maintained='N'
$(printf "%s" "${tmp_pred}")
)
SELECT LISTAGG(username, ',') WITHIN GROUP (ORDER BY username)
FROM base;
/
"
awk 'NF{line=$0} END{print line}' "${LOG_DIR}/list_nonmaint_users_${RUN_ID}.log"
}
get_nonmaintained_schemas_tgt() {
local tmp_pred; tmp_pred="$(schemas_skip_predicate)"
run_sql "$TGT_EZCONNECT" "tgt_nonmaint_users" "
SET PAGES 0 FEEDBACK OFF HEADING OFF
WITH base AS (
SELECT username
FROM dba_users
WHERE oracle_maintained='N'
$(printf "%s" "${tmp_pred}")
)
SELECT LISTAGG(username, ',') WITHIN GROUP (ORDER BY username)
FROM base;
/
"
awk 'NF{line=$0} END{print line}' "${LOG_DIR}/tgt_nonmaint_users_${RUN_ID}.log"
}
confirm_edit_value() {
local label="$1" val="${2:-}" ans
echo "${label}: ${val}"
read -rp "Use this value? (Y to accept, N to edit) [Y/N]: " ans
if [[ "${ans^^}" == "N" ]]; then
read -rp "Enter new ${label}: " val
fi
echo "$val"
}
csv_to_inlist() {
local csv="${1:-}" out="" tok
IFS=',' read -r -a arr <<< "$csv"
for tok in "${arr[@]}"; do
tok="$(echo "$tok" | awk '{$1=$1;print}')"
[[ -z "$tok" ]] && continue
tok="${tok//\'/\'\'}"
out+="${out:+,}''${tok^^}''"
done
echo "$out"
}
#------------------------ Dry-run / Preview reports (TARGET) -------------------
report_user_list() {
local tag="$1" inlist="${2:-}" pred q
if [[ -z "$inlist" ]]; then
pred="$(schemas_skip_predicate)"
q="
WITH base AS (SELECT username FROM dba_users WHERE oracle_maintained='N'
${pred}
)
SELECT username FROM base ORDER BY username;
"
else
q="
SELECT username FROM dba_users
WHERE oracle_maintained='N'
AND UPPER(username) IN (${inlist})
ORDER BY username;
"
fi
run_sql "$TGT_EZCONNECT" "${tag}" "
SET HEADING ON PAGES 200 LINES 200 FEEDBACK ON
COLUMN USERNAME FORMAT A30
PROMPT === USER LIST (TARGET) ===
${q}
PROMPT === TOTAL USERS ===
SELECT COUNT(*) AS cnt FROM (
${q}
);
/
"
print_log "${tag}"
}
report_object_counts() {
local tag="$1" inlist="${2:-}" pred owner_pred
if [[ -z "$inlist" ]]; then
pred="$(schemas_skip_predicate)"
owner_pred="owner IN (SELECT username FROM dba_users WHERE oracle_maintained='N' $(printf "%s" "${pred}"))"
else
owner_pred="UPPER(owner) IN (${inlist})"
fi
run_sql "$TGT_EZCONNECT" "${tag}" "
SET HEADING ON PAGES 200 LINES 200 FEEDBACK ON
COLUMN OWNER FORMAT A30
COLUMN OBJECT_TYPE FORMAT A25
PROMPT === OBJECT COUNTS BY OWNER & TYPE (TARGET) ===
SELECT owner, object_type, COUNT(*) AS cnt
FROM dba_objects
WHERE ${owner_pred}
AND object_name NOT LIKE 'BIN$%'
GROUP BY owner, object_type
ORDER BY owner, object_type;
PROMPT === TOTAL OBJECTS (TARGET) ===
SELECT COUNT(*) AS total_objects
FROM dba_objects
WHERE ${owner_pred}
AND object_name NOT LIKE 'BIN$%';
/
"
print_log "${tag}"
}
#------------------------ EXPDP: Menus & actions -------------------------------
exp_full_menu() {
while true; do
cat <<'EOS'
Export FULL (choose content):
1) metadata_only (CONTENT=METADATA_ONLY)
2) full (CONTENT=ALL)
3) Back
EOS
read -rp "Choose: " c
case "$c" in
1)
ensure_directory_object "$SRC_EZCONNECT" "src"
local pf; pf=$(par_common expdp "exp_full_meta")
{ echo "full=Y"; echo "content=METADATA_ONLY"; } >> "$pf"
ok "Parfile: $pf"; expdp "sys/${SYS_PASSWORD}@${SRC_EZCONNECT} as sysdba" parfile="$pf"
;;
2)
ensure_directory_object "$SRC_EZCONNECT" "src"
local pf; pf=$(par_common expdp "exp_full_all")
{ echo "full=Y"; echo "content=ALL"; } >> "$pf"
ok "Parfile: $pf"; expdp "sys/${SYS_PASSWORD}@${SRC_EZCONNECT} as sysdba" parfile="$pf"
;;
3) break ;;
*) warn "Invalid choice" ;;
esac
done
}
exp_schemas_menu() {
while true; do
cat <<'EOS'
Export SCHEMAS:
1) All accounts (exclude Oracle-maintained; honors SKIP_SCHEMAS)
2) User input or value from conf (SCHEMAS_LIST_EXP) with confirmation
3) Back
EOS
read -rp "Choose: " c
case "$c" in
1)
ensure_directory_object "$SRC_EZCONNECT" "src"
local schemas; schemas="$(get_nonmaintained_schemas)"
schemas="$(confirm_edit_value "Schemas" "$schemas")"
local pf; pf=$(par_common expdp "exp_schemas_auto")
{ echo "schemas=${schemas}"; echo "content=ALL"; } >> "$pf"
ok "Parfile: $pf"; expdp "sys/${SYS_PASSWORD}@${SRC_EZCONNECT} as sysdba" parfile="$pf"
;;
2)
ensure_directory_object "$SRC_EZCONNECT" "src"
local init="${SCHEMAS_LIST_EXP:-}"; [[ -z "$init" ]] && read -rp "Enter schemas (comma-separated): " init
local schemas; schemas="$(confirm_edit_value "Schemas" "$init")"
local pf; pf=$(par_common expdp "exp_schemas_user")
{ echo "schemas=${schemas}"; echo "content=ALL"; } >> "$pf"
ok "Parfile: $pf"; expdp "sys/${SYS_PASSWORD}@${SRC_EZCONNECT} as sysdba" parfile="$pf"
;;
3) break ;;
*) warn "Invalid choice" ;;
esac
done
}
exp_tablespaces() {
ensure_directory_object "$SRC_EZCONNECT" "src"
read -rp "Tablespaces (comma-separated): " tbs
local pf; pf=$(par_common expdp "exp_tbs")
echo "transport_tablespaces=${tbs}" >> "$pf"
ok "Parfile: $pf"; expdp "sys/${SYS_PASSWORD}@${SRC_EZCONNECT} as sysdba" parfile="$pf"
}
exp_tables() {
ensure_directory_object "$SRC_EZCONNECT" "src"
read -rp "Tables (SCHEMA.TAB,SCHEMA2.TAB2,...): " tabs
local pf; pf=$(par_common expdp "exp_tables")
echo "tables=${tabs}" >> "$pf"
ok "Parfile: $pf"; expdp "sys/${SYS_PASSWORD}@${SRC_EZCONNECT} as sysdba" parfile="$pf"
}
#------------------------ IMPDP: Menus & actions -------------------------------
imp_full_menu() {
while true; do
cat <<'EOS'
Import FULL (choose content):
1) metadata_only (CONTENT=METADATA_ONLY)
2) full (CONTENT=ALL)
3) Back
EOS
read -rp "Choose: " c
case "$c" in
1)
ensure_directory_object "$TGT_EZCONNECT" "tgt"
local pf; pf=$(par_common impdp "imp_full_meta")
{ echo "full=Y"; echo "content=METADATA_ONLY"; } >> "$pf"
ok "Parfile: $pf"; impdp "sys/${SYS_PASSWORD}@${TGT_EZCONNECT} as sysdba" parfile="$pf"
;;
2)
ensure_directory_object "$TGT_EZCONNECT" "tgt"
local pf; pf=$(par_common impdp "imp_full_all")
{ echo "full=Y"; echo "content=ALL"; } >> "$pf"
ok "Parfile: $pf"; impdp "sys/${SYS_PASSWORD}@${TGT_EZCONNECT} as sysdba" parfile="$pf"
;;
3) break ;;
*) warn "Invalid choice" ;;
esac
done
}
imp_schemas_menu() {
while true; do
cat <<'EOS'
Import SCHEMAS:
1) All accounts (exclude Oracle-maintained; honors SKIP_SCHEMAS)
2) User input or value from conf (SCHEMAS_LIST_IMP / SCHEMAS_LIST_EXP) with confirmation
3) Back
EOS
read -rp "Choose: " c
case "$c" in
1)
ensure_directory_object "$TGT_EZCONNECT" "tgt"
local schemas; schemas="$(get_nonmaintained_schemas)"
schemas="$(confirm_edit_value "Schemas" "$schemas")"
local pf; pf=$(par_common impdp "imp_schemas_auto")
{ echo "schemas=${schemas}"; echo "content=ALL"; } >> "$pf"
ok "Parfile: $pf"; impdp "sys/${SYS_PASSWORD}@${TGT_EZCONNECT} as sysdba" parfile="$pf"
;;
2)
ensure_directory_object "$TGT_EZCONNECT" "tgt"
local base="${SCHEMAS_LIST_IMP:-${SCHEMAS_LIST_EXP:-}}"
[[ -z "$base" ]] && read -rp "Enter schemas (comma-separated): " base
local schemas; schemas="$(confirm_edit_value "Schemas" "$base")"
local pf; pf=$(par_common impdp "imp_schemas_user")
{ echo "schemas=${schemas}"; echo "content=ALL"; } >> "$pf"
ok "Parfile: $pf"; impdp "sys/${SYS_PASSWORD}@${TGT_EZCONNECT} as sysdba" parfile="$pf"
;;
3) break ;;
*) warn "Invalid choice" ;;
esac
done
}
imp_tablespaces() {
ensure_directory_object "$TGT_EZCONNECT" "tgt"
read -rp "Transported tablespaces (comma-separated): " tbs
local pf; pf=$(par_common impdp "imp_tbs")
echo "transport_tablespaces=${tbs}" >> "$pf"
ok "Parfile: $pf"; impdp "sys/${SYS_PASSWORD}@${TGT_EZCONNECT} as sysdba" parfile="$pf"
}
imp_tables() {
ensure_directory_object "$TGT_EZCONNECT" "tgt"
read -rp "Tables (SCHEMA.TAB,SCHEMA2.TAB2,...): " tabs
local pf; pf=$(par_common impdp "imp_tables")
echo "tables=${tabs}" >> "$pf"
ok "Parfile: $pf"; impdp "sys/${SYS_PASSWORD}@${TGT_EZCONNECT} as sysdba" parfile="$pf"
}
#------------------------ IMPORT CLEANUP (Drop/Reset) --------------------------
drop_users_cascade_all_nonmaint() {
local users_csv; users_csv="$(get_nonmaintained_schemas_tgt)"
local inlist; inlist="$(csv_to_inlist "$users_csv")"
report_user_list "dry_users_all" "$inlist"
report_object_counts "dry_objs_all" "$inlist"
if [[ "${DRY_RUN_ONLY^^}" == "Y" ]]; then
warn "DRY_RUN_ONLY=Y: Skipping DROP USER execution."
return
fi
local confirm
echo "This will DROP ALL above users (CASCADE) on TARGET: ${TGT_EZCONNECT}"
read -rp "Type YES to proceed (anything else cancels): " confirm
[[ "$confirm" == "YES" ]] || { warn "Cancelled."; return; }
run_sql "$TGT_EZCONNECT" "drop_users_all" "
SET SERVEROUTPUT ON
DECLARE v_stmt VARCHAR2(4000);
BEGIN
FOR r IN (SELECT username FROM dba_users WHERE oracle_maintained='N' AND UPPER(username) IN (${inlist}) ORDER BY username) LOOP
v_stmt := 'DROP USER '||r.username||' CASCADE';
BEGIN EXECUTE IMMEDIATE v_stmt; DBMS_OUTPUT.PUT_LINE('DROPPED USER: '||r.username);
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('FAILED: '||v_stmt||' - '||SQLERRM); END;
END LOOP;
END;
/
"
}
drop_objects_all_nonmaint() {
local users_csv; users_csv="$(get_nonmaintained_schemas_tgt)"
local inlist; inlist="$(csv_to_inlist "$users_csv")"
report_user_list "dry_users_obj_all" "$inlist"
report_object_counts "dry_objs_obj_all" "$inlist"
if [[ "${DRY_RUN_ONLY^^}" == "Y" ]]; then
warn "DRY_RUN_ONLY=Y: Skipping DROP OBJECTS execution."
return
fi
local confirm
echo "This will DROP ALL OBJECTS for the above owners on TARGET (users remain)."
read -rp "Type YES to proceed (anything else cancels): " confirm
[[ "$confirm" == "YES" ]] || { warn "Cancelled."; return; }
run_sql "$TGT_EZCONNECT" "drop_objs_all" "
SET SERVEROUTPUT ON
DECLARE
PROCEDURE exec_ddl(p_sql IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE p_sql; EXCEPTION WHEN OTHERS THEN NULL; END;
PROCEDURE drop_for_owner(p_owner IN VARCHAR2) IS
BEGIN
FOR r IN (SELECT object_name FROM dba_objects WHERE owner=p_owner AND object_type='SYNONYM') LOOP exec_ddl('DROP SYNONYM '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT mview_name object_name FROM dba_mviews WHERE owner=p_owner) LOOP exec_ddl('DROP MATERIALIZED VIEW '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT view_name object_name FROM dba_views WHERE owner=p_owner) LOOP exec_ddl('DROP VIEW '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT trigger_name object_name FROM dba_triggers WHERE table_owner=p_owner) LOOP exec_ddl('DROP TRIGGER '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT object_name FROM dba_objects WHERE owner=p_owner AND object_type='PACKAGE BODY') LOOP exec_ddl('DROP PACKAGE BODY '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT object_name FROM dba_objects WHERE owner=p_owner AND object_type='PACKAGE') LOOP exec_ddl('DROP PACKAGE '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT procedure_name object_name FROM dba_procedures WHERE owner=p_owner AND object_type='PROCEDURE') LOOP exec_ddl('DROP PROCEDURE '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT object_name FROM dba_objects WHERE owner=p_owner AND object_type='FUNCTION') LOOP exec_ddl('DROP FUNCTION '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT type_name object_name FROM dba_types WHERE owner=p_owner) LOOP exec_ddl('DROP TYPE '||p_owner||'.\"'||r.object_name||'\" FORCE'); END LOOP;
FOR r IN (SELECT sequence_name object_name FROM dba_sequences WHERE sequence_owner=p_owner) LOOP exec_ddl('DROP SEQUENCE '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT index_name object_name FROM dba_indexes WHERE owner=p_owner AND table_owner=p_owner) LOOP exec_ddl('DROP INDEX '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT table_name object_name FROM dba_tables WHERE owner=p_owner) LOOP exec_ddl('DROP TABLE '||p_owner||'.\"'||r.object_name||'\" CASCADE CONSTRAINTS PURGE'); END LOOP;
END;
BEGIN
FOR u IN (SELECT username FROM dba_users WHERE oracle_maintained='N' AND UPPER(username) IN (${inlist}) ORDER BY username) LOOP
DBMS_OUTPUT.PUT_LINE('Dropping objects for '||u.username);
drop_for_owner(u.username);
END LOOP;
END;
/
"
}
drop_users_cascade_listed() {
local base="${SCHEMAS_LIST_IMP:-${SCHEMAS_LIST_EXP:-}}"
[[ -z "$base" ]] && read -rp "Enter schemas to DROP (comma-separated): " base
local schemas; schemas="$(confirm_edit_value "Drop these users" "$base")"
local inlist; inlist="$(csv_to_inlist "$schemas")"
report_user_list "dry_users_listed" "$inlist"
report_object_counts "dry_objs_listed" "$inlist"
if [[ "${DRY_RUN_ONLY^^}" == "Y" ]]; then
warn "DRY_RUN_ONLY=Y: Skipping DROP USER execution."
return
fi
local confirm
echo "This will DROP these users CASCADE on TARGET: ${schemas}"
read -rp "Type YES to proceed (anything else cancels): " confirm
[[ "$confirm" == "YES" ]] || { warn "Cancelled."; return; }
run_sql "$TGT_EZCONNECT" "drop_users_listed" "
SET SERVEROUTPUT ON
DECLARE v_stmt VARCHAR2(4000);
BEGIN
FOR r IN (
SELECT username FROM dba_users
WHERE oracle_maintained='N'
AND UPPER(username) IN (${inlist})
ORDER BY username
) LOOP
v_stmt := 'DROP USER '||r.username||' CASCADE';
BEGIN EXECUTE IMMEDIATE v_stmt; DBMS_OUTPUT.PUT_LINE('DROPPED USER: '||r.username);
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('FAILED: '||v_stmt||' - '||SQLERRM);
END;
END LOOP;
END;
/
"
}
drop_objects_listed() {
local base="${SCHEMAS_LIST_IMP:-${SCHEMAS_LIST_EXP:-}}"
[[ -z "$base" ]] && read -rp "Enter schemas (owners) to purge objects for (comma-separated): " base
local owners; owners="$(confirm_edit_value "Purge objects for owners" "$base")"
local inlist; inlist="$(csv_to_inlist "$owners")"
report_user_list "dry_users_obj_listed" "$inlist"
report_object_counts "dry_objs_obj_listed" "$inlist"
if [[ "${DRY_RUN_ONLY^^}" == "Y" ]]; then
warn "DRY_RUN_ONLY=Y: Skipping DROP OBJECTS execution."
return
fi
local confirm
echo "This will DROP ALL OBJECTS for these owners on TARGET (users remain): ${owners}"
read -rp "Type YES to proceed (anything else cancels): " confirm
[[ "$confirm" == "YES" ]] || { warn "Cancelled."; return; }
run_sql "$TGT_EZCONNECT" "drop_objs_listed" "
SET SERVEROUTPUT ON
DECLARE
PROCEDURE exec_ddl(p_sql IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE p_sql; EXCEPTION WHEN OTHERS THEN NULL; END;
PROCEDURE drop_for_owner(p_owner IN VARCHAR2) IS
BEGIN
FOR r IN (SELECT object_name FROM dba_objects WHERE owner=p_owner AND object_type='SYNONYM') LOOP exec_ddl('DROP SYNONYM '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT mview_name object_name FROM dba_mviews WHERE owner=p_owner) LOOP exec_ddl('DROP MATERIALIZED VIEW '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT view_name object_name FROM dba_views WHERE owner=p_owner) LOOP exec_ddl('DROP VIEW '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT trigger_name object_name FROM dba_triggers WHERE table_owner=p_owner) LOOP exec_ddl('DROP TRIGGER '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT object_name FROM dba_objects WHERE owner=p_owner AND object_type='PACKAGE BODY') LOOP exec_ddl('DROP PACKAGE BODY '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT object_name FROM dba_objects WHERE owner=p_owner AND object_type='PACKAGE') LOOP exec_ddl('DROP PACKAGE '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT procedure_name object_name FROM dba_procedures WHERE owner=p_owner AND object_type='PROCEDURE') LOOP exec_ddl('DROP PROCEDURE '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT object_name FROM dba_objects WHERE owner=p_owner AND object_type='FUNCTION') LOOP exec_ddl('DROP FUNCTION '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT type_name object_name FROM dba_types WHERE owner=p_owner) LOOP exec_ddl('DROP TYPE '||p_owner||'.\"'||r.object_name||'\" FORCE'); END LOOP;
FOR r IN (SELECT sequence_name object_name FROM dba_sequences WHERE sequence_owner=p_owner) LOOP exec_ddl('DROP SEQUENCE '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT index_name object_name FROM dba_indexes WHERE owner=p_owner AND table_owner=p_owner) LOOP exec_ddl('DROP INDEX '||p_owner||'.\"'||r.object_name||'\"'); END LOOP;
FOR r IN (SELECT table_name object_name FROM dba_tables WHERE owner=p_owner) LOOP exec_ddl('DROP TABLE '||p_owner||'.\"'||r.object_name||'\" CASCADE CONSTRAINTS PURGE'); END LOOP;
END;
BEGIN
FOR u IN (SELECT username FROM dba_users WHERE oracle_maintained='N' AND UPPER(username) IN (${inlist}) ORDER BY username) LOOP
DBMS_OUTPUT.PUT_LINE('Dropping objects for '||u.username);
drop_for_owner(u.username);
END LOOP;
END;
/
"
}
import_cleanup_menu() {
while true; do
cat <"$out" 2>"${out}.log"
${ddl_sql_prolog}
${body}
EXIT
SQL
if grep -qi "ORA-" "${out}.log"; then
err "DDL extract error (see ${out}.log) for $(basename "$out")"
tail -n 120 "${out}.log" | mask_pwd | sed 's/^/ /'
exit 1
fi
ok "Wrote: $out"
}
ddl_users() { local f="${DDL_DIR}/01_users_${RUN_ID}.sql"; ddl_spool "$f" "
SELECT DBMS_METADATA.GET_DDL(''USER'', username)
FROM dba_users
WHERE oracle_maintained = ''N''
ORDER BY username;"; }
ddl_profiles() { local f="${DDL_DIR}/02_profiles_${RUN_ID}.sql"; ddl_spool "$f" "
SELECT DBMS_METADATA.GET_DDL(''PROFILE'', profile)
FROM (SELECT DISTINCT profile FROM dba_profiles ORDER BY 1);"; }
ddl_roles() { local f="${DDL_DIR}/03_roles_${RUN_ID}.sql"; ddl_spool "$f" "
SELECT DBMS_METADATA.GET_DDL(''ROLE'', role)
FROM dba_roles
WHERE NVL(oracle_maintained, 'N') = 'N'
ORDER BY role;"; }
ddl_privs_to_roles() { local f="${DDL_DIR}/04_role_privs_${RUN_ID}.sql"; ddl_spool "$f" "
WITH roles AS (SELECT role FROM dba_roles WHERE NVL(oracle_maintained,'N')='N')
SELECT DBMS_METADATA.GET_GRANTED_DDL(''SYSTEM_GRANT'', role) FROM roles
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(''OBJECT_GRANT'', role) FROM roles;"; }
ddl_sysprivs_to_users() { local f="${DDL_DIR}/05_user_sysprivs_${RUN_ID}.sql"; ddl_spool "$f" "
SELECT DBMS_METADATA.GET_GRANTED_DDL(''SYSTEM_GRANT'', username)
FROM dba_users
WHERE oracle_maintained='N'
ORDER BY username;"; }
ddl_sequences_all_users() { local f="${DDL_DIR}/06_sequences_${RUN_ID}.sql"; ddl_spool "$f" "
SELECT DBMS_METADATA.GET_DDL(''SEQUENCE'', sequence_name, owner)
FROM dba_sequences
WHERE owner IN (SELECT username FROM dba_users WHERE oracle_maintained='N')
ORDER BY owner, sequence_name;"; }
ddl_public_synonyms() { local f="${DDL_DIR}/07_public_synonyms_${RUN_ID}.sql"; ddl_spool "$f" "
SELECT DBMS_METADATA.GET_DDL(''SYNONYM'', synonym_name, ''PUBLIC'')
FROM dba_synonyms
WHERE owner = ''PUBLIC''
ORDER BY synonym_name;"; }
ddl_private_synonyms_all_users() { local f="${DDL_DIR}/08_private_synonyms_${RUN_ID}.sql"; ddl_spool "$f" "
SELECT DBMS_METADATA.GET_DDL(''SYNONYM'', synonym_name, owner)
FROM dba_synonyms
WHERE owner <> ''PUBLIC''
AND owner IN (SELECT username FROM dba_users WHERE oracle_maintained='N')
ORDER BY owner, synonym_name;"; }
ddl_all_ddls_all_users() {
local f="${DDL_DIR}/09_all_ddls_${RUN_ID}.sql"
local types_clause; types_clause="$(echo "$DDL_OBJECT_TYPES" | awk -F',' '{for(i=1;i<=NF;i++){gsub(/^[ \t]+|[ \t]+$/, "", $i); printf("%s''%s''", (i>1?",":""), $i)} }')"
ddl_spool "$f" "
WITH owners AS (
SELECT username AS owner FROM dba_users WHERE oracle_maintained='N'
),
objs AS (
SELECT owner, object_type, object_name
FROM dba_objects
WHERE owner IN (SELECT owner FROM owners)
AND object_type IN (${types_clause})
AND object_name NOT LIKE 'BIN$%%'
AND temporary = 'N'
)
SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner)
FROM objs
ORDER BY owner, object_type, object_name;"; }
ddl_tablespaces() {
local f="${DDL_DIR}/10_tablespaces_${RUN_ID}.sql"
local skip_clause; skip_clause="$(echo "$SKIP_TABLESPACES" | awk -F',' '{for(i=1;i<=NF;i++){gsub(/^[ \t]+|[ \t]+$/, "", $i); printf("%s''%s''", (i>1?",":""), toupper($i))} }')"
ddl_spool "$f" "
SELECT DBMS_METADATA.GET_DDL(''TABLESPACE'', tablespace_name)
FROM dba_tablespaces
WHERE UPPER(tablespace_name) NOT IN (${skip_clause})
ORDER BY tablespace_name;"; }
ddl_role_grants_to_users() { local f="${DDL_DIR}/11_role_grants_to_users_${RUN_ID}.sql"; ddl_spool "$f" "
SELECT DBMS_METADATA.GET_GRANTED_DDL(''ROLE_GRANT'', grantee)
FROM (SELECT username AS grantee FROM dba_users WHERE oracle_maintained='N')
ORDER BY 1;"; }
ddl_default_roles() { local f="${DDL_DIR}/12_default_roles_${RUN_ID}.sql"; ddl_spool "$f" "
WITH u AS (SELECT username FROM dba_users WHERE oracle_maintained='N'),
r AS (
SELECT grantee AS username, LISTAGG(role, ',') WITHIN GROUP (ORDER BY role) AS roles
FROM dba_role_privs
WHERE default_role = 'YES' AND grantee IN (SELECT username FROM u)
GROUP BY grantee
)
SELECT 'ALTER USER '||username||' DEFAULT ROLE '||
CASE WHEN roles IS NULL THEN 'ALL' ELSE roles END || ';'
FROM u LEFT JOIN r USING (username)
ORDER BY username;"; }
ddl_directories() { local f="${DDL_DIR}/13_directories_${RUN_ID}.sql"; ddl_spool "$f" "
SELECT DBMS_METADATA.GET_DDL(''DIRECTORY'', directory_name)
FROM (SELECT DISTINCT directory_name FROM dba_directories ORDER BY 1);"; }
ddl_db_links_by_owner() {
read -rp "Enter owner for DB links (schema name): " owner
local f="${DDL_DIR}/14_db_links_${owner^^}_${RUN_ID}.sql"
ddl_spool "$f" "
SELECT DBMS_METADATA.GET_DDL(''DB_LINK'', db_link, owner)
FROM dba_db_links
WHERE owner = UPPER('${owner}')
ORDER BY db_link;"
warn "Note: DB link passwords may be masked/omitted depending on version/security."
}
ddl_menu() {
while true; do
cat <<'EOS'
DDL Extraction (Source DB):
1) USERS (exclude Oracle-maintained)
2) PROFILES
3) ROLES (exclude Oracle-maintained)
4) PRIVILEGES -> ROLES
5) SYSTEM PRIVILEGES -> USERS (exclude Oracle-maintained)
6) SEQUENCES for USERS (exclude Oracle-maintained)
7) PUBLIC SYNONYMS
8) PRIVATE SYNONYMS for USERS (exclude Oracle-maintained)
9) ALL OBJECT DDLs for USERS (exclude Oracle-maintained) [heavy]
10) TABLESPACE DDLs (skip system/temp/undo)
11) ROLE GRANTS -> USERS
12) DEFAULT ROLES per USER (ALTER USER DEFAULT ROLE ...)
13) DIRECTORY OBJECTS
14) DB LINKS by OWNER (prompt)
B) Back
EOS
read -rp "Choose: " c
case "${c^^}" in
1) ddl_users ;; 2) ddl_profiles ;; 3) ddl_roles ;; 4) ddl_privs_to_roles ;;
5) ddl_sysprivs_to_users ;; 6) ddl_sequences_all_users ;; 7) ddl_public_synonyms ;;
8) ddl_private_synonyms_all_users ;; 9) ddl_all_ddls_all_users ;; 10) ddl_tablespaces ;;
11) ddl_role_grants_to_users ;; 12) ddl_default_roles ;; 13) ddl_directories ;;
14) ddl_db_links_by_owner ;; B) break ;; *) warn "Invalid choice" ;;
esac
done
}
#------------------------ Compare helpers (type predicates) --------------------
compare_type_predicate() {
local inc="$COMPARE_INCLUDE_TYPES" exc="$COMPARE_EXCLUDE_TYPES" out=""
if [[ -n "$inc" ]]; then
local list; list="$(echo "$inc" | awk -F',' '{for(i=1;i<=NF;i++){gsub(/^[ \t]+|[ \t]+$/, "", $i); printf("%s'\''%s'\''",(i>1?",":""), toupper($i))}}')"
out+=" AND object_type IN (${list})"${'\n'}
fi
if [[ -n "$exc" ]]; then
local list; list="$(echo "$exc" | awk -F',' '{for(i=1;i<=NF;i++){gsub(/^[ \t]+|[ \t]+$/, "", $i); printf("%s'\''%s'\''",(i>1?",":""), toupper($i))}}')"
out+=" AND object_type NOT IN (${list})"${'\n'}
fi
printf "%s" "$out"
}
compare_deep_type_predicate() {
local inc="$COMPARE_DEEP_INCLUDE_TYPES" exc="$COMPARE_DEEP_EXCLUDE_TYPES" out=""
if [[ -n "$inc" ]]; then
local list; list="$(echo "$inc" | awk -F',' '{for(i=1;i<=NF;i++){gsub(/^[ \t]+|[ \t]+$/, "", $i); printf("%s'\''%s'\''",(i>1?",":""), toupper($i))}}')"
out+=" AND object_type IN (${list})"${'\n'}
fi
if [[ -n "$exc" ]]; then
local list; list="$(echo "$exc" | awk -F',' '{for(i=1;i<=NF;i++){gsub(/^[ \t]+|[ \t]+$/, "", $i); printf("%s'\''%s'\''",(i>1?",":""), toupper($i))}}')"
out+=" AND object_type NOT IN (${list})"${'\n'}
fi
printf "%s" "$out"
}
#------------------------ Shallow compare (presence + STATUS) ------------------
snapshot_schema_objects() {
# $1=ezconnect $2=SCHEMA $3=side_tag (src|tgt)
local ez="$1" schema="${2^^}" side="$3"
local types_pred; types_pred="$(compare_type_predicate)"
local tag="cmp_snap_${side}_${schema}_${RUN_ID}"
local out="${COMPARE_DIR}/${schema}_${side}.lst"
run_sql "$ez" "$tag" "
SET PAGES 0 FEEDBACK OFF HEADING OFF LINES 32767
SELECT object_type||'|'||object_name||'|'||status
FROM dba_objects
WHERE owner = UPPER('${schema}')
AND temporary = 'N'
AND object_name NOT LIKE 'BIN$%'
${types_pred}
ORDER BY object_type, object_name;
/
"
awk 'NF{print $0}' "${LOG_DIR}/${tag}_${RUN_ID}.log" > "$out"
ok "Snapshot ${schema}@${side} -> $(basename "$out") ($(wc -l < "$out") rows)"
}
compare_one_schema() {
local schema="${1^^}"
[[ -z "$schema" ]] && { warn "Schema is empty"; return 1; }
snapshot_schema_objects "$SRC_EZCONNECT" "$schema" "src"
snapshot_schema_objects "$TGT_EZCONNECT" "$schema" "tgt"
local fsrc="${COMPARE_DIR}/${schema}_src.lst"
local ftgt="${COMPARE_DIR}/${schema}_tgt.lst"
local ksrc="${COMPARE_DIR}/${schema}_src.key"; awk -F'|' '{printf "%s|%s|%s\n",$1,$2,$3}' "$fsrc" | sort -t'|' -k1,1 -k2,2 > "$ksrc"
local ktgt="${COMPARE_DIR}/${schema}_tgt.key"; awk -F'|' '{printf "%s|%s|%s\n",$1,$2,$3}' "$ftgt" | sort -t'|' -k1,1 -k2,2 > "$ktgt"
local rep="${COMPARE_DIR}/${schema}_compare_${RUN_ID}.txt"
{
echo "=== Schema Object Comparison: ${schema} ==="
echo "Source: ${SRC_EZCONNECT}"
echo "Target: ${TGT_EZCONNECT}"
echo "Generated: $(date)"
echo
echo "== Summary counts by OBJECT_TYPE (Source) =="
awk -F'|' '{c[$1]++} END{for(t in c) printf "%-30s %8d\n", t, c[t]}' "$fsrc" | sort
echo
echo "== Summary counts by OBJECT_TYPE (Target) =="
awk -F'|' '{c[$1]++} END{for(t in c) printf "%-30s %8d\n", t, c[t]}' "$ftgt" | sort
echo
echo "== Only in SOURCE =="
join -t'|' -v1 -1 1,2 -2 1,2 \
<(awk -F'|' '{print $1"|"$2"|"$3}' "$ksrc") \
<(awk -F'|' '{print $1"|"$2"|"$3}' "$ktgt") \
| awk -F'|' '{printf "%-25s %-64s status=%s\n",$1,$2,$3}'
echo
echo "== Only in TARGET =="
join -t'|' -v2 -1 1,2 -2 1,2 \
<(awk -F'|' '{print $1"|"$2"|"$3}' "$ksrc") \
<(awk -F'|' '{print $1"|"$2"|"$3}' "$ktgt") \
| awk -F'|' '{printf "%-25s %-64s status=%s\n",$1,$2,$6}'
echo
echo "== In BOTH but STATUS differs =="
join -t'|' -j 1 \
<(awk -F'|' '{print $1"|"$2"|"$3}' "$ksrc") \
<(awk -F'|' '{print $1"|"$2"|"$3}' "$ktgt") \
| awk -F'|' 'BEGIN{OFS="|"} {src=$3; tgt=$6; if(src!=tgt) printf "%-25s %-64s src_status=%-10s tgt_status=%-10s\n",$1,$2,src,tgt}'
echo
} > "$rep"
ok "Report written: $rep"
echo "Open report: $rep"
# Build HTML + email
local html="${COMPARE_DIR}/${schema}_compare_${RUN_ID}.html"
{
html_begin "Shallow Compare ${schema}"
cat <Shallow Compare: ${schema}
Source: ${SRC_EZCONNECT} Target: ${TGT_EZCONNECT} Generated: $(date)
Summary counts
Source
| Object Type | Count |
|---|
HDR
awk -F'|' '{c[$1]++} END{for(t in c) printf "| %s | %d |
\n", t, c[t]}' "$fsrc" | sort
cat <
Target
| Object Type | Count |
|---|
MID1
awk -F'|' '{c[$1]++} END{for(t in c) printf "| %s | %d |
\n", t, c[t]}' "$ftgt" | sort
cat <
Only in Source
| Type | Name | Status |
|---|
MID2
join -t'|' -v1 -1 1,2 -2 1,2 \
<(awk -F'|' '{print $1"|"$2"|"$3}' "$ksrc") \
<(awk -F'|' '{print $1"|"$2"|"$3}' "$ktgt") \
| awk -F'|' '{printf "| %s | %s | %s |
\n",$1,$2,($3=="VALID"?"ok":"bad"),$3}'
cat <
Only in Target
| Type | Name | Status |
|---|
MID3
join -t'|' -v2 -1 1,2 -2 1,2 \
<(awk -F'|' '{print $1"|"$2"|"$3}' "$ksrc") \
<(awk -F'|' '{print $1"|"$2"|"$3}' "$ktgt") \
| awk -F'|' '{printf "| %s | %s | %s |
\n",$1,$2,($6=="VALID"?"ok":"bad"),$6}'
cat <
In both but Status differs
| Type | Name | Source Status | Target Status |
|---|
MID4
join -t'|' -j 1 "$ksrc" "$ktgt" \
| awk -F'|' '{src=$3;tgt=$6;if(src!=tgt) printf "| %s | %s | %s | %s |
\n",$1,$2,(src=="VALID"?"ok":"bad"),src,(tgt=="VALID"?"ok":"bad"),tgt}'
echo "
"
html_end
} > "$html"
ok "HTML report: $html"
if [[ -n "$REPORT_EMAILS" ]]; then
local subject="${MAIL_SUBJECT_PREFIX} Shallow Compare ${schema}"
mail_send_html "$subject" "$html" "$REPORT_EMAILS"
fi
}
#------------------------ Deep compare (DDL hash + rowcounts) ------------------
snapshot_schema_ddls_sha() {
# $1=ezconnect $2=schema $3=side (src|tgt)
local ez="$1" schema="${2^^}" side="$3"
local tag="cmp_ddl_${side}_${schema}_${RUN_ID}"
local out="${COMPARE_DIR}/${schema}_${side}.ddlhash"
local types_pred; types_pred="$(compare_deep_type_predicate)"
run_sql "$ez" "$tag" "
SET PAGES 0 FEEDBACK OFF HEADING OFF LINES 32767 LONG 1000000 LONGCHUNKSIZE 1000000
WITH
FUNCTION hash_clob_sha256(p CLOB) RETURN VARCHAR2 IS
l_ctx DBMS_CRYPTO.HASH_CTX_T;
l_pos PLS_INTEGER := 1;
l_len PLS_INTEGER := DBMS_LOB.GETLENGTH(p);
l_chunk PLS_INTEGER := 32767;
l_raw RAW(32767);
BEGIN
DBMS_CRYPTO.HASH_INIT(l_ctx, DBMS_CRYPTO.HASH_SHA256);
WHILE l_pos <= l_len LOOP
l_raw := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(p, l_chunk, l_pos));
DBMS_CRYPTO.HASH_UPDATE(l_ctx, l_raw);
l_pos := l_pos + l_chunk;
END LOOP;
RETURN LOWER(RAWTOHEX(DBMS_CRYPTO.HASH_FINAL(l_ctx)));
END;
SELECT object_type||'|'||object_name||'|'||
hash_clob_sha256(DBMS_METADATA.GET_DDL(object_type, object_name, owner))
FROM dba_objects
WHERE owner = UPPER('${schema}')
AND temporary = 'N'
AND object_name NOT LIKE 'BIN$%'
${types_pred}
ORDER BY object_type, object_name;
/
"
awk 'NF{print $0}' "${LOG_DIR}/${tag}_${RUN_ID}.log" > "$out"
ok "DDL hash snapshot ${schema}@${side} -> $(basename "$out") ($(wc -l < "$out") rows)"
}
snapshot_table_counts() {
# $1=ezconnect $2=schema $3=side (src|tgt)
local ez="$1" schema="${2^^}" side="$3"
local tag="cmp_tbl_${side}_${schema}_${RUN_ID}"
local out="${COMPARE_DIR}/${schema}_${side}.tblcnt"
if [[ "${EXACT_ROWCOUNT^^}" == "Y" ]]; then
run_sql "$ez" "$tag" "
SET SERVEROUTPUT ON PAGES 0 FEEDBACK OFF HEADING OFF
DECLARE
v_sql VARCHAR2(4000);
v_cnt NUMBER;
BEGIN
FOR t IN (SELECT table_name
FROM dba_tables
WHERE owner=UPPER('${schema}')
AND temporary='N'
ORDER BY table_name)
LOOP
BEGIN
v_sql := 'SELECT COUNT(*) FROM '||DBMS_ASSERT.ENQUOTE_NAME('${schema}',FALSE)
||'.'||DBMS_ASSERT.ENQUOTE_NAME(t.table_name,FALSE);
EXECUTE IMMEDIATE v_sql INTO v_cnt;
DBMS_OUTPUT.PUT_LINE(t.table_name||'|'||v_cnt||'|EXACT');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(t.table_name||'|ERROR:'||REPLACE(SQLERRM,'|','/')||'|EXACT');
END;
END LOOP;
END;
/
"
awk 'NF{print $0}' "${LOG_DIR}/${tag}_${RUN_ID}.log" > "$out"
else
run_sql "$ez" "$tag" "
SET PAGES 0 FEEDBACK OFF HEADING OFF LINES 32767
SELECT table_name||'|'||NVL(num_rows,-1)||'|STATS '||
NVL(TO_CHAR(last_analyzed,'YYYY-MM-DD HH24:MI:SS'),'')
FROM dba_tables
WHERE owner=UPPER('${schema}')
AND temporary='N'
ORDER BY table_name;
/
"
awk 'NF{print $0}' "${LOG_DIR}/${tag}_${RUN_ID}.log" > "$out"
fi
ok "Rowcount snapshot ${schema}@${side} -> $(basename "$out") ($(wc -l < "$out") rows)"
}
compare_one_schema_deep() {
local schema="${1^^}"
[[ -z "$schema" ]] && { warn "Schema is empty"; return 1; }
snapshot_schema_ddls_sha "$SRC_EZCONNECT" "$schema" "src"
snapshot_schema_ddls_sha "$TGT_EZCONNECT" "$schema" "tgt"
snapshot_table_counts "$SRC_EZCONNECT" "$schema" "src"
snapshot_table_counts "$TGT_EZCONNECT" "$schema" "tgt"
local ddl_src="${COMPARE_DIR}/${schema}_src.ddlhash"
local ddl_tgt="${COMPARE_DIR}/${schema}_tgt.ddlhash"
local tbl_src="${COMPARE_DIR}/${schema}_src.tblcnt"
local tbl_tgt="${COMPARE_DIR}/${schema}_tgt.tblcnt"
local rep="${COMPARE_DIR}/${schema}_deep_${RUN_ID}.txt"
local dsrc="${ddl_src}.key"; awk -F'|' '{printf "%s|%s|%s\n",$1,$2,$3}' "$ddl_src" | sort -t'|' -k1,1 -k2,2 > "$dsrc"
local dtgt="${ddl_tgt}.key"; awk -F'|' '{printf "%s|%s|%s\n",$1,$2,$3}' "$ddl_tgt" | sort -t'|' -k1,1 -k2,2 > "$dtgt"
local tsrc="${tbl_src}.key"; awk -F'|' '{printf "%s|%s|%s\n",toupper($1),$2,$3}' "$tbl_src" | sort -t'|' -k1,1 > "$tsrc"
local ttgt="${tbl_tgt}.key"; awk -F'|' '{printf "%s|%s|%s\n",toupper($1),$2,$3}' "$tbl_tgt" | sort -t'|' -k1,1 > "$ttgt"
{
echo "=== Deep Compare Report (DDL checksum + row counts) ==="
echo "Schema: ${schema}"
echo "Source: ${SRC_EZCONNECT}"
echo "Target: ${TGT_EZCONNECT}"
echo "Table count mode: $([[ "${EXACT_ROWCOUNT^^}" == "Y" ]] && echo EXACT || echo STATS)"
echo "Generated: $(date)"
echo
echo "== DDL: Only in SOURCE =="
join -t'|' -v1 -1 1,2 -2 1,2 "$dsrc" "$dtgt" \
| awk -F'|' '{printf "%-25s %-64s sha=%s\n",$1,$2,$3}'
echo
echo "== DDL: Only in TARGET =="
join -t'|' -v2 -1 1,2 -2 1,2 "$dsrc" "$dtgt" \
| awk -F'|' '{printf "%-25s %-64s sha=%s\n",$1,$2,$6}'
echo
echo "== DDL: Present in BOTH but checksum differs =="
join -t'|' -j 1 "$dsrc" "$dtgt" \
| awk -F'|' 'BEGIN{diff=0} {if($3!=$6){diff=1; printf "%-25s %-64s src_sha=%s tgt_sha=%s\n",$1,$2,$3,$6}} END{if(diff==0) print "(none)"}'
echo
echo "== TABLE ROW COUNTS: Only in SOURCE =="
join -t'|' -v1 "$tsrc" "$ttgt" \
| awk -F'|' '{printf "%-64s rows=%s (%s)\n",$1,$2,$3}'
echo
echo "== TABLE ROW COUNTS: Only in TARGET =="
join -t'|' -v2 "$tsrc" "$ttgt" \
| awk -F'|' '{printf "%-64s rows=%s (%s)\n",$1,$4,$6}'
echo
echo "== TABLE ROW COUNTS: Present in BOTH but differ =="
join -t'|' -j 1 "$tsrc" "$ttgt" \
| awk -F'|' 'BEGIN{d=0} {
s=$2; t=$4;
if(s!=t){d=1; printf "%-64s src=%s (%s) tgt=%s (%s)\n",$1,$2,$3,$4,$6}
} END{if(d==0) print "(none)"}'
echo
} > "$rep"
ok "Deep report written: $rep"
echo "Open report: $rep"
# HTML + mail
local html="${COMPARE_DIR}/${schema}_deep_${RUN_ID}.html"
{
html_begin "Deep Compare ${schema}"
cat <Deep Compare: ${schema}
Source: ${SRC_EZCONNECT} Target: ${TGT_EZCONNECT} Generated: $(date)
Rowcount mode: $([[ "${EXACT_ROWCOUNT^^}" == "Y" ]] && echo EXACT || echo STATS)
DDL — Only in Source
| Type | Name | SHA-256 |
|---|
HDR
join -t'|' -v1 -1 1,2 -2 1,2 "$dsrc" "$dtgt" \
| awk -F'|' '{printf "| %s | %s | %s |
\n",$1,$2,$3}'
cat <
DDL — Only in Target
| Type | Name | SHA-256 |
|---|
MID1
join -t'|' -v2 -1 1,2 -2 1,2 "$dsrc" "$dtgt" \
| awk -F'|' '{printf "| %s | %s | %s |
\n",$1,$2,$6}'
cat <
DDL — Present in both but checksum differs
| Type | Name | Source SHA | Target SHA |
|---|
MID2
join -t'|' -j 1 "$dsrc" "$dtgt" \
| awk -F'|' '{if($3!=$6) printf "| %s | %s | %s | %s |
\n",$1,$2,$3,$6}'
echo "
"
cat <Table Rowcounts — Only in Source
| Table | Rows | Source |
|---|
MID3
join -t'|' -v1 "$tsrc" "$ttgt" \
| awk -F'|' '{printf "%s | %s | %s |
\n",$1,$2,$3}'
cat <
Table Rowcounts — Only in Target
| Table | Rows | Source |
|---|
MID4
join -t'|' -v2 "$tsrc" "$ttgt" \
| awk -F'|' '{printf "%s | %s | %s |
\n",$1,$4,$6}'
cat <
Table Rowcounts — Present in both but differ
| Table | Source Rows | Target Rows |
|---|
MID5
join -t'|' -j 1 "$tsrc" "$ttgt" \
| awk -F'|' '{if($2!=$4) printf "%s | %s (%s) | %s (%s) |
\n",$1,$2,$3,$4,$6}'
html_end
} > "$html"
ok "HTML report: $html"
if [[ -n "$REPORT_EMAILS" ]]; then
local subject="${MAIL_SUBJECT_PREFIX} Deep Compare ${schema}"
mail_send_html "$subject" "$html" "$REPORT_EMAILS"
fi
}
#------------------------ Compare menu (shallow + deep) ------------------------
compare_schema_menu() {
while true; do
cat <<'EOS'
Compare Objects (Source vs Target):
1) Shallow compare for one schema (object presence & STATUS)
2) Shallow compare for multiple schemas (comma-separated)
3) DEEP compare for one schema (DDL checksum + table row counts)
4) DEEP compare for multiple schemas (comma-separated)
5) Back
EOS
read -rp "Choose: " c
case "$c" in
1) read -rp "Schema name: " s; compare_one_schema "$s" ;;
2) read -rp "Schema names (comma-separated): " list
IFS=',' read -r -a arr <<< "$list"
for s in "${arr[@]}"; do s="$(echo "$s" | awk '{$1=$1;print}')"; [[ -z "$s" ]] && continue; compare_one_schema "$s"; done ;;
3) read -rp "Schema name: " s; compare_one_schema_deep "$s" ;;
4) read -rp "Schema names (comma-separated): " list
IFS=',' read -r -a arr <<< "$list"
for s in "${arr[@]}"; do s="$(echo "$s" | awk '{$1=$1;print}')"; [[ -z "$s" ]] && continue; compare_one_schema_deep "$s"; done ;;
5) break ;;
*) warn "Invalid choice" ;;
esac
done
}
#------------------------ Menus (Export/Import/Main) ---------------------------
export_menu() {
while true; do
cat <<'EOS'
Export Menu:
1) FULL database (submenu: metadata_only / full)
2) SCHEMAS (submenu: all non-maintained / user|conf)
3) TABLESPACES (transport)
4) TABLES
5) Back
EOS
read -rp "Choose: " c
case "$c" in
1) exp_full_menu ;;
2) exp_schemas_menu ;;
3) exp_tablespaces ;;
4) exp_tables ;;
5) break ;;
*) warn "Invalid choice" ;;
esac
done
}
import_menu() {
while true; do
cat < submenu (with preview/dry-run)
6) Back
EOS
read -rp "Choose: " c
case "$c" in
1) imp_full_menu ;;
2) imp_schemas_menu ;;
3) imp_tablespaces ;;
4) imp_tables ;;
5) import_cleanup_menu ;;
6) break ;;
*) warn "Invalid choice" ;;
esac
done
}
main_menu() {
while true; do
cat < sub menu
3) Import (Data Pump) -> sub menu
4) Monitor/Status -> DBA_DATAPUMP_JOBS + tail logs
5) Drop DIRECTORY objects -> cleanup
6) DDL Extraction (Source DB) -> sub menu
7) Compare Objects (Source vs Target) -> sub menu
8) Quit
EOS
read -rp "Choose: " choice
case "$choice" in
1) ensure_directory_object "$SRC_EZCONNECT" "src"; ensure_directory_object "$TGT_EZCONNECT" "tgt"; ok "DIRECTORY ${COMMON_DIR_NAME} ready on both." ;;
2) export_menu ;;
3) import_menu ;;
4) show_jobs ;;
5) cleanup_dirs ;;
6) ddl_menu ;;
7) compare_schema_menu ;;
8) ok "Bye."; exit 0 ;;
*) warn "Invalid choice." ;;
esac
done
}
main_menu
