Skip to Main Content
  • Questions

  • Database Links password and connectivity

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sumit.

Asked: May 27, 2016 - 5:36 pm UTC

Last updated: May 28, 2016 - 4:22 am UTC

Version: 11gr2

Viewed 1000+ times

You Asked

Hi Guys,

Hope you are doing well.

I have a question on DB links when they are created.
Say we have two Database A and B and I have created DB link in database A (local database) to access the objects present in database B(remote database).Now there are two questions :-

1. While creating db link by giving username and password in create dblink statement if the database B is down still DB link will be created.
The create database link statement doesn't checks the credentials passed and whether the remote DB is up or not.
I believe local DB won't be aware of anything for remote Database but still in this case it should not allow creation of db link if remote database is not up or working.

Can you please explain why such check is not there while connecting to remote DB over db links because there won't be any error in create statement and user will hit error at when object accessed over dblink fails.


2. DB link exists between local database A and remote database B but say if password has changed for remote database then dblink will exists and won't become invalid.
User will only come to know that DB link is not functioning when he access objects over db link which may be over months after the remote Database password has changed.
Here my point is during password change of database how a user make sure that db links are not invalid or make them workable as before within seconds considering high availability environments.
Or is there another way of creating database link where password can be updated accordingly in this scenario.

Awaiting your response!!

Regards
Sumit Pawar

and Connor said...

1) We dont check database links for the second reason you mentioned, namely, checking a database link now in no way guarantees that it will work in 10 seconds from now (because the password my change, the user account may be dropped etc etc).

2) We can't really do a validity check, because the connectivity might not exist. For example, lets say there is a link from database A => database B. If we drop the user on B that was being used, then how does "B" notify "A" - it has no ability to "contact" A, because there is no link from B => A, only from A => B.

Thus if you want to have a "link checker" you'd need to perhaps build one yourself - it could simply check each link with a simple "select * from dual@link" and catch any errors.

However, dont forget - this check in itself could *change* the target database. For example, if your link checker failed 'n' times on a link, the end result might be that it locks the account on the target database.


Rating

  (1 rating)

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

Comments

Thanks Connor

Sumit Pawar, May 28, 2016 - 6:03 am UTC

Perfect answer.Thanks a lot.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.