Been there experienced that
A reader, October 11, 2013 - 4:21 am UTC
For starters I am a DBA ;o)
Had encountered a similar problem some time on a QA database. One of the schemas password was the as on Prod and for some reason the password was not changed on Prod.
The developers got wind of it and started using an implicit DB link with @"Prod DB Service Name".
They were using it for a long time and I found it by chance when thry approached me for performance tuning of one of the packages.
Needless to say we could not change the password as it could affect many downstream jobs.
So we put in a report which will check for such links and alert us in case it finds any. The developers have been asked not to use it and so far so good :)
Cheers
Spot on
A reader, October 11, 2013 - 11:52 am UTC
Thanks a lot for such a quick and correct answer, Tom.
We weren't aware of global db links being used (I didn't even know they exist, so I learned something new again ;)), but as always you're right.
It turned out that something completely different caused some problems (lots of invalid objects) and the global db links were 'accidentally discovered' at the same time, during a cleanup and check afterwards on local db links. This caused all the confusion.
Regards,
Hoek
Small follow-up question
Martijn Hoekstra, October 11, 2013 - 5:05 pm UTC
From a 'non-DBA privileged perspective':
Is V$DBLINK the only way to identify Global Database Links?
DBA_DB_LINKS doesn't show them and V$DBLINK only when they're referenced first (per session).
Is there no other way to get a list of all available gdblinks (for a developer) through some query ?
November 01, 2013 - 5:56 pm UTC
Don't be anxious about that...
J. Laurindo Chiappa, October 18, 2013 - 9:40 pm UTC
Let´s wait for Tom´s answer, but I think that no type of 'register' for global database links will be maintained inside the database : by definition, a global database link is an object residind OUTSIDE the database core, in some DNS-like software...
And by no means it is a new concept : see
http://www.oreillynet.com/pub/a/oreilly/oracle/news/oraclenet8_1200.html (an except of a year-2000 book, very old) speaking about it...
best regards,
J. Laurindo Chiappa
dba|all_db_links really don't list global database links
Hoek, November 04, 2013 - 1:21 pm UTC
Hi Tom,
Just for the record: global db links appear not to show up in those views:
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
5 rows selected.
SQL> select * from dual@xxxxxxxx;
D
-
X
1 row selected.
SQL> select db_link from v$dblink;
DB_LINK
---------------------------------------------------------------------------------------------
XXXXXXXX
1 row selected.
SQL> select db_link from dba_db_links;
no rows selected
SQL> select db_link from all_db_links;
no rows selected
Documentation is not really clear about that.
Regards,
Hoek