Oraclue

Oracle internals, debugging and undocumented features

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, ‘ _’, ‘ ‘)
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: