linux常用ogg脚本,OGG通过shell对比源端、目标端和目标端表结构脚本

#!/bin/bash

owner_table=($(sqlplus -s / as sysdba << eof

set feedback off

set heading off

set pagesize 0

select owner||'.'||table_name from dba_tables where owner in('ISC_UAP_TEMP','ISC02')

order by owner,table_name;

eof))

if [ $# -ne 3 ];then

echo "ERROR

arameter error"

echo "USAGE:$0 target_connect_command sourceuser.tablename targetuser.tablename"

echo "EXAMPLE:$0 \"sqlplus -S sys/123qwe@192.168.65.53:1521/orcl\" \"${owner_table}\" \""${owner_table}\""

exit

fi

printf "\n"

target_connect=$1

source_table=$2

target_table=$3

s_user=`echo ${source_table%%.*} | tr '[a-z]' '[A-Z]'`

s_table=`echo ${source_table##*.} | tr '[a-z]' '[A-Z]'`

t_user=`echo ${source_table%%.*} | tr '[a-z]' '[A-Z]'`

t_table=`echo ${target_table##*.} | tr '[a-z]' '[A-Z]'`

##########column##########

source_column_name=($(sqlplus -s / as sysdba << eof

set feedback off

set heading off

set pagesize 0

select column_name

from dba_tab_columns where owner='${s_user}' and table_name='${s_table}'

order by column_id;

eof))

source_column_nullable=($(sqlplus -s / as sysdba << eof

set feedback off

set heading off

set pagesize 0

select replace(replace(nullable,'Y','YES'),'N','NO') nullable

from dba_tab_columns where owner='${s_user}' and table_name='${s_table}'

order by column_id;

eof))

source_column_data_type=($(sqlplus -s / as sysdba << eof

set feedback off

set heading off

set pagesize 0

select data_type

from dba_tab_columns where owner='${s_user}' and table_name='${s_table}'

order by column_id;

eof))

source_column_data_length=($(sqlplus -s / as sysdba << eof

set feedback off

set heading off

set pagesize 0

select data_length

from dba_tab_columns where owner='${s_user}' and table_name='${s_table}'

order by column_id;

eof))

target_column_name=($(${target_connect} << eof

select column_name

from dba_tab_columns where owner='${t_user}' and table_name='${t_table}'

order by column_id;

eof))

target_column_nullable=($(${target_connect} << eof

select replace(replace(nullable,'Y','YES'),'N','NO') nullable

from dba_tab_columns where owner='${t_user}' and table_name='${t_table}'

order by column_id;

eof))

target_column_data_type=($(${target_connect} << eof

select data_type

from dba_tab_columns where owner='${t_user}' and table_name='${t_table}'

order by column_id;

eof))

target_column_data_length=($(${target_connect} << eof

select data_length

from dba_tab_columns where owner='${t_user}' and table_name='${t_table}'

order by column_id;

eof))

column_compare()

{

if [ "${source_column_name

}" != "${target_column_name

}" ];then

return 1

fi

if [ "${source_column_nullable

}" != "${target_column_nullable

}" ];then

return 1

fi

for((i=0;i

} || i

};i++));

do

if [ "${source_column_data_type[$i]}" == "NUMBER" -a "${target_column_data_type[$i]}" != "NUMBER" ];then

var=$i

return 1

elif [ "${source_column_data_type[$i]}" == "VARCHAR2" -a "${target_column_data_type[$i]}" != "VARCHAR2" ];then

return 1

elif [ "${source_column_data_type[$i]}" == "CHAR" -a "${target_column_data_type[$i]}" != "CHAR" ];then

return 1

elif [ "${source_column_data_type[$i]}" == "DATE" -a "${target_column_data_type[$i]}" != "DATE" ];then

return 1

elif [ "${source_column_data_type[$i]}" == "FLOAT" -a "${target_column_data_type[$i]}" != "FLOAT" ];then

return 1

elif [ "${source_column_data_type[$i]}" == "BLOB" -a "${target_column_data_type[$i]}" != "BLOB" ];then

return 1

elif [ "${source_column_data_type[$i]}" == "CLOB" -a "${target_column_data_type[$i]}" != "CLOB" ];then

