Re: Current Timestamp over DB Link
- From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
- To: elmaris@xxxxxxxxx
- Date: Sat, 21 Jun 2014 13:14:05 -0700
What about some existing function like this one? (You'd need to grant exec
access to it and as it's a SQLTUNE one, it probably needs tuning pack
license too):
SQL> SELECT sysdate, sys.dbms_sqltune_util1.get_current_time local_time,
sys.dbms_sqltune_util1.get_current_time@sol121 remote_time FROM dual;
SYSDATE LOCAL_TIME REMOTE_TIME
----------------- ----------------- -----------------
20140621 22:41:36 20140621 22:41:35 20140617 13:52:36
But there may be more existing functions like this one out there...
(interestingly the local_time and local sysdate are occasionally 1 second
off, I guess a rounding difference).
Btw, whatever pre-existing function you'd end up using, it's worth testing
it with sql_trace, to make sure it doesn't do any more stuff in the DB
under the hood. I haven't tested if this one has any side-effects.
Tanel
On Wed, Jun 18, 2014 at 6:07 AM, Maris Elsins <elmaris@xxxxxxxxx> wrote:
> Hi,
>
>
> > select systimestamp, systimestamp@remotedb from dual
> doesn't work because of ERROR ORA-00923: FROM keyword not found where
> expected (and the pointer is at "@" sign)
>
> > create view ...
> I can't create objects in databases
>
>
> >
> https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:50128786135391
> this could work, but it's so complicated, I thought something more simple
> was possible, but looks like it's not.
>
> ---
> Maris Elsins
> @MarisElsins <https://twitter.com/MarisElsins>
> www.facebook.com/maris.elsins
>
>
>
>
> On Wed, Jun 18, 2014 at 3:49 PM, Nigel Thomas <
> nigel.cl.thomas@xxxxxxxxxxxxxx> wrote:
>
>> Maris
>>
>> The optimiser selects a row from the remote table but executes the
>> function systimestamp locally.
>>
>> You can either:
>>
>> select systimestamp, systimestamp@remotedb from dual
>>
>>
>> or create a view on the remote database:
>>
>> create view vtime as select systimestamp remote_time from dual;
>>
>>
>> and then select from it:
>>
>> select systimestamp local_time, remote_time from vtime@remotedb
>>
>>
>>
>> HTH Nigel
>>
>>
>> On 18 June 2014 13:02, Maris Elsins <elmaris@xxxxxxxxx> wrote:
>>
>>> HI All,
>>>
>>> I'm trying to compare the local and remote timestamps to assess the time
>>> offset between 2 DBs, but this query gives the local timestamp only:
>>>
>>> SQL> select systimestamp local_time, (select systimestamp from
>>> dual@REMOTE_DB) remote_time from dual;
>>>
>>> LOCAL_TIME
>>>
>>> ---------------------------------------------------------------------------
>>> REMOTE_TIME
>>>
>>> ---------------------------------------------------------------------------
>>> 18-JUN-14 07.00.06.611471 AM -05:00
>>> 18-JUN-14 07.00.06.611471 AM -05:00
>>>
>>> Can anyone suggest a way to retrieve the current timestamp from a remote
>>> DB over DB Link?
>>>
>>> ---
>>> Maris Elsins
>>> @MarisElsins <https://twitter.com/MarisElsins>
>>> www.facebook.com/maris.elsins
>>>
>>>
>>>
>>
>
Other related posts:
