OraFAQ Forum: SQL & PL/SQL » How to get the connect string from the data base

Home » SQL & PL/SQL » SQL & PL/SQL » How to get the connect string from the data base (Oracle 11g )

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

How to get the connect string from the data base [message #633975]

Mon, 02 March 2015 03:25

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi All ,

I need to get the connect string from the data base which is already connected.

Is there any way to get the Connect string from the data base.

The following query gives the information about the user , insane name

select instance_name , user from v$instance;

Thanks
SaiPradyumn

Report message to a moderator

Re: How to get the connect string from the data base [message #633977 is a reply to message #633975]

Mon, 02 March 2015 03:40

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

saipradyumn wrote on Mon, 02 March 2015 14:55

select instance_name , user from v$instance;

So what's the problem?

SQL> show user
USER is "LALIT"
SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      orcl
SQL>
SQL> select user, instance_name from v$instance;

USER                           INSTANCE_NAME
------------------------------ ----------------
LALIT                          orcl

SQL>

Report message to a moderator

Re: How to get the connect string from the data base [message #633979 is a reply to message #633977]

Mon, 02 March 2015 03:51

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Lalit

Thanks for your reply. That what even I had tried, but instance names gives only the name of the instance. But I need the connection string to the data base.

Ex user/pwd@connection string

In our data base these two are different.

Thanks
Sai Pradyumn .

Report message to a moderator

Re: How to get the connect string from the data base [message #633980 is a reply to message #633979]

Mon, 02 March 2015 03:54

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

Senior Member

I doubt you can - the DB doesn't care what the connection string was, if it's different from the instance name then that's sorted out by the listener.

Report message to a moderator

Re: How to get the connect string from the data base [message #633982 is a reply to message #633979]

Mon, 02 March 2015 04:42

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

Senior Member
Account Moderator

You cannot get it from the database; this is an information that even the listener does not get, this is a pure client information; the listener only gets the translation of "connection string" from your tnsnames.ora and not this name "connection string".

Report message to a moderator

Re: How to get the connect string from the data base [message #633984 is a reply to message #633979]

Mon, 02 March 2015 05:18

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

saipradyumn wrote on Mon, 02 March 2015 15:21

But I need the connection string to the data base.

As others have suggested, Connect strings are defined in your local TNSNAMES.ORA file.

Report message to a moderator

Re: How to get the connect string from the data base [message #633991 is a reply to message #633984]

Mon, 02 March 2015 06:06

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

Senior Member

Undocumented:

SELECT * FROM V$LISTENER_NETWORK;

SY.

Report message to a moderator

Re: How to get the connect string from the data base [message #633992 is a reply to message #633991]

Mon, 02 March 2015 06:12

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

Senior Member
Account Moderator

This does not give the "connectstring" (or tnsnames.ora alias) you gave on connection just the connection strings and service names you can use to connect.

Report message to a moderator

Re: How to get the connect string from the data base [message #633993 is a reply to message #633975]

Mon, 02 March 2015 07:06

EdStevens
Messages: 1377
Registered: September 2013

Senior Member

As others have said, there is no way to know this from the database. When a client provides a 'connect string', that string is ultimately translated to an IP address, port number, and service name. This translation is done on the client side of the connection, and it is that information (ip, port, service) that is is used in routing the request to the server.

Suppose I could give you some magic, secret query that would produce the information you ask for. What would you do differently from what you are doing today? In other words, why is it deemed important to know the connect string that was used? That's like wanting to know what kind of telephone someone is using when they call you.

Report message to a moderator

Re: How to get the connect string from the data base [message #633996 is a reply to message #633993]

Mon, 02 March 2015 07:10

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

Senior Member
Account Moderator

Quote:

That's like wanting to know what kind of telephone someone is using when they call you.

Report message to a moderator

Previous Topic:	SQL or PL/SQL method required to fill up the values based on other table values
Next Topic:	Storing varying number of columns in table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 27 17:24:26 CDT 2026