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.
EDIT – wordpress.com just will not properly format the script code, so its uploaded in this .doc file.
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.
please help me to get the information of my oracle memory.