# $Header: fseggrth.tcl 20-apr-00.05:42:37 jmansur Exp $
#
# copyright (c) 1999 by the Oracle Corporation
#
# NAME:
#
#  fseggrth.tcl : Event tcl file that checks if segments are growing faster than the rate specified.
#
#
# ARGUMENTS:
#           argv(0) == connect string
#           argv(1) == filter of the tablespace names to be monitored, or *
#                      for all tablespaces. Examples of filters are
#                      "= 'SYSTEM'" or "in ('SYSTEM', 'TOOL')"
#           argv(2) == filter of the segment names to be monitored, or *
#                      for all segment names.
#           argv(3) == filter of the segment types to be monitored, or *
#                      for all segment types.  Segment types 'CACHE' and
#                      'DEFERRED ROLLBACK' are excluded.
#           argv(4) == threshold value for alert
#           argv(5) == threshold value for warning
#
#
# RETURN:
#           $SCRIPT_FAIL or
#           $CLEAR_EVENT or
#           $NO_EVENT or
#           $WARNING_EVENT or
#           $ALERT_EVENT
#
# OUTPUT:
#           A list of the segment name, segment_type, tablespace name, owner, growth size of the segment.
#
#


# Event definition
oradefine event /oracle/rdbms/space/fseggrth description=VOC-01348 \
report=VOC-01349 frequency=86400
oraarguments connect_str tablespace_name segment_name segment_type alert_threshold warning_threshold
oravardesc connect_str oracle_signon
oravardesc tablespace_name string default=* message=VOC-01350
oravardesc segment_name string default=* message=VOC-01351
oravardesc segment_type string default=* message=VOC-01352
oravardesc alert_threshold unsigned default=3 message=VOC-01353
oravardesc warning_threshold unsigned default=2 message=VOC-01354
oraresults event_segments event_tablespaces event_growths
oravardesc event_segments string
oravardesc event_tablespaces string
oravardesc event_growths int
oradefine end


# Initialize global variables
set output ""
set initial 1
set last_seg_name {}               
set last_seg_type {}
set last_seg_owner {}
set last_seg_extent {}
oraeventpersist last_alert_tablespaces {}
oraeventpersist last_alert_segments {}
oraeventpersist last_warn_tablespaces {}
oraeventpersist last_warn_segments {}

