# $Header: chainrow.tcl 14-apr-00.04:54:30 jmansur Exp $
#
# copyright (c) 1999 by the Oracle Corporation
#
# NAME:
#
#  chainrow.tcl : Event tcl file that checks if there are any chained rows.
#
# ARGUMENTS:
#           argv(0) == filter of the segment names to be monitored, or *
#                      for all segment names. 
#           argv(1) == filter of the segment owners to be monitored, or *
#                      for all segment owners. Owners SYS and SYSTEM are
#                      excluded.
#           argv(2) == filter of the segment types to be monitored, or *
#                      for all segment types. Segment types allowed are
#                      'TABLE' and 'CLUSTER'.
#           This script will perform an and for the above 3 arguments.
#
# RETURN:
#           $SCRIPT_FAIL or
#           $CLEAR_EVENT or
#           $NO_EVENT or
#           $WARNING_EVENT or
#           $ALERT_EVENT
#
# OUTPUT:
#           A list of the tables or clusters which have chained rows
#
# $Log:  $
# Revision 1.10  1996/06/24  23:53:59  yliu
# fixed bug 370148
#
# Revision 1.9  1996/02/07  01:46:54  yliu
# use character set conversion
#
# Revision 1.8  1996/01/10 01:29:37  yliu
# use message file
#
# Revision 1.7  1995/11/11 01:52:08  yliu
# set return limit to 20
#
# Revision 1.6  1995/11/08  21:27:36  yliu
# fully qualify dba tables
#
# Revision 1.5  1995/10/25  01:47:53  yliu
# add connect string to the parameter list
#

