1) drop
#!/bin/bash
# Oracle Schema Migration Script
# Description: Exports a schema from Source DB (A) and imports into Target DB (B)
# Pre-requisites:
# - Oracle Data Pump directories must be configured on both databases
# - User must have necessary privileges (EXP_FULL_DATABASE, IMP_FULL_DATABASE)
# - Target schema user must exist in Database B
# Configuration
SOURCE_DB="SOURCE_USER/SOURCE_PASSWORD@DATABASE_A"
TARGET_DB="TARGET_USER/TARGET_PASSWORD@DATABASE_B"
SCHEMA_NAME="YOUR_SCHEMA_NAME"
DATA_PUMP_DIR="DATA_PUMP_DIR_NAME" # Oracle directory object name
DUMPFILE="${SCHEMA_NAME}_export.dmp"
LOGFILE_EXPORT="${SCHEMA_NAME}_export.log"
LOGFILE_IMPORT="${SCHEMA_NAME}_import.log"
# Function to drop all objects in target schema
drop_target_objects() {
echo "Dropping all objects in target schema: ${SCHEMA_NAME}"
sqlplus -S /nolog <
" > $REPORT_FILE
echo "Schema Comparison Report" >> $REPORT_FILE
echo "" >> $REPORT_FILE
echo "Schema Comparison Report
" >> $REPORT_FILE
# Step 3: Compare missing objects (tables, views, indexes, sequences, procedures)
echo "Missing Objects
" >> $REPORT_FILE
echo "| Object Type | Object Name |
|---|
" >> $REPORT_FILE
sqlplus -s $ORACLE_USER/$ORACLE_PASSWORD@$TARGET_DB_B < temp_missing_objects.txt
SET PAGESIZE 500
SET LINESIZE 200
SET HEADING OFF
SELECT 'Table', table_name FROM all_tables@${DBLINK_NAME}
WHERE table_name NOT IN (SELECT table_name FROM all_tables WHERE owner = UPPER('$SCHEMA_NAME'));
SELECT 'View', view_name FROM all_views@${DBLINK_NAME}
WHERE view_name NOT IN (SELECT view_name FROM all_views WHERE owner = UPPER('$SCHEMA_NAME'));
SELECT 'Index', index_name FROM all_indexes@${DBLINK_NAME}
WHERE index_name NOT IN (SELECT index_name FROM all_indexes WHERE owner = UPPER('$SCHEMA_NAME'));
SELECT 'Sequence', sequence_name FROM all_sequences@${DBLINK_NAME}
WHERE sequence_name NOT IN (SELECT sequence_name FROM all_sequences WHERE owner = UPPER('$SCHEMA_NAME'));
SELECT 'Procedure', object_name FROM all_procedures@${DBLINK_NAME}
WHERE object_name NOT IN (SELECT object_name FROM all_procedures WHERE owner = UPPER('$SCHEMA_NAME'));
EXIT;
EOF
while read line; do
object_type=$(echo $line | awk '{print $1}')
object_name=$(echo $line | awk '{$1=""; print $0}' | sed 's/^ *//g')
if [ ! -z "$object_name" ]; then
echo "| $object_type | $object_name |
" >> $REPORT_FILE
fi
done < temp_missing_objects.txt
echo "
" >> $REPORT_FILE
rm temp_missing_objects.txt
# Step 4: Compare row counts for all tables
echo "Row Count Differences
" >> $REPORT_FILE
echo "| Table Name | Source Count | Target Count | Status |
|---|
" >> $REPORT_FILE
sqlplus -s $ORACLE_USER/$ORACLE_PASSWORD@$TARGET_DB_B < temp_row_counts.txt
SET PAGESIZE 500
SET LINESIZE 200
SET HEADING OFF
SELECT table_name,
(SELECT COUNT(*) FROM ${SCHEMA_NAME}."||table_name||"@$DBLINK_NAME) AS SRC_COUNT,
(SELECT COUNT(*) FROM ${SCHEMA_NAME}."||table_name||") AS TGT_COUNT
FROM all_tables WHERE owner = UPPER('$SCHEMA_NAME');
EXIT;
EOF
while read line; do
table_name=$(echo $line | awk '{print $1}')
src_count=$(echo $line | awk '{print $2}')
tgt_count=$(echo $line | awk '{print $3}')
if [ "$src_count" == "$tgt_count" ]; then
status="MATCH"
else
status="MISMATCH"
fi
echo "| $table_name | $src_count | $tgt_count | $status |
" >> $REPORT_FILE
done < temp_row_counts.txt
echo "
" >> $REPORT_FILE
rm temp_row_counts.txt
# Step 5: Finish HTML Report
echo "