Oracle 12C DBA_DB_LINK_SOURCES

Prior to Oracle 12C when ever a database migration which involves an IP change was planned, we used to query the session history to find out the possible sources from which connection are established to databases. So that application changes (connections strings) required and firewall rules to be implemented can be identified. Normally a variant of below query was used which is based on dba_hist_active_sess_history (licensable),

  SELECT machine,  
      program,  
      username,  
      COUNT (*)  
   FROM (SELECT a.machine, a.program, b.username  
       FROM dba_hist_active_sess_history a, dba_users b  
       WHERE   username IS NOT NULL  
          AND A.USER_ID = B.USER_ID  
          AND machine != '<hostname>')  
 GROUP BY machine, program, username  
 ORDER BY 1, 2, 3;  

Among the identified sources, one of the main contributor was  db_links in many of the distributed systems. Although the information returned from above query was informative, we always used to manually check on the source database as most of these links are critical for the applications.

But starting from 12C Oracle is providing a view DBA_DB_LINK_SOURCES, that can be used to get required data related to all external db links connecting to a particular database. This removes the manual intervention to verify the db links, which is a handy option during database IP migrations.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/DBA_DB_LINK_SOURCES.html#GUID-0E6F0B9A-F816-4791-8DCB-00623DDD7456