当前位置： 文江博客话题详情

需要监控SQL查询结果的工具

发布于 2024-08-13 14:16:55 字数 337 浏览 1 评论 0原文

我正在寻找一种工具来监视定期运行的 SQL 查询的结果，并根据查询返回任何结果的事实发出通知。 （欢迎任何其他过滤器）

我需要监视事务表中的错误，如果我的 sql 查询可以在后台运行、定期刷新并在有任何结果时显示通知，那就太好了。

我需要连接到 Oracle DB，并且当前使用 PL/SQL Developer 或 Oracle SQL Developer。

首选免费、操作系统和轻量级解决方案:)

更新：

最好我不想创建/修改任何数据库对象。我们也希望在我们的客户数据库上使用它，但并非所有客户都拥有修改其 Oracle 应用程序运行的数据库的许可。

先感谢您

原文

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

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

I need to connect to Oracle DB and I currently use PL/SQL Developer or Oracle SQL Developer.

Free, OS and lightweight solutions preferred :)

UPDATE:

Preferably I would like to not create/modify any database objects. We would like to use this on our clients databases too and not all of them have license to modify the DBs where their Oracle Apps run.

Thank you in advance

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（3）

忘你却要生生世世 2024-08-20 14:16:55

如何使用 DBMS_Scheduler 运行查询表的存储过程，然后在出现问题时使用 UTL_Mail 发送电子邮件？

How about using DBMS_Scheduler to run a stored procedure that queries the table and then uses UTL_Mail to send an email in case of a problem?

回复 原文
鱼忆七猫命九 2024-08-20 14:16:55

将查询放入存储过程中。那里有您的监控/通知代码。

Put the query in a stored procedure. Have your monitoring / notification code there.

回复 原文
尝蛊 2024-08-20 14:16:55

我用这个并且效果非常好。

-- 1. 创建程序：

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. 创建日程程序：

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.最后将两者链接在一起创建一个作业：

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;

现在您可以使用类似 utl_mail.send 的东西

begin
 UTL_MAIL.SEND_ATTACH_VARCHAR2
(
        sender => '[email protected]'
       ,recipients => '[email protected],[email protected],[email protected],[email protected]'
       ,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'
   );

I use this and it works absolutely fine.

-- 1. Create 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 link both together to create a JOB:

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 => '[email protected]'
       ,recipients => '[email protected],[email protected],[email protected],[email protected]'
       ,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 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

lioqio

文章 0 评论 0

Single

文章 0 评论 0

禾厶谷欠

文章 0 评论 0

alipaysp_2zg8elfGgC

文章 0 评论 0

qq_N6d4X7

文章 0 评论 0

放低过去

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文