오라클 히든파라미터 조회 방법

OS환경 : Oracle Linux6.8(64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

쿼리 : 오라클 히든파라미터 조회 방법

조회 쿼리

SQL>
col KSPPSTVL for a10
col KSPPINM for a20
SELECT KSPPINM, KSPPSTVL
FROM X$KSPPI X, X$KSPPCV Y
WHERE X.INDX = Y.INDX
AND X.KSPPINM LIKE '%in_memory_undo%' 
AND SUBSTR(X.KSPPINM, 1, 1) = '_'
/
 
KSPPINM          KSPPSTVL
-------------------- ----------
_in_memory_undo      TRUE

AND X.KSPPINM LIKE '%in_memory_undo%'  -- 이부분에 히든파라미터 명 입력

 

 

default 값까지 확인

SQL>
set lines 200 pages 1000
col desc for a70
col name for a40
col current_value for a15
col default_value for a15
col default_t_f for a15
select
ksppinm "name",
ksppdesc "desc",
ksppstvl "current_value",
b.ksppstdfl "default_value",
b.ksppstdf "default_t_f"
from x$ksppi a, x$ksppcv b
where 1=1
and a.indx=b.indx
AND SUBSTR(a.KSPPINM, 1, 1) = '_'
and a.ksppinm like '%_rowsource_execution_statistics%' 
order by 1;
 
name                                     desc                                                                   current_value   default_value   default_t_f
---------------------------------------- ---------------------------------------------------------------------- --------------- --------------- ---------------
_rowsource_execution_statistics          if TRUE, Oracle will collect rowsource statistics                      FALSE           FALSE           TRUE

 

 

참조 : https://community.oracle.com/mosc/discussion/4060749/hidden-parameters-default-value