April 1999
Intermediate to advanced
552 pages
15h 39m
English
--------------------------------------------------------------------------- -- Filename: latent.sql -- Purpose: Lists outstanding transactions by destination. -- Author: Chas. Dye (cdye@excitecorp.com) -- Date: 09-Jul-1996 --------------------------------------------------------------------------- col dblink heading "Destination" format a16 col earliest heading "Least Recently|Queued Transaction" format a20 col latest heading "Most Recently|Queued Transaction" format a20 col out heading "Total|Txns|Queued" format 999,999 col timenow heading "Current|Time" format a8 col latency heading "Maximum|Latency|dd:hh:mi:ss" format a12 clear breaks clear computes set head off set feedback off select 'Propagation Latency Instance: '||name||'. Time: ' || to_char(sysdate, 'DD-Mon-YY HH24:mi:ss') from v$database / set head on set feedback on compute sum of out on report break on report skip 1 SELECT d.dblink, min(t.start_time) earliest, max(t.start_time) latest, count(*) out, ltrim(to_char(trunc( sysdate - ( min(start_time)) ), '09')) || ':' || ltrim(to_char(trunc(24*((sysdate-min(start_time)) - trunc(sysdate-min(start_time)))), '09'))||':' || ltrim(to_char(mod(trunc(1440*((sysdate-min(start_time)) - trunc(sysdate-min(start_time)))), 60), '09')) ||':' || ltrim(to_char(mod(trunc(86400*((sysdate-min(start_time)) - trunc(sysdate-min(start_time)))), 60), '09')) latency FROM deftrandest d, deftran t WHERE d.deferred_tran_id = t.deferred_tran_id AND d.delivery_order = t.delivery_order GROUP ...