-
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
-
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?
-
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
-
(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.
-
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?
-
. . . 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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|