A snippet to monitor the last INSERT/UPDATE in an Oracle DB from C#?

Question

I'm looking for a simple, sample snippet of C# code to monitor an Oracle database and basically check for its last update. It could be either command line (great for future integration with Nagios) or GUI. I've did some prototypes but the code tend to get lengthy as I'm still kind of learning the language.

Any suggestion/comment would be much appreciated.

Answer 1

Could you be more specific please? Is there a particular table you want to monitor? What do you want to do on detecting an update? Your problem may have another, and simpler solution than the one you're trying to find.

Answer 2

One possible solution is:

1. Add a DATE field that represents the last update time to MY_TABLE table.

   ALTER TABLE my_table ADD (last_update_time DATE);

2. Create an index on that field.

   CREATE INDEX i_my_table_upd_time ON my_table (last_update_time);

3. Create a database trigger on that table that fires ON UPDATE and ON INSERT and stores SYSDATE into the new field.

   CREATE OR REPLACE TRIGGER my_table_insert_trg  
   BEFORE INSERT OR UPDATE  
   ON my_table  
   FOR EACH ROW  
   BEGIN  
      :new.last_update_time := SYSDATE;  
   END;
   

Now you can issue the following query every 5 minutes

SELECT max(last_update_time) FROM my_table;

and it will give you the time when your table was last updated.

There is no easy way to get a notification from Oracle, sorry.