# $Header: snpshtsz.tcl 14-apr-00.04:54:58 jmansur Exp $
#
# copyright (c) 1999 by the Oracle Corporation
#
# NAME:
#
#  snpshtsz.tcl : Event tcl file that checks if a snapshot log table' size 
#                 (number of rows ) exceeds a user specified limit
#
# ARGUMENTS:
#           argv(0) == connect string
#           argv(1) == threshold value for alert
#           argv(2) == upper limit on the number of rows in any Snapshot 
#                      log table
#           
#
# RETURN:
#           $SCRIPT_FAIL
#           $CLEAR_EVENT
#           $NO_EVENT
#           $WARNING_EVENT
#           $ALERT_EVENT
#
#
# OUTPUT:
#           A list of snapshot log tables and their sizes
#
# $Log:  $
# Revision 1.5  12/12/97  18:28:59  rposham
# initial
#

# Event definition
#
#
#
oradefine event /oracle/rdbms/space/snpshtsz description=VOC-01916 \
report=VOC-01917
oraarguments connect_str alert_threshold log_table_size
oravardesc connect_str oracle_signon
oravardesc alert_threshold unsigned default=1 message=VOC-01918
oravardesc log_table_size unsigned default=100 message=VOC-01919
oraresults log_tables log_table_sizes
oravardesc log_tables string
oravardesc log_table_sizes int
oradefine end
#



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


# The main event checking functions
proc EvalEvent {} {


    # Declare globals we will use
    global argv last_report output
    global SCRIPT_FAIL CLEAR_EVENT NO_EVENT WARNING_EVENT ALERT_EVENT
    global oramsg 
    global last_log_tables

    # initialize the return code and output
    set ret_code $CLEAR_EVENT
    set output ""
    set err ""

    # 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 cur1 [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
        }
    }

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

    # set the maximum size; specified in # of rows, that a snapshot log's table
    # can be
    set log_table_size [lindex $argv 2] 


    # get snapshot log tables
    set sql "select DISTINCT log_owner, log_table from  sys.dba_snapshot_logs"
    if { [catch { orasql $cur1 $sql }] } {
        lappend output [convertout $oramsg(db_characterset) \
            $oramsg(errortxt)] $sql
        catch {oraclose $cur1} err
        catch {oraclose $cur2} err
        catch {oralogoff $lda} err
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }

    set log_tables ""
    set log_table_sizes ""


    if {[catch {set row [orafetch $cur1]} err]} {
        lappend output $err
        catch {oraclose $cur1} err
        catch {oraclose $cur2} 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} {
        set sql2 [format "select count(*) from \"%s\".\"%s\"" \
                         [lindex $row 0] \
                         [lindex $row 1]]
        if { [catch { orasql $cur2 $sql2 }] } {
          if { $ormsg(rc) == 942 } {
            set tmp [format "%s.%s" [lindex $row 0] [lindex $row 1]]
            ORATCL_DEBUG "snpshtsz : $oramsg(oraobject) : log table $tmp doesn't exist."
            if {[catch {set row [orafetch $cur1]} err]} {
                lappend output $err
                catch {oraclose $cur1} err
                catch {oraclose $cur2} err
                catch {oralogoff $lda} err
                if {$last_report == $SCRIPT_FAIL} {
                    return $NO_EVENT
                } else {
                    set last_report $SCRIPT_FAIL
                    return $SCRIPT_FAIL
                }
            }
            continue
          } else {
            lappend output [convertout $oramsg(db_characterset) \
                $oramsg(errortxt)] $sql2
            catch {oraclose $cur1} err
            catch {oraclose $cur2} err
            catch {oralogoff $lda} err
            if {$last_report == $SCRIPT_FAIL} {
                return $NO_EVENT
            } else {
                set last_report $SCRIPT_FAIL
                return $SCRIPT_FAIL
            }
          }
        }

        if {[catch {set count [orafetch $cur2]} err]} {
            lappend output $err
            catch {oraclose $cur1} err
            catch {oraclose $cur2} err
            catch {oralogoff $lda} err
            if {$last_report == $SCRIPT_FAIL} {
                return $NO_EVENT
            } else {
                set last_report $SCRIPT_FAIL
                return $SCRIPT_FAIL
            }
        }        

        if { $count > $log_table_size } { 
            set tmp [format "%s.%s" [lindex $row 0] [lindex $row 1]]
            set tmp [convertout $oramsg(db_characterset) $tmp]
            lappend log_tables $tmp
            lappend log_table_sizes $count
        }     

        if {[catch {set row [orafetch $cur1]} err]} {
            lappend output $err
            catch {oraclose $cur1} err
            catch {oraclose $cur2} 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 $cur1} err
    catch {oraclose $cur2} err
    catch {oralogoff $lda} err

    # print out event trace info 
    ORATCL_DEBUG "snpshtsz : $oramsg(oraobject) : [oratime] : [llength $log_tables]"

    # check number of snapshot log's tables that exceed the size limit against 
    # alert threshold 
    if {[llength $log_tables] > [lindex $argv 1]} {
        set ret_code $ALERT_EVENT
        lappend output $log_tables
        lappend output $log_table_sizes
    } 
    
    
    # return
    if { $ret_code == $last_report && [string compare $last_log_tables $log_tables] == 0 } {
        return $NO_EVENT
    } else {
        ORATCL_DEBUG "snpshtsz : $oramsg(oraobject) : [oratime] : MESSAGE - $ret_code, $output"
        set last_report $ret_code
        set last_log_tables $log_tables
        return $ret_code 
    }

}
