Scripts for setting the environment for Oracle Database Multitenant

When working on shared database servers with several Oracle Container Databases installed in different homes and different versions it can be hard to find the pdb you are looking for quickly. We used a shell script to set the environment and I added a DB query function to display the PDBS. Example output: Valid Oracle SIDs are: DBSXX1 SGA: 4900 MB PGA: 1350 MB DBROLE: PRIMARY DBXXX2 SGA: 4900 MB PGA: 1380 MB DBROLE: PHYSICAL STANDBY CDBXX001 [ PDB$SEED PDBXXX1 PDBXXX2 ...] SGA: 64000 MB PGA: 16777 MB DBROLE: PRIMARY CDBXX002 [ PDB$SEED PDBXXX1 PDBXXX2 ...] SGA: 64000 MB PGA: 4443 MB DBROLE: PRIMARY CDBXX003 [ PDB$SEED PDBXXX1 PDBXXX2 ...] SGA: 32000 MB PGA: 11211 MB DBROLE: PRIMARY CDBXX004 [ PDB$SEED PDBXXX1 PDBXXX2 ...] SGA: 32000 MB PGA: 1111 MB DBROLE: PRIMARY CDBXX005: Database offline ... Enter the Oracle SID you require (default: CDBXX001): Oracle SID is now CDBXX001, Oracle Home is /u01/app/oracle/product/19000/) The problem is the performance of the script. The query causes a delay. Are there better solutions to set the environment/display for oracle multitenant ? Improvement ideas? Full script: if [ -t 0 ] function query_db() { export ORACLE_SID=$1 export ORACLE_HOME=grep "^${ORACLE_SID}:" /etc/oratab|cut -d: -f2 -s sqlplus -s / as sysdba<cat /etc/oratab|grep -v "^#"|cut -f1 -d: -s do if [[ $SID == ps -ef | grep -w "ora_smon_$SID" | grep -v grep | cut -d _ -f3 ]] then CDB=$(query_db ${SID} "select cdb from v\$database;") PDBS=$(query_db ${SID} "select name from v\$pdbs;") #DBSIZE=$(query_db ${SID} "select sum (bytes) / 1024/1024/1024 AS SIZE from dba_data_files;") SGA=$(query_db ${SID} "select trunc((sum(value)/1024/1024),-1) SGA from v\$sga;") PGA=$(query_db ${SID} "select trunc((sum(pga_max_mem)/1024/1024),-1) PGA from v\$process;") DBROLE=$(query_db ${SID} "select database_role from v\$database;") if [[ "$CDB" == "YES" ]]; then echo -e "\t\t${SID} "[" "${PDBS}" "]" "SGA:" "${SGA} MB" "PGA:" "${PGA} MB" "DBROLE: " ${DBROLE}" else echo -e "\t\t${SID} "SGA:" "${SGA} MB" "PGA:" "${PGA} MB" "DBROLE: " ${DBROLE} " fi else echo -e "\t\t${SID} : Database offline" fi done DEFAULT=cat /etc/oratab|grep -v "^#"|cut -d: -f1 -s|head -1 echo -e "\nEnter the Oracle SID you require (default: $DEFAULT): \c" read ANSWER [ "${ANSWER}" = "" ] && ANSWER=$DEFAULT export ORACLE_SID=grep "^${ANSWER}:" /etc/oratab|cut -d: -f1 -s export ORACLE_HOME=grep "^${ANSWER}:" /etc/oratab|cut -d: -f2 -s export AGENT_HOME="u01/app/oracle/product/emagent/agent_inst/" export OH=$ORACLE_HOME export DIAG="/u01/app/oracle/product/diag/rdbms/"${ORACLE_SID,,}"/"${ORACLE_SID^^}"/trace" export DIAGS="/u01/app/oracle/product/diag/rdbms/"${ORACLE_SID,,}s"/"${ORACLE_SID^^}"/trace" export TNS_ADMIN="/u01/app/oracle/product/network/admin" NEW_VER=cat /etc/oratab | grep ^\echo ${ORACLE_SID}:\ | cut -d'/' -f 5 export PATH=echo $PATH | sed "s@/u01/app/oracle/[a-z]*/[0-9]*/[a-z0-9_]*@$ORACLE_HOME@g" export LD_LIBRARY_PATH=echo ${LD_LIBRARY_PATH} | sed "s@/u01/app/oracle/[a-z]*/[0-9]*/[a-z0-9_]*@$ORACLE_HOME@g" if [ "${ORACLE_SID}" = "" ] then echo -e "\n\n ${ANSWER} : Invalid Oracle SID \c" sleep 2 fi done else # Set to first entry in oratab export ORACLE_SID=cat /etc/oratab|grep -v "^#"|cut -d: -f1 -s|head -1 export ORACLE_HOME=cat /etc/oratab|grep -v "^#"|cut -d: -f2 -s|head -1 export ORACLE_SID=grep "^${ANSWER}:" /etc/oratab|cut -d: -f1 -s export ORACLE_HOME=grep "^${ANSWER}:" /etc/oratab|cut -d: -f2 -s NEW_VER=cat /etc/oratab | grep ^\echo ${ORACLE_SID}:\ | cut -d'/' -f 5 export PATH=echo ${PATH} | sed "s@/u01/app/oracle/[a-z]*/[0-9]*/[a-z0-9_]*@$ORACLE_HOME@g" export LD_LIBRARY_PATH=echo ${LD_LIBRARY_PATH} | sed "s@/u01/app/oracle/[a-z]*/[0-9]*/[a-z0-9_]*@$ORACLE_HOME@g" export OH=$ORACLE_HOME export AGENT_HOME="/u01/app/oracle/product/emagent/agent_inst/" export DIAG="/u01/app/oracle/product/diag/rdbms/"${ORACLE_SID,,}"/"${ORACLE_SID^^}"/trace" export DIAGS="/u01/app/oracle/product/diag/rdbms/"${ORACLE_SID,,}s"/"${ORACLE_SID^^}"/trace" export TNS_ADMIN="/u01/app/oracle/product/network/admin" fi echo echo Oracle SID is now tput rev$ORACLE_SIDtput rmso, Oracle Home is tput rev$ORACLE_HOMEtput rmso echo