# $Header: tbspfull.tcl 20-apr-00.09:18:33 jmansur Exp $
#
# copyright (c) 1999 by the Oracle Corporation
#
# NAME:
#
#  tbspfull.tcl : Event tcl file that checks if the tablespace is full
#
# 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) == threshold value for alert
#           argv(3) == threshold value for warning
#
# RETURN:
#           $SCRIPT_FAIL or
#           $CLEAR_EVENT or
#           $NO_EVENT or
#           $WARNING_EVENT or
#           $ALERT_EVENT
#
# OUTPUT:
#           The free space in the tablespace.
#
# $Log:  $
#

# Event definition
oradefine event /oracle/rdbms/space/tbspfull description=VOC-01281 \
report=VOC-01282
oraarguments connect_str tablespace_name alert_threshold warning_threshold
oravardesc connect_str oracle_signon
oravardesc tablespace_name string default=* message=VOC-01283
oravardesc alert_threshold number default=90 minimum=0 maximum=100 message=VOC-01284
oravardesc warning_threshold number default=80 minimum=0 maximum=100 message=VOC-01285
oraresults event_tablespaces event_ratios event_free_kbytes
oravardesc event_tablespaces string
oravardesc event_ratios number
oravardesc event_free_kbytes 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_warning_tablespaces {}

