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 |