# $Header: multext.tcl 20-apr-00.08:49:54 jmansur Exp $
#
# copyright (c) 1999 by the Oracle Corporation
#
# NAME:
#
#  multext.tcl : Event tcl file that checks if segments have too many extents.
#
# 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 
#           many extents are currently allocated for the segment
#
# $Log:  $

#

# Event definition
oradefine event /oracle/rdbms/space/multext description=VOC-01328 \
report=VOC-01329 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-01330
oravardesc segment_name string default=* message=VOC-01331
oravardesc segment_type string default=* message=VOC-01332
oravardesc alert_threshold unsigned default=50 message=VOC-01333
oravardesc warning_threshold unsigned default=30 message=VOC-01334
oraresults event_segments event_tablespaces event_extents
oravardesc event_segments string
oravardesc event_tablespaces string
oravardesc event_extents int
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 owner, segment_name, tablespace_name, 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 "multext : $oramsg(oraobject) : [oratime] : "


    # Check for alert threshold
    set alerts 0
    set warnings 0

    while {$oramsg(rc) == 0} {
        if {[lindex $row 3] > [lindex $argv 4]} {
            set ret_code $ALERT_EVENT

            if {[string compare $db_version "80"] >= 0} {
                set partition_segment [string compare [lindex $row 4] ""]
            } else {
                set partition_segment 0
            }
            if {$partition_segment != 0} {
                set tmp [format "%s.%s(%s)" \
                         [lindex $row 0] [lindex $row 1] [lindex $row 4]]
            } else {
                set tmp [format "%s.%s" [lindex $row 0] [lindex $row 1]]
            }
            set tmp [convertout $oramsg(db_characterset) $tmp]
            lappend alert_event_segments $tmp
            set tmp [convertout $oramsg(db_characterset) [lindex $row 2]]
            lappend alert_event_tablespaces $tmp
            lappend alert_event_extents [lindex $row 3]
            incr alerts
        } elseif {[lindex $row 3] > [lindex $argv 5]} {
            if {$ret_code == $CLEAR_EVENT} {
                set ret_code $WARNING_EVENT
            }
            if {[string compare $db_version "80"] >= 0} {
                set partition_segment [string compare [lindex $row 4] ""]
            } else {
                set partition_segment 0
            }
            if {$partition_segment != 0} {
                set tmp [format "%s.%s(%s)" \
                        [lindex $row 0] [lindex $row 1] [lindex $row 4]]
            } else {
                set tmp [format "%s.%s" [lindex $row 0] [lindex $row 1]]
            }
            set tmp [convertout $oramsg(db_characterset) $tmp]
            lappend warn_event_segments $tmp
            set tmp [convertout $oramsg(db_characterset) [lindex $row 2]]
            lappend warn_event_tablespaces $tmp
            lappend warn_event_extents [lindex $row 3]
            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 "multext : $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_extents [lindex $alert_event_extents $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_extents [lindex $warn_event_extents $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_extents ...
            }

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

