#  $Header: emdb/sysman/admin/scripts/problemTbsp.pl /st_emdbsa_11.2/1 2009/03/26 11:00:10 mpawelko Exp $
#
# Copyright (c) 2001, 2009, Oracle and/or its affiliates. All rights reserved. 
#
#    NAME
#      problemTbsp.pl - <one-line expansion of the name>
#
#    DESCRIPTION
#      Event file that checks the tablespaces with problem segments
#
#    OUTPUT:
#       The percentage of space used in the tablespaces
#       and the problem segments within
#
#    NOTES
#      <other useful comments, qualifications, etc.>
#
#    MODIFIED   (MM/DD/YY)
#      mpawelko  03/18/09 - XBranchMerge 6143150: use sys.fet$, dba_lmt_free_space instead
#                           of dba_free_space for better perf, avoid recycle bin
#      rreilly   07/12/05 - bug 4373323 exclude recycle bin tables (local only)
#      rreilly   07/01/05 - bug 4294495 exclude tbsp groups 
#      rreilly   12/23/04 - bug 4087181 check extents not able to extend for 
#                           converted locally managed tbsps 
#      rreilly   12/15/04 - bug 3306591 order sql by tablespace name and file 
#                           name to get datafile logic to work correctly 
#      rreilly   08/05/04 - fix != to be ne when comparing dir names (strings)
#      glavash   12/09/03 - fix multiple datafile, txn -> sbadrina_bug-3306591
#      lhan      11/25/03 - More performance improvement
#      lhan      11/11/03 - More performance improvement, remove GTT 
#      glavash   11/06/03 - change logic flow
#      glavash   11/03/03 - add ts# to seg$ queries 
#      lhan      10/30/03 - turn off autocommit
#      lhan      10/28/03 - Use GTT to fix 3206888 
#      pbantis   07/11/03 - Convert from Oraperl to Perl DBI
#      aaitghez  05/27/03 - review comments
#      aaitghez  05/19/03 - remove credentials from environment
#      lhan      05/14/03 - remove db_version check 
#      lhan      04/15/03 - No output if tbsp has no seg problem
#      lhan      04/14/03 - change from bytes_cached to bytes_used
#      lhan      04/08/03 - split problemTbsp into two
#      xshen     02/03/03 - insufficient privilege error message
#      xshen     07/22/02 - remove group by, order by
#      xshen     06/26/02 - fix closing un-opened cursor
#      xxu       06/24/02 - remove /usr/local/bin/perl
#      xshen     04/19/02 - cut from new tcl: chunk_sml_segs, max_ext_segs events
#      icherpak  10/05/01 - remove 9i specific querries
#      qsong     09/07/01 - fix bug 1982105
#      qsong     09/04/01 - fix bug 1974803
#      xxu       08/15/01 - handle NULL in extent column
#      aaitghez  08/05/01 - adding dbms_registration call.
#      qsong     08/09/01 - fix MaxExt message Null problem
#      qsong     08/07/01 - fix to handle multi data files
#      xxu       07/31/01 - add trace support
#      xxu       07/25/01 - fix for 80x
#      qsong     07/23/01 - exit when error using diskusage
#      qsong     07/15/01 -
#      aaitghez  06/28/01 - removing setting ORACLE_HOME.
#      aaitghez  06/22/01 - naming convention.
#      aaitghez  06/22/01 - set oracle_home at start of script.
#      xxu       05/29/01 - rm 73 support, kbytes-> mbytes
#      xxu       05/21/01 - cut over from tcl
#      xxu       05/21/01 - Creation
#

use strict;
use DBI;

require "emd_common.pl";
require "semd_common.pl";

my %stdinArgs = get_stdinvars();
my $username = $stdinArgs{"EM_TARGET_USERNAME"};
my $password = $stdinArgs{"EM_TARGET_PASSWORD"};
my $address = $ENV{EM_TARGET_ADDRESS};
my $role = $ENV{EM_TARGET_ROLE};
my $mode = 0;
if($role =~ /SYSDBA/i)
{
  $mode = 2;
}
elsif($role =~ /SYSOPER/i)
{
  $mode = 4;
}