# Event definition
oradefine event /oracle/rdbms/perf/chainrow description=VOC-01121 \
report=VOC-01122 frequency=86400
oraarguments connect_str segment_name segment_owner segment_type 
oravardesc connect_str oracle_signon
oravardesc segment_name string default=* message=VOC-01123
oravardesc segment_owner string default=* message=VOC-01124
oravardesc segment_type string default=* message=VOC-01125
oraresults chained_segments
oravardesc chained_segments string
oradefine end


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

    # initialize the return code and output
    set ret_code $CLEAR_EVENT
    set output ""
    set err ""
    set db_version ""

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

    # create or replace the aux table chained_rows
    set sql {drop table EVT_CHAINED_ROWS}
    catch {orasql $cur $sql}

    # 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 "81"] >= 0} {
        set sql { create table EVT_CHAINED_ROWS (
                     owner_name         varchar2(30),
                     table_name         varchar2(30),
                     cluster_name       varchar2(30),
                     partition_name     varchar2(30),
                     subpartition_name  varchar2(30),
                     head_rowid         rowid,
                     analyze_timestamp  date
                ) }
    } elseif {([string compare $db_version "80"] >= 0) && ([string compare $db_version "81"] < 0)} {
        set sql {create table EVT_CHAINED_ROWS (
                    owner_name          varchar2(30),
                    table_name          varchar2(30),
                    cluster_name        varchar2(30),
                    partition_name      varchar2(30),
                    head_rowid          rowid,
                    analyze_timestamp   date
                 ) }
    } else {
        set sql {create table EVT_CHAINED_ROWS (
                    owner_name    varchar2(30),
                    table_name    varchar2(30),
                    cluster_name  varchar2(30),
                    head_rowid    rowid,
                    timestamp     date
                 ) }
     }

    if {[catch {orasql $cur $sql}]} {
        lappend output [convertout $oramsg(db_characterset) \
            $oramsg(errortxt)] $sql
        catch {oraclose $cur} err
        catch {oralogoff $lda} err
 	lappend output $err
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }

    # fetch info on all segments to be monitored.
    # NOTE: Segment name and segment type could be non-ascii, should do a
    # character set conversion
    if {[string compare $db_version "80"] >= 0} {
        set sql {select segment_name, segment_type, owner, partition_name from sys.dba_segments 
                 where owner not in ('SYS', 'SYSTEM') }
    } else {
        set sql {select segment_name, segment_type, owner from sys.dba_segments 
                 where owner not in ('SYS', 'SYSTEM') }
    }
    if {[string compare [lindex $argv 1] *] != 0} {
        lappend sql and segment_name
        set tmp [convertin $oramsg(db_characterset) [lindex $argv 1]]
        set sql [concat $sql $tmp]
    }
    if {[string compare [lindex $argv 2] *] != 0} {
        lappend sql and owner
        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]]
    } else {
        set sql [concat $sql { and segment_type in ('TABLE', 'CLUSTER', 'TABLE PARTITION')}]
    }
    if {[catch {orasql $cur $sql}]} {
        lappend output [convertout $oramsg(db_characterset) \
            $oramsg(errortxt)] $sql
        catch {oraclose $cur} err
        catch {oralogoff $lda} err
        lappend output $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
	lappend output $err
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }

    while {$oramsg(rc) == 0} {
        lappend segments [lindex $row 0]
        lappend segment_type [lindex $row 1]
        lappend segment_owner [lindex $row 2]
        if {[string compare $db_version "80"] >= 0} {
            lappend segment_partition [lindex $row 3]
        }
        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
            }
    	}
    }


    # analyze all segments that need monitoring and write them
    # into the table chained_rows
    set i 0
    set loop [llength $segments]
    while {$i < $loop} { 
        if {[string compare [lindex $segment_type $i] "TABLE PARTITION"] == 0} {
            set sql [format "analyze table \"%s\".\"%s\" partition (\"%s\") list chained rows into EVT_CHAINED_ROWS" \
                            [lindex $segment_owner $i] \
                            [lindex $segments $i] \
                            [lindex $segment_partition $i]]
        } else {
            set sql [format "analyze %s \"%s\".\"%s\" list chained rows into EVT_CHAINED_ROWS"  \
                     [lindex $segment_type $i] \
                     [lindex $segment_owner $i] \
                     [lindex $segments $i]]
        }

        if {[catch {orasql $cur $sql}]} {
          if { ($oramsg(rc) != 942) && ($oramsg(rc) != 943) } {
            lappend output [convertout $oramsg(db_characterset) \
                $oramsg(errortxt)] $sql
            catch {oraclose $cur} err
            catch {oralogoff $lda} err
	    lappend output $err
            if {$last_report == $SCRIPT_FAIL} {
                return $NO_EVENT
            } else {
                set last_report $SCRIPT_FAIL
                return $SCRIPT_FAIL
            }
          }
        }
        incr i
    } 
    catch {oracommit $cur} err    

    set number_of_alerts 0
    set chained_segments ""
    set all_chained_segments ""

    # query the chained_rows table for the results of analyze
    if {[string compare $db_version "80"] >= 0} {
        set sql {select partition_name, table_name, owner_name from EVT_CHAINED_ROWS
                 group by partition_name, table_name, owner_name}
        if {[catch {orasql $cur $sql}]} {
            lappend output [convertout $oramsg(db_characterset) \
                $oramsg(errortxt)] $sql
            catch {oraclose $cur} err
            catch {oralogoff $lda} err
	    lappend output $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
            }
    	}
        while {$oramsg(rc) == 0} {
            set partition_segment [string compare [lindex $row 0] ""]
            if {$partition_segment != 0} {
                set tmp [format "%s.%s(%s)" [lindex $row 2] [lindex $row 1] [lindex $row 0]]
            } else {
                set tmp [format "%s.%s" [lindex $row 2] [lindex $row 1]]
            }
            set tmp [convertout $oramsg(db_characterset) $tmp]

            if {$number_of_alerts < 20} {
                lappend chained_segments $tmp
            }
            lappend all_chained_segments $tmp

            set ret_code $ALERT_EVENT
            incr number_of_alerts
            # only send first 20 to oms, but compare all so don't miss diffs
            if {$number_of_alerts == 20} {
                lappend chained_segments ...
            }
            
            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
                }
    	    }
        }
    } else {
        set sql {select table_name, owner_name from EVT_CHAINED_ROWS
                 group by table_name, owner_name}
        if {[catch {orasql $cur $sql}]} {
            lappend output [convertout $oramsg(db_characterset) \
                $oramsg(errortxt)] $sql
            catch {oraclose $cur} err
            catch {oralogoff $lda} err
	    lappend output $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
            }
    	}
        while {$oramsg(rc) == 0} {
            set tmp [format "%s.%s" [lindex $row 1] [lindex $row 0]]
            set tmp [convertout $oramsg(db_characterset) $tmp]


            if {$number_of_alerts < 20} {
                lappend chained_segments $tmp
            }
            lappend all_chained_segments $tmp

            set ret_code $ALERT_EVENT
            incr number_of_alerts
            # only send first 20 to oms, but compare all so don't miss diffs
            if {$number_of_alerts == 20} {
                lappend chained_segments ...
            }

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

    # printout trace info
    ORATCL_DEBUG "chainrow : $oramsg(oraobject) : [oratime] : $number_of_alerts,$chained_segments"


    # drop the aux table and log off
    set sql {drop table EVT_CHAINED_ROWS}
    catch {orasql $cur $sql}
    catch {oraclose $cur} err
    catch {oralogoff $lda} err

    # return
    if { $ret_code == $last_report && [string compare $last_chained_segments $all_chained_segments] == 0 } {
        return $NO_EVENT
    } else {
        append output $chained_segments

        ORATCL_DEBUG "chainrow : $oramsg(oraobject) : [oratime] : MESSAGE - $ret_code, $output"
        set last_report $ret_code
        set last_chained_segments $all_chained_segments
        return $ret_code 
    }


}

