You Asked

Tom,

DBA.DBLINKS - I am confused about what's in DBA_LINKS - I have never used them. I just want to understand what the column definitions mean. They are too vague.

OWNER - The user who created the database link. If the link is public, then the user is listed as PUBLIC. Is that the owner of the current or remote DB?

DB_LINK - The name of the database link.

USERNAME - The remote database user. If the link definition includes a fixed user, then this column displays the username of the fixed user. If there is no fixed user, the column is NULL. Who is this? Is this the owner in the current database?

HOST/SERVICE - The net service name used to connect to the remote database.

SCHEMA - The owner in the current database. The collection of logical structures of data, or schema objects - like a table, index, view, synonym, procedure, package, or a database link. Where does that fit in a DBLINK definition?

DBLINK || USER || HOST || OWNER || SCHEMA
----------------------------------------------------------------------------------
XXXAPI.YYYUSA.COM || ABCD || Jim1 || H1 || ???

Jim

and Connor said...

Sorry - schema ? I dont see such a column

SQL> desc ALL_DB_LINKS
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 OWNER                         NOT NULL VARCHAR2(128)
 DB_LINK                       NOT NULL VARCHAR2(128)
 USERNAME                               VARCHAR2(128)
 HOST                                   VARCHAR2(2000)
 CREATED                       NOT NULL DATE

OWNER is self explanatory - PUBLIC or whoever *created* the link in *this* database.

DB_LINK - any meaningful name (lets call it ABC)

USERNAME - who you connect to in the *remote* database

HOST - *how* you get to the *remote* database

So HOST could be (for example) "MY_REMOTE_DB" where MY_REMOTE_DB is an entry in the tnsnames.ora file. Or it could be the full description, eg

"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db122)))"

So when I do

select * from some_table@abc

I will do:

- lookup link ABC
- find that it connect to a username called (say) scott
- I use scott and either the password I specified when I created the link, or if not, the password I currently have in this local session
- I connect to the host indicated by HOST

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DBLINKS

Jim Heffernan, March 24, 2017 - 3:51 pm UTC

Nice Connor!

Ed, March 28, 2024 - 3:41 am UTC

USERNAME is NULL though, so what username is used? The owner's?

April 02, 2024 - 5:49 am UTC

If username is null, we use the current user, eg

If I had done:

create database link XYZ using 'my_tns_network_name';

and I was connected as SCOTT on my current database, then doing:

select * from emp@xyz

will send over my username/password from THIS database to connect to the remote database. Its a nice way of not hard coding passwords into the db link (as long your password are in sync across the two databases)

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

DBLINKS

More to Explore

PL/SQL demos

PL/SQL docs