committed row insert doesn't show first time...

[Purcell, Sandra]

 two oracle 8174 databases on one aix 4.3.3 server
 
database1 = CMS
database2 = HAT
 
 
 
In database2 (HAT)
 
insert a row into table card_status_log on hat
 
--The insert is happening directly in database2 from a
--direct sqlplus connect to the hat database.
--no link involved with insert
 
commit;
 
successful
no errors
-------------------
 

from database1 (CMS)
--Distributed query run via sqlplus session on CMS:
SELECT
card_id,
       card_status_id,
       cardtype_cd,
       cardstatus_cd,
       card_status_reason_cd,
       status_date,
       user_id
FROM hat_card_status_log (synonym over a link to card_status_log table in
database2)
where card_id = 302
UNION
SELECT
       card_id,
       card_status_id,
       cardtype_cd,
       cardstatus_cd,
       card_status_reason_cd,
       status_date,
       user_id
FROM card_outline_status_log  (table in database1)
where card_id = 302;
 
open a sqlplus session from database1 (CMS), query for newly inserted row
from
initial insert above
 
new row does not show
 
rerun query for new row from same sqlplus session in database1
 
newly inserted row shows
 
--Remote query
running first part of the query (select to database2 without union)
shows newly created row on first attempt
 
***************************************************
work around... add hint, qualify object names:
 

SELECT
/*+ DRIVING_SITE(hcsl) */
hcsl.card_id,
       hcsl.card_status_id,
       hcsl.cardtype_cd,
       hcsl.cardstatus_cd,
       hcsl.card_status_reason_cd,
       hcsl.status_date,
       hcsl.user_id
FROM hat_card_status_log hcsl
where hcsl.card_id = 302
UNION
SELECT
       csl.card_id,
       csl.card_status_id,
       csl.cardtype_cd,
       csl.cardstatus_cd,
       csl.card_status_reason_cd,
       csl.status_date,
       csl.user_id
FROM card_outline_status_log csl
where csl.card_id = 302;
 
open sqlplus session
 
query for newly inserted row from database1 (CMS), shows with initial query
 
 
 
why doesn't the first distributed query work on initial execution?
 
thanks
 
sandy, long time lurker

<<Blank Bkgrd.gif>>