# $Header: chunksml.tcl 20-apr-00.08:39:50 jmansur Exp $
#
# copyright (c) 1999 by the Oracle Corporation
#
# NAME:
#
#  chunksml.tcl : Event tcl file that checks if the largest chunk in a 
#                 tablespace is large enough for additional 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 tablespace name, segment names, segment types,
#           next extent sizes and largest chunk in the tablespace.
#
# $Log:  $
# Revision 1.11  1996/06/24  23:56:33  yliu
# fixed bug 370148
#
# Revision 1.10  1996/02/07  01:57:31  yliu
# use character set conversion
#
# Revision 1.9  1996/01/10 02:13:50  yliu
# use message file
#
# Revision 1.8  1995/11/11 01:30:21  yliu
# set alert and warning limit to 20
#
# Revision 1.7  1995/11/08  21:29:12  yliu
# fully qualify dba tables
#
# Revision 1.6  1995/10/31  21:25:11  yliu
# outer join dba_free_space in the sql statement
#

# Event definition
oradefine event /oracle/rdbms/space/chunksml description=VOC-01051 \
report=VOC-01052 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-01053
oravardesc segment_name string default=* message=VOC-01054
oravardesc segment_type string default=* message=VOC-01055
oravardesc alert_threshold unsigned default=1 message=VOC-01056
oravardesc warning_threshold unsigned default=2 message=VOC-01057
oraresults segments tablespaces largest_chunk
oravardesc segments string
oravardesc tablespaces string
oravardesc largest_chunk unsigned
orafixit /oracle/rdbms/space/chgnext
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 {}

proc nextext {base pct_increase times db_block_size} {
    # procedure to calculate the accumulated next extent size

    set blocks [expr ceil ([expr $base / $db_block_size])]
    set result [expr $blocks * $db_block_size]
    set pct_increase [format "%d.0" $pct_increase]

    while {$times > 1} {
        set blocks [expr ceil ([expr $blocks * (1 + ($pct_increase / 100))])]
        set result [expr $result + [expr $blocks * $db_block_size]]
        incr times -1
    }

    return $result
}


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

    # calculate the smallest chunks
    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 $db_version "80"] >= 0} {
        set sql [concat $sql "and contents = 'PERMANENT'"]
    }
    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
            }
        }
    }

    # get the biggest chunk size for each tablespace
    # for 8 or later version, we need to consider auto extend for each data file
    set i 0
    set loop [llength $tablespaces]
    while {$i < $loop} {

        set tbspname [format '%s' [lindex $tablespaces $i]]
        set chunk_size($tbspname) 0

        # Retrieve the biggest chunk size information
        if {[string compare $db_version "80"] >= 0} {
            set sql {select f.bytes, f.maxbytes, s.bytes from sys.dba_data_files f, v$datafile d, sys.dba_free_space s
                    where f.file_id = d.file# and f.file_id = s.file_id and d.status in ('ONLINE', 'SYSTEM')
                    and s.tablespace_name = }
            set sql [concat $sql $tbspname ]
        } else {
            set sql {select floor(max(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 ]
        }

        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 "chunksml: $oramsg(oraobject) : $sql : $debug_msg"
            ORATCL_DEBUG "        : tablespace $tbspname may have been dropped by user."
            incr i
            continue
        }

        if {[string compare $db_version "80"] >= 0} {
            while {$oramsg(rc) == 0} {
                set file_bytes [format "%f" [lindex $row 0]]
                set file_maxbytes [format "%f" [lindex $row 1]]
                set free_bytes [format "%f" [lindex $row 2]]
                if { $file_bytes < $file_maxbytes} {
                    set extend_chunk_size [expr $file_maxbytes - $file_bytes]
                } else {
                    set extend_chunk_size 0
                }

                if { $extend_chunk_size < $free_bytes } {
                    set this_chunk_size $free_bytes
                } else {
                    set this_chunk_size $extend_chunk_size
                }

                if {$this_chunk_size > $chunk_size($tbspname)} {
                    set chunk_size($tbspname) $this_chunk_size
                }

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

            set chunk_size($tbspname) [divide $chunk_size($tbspname) 1024]
        } else {
            set chunk_size($tbspname) [format "%f" [lindex $row 0]]
        }

        ORATCL_DEBUG "chunksml: $oramsg(oraobject) : The biggest chunk for tablespace $tbspname is $chunk_size($tbspname)."
        incr i
    }

# get db block size from v$parameter
    set sql {select value from v$parameter where name = 'db_block_size'}

    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 1015]
        ORATCL_DEBUG "chunksml: $oramsg(oraobject) : $sql : $output"
        ORATCL_DEBUG "        : failed to get db block size."
        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 db_block_size [divide [format "%f" [lindex $row 0]] 1024]
    ORATCL_DEBUG "chunksml: $oramsg(oraobject) : db_block_size = $db_block_size"

    set sql {select s.tablespace_name, s.owner, s.segment_name, s.segment_type,
             ceil(s.next_extent/1024), s.pct_increase
            }
    if {[string compare $db_version "80"] >= 0} {
        lappend sql , nvl(s.partition_name,'') 
    }
    set sql [concat $sql "from sys.dba_segments s
             where s.segment_type not in ('CACHE', 'DEFERRED ROLLBACK')
             and s.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 s.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 s.segment_type
        set sql [concat $sql [lindex $argv 3]]
    }
    lappend sql group by s.tablespace_name, s.owner, s.segment_name, s.segment_type,
    lappend sql s.next_extent,s.pct_increase
    if {[string compare $db_version "80"] >= 0} {
        lappend sql ,s.partition_name 
    }

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

    # Check for crossings of the alert and warning thresholds
    set alerts 0
    set warnings 0
    
    while {$oramsg(rc) == 0} {
        set tbspname [format '%s' [lindex $row 0]]
        set max_chunk_size $chunk_size($tbspname)
        set alert_size [nextext [lindex $row 4] [lindex $row 5] [lindex $argv 4] $db_block_size]
        set warning_size [nextext [lindex $row 4] [lindex $row 5] [lindex $argv 5] $db_block_size]

        if {$max_chunk_size < $alert_size} {
            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 6] ""]
            } else {
                set partition_segment 0
            }
            if {$partition_segment != 0} {
                set tmp [format "%s.%s(%s)" \
                        [lindex $row 1] [lindex $row 2] [lindex $row 6]]
            } 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_chunk $max_chunk_size
            incr alerts
        } elseif {$max_chunk_size < $warning_size} {
            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 6] ""]
            } else {
                set partition_segment 0
            }
            if {$partition_segment != 0} {
                set tmp [format "%s.%s(%s)" \
                        [lindex $row 1] [lindex $row 2] [lindex $row 6]]
            } 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_chunk $max_chunk_size
            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 "chunksml : $oramsg(oraobject) : [oratime] : alerts found : $alerts : warnings found : $warnings"

    # return
    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_chunk [lindex $alert_event_chunk $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_chunk [lindex $warn_event_chunk $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_chunk ...
            }

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