# $Header: maxext.tcl 20-apr-00.08:49:35 jmansur Exp $
#
# copyright (c) 1999 by the Oracle Corporation
#
# NAME:
#
#  maxext.tcl : Event tcl file that checks if segments within a tablespace are
#               reaching their max extent.
#
# 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 segments name, tablespace names and how 
#           close they are to the max extents
#
# $Log:  $
# Revision 1.10  1996/06/24  23:56:43  yliu
# fixed bug 370148
#
# Revision 1.9  1996/02/07  01:57:45  yliu
# use character set conversion
#
# Revision 1.8  1996/01/10 02:14:07  yliu
# use message file
#
# Revision 1.7  1995/11/11 01:29:49  yliu
# set alert and warning limit to 20
#
# Revision 1.6  1995/11/08  21:30:03  yliu
# fully qualify dba tables
#
# Revision 1.5  1995/10/25  18:28:59  yliu
# add connect string to the parameter list
#

# Event definition
oradefine event /oracle/rdbms/space/maxext description=VOC-01041 \
report=VOC-01042 frequency=600
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-01043
oravardesc segment_name string default=* message=VOC-01044
oravardesc segment_type string default=* message=VOC-01045
oravardesc alert_threshold unsigned default=1 message=VOC-01046
oravardesc warning_threshold unsigned default=2 message=VOC-01047
oraresults event_segments event_tablespaces event_full
oravardesc event_segments string
oravardesc event_tablespaces string
oravardesc event_full int
orafixit /oracle/rdbms/space/chgmext
oradefine end


# Initialize global variables
#comment out to fix bug#606739
#set last_report $CLEAR_EVENT
set output ""
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
    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
        }
    }

    set sql {select tablespace_name, owner, segment_name, segment_type, max_extents - 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 tmp [convertin $oramsg(db_characterset) [lindex $argv 1]]
        set sql [concat $sql $tmp]
    }
    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]]
    }


    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 "maxext : $oramsg(oraobject) : [oratime] : "

    set alerts 0
    set warnings 0

    # Check for alert threshold
    while {$oramsg(rc) == 0} {
        if {[lindex $row 4] < [lindex $argv 4]} {
            set ret_code $ALERT_EVENT
            set tmp [convertout $oramsg(db_characterset) [lindex $row 0]]
            lappend alert_event_tablespaces $tmp
            if {[string compare $db_version "80"] >= 0} {
                set partition_segment [string compare [lindex $row 5] ""]
            } else {
                set partition_segment 0
            }
            if {$partition_segment != 0} {
                set tmp [format "%s.%s(%s)" \
                         [lindex $row 1] [lindex $row 2] [lindex $row 5]]
            } else {
                set tmp [format "%s.%s" [lindex $row 1] [lindex $row 2]]
            }
            set tmp [convertout $oramsg(db_characterset) $tmp]
            lappend alert_event_segments $tmp
            lappend alert_event_types [lindex $row 3]
            lappend alert_event_full [lindex $row 4]
            incr alerts
        } elseif {[lindex $row 4] < [lindex $argv 5]} {
            if {$ret_code == $CLEAR_EVENT} {
                set ret_code $WARNING_EVENT
            }
            set tmp [convertout $oramsg(db_characterset) [lindex $row 0]]
            lappend warn_event_tablespaces $tmp
            if {[string compare $db_version "80"] >= 0} {
                set partition_segment [string compare [lindex $row 5] ""]
            } else {
                set partition_segment 0
            }
            if {$partition_segment != 0} {
                set tmp [format "%s.%s(%s)" \
                        [lindex $row 1] [lindex $row 2] [lindex $row 5]]
            } else {
                set tmp [format "%s.%s" [lindex $row 1] [lindex $row 2]]
            }
            set tmp [convertout $oramsg(db_characterset) $tmp]
            lappend warn_event_segments $tmp
            lappend warn_event_types [lindex $row 3]
            lappend warn_event_full [lindex $row 4]
            incr warnings
        }
        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 "maxext : $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_full [lindex $alert_event_full $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_full [lindex $warn_event_full $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_full ...
            }

            lappend output $event_segments $event_tablespaces $event_full
        }
        ORATCL_DEBUG "maxext : $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 
    }
}