#my $username = "sysman";
#my $password = "sysman";
#my $address = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=))(CONNECT_DATA=(SID=)))";

my $lda = DBI->connect('dbi:Oracle:', "$username@".$address, "$password", 
    {ora_session_mode => $mode, PrintError => 0, RaiseError => 0})
    or die "em_error=Could not connect to $username/$address: $DBI::errstr\n";
register_metric_call($lda);

# check if the tablespace and segment sql condition clause is set
# if so, append the string value to the proper sql statment
#
my $hasTbspCondition = 0;
my $hasSegCondition  = 0;
my $target_tbsp_condition_clause = $ENV{EM_TARGET_TABLESPACE_CONDITION_CLAUSE};
my $target_seg_condition_clause  = $ENV{EM_TARGET_SEGMENT_CONDITION_CLAUSE};

if ($target_tbsp_condition_clause ne "" ) {
   $hasTbspCondition = 1;
}
if ($target_seg_condition_clause ne "" ) {
   $hasSegCondition = 1;
}

#-------------------------------------------------------------------------------
# Error message for insufficient dbsnmp privilege in v817 database 
#-------------------------------------------------------------------------------
my $error_detail = 
    "Make sure that user ".$stdinArgs{"EM_TARGET_USERNAME"}." has been granted "
    . "select on sys.ts\$, sys.user\$, sys.seg\$, "
    . "sys.obj\$ and sys.sys_objects\n" ;

#-------------------------------------------------------------------------------
# Fix bug 3067929 - exit if database is read only
#-------------------------------------------------------------------------------

my $ro_sql = "select open_mode from v\$database ";

my $ro_cur = $lda->prepare($ro_sql)
    or die "em_error=prepare($ro_sql): $DBI::errstr\n";
$ro_cur->execute()
    or die "em_error=ro_cur->execute(): $DBI::errstr\n";

my @fetch_row;
@fetch_row = $ro_cur->fetchrow_array();
my $open_mode = $fetch_row[0];

if ( $open_mode eq "READ ONLY" ) {
    EMD_PERL_DEBUG("open_mode: $open_mode\n");
    exit 0;
}

#-------------------------------------------------------------------------------
# Retrieve a list of all online, permanent (contents=0), dictionary managed (bitmapped=0)
# tablespaces, keeping track of their name and other necessary information upfront
# Note: (ts.bitmapped != 0 AND bitand(ts.flags,3) = 0)) includes dictionary managed
# converted to locally managed
# Note: bitand(flags,2048) != 2048 excludes tablespace groups
# NOTE: bug 3306591 MUST order by tablespace_name or get duplicate key metric error
#-------------------------------------------------------------------------------

my $sql = "select a.tablespace_name,a.meg, a.maxmeg,a.file_name,a.file_id,a.ts#, a.blocksize,a.flag, "
 . "     b.freebytes, b.maxfreebytes,nvl(c.ts#,-99) "
 . "     from "
 . "     (select   f.tablespace_name, (f.bytes)/1048576 meg,  "
 . "       (f.maxbytes)/1048576 maxmeg, f.file_name, f.file_id,  "
 . "       ts.ts#, ts.blocksize,  "
 . "        DECODE(BITAND(ts.flags,3),1,ts.dflminlen,0) flag "
 . "        from     sys.ts\$ ts, sys.dba_data_files f  "
 . "        where    ts.contents\$ = 0 and ts.online\$ = 1 "
 . "        AND ( ts.bitmapped = 0   OR (ts.bitmapped != 0 AND bitand(ts.flags,3) = 0)) "
 . "        AND bitand(flags,2048) != 2048 "
 . "        and      f.tablespace_name = ts.name) a, "
 . "     (SELECT ts.name tablespace_name, SUM(e.length * ts.blocksize/1048576) freebytes, "
 . "        MAX(e.length * ts.blocksize/1024) maxfreebytes "
 . "        FROM sys.fet\$ e, sys.ts\$ ts "
 . "        WHERE ts.ts# = e.ts# GROUP BY ts.name "
 . "      UNION ALL "
 . "      SELECT ts.name tablespace_name, SUM(e.blocks * ts.blocksize/1048576) freebytes, "
 . "        MAX(e.blocks * ts.blocksize/1024) maxfreebytes "
 . "        FROM sys.dba_lmt_free_space e, sys.ts\$ ts "
 . "        WHERE ts.ts# = e.tablespace_id GROUP BY ts.name) b, "
 . "     (select distinct s.ts# from sys.seg\$ s where s.maxexts < 2100000000 and s.maxexts - extents < 2 "
 . "      and exists (select 1 from sys.sys_objects so where so.header_block = s.block# and "
 . "      s.file#  = so.header_file and s.ts# = so.ts_number)) c"
 . "      where a.tablespace_name = b.tablespace_name and a.ts# = c.ts#(+) order by a.tablespace_name, a.file_name";

