Very useful SQL, from Tom Kyte:
select decode(count(*), 1, 'spfile', 'pfile' ) from v$spparameter where rownum=1 and isspecified='TRUE' ;
If it it using an spfile, an easy way to see the parameters is to generate an init.ora copy:
SQL> conn / as sysdba
(or connect as sysoper)
SQL> create pfile = '/tmp/init.ora' from spfile;
Thanks to Laurent Schneider for tips on how to handle listener passwords in scripts. [Update 12-Jan-2007 - ammended script following Laurent's comment, and update 09-Feb-2007 - ammended script following Kevin's comment]
A system startup/shutdown shell script I just used that copes with listener passwords is below.
The location of the listener.ora file was /etc in my case, but that can be different in other servers. The script assumes oracle environment variables are all set correctly at login (the “su -”). This is 9i specific – as explained on Laurent Schneider’s blog post, things are different/better with 10g. Also is only designed to work with a single listener with the default name, LISTENER).
For this script to actually run at system startup/shutdown time, it needs to be linked into the /etc/rc*.d directories, at least /etc/rc3.d (the default startup/shutdown level) although I put it in the others also. That is, as root user:
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.