to find the Database links 2

[AQWQ]

can anyone pls help in getting the user defined database links available in a database.I mean i need the query to list out the database links active in a database.

Thanx in advance Santhosh Ravindran

 

[Strife]

select * from sys.link$

-- this shows all including passwords!

 

[AQWQ]

fsdfsf Santhosh Ravindran

 

[AQWQ]

Hi strife,

The Query given by u select the tnsnames and not the db_links.Actually i need the list of db_links which we we create using the command "create public database link db_linkname connect "user" identified by "password" using "tnsservice"..got it???please help me out.

Thanx in advance Santhosh Ravindran

 

[Strife]

Hi,

Actually Santhosh, you asked for a list of defined database links in the database and LINK$ is the source table for this..

is this what you need?

select 'create public database link ' || name ||
' connect to ' || userid ||
' identified by ' || password ||
' using ''' || host || ''''
from link$;

 

[Strife]

or if you need to identify the active open database links then try:

select * from sys.v_$dblink;

-- This will only return rows if the link is active!

 

[AQWQ]

Hi Strife!!

ok thats fine i can get the active link with that query.but how to get all the links available in the database?? could u please get me that query too..

Thanx in advance. Santhosh Ravindran

 

[karluk]

Hi Santhosh,
I'm not sure why you think sys.link$ doesn't give you the database links, but I can vouch for Strife's suggestion. When I query link$, I see the db links I've defined on my system. Please give it a try.

 

[karluk]

By the way, DBA_DB_LINKS also contains information about database links. However the information isn't as complete as you will find in LINK$.

 

[AQWQ]

Thanks Strife and karluk for ur suggestions...It worked fine.

Santhosh Ravindran