# $Header: userblk.tcl 20-apr-00.13:27:20 jmansur Exp $
#
# copyright (c) 1999 by the Oracle Corporation
#
# NAME:
#
#  userblk.tcl : Event tcl file that checks if there are any sessions which
#                hold a lock and block other processes
#
# ARGUMENTS:
#           argv(0) == connect string
#           argv(1) == no of occurrences
#
# RETURN:
#           $SCRIPT_FAIL or
#           $CLEAR_EVENT or
#           $NO_EVENT or
#           $WARNING_EVENT or
#           $ALERT_EVENT
#
# OUTPUT:
#           A list of the session ids who block other processes
#
# $Log:  $
# Revision 1.6  1996/06/24  23:53:05  yliu
# fixed bug 370148
#
# Revision 1.5  1996/02/06  21:58:00  yliu
# added character set conversion
#
# Revision 1.4  1996/01/10  00:02:55  yliu
# convert error number to error message
#
# Revision 1.3  1995/11/08 21:26:39  yliu
# fully qualify dba tables
#
# Revision 1.2  1995/10/25  18:51:23  yliu
# add connect string to the parameter list
#
# Revision 1.1  1995/10/13  23:03:01  yliu
# Initial revision
#


# Event definition
oradefine event /oracle/rdbms/fault/userblk description=VOC-01201 \
report=VOC-01202
oraarguments connect_str no_occurrence
oravardesc connect_str oracle_signon
oravardesc no_occurrence unsigned default=3 message=VOC-01203
oraresults session_ids
oravardesc session_ids string
oradefine end

# Initialize global variables
#comment out to fix bug#606739
#set last_report $CLEAR_EVENT
set output ""
set last_blockers {} 
set last_occurrences {} 
oraeventpersist last_event_blockers {} 


# The main event checking functions
proc EvalEvent {} {

    # Declare globals we will use
    global argv last_report output last_blockers last_occurrences \
           last_event_blockers
    global SCRIPT_FAIL CLEAR_EVENT NO_EVENT WARNING_EVENT ALERT_EVENT
    global oramsg

    # initialize the return code and output
    set ret_code $CLEAR_EVENT
    set output ""
    set err ""
    set blockers {}
    set occurrences {}
    set event_blockers {}

    # connect to the database and open a cursor
    set connect [format "%s@%s" [lindex $argv 0] $oramsg(oraobject)]
    if {[catch {oralogon $connect} lda]} {
        lappend output [msgtxt [RDBMS] ora $oramsg(rc)] ""
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }

    if {[catch {set cur [oraopen $lda]} err]} {
	lappend output $err
        catch {oralogoff $lda} err
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }

    # look for sessions blocking another one and increment number of times
    # they've blocked someone else
    set sql {select distinct sid from v$lock h
             where (h.id1, h.id2, h.type) in 
                   (select w.id1, w.id2, w.type from v$lock w where w.request <> 0)
               and h.lmode not in (0,1) and h.block = 1}

    if {[catch {orasql $cur $sql}] == 1} {
        lappend output [convertout $oramsg(db_characterset) $oramsg(errortxt)] $sql
        catch {oraclose $cur} err
        catch {oralogoff $lda} err
        lappend output $err
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }
    
    if {[catch {set row [orafetch $cur]} err]} {
	lappend output $err
        catch {oraclose $cur} err
        catch {oralogoff $lda} err
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }

    while {$oramsg(rc) == 0} {
        lappend blockers [lindex $row 0]
        set i [lsearch $last_blockers [lindex $row 0]]
        if {$i != -1} {
            lappend occurrences [expr [lindex $last_occurrences $i] + 1]
        } else {
            lappend occurrences 1
        }
        if {[catch {set row [orafetch $cur]} err]} {
            lappend output $err
            catch {oraclose $cur} err
            catch {oralogoff $lda} err
	    if {$last_report == $SCRIPT_FAIL} {
        	return $NO_EVENT
            } else {
                set last_report $SCRIPT_FAIL
                return $SCRIPT_FAIL
            }
        }
    }


    # log off
    catch {oraclose $cur} err
    catch {oralogoff $lda} err

    # print out event trace info 
    ORATCL_DEBUG "userblk : $oramsg(oraobject) : block session ids : $blockers"
    ORATCL_DEBUG "userblk : $oramsg(oraobject) : [oratime] : $occurrences"

    # determine if any blockers have blocked too often
    set i 0
    foreach no $occurrences {
        if {$no > [lindex $argv 1]} {
            set ret_code $ALERT_EVENT
            lappend event_blockers [lindex $blockers $i]
        }
        incr i
    }

    set last_occurrences $occurrences
    set last_blockers $blockers

    # return
    if { $ret_code == $last_report &&
         [string compare $last_event_blockers $event_blockers] == 0 } {
        return $NO_EVENT
    } else {
        lappend output $event_blockers
        ORATCL_DEBUG "userblk : $oramsg(oraobject) : [oratime] : MESSAGE - $ret_code, $output"
        set last_report $ret_code
        set last_event_blockers $event_blockers
        return $ret_code 
    }
}

