Simple Loop Script

The following are example of simple scripts that loop thru running databases on a server


sqlplus -s / as sysdba <<EOF
set pages 0 lines 2000 feedback off
col sid format a12
col name format a15
col value format a64
col SID_COL_PLUS_SHOW_SPPARAM format a12
col SID_COL_PLUS_SHOW_PARAM format a12
column VALUE_COL_PLUS_SHOW_PARAM format A64
column VALUE_COL_PLUS_SHOW_SPPARAM format A64
SELECT version FROM V\$INSTANCE;
!echo show parameter output
show parameter local_listener
!echo show spparameter output
show spparameter local_listener
!echo checking parameter output
select inst_id, name, value from gv\$parameter where name = 'local_listener' order by 1;
!echo checking spparameter output
select inst_id, sid, name, value from gv\$spparameter where name = 'local_listener' order by 1;
EOF


Example for local spparameter only

for OSID in `ps -ef | grep pmon | grep -v grep | grep -v ASM | grep -v APX | grep -v MGMT | awk '{print $NF}' | cut -c10-20 | sort`
do
export ORAENV_ASK=NO;
export ORACLE_SID=$OSID
. oraenv -s;
export ORAENV_ASK=YES;
echo Logging into $OSID
sqlplus -s / as sysdba <<EOF
set pages 0 lines 2000 feedback off
col sid format a12
col name format a15
col value format a64
col SID_COL_PLUS_SHOW_SPPARAM format a12
col SID_COL_PLUS_SHOW_PARAM format a12
column VALUE_COL_PLUS_SHOW_PARAM format A64
column VALUE_COL_PLUS_SHOW_SPPARAM format A64
!echo show spparameter output
show spparameter local_listener
EOF
done


Example of local parameter only

for OSID in `ps -ef | grep pmon | grep -v grep | grep -v ASM | grep -v APX | grep -v MGMT | awk '{print $NF}' | cut -c10-20 | sort`
do
export ORAENV_ASK=NO;
export ORACLE_SID=$OSID
. oraenv -s;
export ORAENV_ASK=YES;
echo Logging into $OSID
sqlplus -s / as sysdba <<EOF
set pages 0 lines 2000 feedback off
col sid format a12
col name format a15
col value format a64
col SID_COL_PLUS_SHOW_SPPARAM format a12
col SID_COL_PLUS_SHOW_PARAM format a12
column VALUE_COL_PLUS_SHOW_PARAM format A64
column VALUE_COL_PLUS_SHOW_SPPARAM format A64
!echo show parameter output
show parameter local_listener
EOF
done