# the tablespace sql condition clause is set, append it to the sql statement
if ( $hasTbspCondition == 1 ) {
   $sql .= " and tablespace_name $target_tbsp_condition_clause";
}

my $cur = $lda->prepare($sql)
    or die "em_error=prepare($sql): $DBI::errstr\n";
$cur->execute()
    or die "em_error=cur->execute(): $DBI::errstr\n";

my $tbsp_name;

my @all_tbsp_names;
my @all_tbsp_meg;
my @all_tbsp_maxmeg;
my @all_tbsp_file_name;
my @all_tbsp_file_id;
my @all_tbsp_ts;
my @all_tbsp_blocksize;
my @all_tbsp_flag;
my @all_tbsp_freebytes;
my @all_tbsp_maxfreebytes;
my @all_tbsp_ext_ts;

my @fetch_row;

while ( @fetch_row = $cur->fetchrow_array() ) {
    $tbsp_name = $fetch_row[0];
    push (@all_tbsp_names, $tbsp_name);
    push (@all_tbsp_meg, $fetch_row[1]);
    push (@all_tbsp_maxmeg, $fetch_row[2]);
    push (@all_tbsp_file_name, $fetch_row[3]);
    push (@all_tbsp_file_id, $fetch_row[4]);
    push (@all_tbsp_ts, $fetch_row[5]);
    push (@all_tbsp_blocksize, $fetch_row[6]);
    push (@all_tbsp_flag, $fetch_row[7]);
    push (@all_tbsp_freebytes, $fetch_row[8]);
    push (@all_tbsp_maxfreebytes, $fetch_row[9]);
    push (@all_tbsp_ext_ts, $fetch_row[10]);
    
}
warn "Data fetching terminated early by error: $DBI::errstr\n"
    if $DBI::err;

#-------------------------------------------------------------------------------
# For each tablespace determine its maximum size and free space in order to
# determine pctused.  Also for permanent, dictionary managed tablespaces
# determine if the tablespace contains any segments that are unable to extend
# or are approaching their maxextents.
#-------------------------------------------------------------------------------


# query for chunk_sml_segs and max_ext_segs, EXT_size will be determined
#       according to the flag value of that tablespace
#
my $sql_chunk_sml_test    = "";
my $sql_chunk_sml_segs    = "";
my $sql_chunk_sml_segs_ct = "";
my $sql_max_ext_segs      = "";
my $sql_max_ext_segs_ct   = "";

my $cur_chunk_sml_test_open = 0;
my $cur_chunk_sml_test;

my $cur_chunk_sml_segs_open = 0;
my $cur_chunk_sml_segs;

my $cur_max_ext_segs_open = 0;
my $cur_max_ext_segs;



my $cur_chunk_sml_segs_ct_open = 0;
my $cur_chunk_sml_segs_ct;

my $cur_max_ext_segs_ct_open = 0;
my $cur_max_ext_segs_ct;

my $previous_dir="N/A";
my $avail_kbytes = 0;
my $avail_mbytes = 0;
my @diskusage;
my $prev_EXT_size = "N/A";

#-------------------------------------------------------------------------------
# Loop through tablespaces
#-------------------------------------------------------------------------------

