This discussion is archived
5 Replies Latest reply: Jan 22, 2013 9:43 PM by baskar.l RSS

sessions monitoring - alert

user9338712 Newbie
Currently Being Moderated
Dear all,

10.2.0.4 on Solaris 10

Very often we have huge number of sessions in the database, if left unnoticed, this sessions are degrading the performance of the database.

So, I need to monitor the sessions in the database, if the output of select * from gv$session goes more than 900, then an email as an alert need to be immediately sent to DBA Team (mail id).

Can Any one give me some pointers or hints to do the same. I don't need the full fledged solution, but some insight, from which I will develop the solution.

Thanks
  • 1. Re: sessions monitoring - alert
    sb92075 Guru
    Currently Being Moderated
    user9338712 wrote:
    Dear all,

    10.2.0.4 on Solaris 10

    Very often we have huge number of sessions in the database, if left unnoticed, this sessions are degrading the performance of the database.

    So, I need to monitor the sessions in the database, if the output of select * from gv$session goes more than 900, then an email as an alert need to be immediately sent to DBA Team (mail id).

    Can Any one give me some pointers or hints to do the same. I don't need the full fledged solution, but some insight, from which I will develop the solution.

    Thanks
    select count(*) from v$session;

    then use UTL_MAIL to send message.

    Handle:     user9338712
    Status Level:     Newbie
    Registered:     Mar 20, 2010
    Total Posts:     39
    Total Questions:     22 (17 unresolved)


    why so many unanswered questions?
  • 2. Re: sessions monitoring - alert
    user9338712 Newbie
    Currently Being Moderated
    Thanks SB, I will clear off the backlogs in the forum.. Meanwhile, I want to check the output of the query . if it is > than some value, then only I need to send emails..

    Please advise
  • 3. Re: sessions monitoring - alert
    karan Pro
    Currently Being Moderated
    Write a procedure for it. http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/u_mail.htm#i1000954
  • 4. Re: sessions monitoring - alert
    sb92075 Guru
    Currently Being Moderated
    user9338712 wrote:
    Thanks SB, I will clear off the backlogs in the forum.. Meanwhile, I want to check the output of the query . if it is > than some value, then only I need to send emails..

    Please advise
    IF (CNT > 850)
    THEN
    UTL_MAIL
    END IF;
  • 5. Re: sessions monitoring - alert
    baskar.l Pro
    Currently Being Moderated
    Hi,

    Check this Monitor all blocking sessions and get email alerts in http://www.runningoracle.com/product_info.php?products_id=178

    You can remove the blocks and just have count(*) in the procedure.

    Baskar.l

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points