Andrew Fraser DBA

Script run against every running database, listing total oracle memory (sga+pga)

This shell script runs against every database that is up and running (has an entry in the ps list). Which I think is better than searching through oratab (since not all of those may be up and running, esp. on dev machines).

You can run any SQL within it, but this particular version prints out the total oracle memory (sga and pga) use. That is useful because there is often a risk of more memory being allocated to oracle than actual physical memory on the server. That is especially true of dev servers with many databases running.

export ORAENV_ASK=NO
for ORACLE_SID in `ps -ef|grep pmon|grep -v grep|awk -F_ '{print $3}'`
do
. oraenv
sqlplus -s "/ as sysdba" <<e1
set pages 0 head off feed off
select '$ORACLE_SID sga', sum(value)/1024/1024 mb from v\$sga
/
select '$ORACLE_SID pga', sum(value)/1024/1024 mb from v\$sesstat s, v\$statname n
where s.statistic# = n.statistic#
and n.name = 'session pga memory'
group by '$ORACLE_SID pga'
/
–- select '$ORACLE_SID sga_max_size', value/1024/1024 mb from v\$parameter where name = 'sga_max_size';
–- select '$ORACLE_SID pga_target', value/1024/1024 mb from v\$parameter where name = 'pga_aggregate_target';
e1
done

The results are best pasted into a spreadsheet where there are many databases.

On 9i on Solaris, sga_max_size preallocates the memory at OS leve, making it largely useless, so there would be worth also checking that, with e.g. the commented out line above.

One thing to watch is that to query a v$ or sm$ view direct from a shell script, you have to escape out the $, hence the “v$” above rather than "v$".

Array use in this script (rather than clumsier writing to temporary files) came from Daniel D’Souza.