See initialization parameters from memory

One of Oracle 11g’s new features is an ease of capturing the initialization parameter values currently in memory into a file.

create pfile from memory;

create spfile from memory;

create pfile='/tmp/cur_pfile.ora' from memory;

This comes in handy when you’d like to quickly verify changes especially after executing “alter system set” with scope=memory.

It also lets you see the current values of memory allocation in the Automatic Memory Management (AMM). Note that those values are at the top of the list prefixing with “__” (double underscores).

In addition to that it allows you peek into the current values of some hidden parameters too. This is a good alternative to having to run a query like below; Sample below is looking for a value of the “_b_tree_bitmap_plans” parameter. I got this code from Coskan’s blog.

col name format a20
col value format a7
col deflt format a7
col type format a7
col description format a10

select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode
(a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and a.ksppinm like '\_%' escape '\'
and a.ksppinm='_b_tree_bitmap_plans'
order by name;

NAME                 VALUE   DEFLT   TYPE    DESCRIPTION
-------------------- ------- ------- ------- -----------------
_b_tree_bitmap_plans TRUE    TRUE    boolean enable the use of
                                              bitmap plans for
					     tables w. only B-
                                             tree indexes

Note that the output of “select pfile from memory” does not show all initialization values – just those currently in memory. If you’re interested in finding out, the orafaq web site has the complete list of all initialization parameters including hidden ones of all versions as early as version 7.3.4.