SQL Scripts‎ > ‎N-R‎ > ‎

para_diff

set echo off
------------------------------------------------------
-- @name: para_diff
-- @author: Dion Cho
-- @description
-- compare parameter change (using db link)
-- prerequisite
-- 1) create db link
-- 2) execute run_by_sys on each db

-- usage: @para_diff target_db_link [parameter name]
------------------------------------------------------

col name format a25
col m_value format a10
col y_value format a10
col description format a25
col mch format a3

define TARGET_DB = &1;
define P_NAME = &2;

with me as (
    select ksppinm name,
       ksppstvl value,
       decode(bitand(ksppiflg/256,1),1,'true','false') ses_modifiable,
       decode(bitand(ksppiflg/65536,3),1,'immediate',2,'deferred',3,'immediate','false') sys_modifiable,
       ksppdesc description
    from sys.xm$ksppi i, sys.xm$ksppcv v
    where i.indx = v.indx and ksppinm like '%&P_NAME%'
),
  you as (
    select ksppinm name,
       ksppstvl value,
       decode(bitand(ksppiflg/256,1),1,'true','false') ses_modifiable,
       decode(bitand(ksppiflg/65536,3),1,'immediate',2,'deferred',3,'immediate','false') sys_modifiable,
       ksppdesc description
    from sys.xm$ksppi@&TARGET_DB i, sys.xm$ksppcv@&TARGET_DB v
    where i.indx = v.indx and ksppinm like '%&P_NAME%'
)
select
  case when m.value = y.value then 'O'
       else 'X'
       end as mch,
  nvl(m.name, y.name) as name,
  m.value as m_value, y.value as y_value,
  nvl(m.description, y.description) as description
from
  me m full outer join you y
on
  m.name = y.name
order by name
;

set echo on

Comments