分享一个小shell

[oracle@vm1 dbmonitor]$ sh t.sh
======================================================================
==              SINGLE SQL IN SINGLE DATABASE                       ==
==                   AUTHOR:ZHANGYU                                 ==
==     demo:selected sql==>selected database==>results              ==
======================================================================


SETP1:WE HAVE QUERY SCRIPTS FOLLOWING,PLEASE SELECT ONE SQL YOU WANTED.


 1) actsess.sql           20) job.sql
 2) asmdg.sql           21) parameter.sql
 3) autotask.sql         22) profile.sql
 4) awrsetting.sql         23) recyclebin.sql
 5) bchit.sql           24) redofile.sql
 6) controlfile.sql         25) registry_history.sql
 7) cursorusage.sql         26) registry.sql
 8) database.sql         27) rmanjob.sql
 9) datafile.sql         28) rman.sql
10) dblink.sql           29) scndays.sql
11) dboption.sql         30) sequence.sql
12) dbproperty.sql         31) sessusage.sql
13) dbtime.sql           32) tablespace.sql
14) directory.sql         33) userdata.sql
15) grant.sql           34) user_roles.sql
16) info_database_summary.sql  35) usersegsum.sql
17) instance.sql         36) users.sql
18) invalididx.sql         37) utinsys.sql
19) invalidobj.sql
please select a num from menu：17


Selected sql_statement: [ instance.sql ]


STEP2:WE HAVE AVALIABLE DATABASES FOLLOWING,PLEASE SELECT ONE DATABASE YOU WANTED.


1) 192.168.56.101:1511/ora11
2) 192.168.56.101:1511/ora11a
please select a num from menu：1


Selected Database: [ 192.168.56.101:1511/ora11 ]


STEP3:THE RESULTS AS FOLLOWING.




DB_NAME       CHK_DATE  INSTANCE_NAME    INSTANCE_NUMBER VERSION         PAR HOST_NAME                  STARTUP_T    UPTIME STATUS
------------------------- --------- ---------------- --------------- ----------------- --- ---------------------------------------------------------------- --------- ---------- ------------
192.168.56.101:1511/ora11 06-NOV-22 ora11         1 11.2.0.4.0        NO  vm1                    05-NOV-22        .3 OPEN

############################################################################
#author:           zhangyu
#version:          V1.0
#production        godba studio           
#last modified     2022-10-28
############################################################################


#!/bin/bash
echo "======================================================================"
echo "==              SINGLE SQL IN SINGLE DATABASE                       =="
echo "==                   AUTHOR:ZHANGYU                                 =="
echo "==     demo:selected sql==>selected database==>results              =="
echo "======================================================================"
sleep 1
echo 


#set envirments
dbc_dir=/home/oracle/dbmonitor/dbs
sql_dir=/home/oracle/dbmonitor/sqls
res_dir=/home/oracle/dbmonitor/res
chk_date=`date +%Y%m%d_%H%m`
username=dbsnmp
password=dbsnmp
ORACLE_HOME=/home/oracle/dbmonitor/instantclient_19_17
PATH=/home/oracle/dbmonitor/instantclient_19_17:$PATH
LD_LIBRARY_PATH=/home/oracle/dbmonitor/instantclient_19_17
PS3="please select a num from menu："


#get username if not configuared
#read -r -p "please enter username:" username
#get password if not configuared
#read -r -s -p "please enter password:" password


# check user is oracle or not
[ $USER != 'oracle' ] && echo "please run the script with oracle user! " && exit 1


# check sqlplus is available
sqlplus -v>/dev/null
[ $? -ne 0 ] && echo -e "\033[31mERROR! sqlplus not found.\033[0m" && exit 1


# check sqlplus version >12 or not
client_version=`sqlplus -v|awk '{print $3}'|awk -F[.] '{print $1}'`
[ $client_version -lt 12 ] && echo -e "\033[31mERROR! sqlplus version must equal to or greater than 12.2.\033[0m" && exit 1
  
#LOAD SECRIPTS
echo -e "\033[31mSETP1:WE HAVE QUERY SCRIPTS FOLLOWING,PLEASE SELECT ONE SQL YOU WANTED.\033[0m"
echo 
select sql_stmt in $( ls $sql_dir )
  do
          export SQL_STMT=$sql_stmt
          echo 
          printf "`echo "Selected sql_statement: ["` `echo -e "\033[33;5m${SQL_STMT}\033[0m"` `echo "]"`\n"
          echo
          break 
  done


#LOAD DATABASE CONNECT STRINGS
echo -e "\033[31mSTEP2:WE HAVE AVALIABLE DATABASES FOLLOWING,PLEASE SELECT ONE DATABASE YOU WANTED.\033[0m"
echo 
select db_con in $( cat $dbc_dir/db_cons |awk -F [@] '{print $2}' )
  do
           export DN=$db_con
           echo 
           printf "`echo "Selected Database: ["` `echo -e "\033[33;5m${DN}\033[0m"` `echo "]"`\n"
           echo
           break
  done


#print results of items as selected above   
echo -e "\033[31mSTEP3:THE RESULTS AS FOLLOWING.\033[0m"
echo
sqlplus -S $username/$password@$DN<<EOF
SET LINESIZE 300
SET PAGESIZE 2000
SET VERIFY OFF
SET ECHO OFF
define dn=$DN
@$sql_dir/$SQL_STMT 
exit;
EOF
exit