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,
(a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type,
a.ksppdesc description
sys.x$ksppi a,
sys.x$ksppcv b
a.indx = b.indx
and a.ksppinm like '\_%' escape '\'
and a.ksppinm='_b_tree_bitmap_plans'
order by name;

-------------------- ------- ------- ------- -----------------
_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.


3 Responses to See initialization parameters from memory

  1. Martin Nash February 28, 2009 at 3:53 pm #

    Hi there,

    I just stumbled across you page and noticed what I believe to be syntax error. The first three commands you list, shouldn’t they read “create from …”?


  2. Martin Nash February 28, 2009 at 3:55 pm #

    That should have read “create X from…” where X is spfile or pfile, with or without file specifier.

    • ittichai February 28, 2009 at 4:55 pm #


      You’re correct! I’ve made correction already.

      Thank you very much for bringing it to my attention.

      BTW, welcome your blog at to Oracle blogging community.


Leave a Reply

Powered by WordPress. Designed by WooThemes