return 1

elif [ "${source_column_data_type[$i]}" == "RAW" -a "${target_column_data_type[$i]}" != "RAW" ];then

return 1

fi

done

for((i=0;i

} || i

};i++));

do

if [ "${source_column_data_type[$i]}" == "VARCHAR2" -a "${source_column_data_length[$i]}" != "${target_column_data_length[$i]}" ];then

return 1

fi

done

}

column_compare ${s_user} ${s_table} ${t_user} ${t_table}

if [ $? -ne 0 ];then

echo -e "\e[31mERROR:Column of the two tables is not the same.\e[m"

tmp=0

for((i=0;i

} || i

};i++));

do

if [ ${tmp} -eq 0 ];then

printf "%-35s%-15s%-15s%-20s%-35s%-15s%-15s%-20s\n" "source_column_NAME" "NULLABLE" "DATA_TYPE" "DATA_LENGTH" "target_column_NAME" "NULLABLE" "DATA_TYPE" "DATA_LENGTH"

printf "%-80s     %-80s\n" "--------------------------------------------------------------------------------" "--------------------------------------------------------------------------------"

tmp=1

fi

if [ $i -eq $var ];then

printf "\e[31m%-35s%-15s%-15s%-20s%-35s%-15s%-15s%-20s\e[m\n" "${source_column_name[$i]}" "${source_column_nullable[$i]}" "${source_column_data_type[$i]}" "${source_column_data_length[$i]}" "${target_column_name[$i]}" "${target_column_nullable[$i]}" "${target_column_data_type[$i]}" "${target_column_data_length[$i]}"

else

printf "%-35s%-15s%-15s%-20s%-35s%-15s%-15s%-20s\n" "${source_column_name[$i]}" "${source_column_nullable[$i]}" "${source_column_data_type[$i]}" "${source_column_data_length[$i]}" "${target_column_name[$i]}" "${target_column_nullable[$i]}" "${target_column_data_type[$i]}" "${target_column_data_length[$i]}"

fi

done

if [ ${tmp} -eq 1 ];then

printf "\n"

fi

fi

##########index##########

source_ind=($(sqlplus -s / as sysdba << eof

set feedback off

set heading off

set pagesize 0

select max(a) source_ind from

(select index_name,wmsys.wm_concat(column_name) over (partition by index_name order by column_position) a

from dba_ind_columns where table_owner='${s_user}' and table_name='${s_table}')

group by index_name order by source_ind;

eof))

source_ind_name=($(sqlplus -s / as sysdba << eof

set feedback off

set heading off

set pagesize 0

select index_name from

(select index_name,max(a) source_ind from

(select index_name,wmsys.wm_concat(column_name) over (partition by index_name order by column_position) a

from dba_ind_columns where table_owner='${s_user}' and table_name='${s_table}')

group by index_name order by source_ind);

eof))

target_ind=($(${target_connect} << eof

select max(a) target_ind from

(select index_name,wmsys.wm_concat(column_name) over (partition by index_name order by column_position) a

from dba_ind_columns where table_owner='${s_user}' and table_name='${s_table}')

group by index_name order by target_ind;

eof))

target_ind_name=($(${target_connect} << eof

select index_name from

(select index_name,max(a) target_ind from

(select index_name,wmsys.wm_concat(column_name) over (partition by index_name order by column_position) a

from dba_ind_columns where table_owner='${s_user}' and table_name='${s_table}')

group by index_name order by target_ind);

eof))

tmp=0

for((i=0;i

} || i

};i++));

do

if [ "${source_ind[$i]}" != "${target_ind[$i]}" ];then

if [ ${tmp} -eq 0 ];then

echo -e "\e[31mERROR:Index of the two tables is not the same.\e[m"

printf "%-35s%-35s\n" "source_ind_NAME" "target_ind_NAME"

printf "%-35s%-35s\n" "------------------------------" "------------------------------"

tmp=1

fi

printf "\e[31m%-35s%-35s\e[m\n" "${source_ind_name[$i]}" "${target_ind_name[$i]}"

fi

done

if [ ${tmp} -eq 1 ];then

printf "\n"

fi