這是從10g升級到18c後要修改的程式,在10g環境可以參考文章
第一隻用在實務管理上的Perl 程式
依據文件How To Find The Process
Identifier (pid, spid) After The Corresponding Session Is Killed?
(Doc ID 387077.1)
11.1 版本之前,本來就沒辦法從被砍的session對應出OS的process
id,Oracle自認它是個bug
用了Oracle 所提供的修正方式改寫原有的Perl程式
程式碼如下:
use strict;
use DBI;
use DBD::Oracle ;
use warnings;
use v5.16;
my $dbi_connect_string = "dbi:Oracle:";
my %connect_properties;
$connect_properties{RaiseError} = 1;
$connect_properties{PrintError} = 0;
$connect_properties{AutoCommit} = 1;
$connect_properties{ora_session_mode} = DBD::Oracle::ORA_SYSDBA;
my $db_user="";
my $db_password="";
my $dbh = DBI->connect($dbi_connect_string, $db_user, $db_password,\%connect_properties);
die "Unable for connect to server $DBI::errstr" unless $dbh;
my $sth;
my @pid;
$sth = $dbh->prepare("select spid from v\$process where addr in (select creator_addr from v\$session where status = 'KILLED')");
if($sth->execute) {
while ( @pid = $sth->fetchrow) {
my $cnt=kill 9, $pid[0];
say "$pid[0] : $cnt" ;
}
}
$sth->finish();
$dbh->disconnect();
這是OUTPUT
---------------------------------------------------------------------------------------------------------------
10/28 把$connect_properties{RaiseError} 調整為1
為了拋ORA-exception
10/28 把$connect_properties{RaiseError} 調整為1
為了拋ORA-exception