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.