Re: How to calculate time using SQL

"Nguyen, David M" wrote:
>
> Jack,
>
> How about substracting a system date with a database replication date to get
> difference of time? Can you please give me a specific command?
>
> SQL> select to_char(sysdate, 'Dy Mon Dd HH24:MI:SS YYYY') from dual;
>
> TO_CHAR(SYSDATE,'DYMONDD
> ------------------------
> Mon Apr 01 11:08:00 2002 ==================> System Time.
>
> SQL> select next_date from user_refresh;
>
> NEXT_DATE
> ------------------------
> Mon Apr 01 11:11:01 2002 ==============> Replication time.
>
> Thanks,
> David
>

David,

   If what you are after is measuring the lag between two replicated databases, you may have to do something slightly more complicated. For one thing, do not forget that system time is, most often that note, time read by the system-engineer on his wrist-watch the last time he set the date. If your machines do not take the time from a common source, you may get a pessimistic or optimistic view, depending on the machine you are looking at. I think that it's better to look how long it takes to replicate from A to B and back - it will allow you to compare a A timestamp to another A timestamp. Dividing by 2 will give you a fair if not absolutely correct assessment of how long it takes to go from A to B or B to A.
 Also, a good way to check how replication goes is to create a dummy table with three columns, A_TIME_START, B_TIME and A_TIME_FINISH, to store (on A) SYSDATE into A_TIME_START, to let replicate and have triggers inserting SYSDATE into the ad hoc column everytime it reaches a node.

HTH, Stephane Faroult
Oriole Software

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).