Menu
I would like to find out if any and which database links are being used, for a schema, and in which tables. Is it possible via the data dictionary somehow?
Is this possible with the Oracle RDBMS?
350
Well, you could try to query various system views and see whether any of them contains a string you're looking for. As you want to check the whole database, you'll probably connect as a privileged user and check dba_ views; otherwise, all_ or user_ would do.
If application schema's password is not known to DBA. We can verify public database link using select * from dual@public_db_link; How private db links can be verified by a DBA if application schema's password is not known.
When the database link in the preceding query is used, Oracle will log into the database specified by the database link, using the username and password provided by the link. It then queries the BOOKSHELF table in that account and returns the data to the user who initiated the query.
A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. The link pointer is actually defined as an entry in a data dictionary table. To access the link, you must be connected to the local database that contains the data dictionary entry.
One way is to check your tnsnames. ora file where you can find (HOST=some_host_name/ip_address) under the desired connection string. If IP address is there then you have got what you wanted and if host name is there, you can ping to that host name in command prompt to get ip address.
I know the answer by Dougman is accepted and accurate. But here is some more information.
If the user is not a DBA user, it will not have access to DBA_DB_LINKS. Also, USER_DB_LINKS will display the db links created by the current user, so that won't list all the DB links that the user has access to.
You can use ALL_DB_LINKS to get the links that the user has access to.
select * from all_db_links;
This will show you any database links set up on the database:
select * from dba_db_links;
You would then have to search for any queries or objects using the db link by doing a text search of them for the link syntax <tablename>@<dblink name>
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
