Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jim.

Asked: March 22, 2017 - 11:49 pm UTC

Last updated: April 02, 2024 - 5:49 am UTC

Version: 11g

Viewed 10K+ times! This question is

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?
Connor McDonald
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)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library