Actually I am just planning to do the above activity.
1. We have 50 databases residing on each own server counterpart (50 servers). 2. Each database has table Patch_LeveL (apps_name, patch_level), which contains just one row only to reflect the latest patch level applied for the apps on this database. Note that are we constantly on applying service packs for this apps. 3. On our central monitoring server (db). I create 50 database LINKS for each of the 50 databases. 4. I created 50 views over these links to make it centralized to represent the 50 Patch_Level tables. As patch_level_view1, 2, 3.....patch_level_view50. 5. I then create a central view as .... as union of the 50 individual views
Is there a limitation of joining "union" of 50 views?What can happen is if the connection to one of these servers is interrupted, the big "union" view will not work. As said by above poster MV, with lets say an hourly refresh, helps with this situation as you have the data that was gathered last time (and most likely it is still valid).