Internal parameters

— view oracle internal parameters, note this can be run by SYS user
— name, value, default value, session modifiable, system modifiable, description
select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode(bitand(a.ksppiflg/256,3),1, 'True', 'False') SESSMOD,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') SYSMOD,
a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '\_%' escape '\'
order by name
/

— If SYSMOD = Immediate (The Parameter can be changed with ALTER SYSTEM without restart of th e instance)
— If SYSMOD = DEFERRED (The Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions)
— If SYSMOD = FALSE (The parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances)

Leave a Reply