OraFAQ Forum: SQL & PL/SQL » Getting Hostname,systimestamp over db link

Home » SQL & PL/SQL » SQL & PL/SQL » Getting Hostname,systimestamp over db link

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Getting Hostname,systimestamp over db link [message #599601]

Sat, 26 October 2013 13:39

sekharsomu
Messages: 72
Registered: December 2008

Member

Hi,

Ihave a staging which connects to RAC clusters via dblink

I have a scenario to collect the hostname,systimestamp whenever i do a query from this rac clusters

So in general below would be cool

Code: [Select all] [Show/ hide]

select UTL_INADDR.GET_HOST_NAME,sysdate,x,y,z from gv$symmetric

would work perfect

But on dblink..

Code: [Select all] [Show/ hide]

select UTL_INADDR.GET_HOST_NAME,sysdate,x,y,z from gv$symmetric@dblink

is returning local timestamp and hostname which is not intended

So i even tried subquery..

Code: [Select all] [Show/ hide]

select (select host_name,systimestamp from v$instance),t UTL_INADDR.GET_HOST_NAME,sysdate,x,y,z from gv$symmetric@dblink

this would work in certain case

But it certain cases it would throw an error saying mutiple rows from asubquery .

All ineed is to get hostname,systimestamp append to all sql i fetch from any this rac clusters through this dblinks

Please guide me through on this

Thanks,
Somu

Note: x,y,z are dummy columns for representation

[Updated on: Sat, 26 October 2013 13:42]

Report message to a moderator

Re: Getting Hostname,systimestamp over db link [message #599602 is a reply to message #599601]

Sat, 26 October 2013 13:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT HOST_NAME FROM V$INSTANCE@DBLINK;

Report message to a moderator

Re: Getting Hostname,systimestamp over db link [message #599605 is a reply to message #599602]

Sat, 26 October 2013 14:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

from http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:50128786135391

Code: [Select all] [Show/ hide]

create or replace function test_remote_sysdate
return date
as
   l_cursor    int;
   l_status    int;
   l_remote_sysdate date;
begin

    l_cursor := dbms_sql.open_cursor@REMOTE_DB;
    
    dbms_sql.parse@REMOTE_DB
        ( l_cursor,
          'begin :x := sysdate; end;',
          dbms_sql.native
        );
        
    dbms_sql.bind_variable@REMOTE_DB
       ( l_cursor, ':x', l_remote_sysdate );

    l_status := dbms_sql.execute@REMOTE_DB
       ( l_cursor );

    dbms_sql.variable_value@REMOTE_DB
       (l_cursor, ':x', l_remote_sysdate );
    
    dbms_sql.close_cursor@REMOTE_DB
       (l_cursor);

    return l_remote_sysdate;
end;
/

Report message to a moderator

Re: Getting Hostname,systimestamp over db link [message #599606 is a reply to message #599602]

Sat, 26 October 2013 14:04

sekharsomu
Messages: 72
Registered: December 2008

Member

no my issue is like this is in need to insert into tables at staging

where columns would be like hostname,systime,and other colums that are expected

for any sql query i get i need to append systimestamp and time stamp

for example :

Code: [Select all] [Show/ hide]

select UTL_INADDR.GET_HOST_NAME, systimestamp, metric_name, value, metric_unit from gv$sysmetric where metric_name like'%CPU%' and group_id=2;

would work perfectly for me

in dblink on rac say 4node

Code: [Select all] [Show/ hide]

select UTL_INADDR.GET_HOST_NAME, systimestamp, metric_name, value, metric_unit from gv$sysmetric@dblink  where metric_name like'%CPU%' and group_id=2;

this would output only local hostname,local time stamp instead of remote .

Making it difficult for me to know on which host this value came from.

But,thanks for the blazing fast reply.@blackswan

Thanks,
Somu

[Updated on: Sat, 26 October 2013 14:10]

Report message to a moderator

Re: Getting Hostname,systimestamp over db link [message #599607 is a reply to message #599606]

Sat, 26 October 2013 14:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I showed how to obtain the desired details so your only challenge is to stitch together a solution using the separate pieces.

you can lead some folks to knowledge, but you can not make them think.

Report message to a moderator

Re: Getting Hostname,systimestamp over db link [message #599608 is a reply to message #599607]

Sat, 26 October 2013 14:12

sekharsomu
Messages: 72
Registered: December 2008

Member

ok got it i didnt see your second post before i posted the above .

Thanks for the help though

--Somu

Report message to a moderator

Re: Getting Hostname,systimestamp over db link [message #599609 is a reply to message #599608]

Sat, 26 October 2013 14:16

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Code: [Select all] [Show/ hide]

select UTL_INADDR.GET_HOST_NAME@dblink from dual;

Unfortunately, you would need a remote function to get remote systimestamp.

SY.

Report message to a moderator

Re: Getting Hostname,systimestamp over db link [message #599612 is a reply to message #599609]

Sat, 26 October 2013 14:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>you would need a remote function to get remote systimestamp.
or a local function which does remote access as I had previously posted in this thread.

Report message to a moderator

Re: Getting Hostname,systimestamp over db link [message #599617 is a reply to message #599612]

Sat, 26 October 2013 16:20

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

> or a local function which does remote access as I had previously posted in this thread.

You could, if remote username in database link has execute on DBMS_SQL.

SY.

Report message to a moderator

Previous Topic:	plsql
Next Topic:	EMP and DEPT Tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 28 03:14:25 CDT 2025