How to select sysdate of remote database

[send2sachin]

I want to fetch the sysdate of a remote database using dblink, I used the following sql :

select sysdate from dual@remote_db_link

But this fetches me local server date and not of the remote one.

Is there a sql to do this, since i can't write procedures in each remote machines which will be called by local sever to do the same task.

Thanks

[jmodic]

Originally posted by send2sachin
I want to fetch the sysdate of a remote database using dblink, I used the following sql :

select sysdate from dual@remote_db_link

But this fetches me local server date and not of the remote one.

Is there a sql to do this, since i can't write procedures in each remote machines which will be called by local sever to do the same task.

Thanks

Darn! This question seemed so trivial at first, yet I have spend couple of hours searching for an answer with no luck! It seems to me that there realy is no other way but to create a function or a view on remote database to get the sysdate of that database over db link.

If anyone finds a direct way to get a remote sysdate without creating any additional object on a remote database, I'd be very interested.

[sree_sri]

Nope!

Only by using function or view, you can retrieve sysdate from remote database.

I found this document in metalink, Give a look..
http://metalink.oracle.com/metalink/...1&p_showHelp=1

Sree.

[DaPi]

(A wild thought born of ignorance) Is there any standard process that can be set off remotely and is recorded in the SYS tables? e.g. ANALYZE dual and look at LAST_ANALYZED?

If this could be done, there would be an error in the time bigger than the network delay - it might be acceptable.

[jmodic]

Don't think so. You can't call a remote procedure over db_link that performs an explicit or implict commit or rollback. And executing ANALYZE or something like that remotely performs implicit commit.....

[DaPi]

. . . so let's look in memory.


a) does anyone know if elapsed time since v$instance.startup_time is stored anywhere?


b) VERY NASTY workaround - far from being bomb-proof:

IF you can uniquely identify the row in v$session (without using SYS_CONTEXT!) that belongs the the current session, e.g. by user & being remote:

 drop database link remote;  -- must be v.recent connection

create database link remote
connect to scott identified by tiger
using 'ORCL';

select to_char(logon_time, 'DD-MON-YYYY HH24:MI:SS') 
from v$session@remote
where osuser = 'NT AUTHORITY\SYSTEM'  -- under NT this is a remote connection
and   username = 'SCOTT';

It's a knotty problem . . .