脚本 f_con_exe_sql.sh

################################################################################
#     sql：需要执行的Sql，根据具体sql代替
#     array_name2：数据库tns
#     实现：在不同厂区执行同一sql：比如需要在所有厂区建索引。
################################################################################
#${dbuser}/${pwd}@${db_ip}:${db_port}/${service_name}
#不同厂区：
array_name1=(
"厂区A"
"厂区B"
"厂区C"
"厂区D"
"厂区E"
"厂区F"
"厂区G"
"厂区H"
)
array_name2=(
userA/password@192.168.10.11:1521/orcl
userA/password@192.168.10.12:1521/orcl
userA/password@192.168.10.13:1521/orcl
userA/password@192.168.10.14:1521/orcl
userA/password@192.168.10.15:1521/orcl
userA/password@192.168.10.16:1521/orcl
userA/password@192.168.10.17:1521/orcl
userA/password@192.168.10.18:1521/orcl
)
#echo ${array_name[@]}
#echo "sqlplus / as sysdba"${array_name[1]}
#echo length=${#array_name[@]}


#需要执行的Sql

sql2="
select sysdate,instance_name from v\$instance;
"

sql="
set line 400
col SEGMENT_NAME for a25
col OWNER for a15
col TABLESPACE_NAME for a20
col PARTITION_NAME for a20
col BYTES for 9999999999999
col size_G for 999999999999
col DEGREE for a5
col TABLE_NAME for a20
col TABLE_OWNER for a15
col INDEX_NAME for a25

select owner,segment_name,partition_name,segment_type,tablespace_name,blocks,bytes,bytes/1024/1024/1024 G from dba_segments where segment_name=upper('table1');

create index idx_table1_n2 on table1(mdate) TABLESPACE tab_INX parallel 5 online;
alter index idx_table1_n2 noparallel;

SELECT i.owner,
       i.index_name,
       i.table_owner,
       i.table_name,
       i.tablespace_name,
       i.degree,
       i.status,
       i.last_analyzed,
       o.created,
       o.last_ddl_time
  FROM dba_indexes i, dba_objects o
 WHERE     i.index_name = o.object_name
       AND i.owner = o.owner
       AND i.index_name IN
              upper('idx_table1_n2');
"


CURRENT_DIR=$(cd `dirname $0`; pwd)
LOG_FILE=${CURRENT_DIR}/sql_exec_$(date +"%Y%m%d_%H%M%S").log.$dbuser
cat /dev/null > $LOG_FILE


display_all_db()
{
for(( i=0;i<${#array_name2[@]};i++)) do
#${#array_name[@]}获取数组长度用于循环
echo ${array_name1[i]}"-"${array_name2[i]#*@}| tee -a $LOG_FILE
done;
}

##----------------遍历所有厂区数据库，并执行sql语句
f_con_exe_sql()
{
echo "------------------------------"| tee -a $LOG_FILE
echo ""|tee -a $LOG_FILE
echo "遍历所有数据库，并执行sql语句.."| tee -a $LOG_FILE
for(( i=0;i<${#array_name2[@]};i++)) 
do
#${#array_name[@]}获取数组长度用于循环
echo "#${i} 数据库-" ${array_name1[i]}"-"${array_name2[i]#*@}| tee -a $LOG_FILE
echo " "|tee -a $LOG_FILE
echo "执行sql： $sql"| tee -a $LOG_FILE
#echo "sqlplus -S ${dbuser}/${pwd}${array_name2[i]}"
echo " "|tee -a $LOG_FILE
sqlplus -S ${array_name2[i]} <<EOF | tee -a $LOG_FILE
$sql
EOF
echo " "|tee -a $LOG_FILE
done;
echo "执行结果: $LOG_FILE " | tee -a  $LOG_FILE
}




#####--------------------------------man--------------------------------#######

echo "------------------------------"| tee -a $LOG_FILE
display_all_db

f_con_exe_sql