Saturday, October 25, 2025
#!/usr/bin/env bash
# dp_migrate.sh - Oracle 19c Data Pump migration & DDL extractor (menu-driven)
# 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}"
# NEW: Global dry-run switch for destructive IMPORT cleanup actions
DRY_RUN_ONLY="${DRY_RUN_ONLY:-N}" # Y=show preview reports only, do NOT execute
#------------------------ Pre-flight checks -----------------------------------
for b in sqlplus expdp impdp; do
command -v "$b" >/devnull 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"
#------------------------ 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"
}
#------------------------ 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) --------------------------
# All options now generate dry-run reports; if DRY_RUN_ONLY=Y, execution is skipped.
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
}
#------------------------ 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) 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) ok "Bye."; exit 0 ;;
*) warn "Invalid choice." ;;
esac
done
}
main_menu
