Is database using pfile (init.ora), or spfile?
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;
Here is another way.
Not using spfile
OPS$ORACLE@TIGGER > show parameter spfile;
NAME TYPE VALUE
———————————— ———– ——————————
spfile string
OPS$ORACLE@TIGGER >
Using spfile
SYS@RMANPRD> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string /oracle/10gr2/db_1/dbs/spfileR
MANPRD.ora
SYS@RMANPRD>