# The main event checking functions
proc EvalEvent {} {


    # Declare globals we will use
    global argv last_report output initial
    global last_seg_name last_seg_type last_seg_owner last_seg_extent last_seg_part
    global SCRIPT_FAIL CLEAR_EVENT NO_EVENT WARNING_EVENT ALERT_EVENT
    global oramsg
    global last_alert_tablespaces last_alert_segments
    global last_warn_tablespaces last_warn_segments


    # initialize the return code and output
    set ret_code $CLEAR_EVENT
    set output ""
    set err ""
    # only send first 20 to oms, but compare all so don't miss diffs
    set max_segments 20
    set db_version ""
    set alert_event_segments {}
    set alert_event_tablespaces {}
    set warn_event_segments {}
    set warn_event_tablespaces {}

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

    #construct the SQL statement based on user input of parameters and excluding cache segment types..

    set sql {select tablespace_name, owner, segment_name, segment_type, extents}

    # get the database version
    if {[catch {set db_version [DB_VERSION]} 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
        }
    }

    if {[string compare $db_version "80"] >= 0} {
        lappend sql , nvl(partition_name,'') 
    }
    set sql [concat $sql "from sys.dba_segments 
             where segment_type not in ('CACHE','DEFERRED ROLLBACK')
             and tablespace_name in
                ( select tablespace_name from sys.dba_tablespaces
                  where status = 'ONLINE'"]
    if {[string compare $db_version "80"] >= 0} {
        set sql [concat $sql "and contents = 'PERMANENT' "]
    }
    if {[string compare [lindex $argv 1] *] != 0} {
        lappend sql and tablespace_name
        set sql [concat $sql [lindex $argv 1]]
    }
    lappend sql )
    if {[string compare [lindex $argv 2] *] != 0} {
        lappend sql and segment_name
        set tmp [convertin $oramsg(db_characterset) [lindex $argv 2]]
        set sql [concat $sql $tmp]
    }
    if {[string compare [lindex $argv 3] *] != 0} {
        lappend sql and segment_type
        set sql [concat $sql [lindex $argv 3]]
    }

    lappend sql order by owner,segment_name,segment_type
    if {[string compare $db_version "80"] >= 0} {
        lappend sql ,partition_name 
    }

    #execute the SQL statement and trap errors.
    if {[catch {orasql $cur $sql}] == 1} {
        lappend output [convertout $oramsg(db_characterset) \
            $oramsg(errortxt)] $sql
        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
        }
    }
    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
        }
    }

    if {$oramsg(rows) == 0} {
        lappend output [msgtxt [NETWORK] nms 1007] $sql
        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
        }
    }



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


    set i 0
    set alerts 0
    set warnings 0

    while {$oramsg(rc) == 0} {
        set fetched_tbs [lindex $row 0]
        set fetched_owner [lindex $row 1]
        set fetched_name [lindex $row 2]
        set fetched_type [lindex $row 3]
        set fetched_extent [lindex $row 4]
        if {[string compare $db_version "80"] >= 0} {
            set fetched_part [lindex $row 5]
        } else {
            set fetched_part ""
        }

        if {$i == [llength $last_seg_owner]} {
            lappend last_seg_owner $fetched_owner
            lappend last_seg_name $fetched_name
            lappend last_seg_type $fetched_type
            lappend last_seg_extent $fetched_extent
            lappend last_seg_part $fetched_part
            incr i
        } else {
            while {$i < [llength $last_seg_owner]} {
                if {([string compare [lindex $last_seg_owner $i] $fetched_owner] == 0) &&
                    ([string compare [lindex $last_seg_name $i] $fetched_name] == 0) &&
                    ([string compare [lindex $last_seg_type $i] $fetched_type] == 0) &&
                    ([string compare [lindex $last_seg_part $i] $fetched_part] == 0)} {
                    # found a match, so check segment growth against thresholds
                    if {$fetched_extent > [lindex $last_seg_extent $i]} {

                        set diff [expr $fetched_extent - [lindex $last_seg_extent $i]]
 
                        if {$diff > [lindex $argv 4]} {
                            set ret_code $ALERT_EVENT
                            set tmp [convertout $oramsg(db_characterset) $fetched_tbs]
                            lappend alert_event_tablespaces $tmp
                            if {[string compare $fetched_part ""] == 0} {
                                set tmp [format "%s.%s" $fetched_owner $fetched_name]
                            } else {
                                set tmp [format "%s.%s(%s)" \
                                        $fetched_owner $fetched_name $fetched_part]
                            }
                            set tmp [convertout $oramsg(db_characterset) $tmp]
                            lappend alert_event_segments $tmp
                            lappend alert_event_growths $diff
                            incr alerts
                        } elseif {$diff > [lindex $argv 5]} {
                            if {$ret_code == $CLEAR_EVENT} {
                                set ret_code $WARNING_EVENT
                            }

                            set tmp [convertout $oramsg(db_characterset) $fetched_tbs]
                            lappend warn_event_tablespaces $tmp
                            if {[string compare $fetched_part ""] == 0} {
                                set tmp [format "%s.%s" $fetched_owner $fetched_name]
                            } else {
                                set tmp [format "%s.%s(%s)" \
                                        $fetched_owner $fetched_name $fetched_part]
                            }
                            set tmp [convertout $oramsg(db_characterset) $tmp]
                            lappend warn_event_segments $tmp
                            lappend warn_event_growths $diff
                            incr warnings
                        }
                    }
                    # update extent info to reflect lastest number of extents
                    set last_seg_extent [lreplace $last_seg_extent $i $i $fetched_extent]
                    incr i
                    break;
                } elseif {([string compare [lindex $last_seg_owner $i] $fetched_owner] == 1) ||
                          ([string compare [lindex $last_seg_name $i] $fetched_name] == 1) ||
                          ([string compare [lindex $last_seg_type $i] $fetched_type] == 1) ||
                          ([string compare [lindex $last_seg_part $i] $fetched_part] == 1)} {
                    # fetched segment is new so add info to lists
                    set last_seg_owner [linsert $last_seg_owner $i $fetched_owner]
                    set last_seg_name [linsert $last_seg_name $i $fetched_name]
                    set last_seg_type [linsert $last_seg_type $i $fetched_type]
                    set last_seg_extent [linsert $last_seg_extent $i $fetched_extent]
                    set last_seg_part [linsert $last_seg_part $i $fetched_part]
                    incr i
                    break;
                } else {
                    # segment in list was not fetched; segment must have been dropped
                    # since last interval, so remove info from lists
                    set last_seg_owner [lreplace $last_seg_owner $i $i]
                    set last_seg_name [lreplace $last_seg_name $i $i]
                    set last_seg_type [lreplace $last_seg_type $i $i]
                    set last_seg_extent [lreplace $last_seg_extent $i $i]
                    set last_seg_part [lreplace $last_seg_part $i $i]
                }
            }
        }
        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

    while {$i < [llength $last_seg_owner]} {
        # segment in list was not fetched; segment must have been dropped
        # since last interval, so remove info from lists
        set last_seg_owner [lreplace $last_seg_owner $i $i]
        set last_seg_name [lreplace $last_seg_name $i $i]
        set last_seg_type [lreplace $last_seg_type $i $i]
        set last_seg_extent [lreplace $last_seg_extent $i $i]
        set last_seg_part [lreplace $last_seg_part $i $i]
        incr i
    }


    # print out event trace info 
    ORATCL_DEBUG "fseggrth : $oramsg(oraobject) : [oratime] : $last_seg_name, $last_seg_owner, $last_seg_part, $last_seg_extent"

    
    # During initialization
    if {$initial == 1} {
        set initial 0
        return $NO_EVENT
    }

    # print out event trace info 
    ORATCL_DEBUG "fseggrth : $oramsg(oraobject) : [oratime] : alerts found : $alerts : warnings found : $warnings"


    if { $ret_code == $last_report &&
         [string compare $last_alert_segments $alert_event_segments] == 0 &&
         [string compare $last_alert_tablespaces $alert_event_tablespaces] == 0 &&
         [string compare $last_warn_segments $warn_event_segments] == 0 &&
         [string compare $last_warn_tablespaces $warn_event_tablespaces] == 0 } {
        return $NO_EVENT
    } else {
        if {$ret_code != $CLEAR_EVENT} {
            set number_of_segments 0

            for {set i 0} {$i < $alerts} {incr i} {
                lappend event_segments [lindex $alert_event_segments $i]
                lappend event_tablespaces [lindex $alert_event_tablespaces $i]
                lappend event_growths [lindex $alert_event_growths $i]
                incr number_of_segments
                if {$number_of_segments == $max_segments} {
                    break;
                }
            }

            if {$number_of_segments < $max_segments} {
                for {set i 0} {$i < $warnings} {incr i} {
                    lappend event_segments [lindex $warn_event_segments $i]
                    lappend event_tablespaces [lindex $warn_event_tablespaces $i]
                    lappend event_growths [lindex $warn_event_growths $i]
                    incr number_of_segments
                    if {$number_of_segments == $max_segments} {
                        break;
                    }
                }
            }

            if {$number_of_segments == $max_segments} {
                lappend event_segments ...
                lappend event_tablespaces ...
                lappend event_growths ...
            }

            lappend output $event_segments $event_tablespaces $event_growths
        }
        ORATCL_DEBUG "fseggrth : $oramsg(oraobject) : [oratime] : MESSAGE - $ret_code, $output"
        set last_report $ret_code
        set last_alert_segments $alert_event_segments
        set last_alert_tablespaces $alert_event_tablespaces
        set last_warn_segments $warn_event_segments
        set last_warn_tablespaces $warn_event_tablespaces
        return $ret_code 
    }
}
