v$parameter视图中查询参数的时候其实都是通过x$ksppi和x$ksppcv这两个内部视图中得到的。
可以通过如下方式查询当前实例的所有隐含参数：
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'modified',4,'system_mod','false') ismod,
decode(bitand(y.ksppstvf,2),2,'true','false') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('instance') and
y.inst_id = userenv('instance') and
x.indx = y.indx
order by
translate(x.ksppinm, ' _', ' ')
/
修改隐含参数的方法：alter system set _cursor_bind_capture_interval=5 （记得加引号）
-----下面部分给出了如何知道隐含参数查询的方法-------
我们在查看内存参数时会看到如下执行计划：
sql> set autotrace traceonly
sql> select * from v$parameter;
342 rows selected.
execution plan
----------------------------------------------------------
plan hash value: 1128103955
------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
------------------------------------------------------------------------------
| 0 | select statement | | 1 | 4414 | 1 (100)| 00:00:01 |
|* 1 | hash join | | 1 | 4414 | 1 (100)| 00:00:01 |
|* 2 | fixed table full| x$ksppi | 1 | 249 | 0 (0)| 00:00:01 |
| 3 | fixed table full| x$ksppcv | 100 | 406k| 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - access(x.indx=y.indx)
filter(translate(ksppinm,'_','#') not like '#%' or
ksppstdf='false' or bitand(ksppstvf,5)>0)
2 - filter(x.inst_id=userenv('instance') and
bitand(ksppiflg,268435456)=0 and translate(ksppinm,'_','#') not
like '##%')
statistics
----------------------------------------------------------
64 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
35905 bytes sent via sql*net to client
661 bytes received via sql*net from client
24 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
342 rows processed
sql> desc x$ksppi;
name null? type
----------------------------------------------------- -------- ------------------------------------
addr raw(4)
indx number
inst_id number
ksppinm varchar2(80)
ksppity number
ksppdesc varchar2(255)
ksppiflg number
ksppilrmflg number
ksppihash number
sql> desc x$ksppcv
name null? type
----------------------------------------------------- -------- ------------------------------------
addr raw(4)
indx number
inst_id number
ksppstvl varchar2(4000)
ksppstdvl varchar2(4000)
ksppstdf varchar2(9)
ksppstvf number
ksppstcmnt varchar2(255)
由此可见，可以通过下面方法查询所有隐含参数
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'modified',4,'system_mod','false') ismod,
decode(bitand(y.ksppstvf,2),2,'true','false') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('instance') and
y.inst_id = userenv('instance') and
x.indx = y.indx
order by
translate(x.ksppinm, ' _', ' ')
/