you might wondering how this will help us? For instance if your environment have multiple of CDBs with Multiple PDBs in each CDB Database then changing dbsnmp password or change database parameters on PDB level is painful tasks so I have written this shell script with loops pdbs databases in CDB level and the sql script on it.

Hope you like it!

#!/bin/bash
# October 2022 Sandeep Reddy Narani
#
# Interactive database picker
# Displays menu of databases defined in oratab then asks which one to use then uses oraenv to set env variables
# Runs sql statements on oracle pdbs and cdbs databases.
# Usage : sh get_runallpdbs.sh | get_runallpdbs.sh <cdb_name>
ALL_DATABASES=`ps -efZ | grep [p]mon | grep -v asm | awk '{print $NF}' | sed s/ora_pmon_// | grep -v / | grep -v "-"`
#ALL_DATABASES=${1}
LOGFILE=dba_list.txt
cd `dirname $0`

function query_db() {
export ORACLE_SID=$1
export ORACLE_HOME=`grep "^${ORACLE_SID}:" /etc/oratab|cut -d: -f2 -s`
sqlplus -s / as sysdba<<SQL
set feedback off
set lines 1000
set pages 0
$2
SQL
}

function pdb_query_db() {
export ORACLE_SID=$1
export ORACLE_HOME=`grep "^${ORACLE_SID}:" /etc/oratab|cut -d: -f2 -s`
sqlplus -s / as sysdba<<SQL
set lines 1000
set pages 0
$2
$3
SQL
}


for DB in $ALL_DATABASES
do
   export ORACLE_SID=$DB
   export ORACLE_HOME=`grep "^${DB}:" /etc/oratab|cut -d: -f2 -s`
   export PATH=$ORACLE_HOME/bin:$PATH
   export rundate=$(date "+%Y%m%d")
   echo "---> Database $ORACLE_SID, using home $ORACLE_HOME"
   if [[ $ORACLE_SID == `ps -ef | grep -w "ora_smon_$ORACLE_SID" | grep -v grep | cut -d _ -f3` ]]
   then
      CDB=$(query_db $ORACLE_SID "select cdb from v\$database;")
      PDBS=$(query_db $ORACLE_SID "select name from v\$pdbs where name not like 'PDB%';")
      echo $ORACLE_SID:${CDB}:${PDBS}
        for PDB in $PDBS
            do
            echo "---> Database $ORACLE_SID: with Pluggable Database : $PDB"
            run=$(pdb_query_db $ORACLE_SID "alter session set container=$PDB;" "alter user dbsnmp identified by dbadeeds account unlock;")
            echo $run
        done
    else 
        echo "something wrong"
    fi
done