Script to list modified parameters
All started with dba-willage post when someone asked how to list static and dynamic parameter..
Answer was to select v$parameter table.I made a comment that also oradebug ( love it ) can be used to list dynamically modified parameter ( one of the previous post ) and got the answer that oradebug is useless when ISMODIFIED column or v$parameter is enough..
Since I like internal stuff a posted a query that can do the same but not using oradebug but underluying x$ tables.. and this will list underscore parameters and session or system modified too…
Demo:
SQL> alter session set “_replace_virtual_columns”=true;
Session altered.
SQL> select
2 a.ksppinm name,
3 b.kspftctxvl value,
4 b.kspftctxdf isdef,
5 decode(bitand(b.kspftctxvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,'FALSE’) ismod,
6 decode(bitand(b.kspftctxvf,2),2,’TRUE’,'FALSE’) isadj
7 from
8 sys.x$ksppi a,
9 sys.x$ksppcv2 b
10 where a.inst_id = userenv(‘Instance’) and
11 b.inst_id = userenv(‘Instance’) and
12 a.indx+1 = b.kspftctxpn and
13 decode(bitand(b.kspftctxvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,'FALSE’) <> ‘FALSE’
14 order by
15 translate(a.ksppinm, ‘ _’, ‘ ‘)
16 /
NAME VALUE ISDEF ISMOD ISADJ
——————————————— ——————————————— —— ———- —–
cursor_sharing exact TRUE SYSTEM_MOD FALSE
_replace_virtual_columns TRUE TRUE MODIFIED FALSE
Here is script:
select
a.ksppinm name,
b.kspftctxvl value,
b.kspftctxdf isdef,
decode(bitand(b.kspftctxvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,'FALSE’) ismod,
decode(bitand(b.kspftctxvf,2),2,’TRUE’,'FALSE’) isadj
from
sys.x$ksppi a,
sys.x$ksppcv2 b
where a.inst_id = userenv(‘Instance’) and
b.inst_id = userenv(‘Instance’) and
a.indx+1 = b.kspftctxpn and
decode(bitand(b.kspftctxvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,'FALSE’) <> ‘FALSE’
order by
translate(a.ksppinm, ‘ _’, ‘ ‘)
/
