Geek Answers Handbook

SQL Query Monitoring Tool

I am looking for a tool to monitor the results of a periodically running sql query and raise the notification based on the fact that the query returns any results. (any other filters are welcome)

I need to look at the transaction table for errors, and it would be great if my sql query could run in the background, refresh itself periodically and show a notification when there are any results.

I need to connect to Oracle DB, and I'm currently using PL / SQL Developer or Oracle SQL Developer.

Free, OS and lightweight solutions are preferable :)

UPDATE:

It is advisable that I do not create / modify any database objects. We would also like to use this in our client databases, and not all of them have a license to change the database where their Oracle applications run.

Thank you in advance

+4
source share
3 answers

How to use DBMS_Scheduler to start a stored procedure that queries a table and then uses UTL_Mail to send email in case of a problem?

+3
source

Put the request in a stored procedure. Enter your monitoring / notification code there.

0
source

I use this and it works absolutely fine.

- 1. Create a program:

BEGIN DBMS_SCHEDULER.CREATE_PROGRAM( program_name=>'user.TABLESPACE_MANAGEMENT', program_action=>'begin EXECUTE IMMEDIATE 'CREATE TABLE IDLE_TIME_TABLE LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING AS SELECT DISTINCT SID, OSUSER, USERNAME, STATUS, TO_CHAR(LOGON_TIME, ''DDth DAY HH24:MI:SS'') LOGON_TIME, FLOOR(LAST_CALL_ET/3600)||'':''|| FLOOR(MOD(LAST_CALL_ET,3600)/60)||'':''||MOD(MOD(LAST_CALL_ET,3600),60) IDLE, PROGRAM FROM V$SESSION WHERE USERNAME IS NOT NULL AND STATUS = ''INACTIVE'' ORDER BY IDLE DESC', program_type=>'PLSQL_BLOCK', number_of_arguments=>0, comments=>'TABLESPACE MANAGEMENT CREATES A TABLE CALLED TABLESPACE_MANAGEMNT ON DAILY BASIS', enabled=>TRUE); END;

- 2. Create a schedule program:

 BEGIN sys.dbms_scheduler.create_schedule( repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=22; BYMINUTE=0; BYSECOND=0;', start_date => to_timestamp_tz('2011-07-26 US/Eastern', 'YYYY-MM-DD TZR'), comments => 'Schedule for what ever u want', schedule_name => '"user"."TABLESPACE_MANAGEMENT_SCHEDULE"'); END;

- 3. And finally, tie both together to create the WORK:

 BEGIN sys.dbms_scheduler.create_job( job_name => '"user"."SPACE"', program_name => 'user.TABLESPACE_MANAGEMENT', schedule_name => 'user.TABLESPACE_MANAGEMENT_SCHEDULE', job_class => 'DEFAULT_JOB_CLASS', comments => 'TABLESPCE_MANAGEMENT', auto_drop => FALSE, enabled => TRUE); END;

Now you can use something like utl_mail.send

 begin UTL_MAIL.SEND_ATTACH_VARCHAR2 ( sender => ' username@whatever.co.uk ' ,recipients => ' username@whatever.co.uk , username@whatever.co.uk , username@whatever.co.uk , username@whatever.co.uk ' ,cc => NULL ,bcc => NULL ,subject => 'send_attach_varchar2' ,message => 'here is a test of send_attach_varchar2' ,mime_type => 'text/plain; charset=us-ascii' ,priority => 3 ,attachment => '<html> <head> <title>Test HTML message</title> </head> <body> <p>This is a <b>HTML</b> <i>version</i> of the test message.</p> <p><img src="http://whatever/images/site_logo.gif" alt="Site Logo" /> </body> </html>' ,att_inline => TRUE ,att_mime_type=>'application/html' ,att_filename => 'cartesien.html' );
0
source

More articles:


All Articles