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?
views:
34answers:
1
+2
A:
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>
Dougman
2010-03-18 03:28:44
It should be mentioned that DBA_DB_LINKS will not be available to normal users unless O7_DICTIONARY_ACCESSIBILITY = TRUE, and the user has SELECT_ANY_TABLE. Otherwise it will usually only be available to DBA users. However, all users can access USER_DB_LINKS, but that would only be sufficient if the query in question only uses dblinks from the user's own schema.
Charles
2010-03-18 03:53:43
@Charles M: Good point about the user's privileges.
Dougman
2010-03-18 04:07:20
I prefer not to do text searches. Are there any packages available to tell me the table references? I have created a new question: http://stackoverflow.com/questions/2467735/do-any-parsers-exist-for-the-oracle-dml-table-reference
JavaRocky
2010-03-18 05:40:50