Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\scripts\segAdv.pl

#!/usr/local/bin/perl
# 
# $Header: segAdv.pl 05-jan-2007.11:29:04 mpawelko Exp $
#
# segAdv.pl
# 
# Copyright (c) 2005, 2007, Oracle. All rights reserved.  
#
#    NAME
#      segAdv.pl - Get the Segment Advisor Recommendation count
#
#    DESCRIPTION
#       The script get the last timestamp when the Segment Advisor 
#       job was run. It compares this timestamp with the timestamp written
#       to the state directory, if they are different then it retrieves the 
#       count of Segment Advisor recommendations and updates the state file with 
#       the new timestamp and the new count. If the timestamps are not different then
#       it returns the old count of recommendations.
#
#    OUTPUT:
#      This script will generate one pieces of output:
#      1) Segment Advisor Recommendation Count - The count of segment advisor 
#         recommendations
#
#    NOTES
#      <other useful comments, qualifications, etc.>
#
#    MODIFIED   (MM/DD/YY)
#    mpawelko    01/05/07 - XbranchMerge mnihalan_bug-5573975 from main
#    mnihalan    12/14/06 - Fix bug 5573975
#    mnihalan    03/17/05 - mnihalan_segment_adv_workflow_change
#    mnihalan    03/07/05 - Creation
#

use strict;
use DBI;

require "emd_common.pl";

if (EMAGENT_isPerlDebugEnabled())
{
  EMD_PERL_DEBUG ("segAdv:Debug --> ++++  Welcome to Segment Advisor Recommendation Debugging+
      ++++ ");
}

# --------------------------------------------------------------------
# +++ VARIABLES
# --------------------------------------------------------------------

# GENERAL
my @fetch_row;
my $seg_adv_cur;
my $seg_adv_sql;
my $timestamp_last_run="";
my $current_time="";
my $seg_adv_last_run_cur;
my $seg_adv_last_run_sql;
my $lda;

# OUTPUT
my $seg_adv_count = 0;

# INPUT
# The DB Connection info is passed in by the metric definition

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 $targetGuid = $ENV{EM_TARGET_GUID};
my $oracle_home = $ENV{EM_TARGET_ORACLE_HOME};
my $state_root = $ENV{EM_AGENT_STATE_DIR};
my $mode = 0;
if($role =~ /SYSDBA/i)
{
  $mode = 2;
}
elsif($role =~ /SYSOPER/i)
{
  $mode = 4;
}

my $separator = $^O =~ m/MSWin32/ ? "\\" : "\/";

#
# Location of the offsets state file is
#  $EMDROOT/sysman/emd/state/<TARGET_GUID>.<filename>
#
my $scannedFile = $state_root.$separator."sysman".$separator."emd".$separator."state".$separator."$targetGuid"."_seg_adv_count.log";

# ------------------------------------------------------------------
# Open scanned file for reading (if scanned file does not exist, create it.)
# ------------------------------------------------------------------
if (!open(SCANNED, "+>> $scannedFile"))
{
    EMAGENT_PERL_ERROR("target: $targetGuid; Cannot open $scannedFile for read/write.");
    exit 1;
}

# seek to top of file

seek(SCANNED, 0, 0);

# file only contains a single line with two numbers:  a timestamp and a count
# get these and close the scanned file

while (<SCANNED>)
{
    my @pos = split('~', $_);
    my $pos = \@pos;
    
    $timestamp_last_run =$pos->[0];;
    $seg_adv_count = $pos->[1];
}

close(SCANNED);
if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("segAdv:Debug  timestamp last run  : $timestamp_last_run");
    EMD_PERL_DEBUG ("segAdv:Debug  Seg Adv Count  : $seg_adv_count");
}
# --------------------------------------------------------------------
# +++ Establish Target DB Connection
# --------------------------------------------------------------------

EMD_PERL_DEBUG ("segAdv:Debug DB Address : $address"); 
$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);

# --------------------------------------------------------------------
# +++ Segment Advisor Recommendation Count
# --------------------------------------------------------------------
if ($timestamp_last_run eq "")
{
      $seg_adv_last_run_sql = "SELECT  TO_CHAR(MAX(CAST(l.execution_end AS TIMESTAMP)), 'DD-MON-YYYY HH24:MI:SSxFF'), TO_CHAR(CAST(sysdate AS TIMESTAMP), 'DD-MON-YYYY HH24:MI:SSxFF') ".
                  " from dba_advisor_tasks l where ".
                  " l.advisor_name='Segment Advisor'";
}
else
{
      $seg_adv_last_run_sql = "SELECT  TO_CHAR(MAX(CAST(l.execution_end AS TIMESTAMP)), 'DD-MON-YYYY HH24:MI:SSxFF'),
TO_CHAR(CAST(sysdate AS TIMESTAMP), 'DD-MON-YYYY HH24:MI:SSxFF') ".
		  " from dba_advisor_tasks l where ".
                  " l.advisor_name='Segment Advisor' and ".
                  " TO_CHAR(CAST(l.execution_end AS TIMESTAMP), 'DD-MON-YYYY HH24:MI:SSxFF') > '". $timestamp_last_run. "'";    
}
if (EMAGENT_isPerlDebugEnabled())
{
  EMD_PERL_DEBUG ("segAdv:Debug  ***** Seg Adv Last Run SQL  : $seg_adv_last_run_sql");
}

$seg_adv_last_run_cur = $lda->prepare($seg_adv_last_run_sql)
    or die "em_error=prepare($seg_adv_last_run_sql): $DBI::errstr\n";
$seg_adv_last_run_cur->execute()
    or die "em_error=seg_adv_last_run_cur->execute(): $DBI::errstr\n";

@fetch_row = $seg_adv_last_run_cur->fetchrow_array();
   
$timestamp_last_run = $fetch_row[0];
$current_time = $fetch_row[1];
 
# --------------------------------------------------------------------
# +++ Segment Advisor Recommendation Count
# --------------------------------------------------------------------
if ($timestamp_last_run ne "")
{
    if (EMAGENT_isPerlDebugEnabled())
    {
        EMD_PERL_DEBUG ("segAdv:Debug  Retrieving Segment Advisor Recommendations ");
    }
    $seg_adv_sql = "SELECT count(*) ".
                       "FROM table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'))";
    
    $seg_adv_cur = $lda->prepare($seg_adv_sql)
        or die "em_error=prepare($seg_adv_sql): $DBI::errstr\n";
    $seg_adv_cur->execute()
        or die "em_error=seg_adv_cur->execute(): $DBI::errstr\n";
    
    @fetch_row = $seg_adv_cur->fetchrow_array();
    $seg_adv_count = $fetch_row[0];
    
}

# --------------------------------------------------------------------
# Reopen scanned file and write out new offset (log size), line number,
# and time
#
# --------------------------------------------------------------------
open (SCANNED, "+> $scannedFile") or die "em_error=Cannot open $scannedFile";
print SCANNED "$current_time~$seg_adv_count";
close(SCANNED);

# --------------------------------------------------------------------
# +++ Print Results
#
# This returns the results to the agent, which will look for a
# standard output line that starts with em_result. The '|' character
# is the delimiter between values.
# --------------------------------------------------------------------

print "em_result=$seg_adv_count\n";

# --------------------------------------------------------------------
# +++ Disconnect from the Target DB
# --------------------------------------------------------------------

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

exit 0;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de