OUTER_LOOP: for ( my $i = 0; $i <= $#all_tbsp_names; $i++ )
{
    $tbsp_name = $all_tbsp_names[$i];

    my $tbsp_status = "ONLINE, READ WRITE";

 
   	# new_query: all permanent tbsp
	  #

    my $tbsp_max_free_chunk_kbytes      = 0;
    my $tbsp_max_free_chunk_kbytes_auto = 0;

    my $tbsp_tot_used_mbytes        = 0.0;
    my $tbsp_cur_free_mbytes        = 0.0;
    my $tbsp_tot_free_mbytes        = 0.0;
    my $tbsp_cur_mbytes             = 0;
    my $tbsp_max_mbytes             = 0;
    my $tbsp_used_pct               = 0;

    # new_query:  ts#, blocksize, flag
    #
    my $tbsp_blocksize = -1;       # bind 3 from result sql_max_size
    my $EXT_size       = "";       # bind 4 determined by flag sql_max_size
    my $ts_numb        = -1;       # bind 5 from result sql_max_size
    my $isLocalSystem  = 0;        # set to true if locally system (flag==0)
    my $flag_value     = "";       # EXT_size if not locally system

    # loop through the tablespace's datafiles determining the tablespace's maximum
    # size as well as the additional free space that would be available in the
    # tablespace should a datafile autoextend
    #

    # loop through while we are still processing the same tbsp

       for (my $j = $i; $j <= $#all_tbsp_names && $all_tbsp_names[$j] eq $all_tbsp_names[$i]; $j++)
      { 
	  my $df_cur_mbytes = $all_tbsp_meg[$j];     # current size of datafile
	  my $df_max_mbytes = $all_tbsp_maxmeg[$j];      # maximum size of datafile
	  my $df_file_name  = $all_tbsp_file_name[$j] ;      # file name


       	  # new_query: fetch ts#, blocksize, flag
	  #            permanent only
	  #

	  $ts_numb        = $all_tbsp_ts[$j];
	  $tbsp_blocksize = $all_tbsp_blocksize[$j];
	  $flag_value     = $all_tbsp_flag[$j];
	  if  ($flag_value ne "0" || $flag_value != 0) {
		     $isLocalSystem = 1;
	  }
	  if ($isLocalSystem) {
		    $EXT_size = $flag_value; 
	    } else {
		    $EXT_size = "s.extsize";
	  }


    # new query to determine chunk too small to extend
    #           need to determine which EXT_size to use 
    #           bind: 1. tbsp_max_free_kbytes, 
	  #                 2. blocksize, 
	  #                 3. tbsp_number
    #

	  $sql_chunk_sml_segs        = "select  u.name||'.'||o.name||DECODE(o.subname,NULL,'','.'||o.subname), "
                                   . "        so.object_type, "
                                   . "        SIGN( :1 - ( $EXT_size * :2) ) "
                                   . "from    sys.user\$ u, sys.obj\$ o, sys.seg\$ s, "
                                   . "        (select  * "
                                   . "         from    sys.sys_objects "
                                   . "         where   ts_number = :3) so "
                                   . "where   s.file#  = so.header_file and "
                                   . "        s.block# = so.header_block and "
                                   . "        s.type#  = so.segment_type_id and "
                                   . "        s.ts#  = so.ts_number and "
                                   . "        o.owner# = u.user# and "
                                   . "        o.obj#   = so.object_id and "
                                   . "        bitand(o.flags,128)=0 and "
                                   . "        o.type#  = so.object_type_id and "
				   . "        ( $EXT_size * :2) > (1024 * :1) "
				   . "order by ( $EXT_size * :2 ) DESC";

      $sql_chunk_sml_test        = "select  count(*)  "
                                   . "from    sys.seg\$ s "
                                   . "where   "
                                   . "        s.ts#  = :3 and "
				   . "        ( $EXT_size * :2) > (1024 * :1) " ;
           
	  $sql_chunk_sml_segs_ct    =  "select count (o.name) "
                                   . "from   sys.user\$ u, sys.obj\$ o, sys.seg\$ s, " 
                                   . " 	     (select  * "
                                   . "        from    sys.sys_objects " 
                                   . "	      where   ts_number = :3) so "
                                   . "where   s.file#  = so.header_file and " 
                                   . "	      s.block# = so.header_block and " 
                                   . "	      s.type#  = so.segment_type_id and "
                                   . "        s.ts#  = so.ts_number and "
                                   . "	      o.owner# = u.user# and "
                                   . "	      o.obj#   = so.object_id and "
                                   . "        bitand(o.flags,128)=0 and "
                                   . "	      o.type#  = so.object_type_id and " 
				   . "	      ( $EXT_size * :2) > (1024 * :1) " ;
#				   . "order by ( $EXT_size * :2 ) DESC";

	 $sql_max_ext_segs          = "select  u.name||'.'||o.name||DECODE(o.subname,NULL,'','.'||o.subname), "
                                   . "        so.object_type "
                                   . "from    sys.user\$ u, sys.obj\$ o, sys.seg\$ s, "
                                   . "        (select  * "
                                   . "         from    sys.sys_objects "
                                   . "         where   ts_number = :1) so "
                                   . "where   s.file#  = so.header_file and "
                                   . "        s.block# = so.header_block and "
                                   . "        s.type#  = so.segment_type_id and "
                                   . "        s.ts#  = so.ts_number and "
                                   . "        o.owner# = u.user# and "
                                   . "        o.obj#   = so.object_id and "
                                   . "        bitand(o.flags,128)=0 and "
                                   . "        o.type#  = so.object_type_id and "
                                   . "	      s.maxexts < 2100000000 and "
				   . "	      s.maxexts - extents < 2 "
				   . "order by s.maxexts - extents ASC ";

	$sql_max_ext_segs_ct       = "select count (o.name) "
                                   . "from    sys.user\$ u, sys.obj\$ o, sys.seg\$ s, "
                                   . "        (select  * "
                                   . "         from    sys.sys_objects "
                                   . "         where   ts_number = :1) so "
                                   . "where   s.file#  = so.header_file and "
                                   . "        s.block# = so.header_block and "
                                   . "        s.type#  = so.segment_type_id and "
                                   . "        s.ts#  = so.ts_number and "
                                   . "        o.owner# = u.user# and "
                                   . "        o.obj#   = so.object_id and "
                                   . "        bitand(o.flags,128)=0 and "
                                   . "        o.type#  = so.object_type_id and "
                                   . "	      s.maxexts < 2100000000 and "
				   . "	      s.maxexts - extents < 2 ";
#				   . "order by s.maxexts - extents ASC ";

    my $df_auto_free_mbytes = 0;            # amount of additional available free space
    my $df_auto_free_kbytes = 0;            #   should the datafile autoextend

    if ( $df_cur_mbytes > $df_max_mbytes ) {

            # if current size is larger than maximum size change maximum size to
            # be current size.  This can happen for one of two reasons:
            #   1. autoextension is not enabled
            #   2. if someone resizes an existing datafile to be smaller
            #      than what is currently allocated.
            #
            $df_max_mbytes = $df_cur_mbytes;
            $df_auto_free_kbytes = 0;
            $df_auto_free_mbytes = 0;

    } else {

            # if maximum size is larger than current size then autoextension
            # is enabled, so determine how much free space is available on
            # the disk when determining how large the datafile could become
            #

            my $dir = dirname ($df_file_name);

            # only get the diskusage if we are looking at a different disk
            # query orders by filename which should keep these calls down
            
            if ($previous_dir ne $dir)
            {
              @diskusage = get_disk_usage ($dir);
               
              if ( $diskusage[0] == -1 ) {
                print "em_error=Couldn't get disk usage for disk $dir\n";
                next OUTER_LOOP;
              } else {
                $avail_kbytes = $diskusage[2];
                $avail_mbytes = ($avail_kbytes / 1024);
              }
            }
            if ( $df_max_mbytes > $avail_mbytes + $df_cur_mbytes) {
		
                # the maximum size is larger than what is available on the disk
                # so reduce the maximum size to be the amount of free space
                # on the disk plus the amount that is currently allocated to
                # the datafile
                #
                # also keep track of the amount of additional free space
                # that would be available should the datafile autoextend to
                # this maximum size
                #
                $df_max_mbytes        = $avail_mbytes + $df_cur_mbytes;
                $df_auto_free_mbytes  = $avail_mbytes;
                $df_auto_free_kbytes  = $avail_kbytes;

            } else {

                # there's enough free space on the disk to satisfy the
                # datafile's maximum size so there's nothing to do other then
                # keeping track of the amount of additional free space
                # that would be available should the datafile autoextend to
                # this maximum size
                #
                $df_auto_free_mbytes = $df_max_mbytes - $df_cur_mbytes;
                $df_auto_free_kbytes = ($df_auto_free_mbytes * 1024);
            }
        }
        warn "$DBI::errstr\n" if $DBI::err;

        # keep track of the largest free chunk that would be available
        # if any of the datafiles autoextended
        #
        if ($df_auto_free_kbytes > $tbsp_max_free_chunk_kbytes_auto ) {
            $tbsp_max_free_chunk_kbytes_auto = $df_auto_free_kbytes;
        }

        # keep track of tablespace's current size, maximum size and
        # amount of additional free space that would be added if the
        # datafile autoextended
        #
        $tbsp_cur_mbytes      += $df_cur_mbytes;
        $tbsp_max_mbytes      += $df_max_mbytes;
        $tbsp_tot_free_mbytes += $df_auto_free_mbytes;
   
        $i = $j; 
     } 
     # end for each datafile 
    # for the tablespace determine the total amount of free space currently
    # existing in the tablespace as well as the largest free chunk
    #
    
 
        $tbsp_cur_free_mbytes       = $all_tbsp_freebytes[$i];
        $tbsp_max_free_chunk_kbytes = $all_tbsp_maxfreebytes[$i];

        $tbsp_tot_free_mbytes = $tbsp_tot_free_mbytes + $tbsp_cur_free_mbytes;
        $tbsp_tot_used_mbytes = $tbsp_max_mbytes - $tbsp_tot_free_mbytes;
  

    # if there is a larger free chunk that will be available if one of the
    # datafiles autoextended use this as the largest free chunk
    #
    if ($tbsp_max_free_chunk_kbytes_auto > $tbsp_max_free_chunk_kbytes) {
        $tbsp_max_free_chunk_kbytes = $tbsp_max_free_chunk_kbytes_auto;
    }

    $tbsp_cur_mbytes            = sprintf("%3.2f", $tbsp_cur_mbytes);
    $tbsp_max_mbytes            = sprintf("%3.2f", $tbsp_max_mbytes);
    $tbsp_tot_used_mbytes       = sprintf("%3.2f", $tbsp_tot_used_mbytes);
    $tbsp_max_free_chunk_kbytes = sprintf("%.0f", $tbsp_max_free_chunk_kbytes);

#    EMD_PERL_DEBUG(" tbsp_cur_mbytes: $tbsp_cur_mbytes");
#    EMD_PERL_DEBUG(" tbsp_max_mbytes: $tbsp_max_mbytes");
#    EMD_PERL_DEBUG(" tbsp_tot_used_mbytes: $tbsp_tot_used_mbytes");
#    EMD_PERL_DEBUG(" tbsp_max_free_chunk_kbytes: $tbsp_max_free_chunk_kbytes");

    # determine the percent used for the tablespace
    #
    if ( $tbsp_max_mbytes == 0 ) {
        EMD_PERL_ERROR ("Couldn't get maximum size for tablspace $tbsp_name.");
    } else {
        $tbsp_used_pct     = sprintf ("%3.2f", $tbsp_tot_used_mbytes / $tbsp_max_mbytes * 100);
    }

    # done with tbsp pct used

    # for permanent, dictionary managed tablespaces check for segment problems
    #

    # check for segments unable to extend
    #
    my $chunk_sml_segs_count    = 0;
    my $chunk_sml_segs_message  = " ";
    my $perform_count_query     = 0;

    # new_query bind parameters
    #
#    EMD_PERL_DEBUG(" binding: 1= $tbsp_max_free_chunk_kbytes  2= $tbsp_blocksize   3= $ts_numb");

    # test if there are any segments that fail before trying to join with sys_objects
    # only prepare if we have a query text change, that occurs only when EXT_size changes
    
     if ($prev_EXT_size != $EXT_size || $cur_chunk_sml_test_open == 0)
     {
       $cur_chunk_sml_test = $lda->prepare($sql_chunk_sml_test)
         or die "em_error=prepare($sql_chunk_sml_test): $DBI::errstr\n";
       $cur_chunk_sml_test_open = 1;
      }

      $prev_EXT_size = $EXT_size;
    
      $cur_chunk_sml_test->bind_param(1, $tbsp_max_free_chunk_kbytes)
        or warn "cur_chunk_sml_test->bind_param(1, $tbsp_max_free_chunk_kbytes): $DBI::errstr\n";
      $cur_chunk_sml_test->bind_param(2, $tbsp_blocksize)
        or warn "cur_chunk_sml_segs->bind_param(2, $tbsp_blocksize): $DBI::errstr\n";
      $cur_chunk_sml_test->bind_param(3, $ts_numb)
        or warn "cur_chunk_sml_segs->bind_param(3, $ts_numb): $DBI::errstr\n";
      $cur_chunk_sml_test->execute()
        or warn "cur_chunk_sml_segs->execute(): $DBI::errstr\n";

        if ( @fetch_row = $cur_chunk_sml_test->fetchrow_array() ) {
            $chunk_sml_segs_count = $fetch_row[0];
        }

    # if we find that there are segments that have chuncks too small then well get object
    # names.
    
      if ($chunk_sml_segs_count > 0)
      {
       $cur_chunk_sml_segs = $lda->prepare($sql_chunk_sml_segs)
        or die "em_error=prepare($sql_chunk_sml_segs): $DBI::errstr\n";
        $cur_chunk_sml_segs_open = 1;
    
        $cur_chunk_sml_segs->bind_param(1, $tbsp_max_free_chunk_kbytes)
          or warn "cur_chunk_sml_segs->bind_param(1, $tbsp_max_free_chunk_kbytes): $DBI::errstr\n";
        $cur_chunk_sml_segs->bind_param(2, $tbsp_blocksize)
          or warn "cur_chunk_sml_segs->bind_param(2, $tbsp_blocksize): $DBI::errstr\n";
        $cur_chunk_sml_segs->bind_param(3, $ts_numb)
          or warn "cur_chunk_sml_segs->bind_param(3, $ts_numb): $DBI::errstr\n";
        $cur_chunk_sml_segs->execute()
          or warn "cur_chunk_sml_segs->execute(): $DBI::errstr\n";
        # reset chunk count to 0
        $chunk_sml_segs_count = 0;

        while ( @fetch_row = $cur_chunk_sml_segs->fetchrow_array() ) {
          $chunk_sml_segs_count++;

          if ($chunk_sml_segs_count > 1) {
            $chunk_sml_segs_message .= ", ";
          }

          if ($chunk_sml_segs_count == 11 ) {
              $chunk_sml_segs_message .= "...";
              $perform_count_query = 1;
              last;
        }

        $chunk_sml_segs_message .= $fetch_row[1];
        $chunk_sml_segs_message .= " ";
        $chunk_sml_segs_message .= $fetch_row[0];
      }
      # end for each chunk_sml_segs
    } 
    # end if there were any chunk small segments found 
    
    # if there were at least 11 segments unable to extend then need to do an
    # additional query to determine the total number of segments unable to
    # extend.  if there were less than 11 problem segments then we can avoid
    # the additional query as the count kept when retrieving the names of the
    # first 10 problems is correct
    #
    if ($perform_count_query == 1) {
        if ($cur_chunk_sml_segs_ct_open == 0) {
            $cur_chunk_sml_segs_ct = $lda->prepare($sql_chunk_sml_segs_ct)
                or die "em_error=prepare($sql_chunk_sml_segs_ct): $DBI::errstr\n";
            $cur_chunk_sml_segs_ct_open = 1;
        }
        $cur_chunk_sml_segs_ct->bind_param(1, $tbsp_max_free_chunk_kbytes)
            or warn "cur_chunk_sml_segs_ct->bind_param(1, $tbsp_max_free_chunk_kbytes): $DBI::errstr\n";
        $cur_chunk_sml_segs_ct->bind_param(2, $tbsp_blocksize)
            or warn "cur_chunk_sml_segs_ct->bind_param(2, $tbsp_blocksize): $DBI::errstr\n";
        $cur_chunk_sml_segs_ct->bind_param(3, $ts_numb)
            or warn "cur_chunk_sml_segs_ct->bind_param(3, $ts_numb): $DBI::errstr\n";
        $cur_chunk_sml_segs_ct->execute()
            or warn "cur_chunk_sml_segs_ct->execute(): $DBI::errstr\n";

        if ( @fetch_row = $cur_chunk_sml_segs_ct->fetchrow_array() ) {
            $chunk_sml_segs_count = $fetch_row[0];
        }
    }
    #end if perform_count_query
    
    # check for segments approaching their maxextents
    #
    my $max_ext_segs_count      = 0;
    my $max_ext_segs_message    = " ";
    $perform_count_query        = 0;

    # did we detect a problem with this tablespace in the initial query? 
    if ($all_tbsp_ext_ts[$i] != -99)
    {
      if ($cur_max_ext_segs_open == 0)
      {
      $cur_max_ext_segs = $lda->prepare($sql_max_ext_segs)
        or die "em_error=prepare($sql_max_ext_segs): $DBI::errstr\n";
      $cur_max_ext_segs_open = 1;
      }
      $cur_max_ext_segs->bind_param(1, $ts_numb)
        or warn "cur_max_ext_segs->bind_param(1, $ts_numb): $DBI::errstr\n";
      $cur_max_ext_segs->execute()
        or warn "cur_max_ext_segs->execute(): $DBI::errstr\n";
      while ( @fetch_row = $cur_max_ext_segs->fetchrow_array() ) {

        $max_ext_segs_count++;

        if ($max_ext_segs_count > 1) {
            $max_ext_segs_message .= ", ";
        }

        if ($max_ext_segs_count == 11 ) {
            $max_ext_segs_message .= "...";
            $perform_count_query = 1;
            last;
        }

        $max_ext_segs_message .= $fetch_row[1];
        $max_ext_segs_message .= " ";
        $max_ext_segs_message .= $fetch_row[0];	
      }
    #end cur_max_ext_segs
    
    # if there were at least 11 segments approaching their maxextents then need
    # to do an additional query to determine the total number of segments approaching
    # their maxextents.  if there were less than 11 problem segments then we can avoid
    # the additional query as the count kept when retrieving the names of the
    # first 10 problems is correct
    #
    if ($perform_count_query == 1) {
        if ($cur_max_ext_segs_ct_open == 0) {
            $cur_max_ext_segs_ct = $lda->prepare($sql_max_ext_segs_ct)
                or die "em_error=prepare($sql_max_ext_segs_ct): $DBI::errstr\n";
            $cur_max_ext_segs_ct_open = 1;
        }

        $cur_max_ext_segs_ct->bind_param(1, $ts_numb)
            or warn "cur_max_ext_segs_ct->bind_param(1, $ts_numb): $DBI::errstr\n";
        $cur_max_ext_segs_ct->execute()
            or warn "cur_max_ext_segs_ct->execute(): $DBI::errstr\n";
        if ( @fetch_row = $cur_max_ext_segs_ct->fetchrow_array() ) {
            $max_ext_segs_count = $fetch_row[0];
        }
    }
    #end perform_count_query
  }
  #end of should we perform the max_ext_segs query  
    if ($chunk_sml_segs_count == 0 &&  $max_ext_segs_count == 0) {
	# no output
    } else {
	print "em_result=$tbsp_name|$chunk_sml_segs_count|$chunk_sml_segs_message|"
          ."$max_ext_segs_count|$max_ext_segs_message\n";
    }
}
#end for each tablespace OUTERLOOP

$lda->disconnect
    or warn "disconnect $DBI::errstr\n";

exit 0;