OraFAQ Forum: SQL & PL/SQL » get the remote DB instance name

Home » SQL & PL/SQL » SQL & PL/SQL » get the remote DB instance name (Oracle 9i Release2 , HP UNIX)

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

get the remote DB instance name [message #643905]

Wed, 21 October 2015 03:30

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

Hi,
CODE :

select sys_context('USERENV','DB_NAME') as Instance from dual@DBLINK_NAME;

//

This query returning the local DB NAME not the remote DB name.

I want the remote DB name ,what modification required ?
Thanks.

Report message to a moderator

Re: get the remote DB instance name [message #643906 is a reply to message #643905]

Wed, 21 October 2015 03:42

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Either your remote usee has the SELECT privilege on v$instance and you can use:

select instance_name from v$instance@DBLINK_NAME;

Either you create a remote procedure to return the instance and grant execute privilege to your db link remote user and call it like:

select get_instance@DBLINK_NAME from dual;

[Updated on: Wed, 21 October 2015 03:42]

Report message to a moderator

Re: get the remote DB instance name [message #643908 is a reply to message #643905]

Wed, 21 October 2015 03:48

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

SQL> SELECT * FROM global_name@<dblink_name>;

Report message to a moderator

Re: get the remote DB instance name [message #643911 is a reply to message #643908]

Wed, 21 October 2015 04:00

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

And just to clarify why your original attempt didn't work - sys_context is a function in the local db (yes it's also in the remote one) the fact that you're selecting it from the remote dual makes no difference.
SELECT <any function without a db link specifier> FROM dual@remote
will run a local function

Unfortunately you can't tack the remote db name onto built-in oracle functions like sys_context - hence Michel's suggestions.

Report message to a moderator

Re: get the remote DB instance name [message #643913 is a reply to message #643911]

Wed, 21 October 2015 05:23

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

Got it. Thanks All.

Report message to a moderator

Re: get the remote DB instance name [message #643926 is a reply to message #643913]

Wed, 21 October 2015 13:24

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

Senior Member

And to expand on cookiemonster's reply:

select name from v$database@dblink_name;

SY.
P.S. Both sys_context('USERENV','DB_NAME') & v$database.name might give you not what you expected in 12C multi-tenant world. It will always return CDB name, not pluggable name.

Report message to a moderator

Previous Topic:	interview question asked (merged 3)
Next Topic:	Unique hash based on user database objects.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Aug 23 03:59:22 CDT 2025