DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How to select sysdate of remote database

  1. #1
    Join Date
    Nov 2003
    Posts
    1

    How to select sysdate of remote database

    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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: How to select sysdate of remote database

    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Feb 2001
    Posts
    203
    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.
    sree

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    (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.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    . . . 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:

    Code:
     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 . . .
    Last edited by DaPi; 11-11-2003 at 04:40 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width