I would choose option number 1 - centralized functionality that uses database links.
Database links have an undeserved bad reputation. One of the main reasons is that too many people use links to a common database, where anyone who connects to the database can use this link. This is obviously a security nightmare, but it is not the default setting, and this trap is easy to avoid.
Some other problems with database links:
- They do not work well for huge inserts of millions of lines. On the other hand, they are great for many small SELECT or INSERT. I often have hundreds of links open and collecting data simultaneously on 10-year-old hardware, and it works great.
- They make execution plans more difficult to troubleshoot.
- Not all data types are supported. This is better in 12.2, but in earlier versions you would need to use INSERT to move data types, such as CLOBs, into tables, and then read from these tables.
- For DDL you need to use
DBMS_UTILITY.EXEC_DDL_STATEMENT@LINK _NAME('create ...'); Be sure to use only DDL. Other types of teams will fail. - Links can freeze indefinitely in several rare situations, for example, if there is an archiver error in the database or a guaranteed restore point is complete. (This is a truly disguised blessing - many tools, such as Oracle Enterprise Manager, will not understand these problems. You might want to check background jobs for database link requests that took longer than X minutes.)
Links should not be hard-coded, otherwise they may lead to invalidation of the package. But that may not matter - you probably want to scroll through the list of databases and use dynamic SQL anyway. And if the link does not exist, it is fairly easy to create a new one. Here is an example:
declare v_result varchar2(4000); begin --Loop through a configuration table of links. for links in ( select database_name, db_link from dbs_to_monitor left join user_db_links on dbs_to_monitor.database_name = user_db_links.db_link order by database_name ) loop --Run the query if the link exists. if links.db_link is not null then begin --Note the user of REPLACE and the alternative quoting mechanism, q'[...]'; --This looks a bit silly with this small example, but in a real-life query --it avoids concatenation hell and makes the query much easier to read. execute immediate replace(q'[ select dummy from dual@ #DB_LINK# ]', '#DB_LINK#', links.db_link) into v_result; dbms_output.put_line('Result: '||v_result); --Catch errors if the links are broken or some other error happens. exception when others then dbms_output.put_line('Error with '||links.db_link||': '||sqlerrm); end; --Error if the link was not created. --You will have to run: --create database link LINK_NAME connect to USERNAME identified by "PASSWORD" using 'TNS_STRING'; else dbms_output.put_line('ERROR - '||links.db_link||' does not exist!'); end if; end loop; end; /
Despite all this, database links are great because you can do everything in PL / SQL in one database. In one language, you can create a risk-free monitoring solution and not worry about installing and fixing agents.
As an example, I built the open-source Method5 program to do everything using database links. With this program you can collect results from hundreds of databases as easy as working select * from table(m5('select * from dba_jobs')); . This program is probably too complicated for your scenario, but shows that all database links are necessary for full monitoring.