# 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 env
    global last_alert_tablespaces last_warning_tablespaces

    # initialize the return code and output
    set ret_code $CLEAR_EVENT
    set output ""
    set err ""
    set db_version ""
    set alert_tablespaces {}
    set warning_tablespaces {}

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

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

    set sql {select tablespace_name from sys.dba_tablespaces where status = 'ONLINE' }
    if {[string compare [lindex $argv 1] *] != 0} {
        lappend sql and tablespace_name
        set tbspname [convertin $oramsg(db_characterset) [lindex $argv 1]]
        set sql [concat $sql $tbspname]
    }

    if {[catch {orasql $cur $sql}]} {
        lappend output [convertout $oramsg(db_characterset) \
            $oramsg(errortxt)] $sql
        oraclose $cur
        oralogoff $lda
        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 1020] $sql
        oraclose $cur
        oralogoff $lda
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }

    while {$oramsg(rc) == 0} {
        lappend tablespaces [lindex $row 0]
        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
            }
        }
    }

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

    # get the maximum size of each tablespace
    # for 8 or later version, we need to check each data file
    # if auto extend enabled, we use max_kbytes instead of kbytes
    set i 0
    set loop [llength $tablespaces]
    while {$i < $loop} { 
        set tbspname [format '%s' [lindex $tablespaces $i]]  
        set max_kbytes($tbspname) 0
        set current_kbytes($tbspname) 0

        # Retrieve total size information
        if {[string compare $db_version "80"] >= 0} {
            set sql {select f.bytes/1024, f.maxbytes/1024, f.file_name }
        } else {
            set sql {select f.bytes/1024 }
        }
        set tmpsql {from sys.dba_data_files f, v$datafile d
                    where f.file_id = d.file# and d.status in ('ONLINE', 'SYSTEM')
                    and tablespace_name = }
        set sql [concat $sql $tmpsql]
        set sql [concat $sql $tbspname ]

        if {[catch {orasql $cur $sql}]} {
            lappend output [convertout $oramsg(db_characterset) \
                $oramsg(errortxt)] $sql
            oraclose $cur
            oralogoff $lda
            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} {
            set debug_msg [msgtxt [NETWORK] nms 1015]
            ORATCL_DEBUG "tbspfull: $sql : $debug_msg"
            ORATCL_DEBUG "tbspfull: tablespace $tbspname may have been dropped by user."
            incr i
            continue
        }

        while {$oramsg(rc) == 0} {

            set this_kbytes [format "%f" [lindex $row 0]]
            set current_kbytes($tbspname) [expr $current_kbytes($tbspname) + $this_kbytes]

            if {[string compare $db_version "80"] >= 0} {
                set this_max_kbytes [format "%f" [lindex $row 1]]
                if { $this_kbytes > $this_max_kbytes } {
                    set max_kbytes($tbspname) [expr $max_kbytes($tbspname) + $this_kbytes]
                } else {
                    set data_file [lindex $row 2]
                    if { [catch {diskusage $data_file} disk_usage] } {
                        lappend output [format [msgtxt [NETWORK] nms 1005] diskfull.tcl]
                        lappend output [msgtxt [NETWORK] nms 1078]
                        if {$last_report == $SCRIPT_FAIL} {
                            return $NO_EVENT
                        } else {
                            set last_report $SCRIPT_FAIL
                            return $SCRIPT_FAIL
                        }
                    }

                    set avail [lindex [lindex $disk_usage 2] 0]
                    if { $this_max_kbytes > [expr $this_kbytes + $avail] } {
                        set max_kbytes($tbspname) [expr $max_kbytes($tbspname) + [expr $this_kbytes + $avail] ]
                    } else {
                        set max_kbytes($tbspname) [expr $max_kbytes($tbspname) + $this_max_kbytes]
                    }
                }
            } else {
                set max_kbytes($tbspname) [expr $max_kbytes($tbspname) + $this_kbytes]
            }

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

        incr i
    }

    # print out event trace info 
    set i 0
    set loop [llength $tablespaces]
    while {$i < $loop} {
      set tbspname [format '%s' [lindex $tablespaces $i]]  
      ORATCL_DEBUG "tbspfull: $oramsg(oraobject): $tbspname, current = $current_kbytes($tbspname), max = $max_kbytes($tbspname) "  
      incr i
    }

    # Check for crossings of the alert and warning thresholds
    set alerts 0
    set warnings 0

    # Check for alert threshold
    set number_of_tablespaces 0
    set i 0
    set loop [llength $tablespaces]
    while {$i < $loop} { 
        set tbspname [format '%s' [lindex $tablespaces $i]]  

        # Retrieve free space information
        set sql {select sum(f.bytes)/1024, floor(sum(f.bytes)/1024)
                 from sys.dba_free_space f, v$datafile d
                 where f.file_id = d.file# and d.status in ('ONLINE', 'SYSTEM')
                 and tablespace_name = }
        set sql [concat $sql $tbspname ]  
        set sql [concat $sql " group by tablespace_name"]

        if {[catch {orasql $cur $sql}]} {
            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} {
            set free_kbytes 0
        } else {
            set free_kbytes [expr double ([lindex $row 0])]
        }

        if {$current_kbytes($tbspname) == 0} {
            ORATCL_DEBUG "tbspfull: no size for $tbspname"
            incr i
            continue
        }
        set ts_size_used [expr $current_kbytes($tbspname) - $free_kbytes]

        if {[string compare $db_version "80"] >= 0} {
          set ratio [expr [divide $ts_size_used $max_kbytes($tbspname)] * 100]
          set free_kbytes [expr $max_kbytes($tbspname) - $ts_size_used]
        } else {
          set ratio [expr [divide $ts_size_used $current_kbytes($tbspname)] * 100]
        }
        set ratio [format "%3.2f" $ratio]
        ORATCL_DEBUG "tbspfull:  $tbspname, used = $ts_size_used, ratio = $ratio, available = $free_kbytes"  

        # Check for alert and warning threshold
        if {$ratio > [lindex $argv 2]} {
            set ret_code $ALERT_EVENT
            set tmp [convertout $oramsg(db_characterset) [lindex $tablespaces $i]]
            lappend alert_tablespaces $tmp
            lappend alert_ratios $ratio
            lappend alert_free_kbytes $free_kbytes
            incr alerts
        } elseif {$ratio > [lindex $argv 3]} {
            if {$ret_code == $CLEAR_EVENT} {
                set ret_code $WARNING_EVENT
            }
            set tmp [convertout $oramsg(db_characterset) [lindex $tablespaces $i]]
            lappend warning_tablespaces $tmp
            lappend warning_ratios $ratio
            lappend warning_free_kbytes $free_kbytes
            incr warnings
        }

        incr i
    }

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

    if { $ret_code == $last_report &&
         [string compare $last_alert_tablespaces $alert_tablespaces] == 0 &&
         [string compare $last_warning_tablespaces $warning_tablespaces] == 0 } {
        return $NO_EVENT
    } else {

        # only send first 20 to oms, but compare all so don't miss diffs
        set max_tablespaces 20

        if {$ret_code != $CLEAR_EVENT} {
            set number_of_tablespaces 0

            for {set i 0} {$i < $alerts} {incr i} {
                lappend event_tablespaces [lindex $alert_tablespaces $i]
                lappend event_ratios [lindex $alert_ratios $i]
                lappend event_free_kbytes [lindex $alert_free_kbytes $i]
                incr number_of_tablespaces
                if {$number_of_tablespaces == $max_tablespaces} {
                    break;
                }
            }

            if {$number_of_tablespaces < $max_tablespaces} {
                for {set i 0} {$i < $warnings} {incr i} {
                    lappend event_tablespaces [lindex $warning_tablespaces $i]
                    lappend event_ratios [lindex $warning_ratios $i]
                    lappend event_free_kbytes [lindex $warning_free_kbytes $i]
                    incr number_of_tablespaces
                    if {$number_of_tablespaces == $max_tablespaces} {
                        break;
                    }
                }
            }

            if {$number_of_tablespaces == $max_tablespaces} {
                lappend event_tablespaces ...
                lappend event_ratios ...
                lappend event_free_kbytes ...
            }

            lappend output $event_tablespaces $event_ratios $event_free_kbytes
        }

        ORATCL_DEBUG "tbspfull : $oramsg(oraobject) : [oratime] : MESSAGE - $ret_code, $output"
        set last_report $ret_code
        set last_alert_tablespaces $alert_tablespaces
        set last_warning_tablespaces $warning_tablespaces
        return $ret_code 
    }
}
