ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用PERL 收集各DB SEGMENT 增长情况信息

使用PERL 收集各DB SEGMENT 增长情况信息

原创 Linux操作系统 作者:xhailiang 时间:2007-07-07 00:00:00 0 删除 编辑

我的第二个PERL 程式,依葫芦画瓢。

监控SEGMENT 增长情况也是DBA 日常工作中的一项,下面这个PERL 写的SCRIPT 可以实现。


#!/usr/bin/perl -w
use strict;
use DBI qw(:sql_types);
$ENV{"ORACLE_HOME"} = "/opt/oracle/product/9ir2";
my $odbh = DBI->connect( 'dbi:Oracle:rman','USER','PASSWORD',
{ RaiseError => 1, AutoCommit => 0 }
) || die "Database connection not made: $DBI::errstr" ;
my $osth = $odbh->prepare(qq{SELECT UPPER(SID) FROM C_TAB_DATABASE});
$osth->execute();
while (my $SID = $osth->fetchrow_array())
{
my $dbh='';
my $DATA_SOURCE = "dbi:Oracle:$SID";
eval { $dbh = DBI->connect( $DATA_SOURCE,'SYSTEM','PASSWORD',
{ RaiseError => 1, AutoCommit => 0 }
) || die "Database connection not made: $DBI::errstr";
};
if ($dbh)
{# success connecting)
my $o_str = $odbh->prepare(qq{select OWNER FROM C_TAB_SEG_OWNER WHERE SID='$SID'});
$o_str->execute();
while (my $OWNER = $o_str->fetchrow_array())
{ my $sth = $dbh->prepare( qq{ SELECT TO_CHAR(TRUNC(SYSDATE),'YYYY-MM-DD') AS TIME,OWNER,
SEGMENT_NAME, SEGMENT_TYPE,BYTES/1024/1024 AS BYTES
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE'
AND SEGMENT_NAME NOT LIKE 'MLOG%'
AND OWNER = '$OWNER'
AND BYTES/1024/1024 > 5
}
);
$sth->execute() or die "ERROR: $sth->errstr ";
while ( my ($TIME,$OWNER, $SEGMENT_NAME,$SEGMENT_TYPE,$BYTES) = $sth->fetchrow_array())
{ my $info = $odbh->prepare(qq{INSERT INTO D_TAB_SEGMENT
(time,SID,OWNER,SEGMENT_NAME,SEGMENT_TYPE,SEGMENT_SIZE)
VALUES
('$TIME','$SID','$OWNER','$SEGMENT_NAME','$SEGMENT_TYPE','$B
YTES')
}
);
$info->execute();
$info->finish;
$odbh->commit();
}
$sth->finish;
}
$o_str->finish;
$dbh->disconnect();
}
else { # failure
#print OUTFILE "$dbh ";
`echo "Cannot connect to $SID !" | mailx -s "Please check $SID as soon as possible!" "xxx@study.com"
`}
}
$osth->finish;
$odbh->disconnect();

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/308563/viewspace-171992/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-17

  • 博文量
    270
  • 访问量
    415369