DB Monitoring Source Code

Hi again OpenRoad SAs and DBAs.  In the spirit of Open Source I am now sharing the code for the database monitoring image I have previously posted.  Some part of the code was actually lifted from forums during my past research, but as the code snippets (session termination, etc.) was public I cannot attribute ownership to anybody.  Other parts of the code however, especially the part to find the locking session) were accidentally "discovered" so to say by me.  I am not an expert in the workings of the IMA database but having access to it one can experiment on some of the values exposed in the tables.  So here it is, the links to the exported applications:

DB Monitoring OR3.5:  u_IMA35.exp
DB Monitoring OR4.1:  u_IMA41.exp

As an added bonus (and mainly because the Monitoring image needs it :P),  I have also included below the application for the node selection utility I have developed.  This app needs some more work done so I think in the right hands this part can be perfected.

vNode Utility OR3.5:  u_vNode_Util35.exp
vNode Utility OR4.1:  u_vNode_Util41.exp

Here are the screenshots for the application catalog and component catalogs after importing the exp files:

And lastly here is the magic code that really does the trick:

  select locklist_session_id
  from ima_locklists
  where locklist_id in (
   select locklist_id
   from ima_locks
   where resource_id in (
        select resource_id
        from ima_locks
        where locklist_id in (
         select locklist_id
         from ima_locklists a, ima_server_sessions b, ima_server_sessions_extra c
         where a.locklist_session_id = b.session_id
         and a.locklist_session_id = c.session_id
         and b.session_id = c.session_id
         and effective_user not like '%<%>%'
         and session_wait_reason = 'LOCK'
         and db_name <> 'imadb'
         and locklist_name0 > 0
         and locklist_wait_id > 0
         and b.server = :var_server
         and b.db_name = :var_db_name
        )
   )
   and locklist_id not in (
    select locklist_id
    from ima_locklists a, ima_server_sessions b, ima_server_sessions_extra c
    where a.locklist_session_id = b.session_id
    and a.locklist_session_id = c.session_id
    and b.session_id = c.session_id
    and effective_user not like '%<%>%'
    and session_wait_reason = 'LOCK'
    and db_name <> 'imadb'
    and locklist_name0 > 0
    and locklist_wait_id > 0
        and b.server = :var_server
        and b.db_name = :var_db_name
       )
   )    
where var_server and var_db_name are the node and database currently being investigated.

If you should use this part of the code kindly mention my name in the comments portion of the code.  It took me quite some time to find out about this trick so I think it's not much of a hassle. :)

You may also use the original applications and add modifications to it (if you don't like my color combinations, hehe..) as long as you add something to remember my contribution.

Comments are welcome and of course so are donations (via the original post) if you are willing.  LOL.