【隐含参数】oracle隐含参数查看

1、查看数据库所有隐含参数

set pages 10000 lines 180
col name for a60;
col value for a15;
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, ' _', ' ');

2、查看指定隐含参数，在上面SQL的基础上添加条件x.ksppinm即可

set pages 10000 lines 180
col name for a60;
col value for a15;
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
and x.ksppinm in ('deferred_segment_creation',
'_optimizer_use_feedback',
'_undo_autotune',
'_serial_direct_read',
'_datafile_write_errors_crash_instance',
'_gc_defer_time',
'_sql_plan_directive_mgmt_control',
'_optimizer_dsdir_usage_control',
'_use_single_log_writer')
order by translate(x.ksppinm, ' _', ' ');