Hi: If I am monitoring my DB and as an example, if I want to know when my datafile has reached a certain limit, or a dead lock happened etc., what is the best way to that? What is the procedure and what are the tools needed? Thanks
Oracle comes with a product called Oracle Enterprise Manager. You can purchase a "Module" for it that allows you to configure events as you specified. It will notify you by email or pager if an event occurs.
You do need another small database to keep the repository of the monitored databases. OEM can be instyalled on your workstation as it does not require much of space but you are right that you will have to keep it running all the time if you want to monitor your database constantly.
In that case write your own scripts (use dbms_pkgs) and set the jobs in CRON to run repeatedly (or I think with DBMS_PKGS you don't even need cron scheduling).
We have a script (called orawatch) that goes out and looks for different things that happen in the database. It runs every 5 minutes from a cron job. If it finds something that needs to be reported, it sends an email message to a group of pagers. Our Paging carrier has a feature that will convert an email message to an Alphanumeric page.
It took about 6 weeks (on and off) to write the script that does something similar to OEM. You have to ask yourself if it is worthwile to write a program that comes with Oracle for free.