1.根据服务器负载压力清理某个具体SQL_ID的会话

#!/bin/bash

starttime=`date +'%Y-%m-%d %H:%M:%S'`

load=`uptime | awk '{print $10}'`

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export ORACLE_SID=DEV

if [ $load <=10 ]

then 

$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' << EOF

set linesize 999 feedback on verify on echo on;

spool /home/oracle/killsess.out;

SELECT SID,SERIAL#,INST_ID,MODULE,STATUS,event FROM gv\$session WHERE USERNAME IS NOT NULL AND MACHINE='oracle.test.com' AND STATUS= 'ACTIVE' AND SQL_ID='7ydfd2vdg9k2w';

begin

  for  rec in (SELECT SID,SERIAL# FROM gv\$session  WHERE USERNAME IS NOT NULL AND MACHINE='oracle.test.com' AND STATUS= 'ACTIVE' AND SQL_ID='7ydfd2vdg9k2w')

  loop 

    execute immediate 'alter system kill session ''' || rec.SID || ', ' ||rec.SERIAL# || '''immediate' ;

  END LOOP;

END;

/

spool off;

exit;

EOF

fi

exit 0

2.根据服务器负载压力清理某个具体SQL_ID的会话

#!/bin/bash

load=`uptime | awk '{print $10}'`

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export ORACLE_SID=DEV

if [ $load <=10 ]

then 

$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' << EOF

set linesize 999;

set serveroutput on;

spool /home/oracle/killsess.out

declare num_session int;

begin

  select count(*) into num_session from gv\$session where STATUS= 'ACTIVE' AND SQL_ID='dj7p00jr2b9vk';

  if  num_session >=3 then

for  rec in (SELECT SID,SERIAL#,EVENT FROM gv\$session  WHERE USERNAME IS NOT NULL AND MACHINE='oracle.vastdata.com' AND STATUS= 'ACTIVE' AND SQL_ID='dj7p00jr2b9vk')

      LOOP 

        execute immediate 'alter system kill session ''' || rec.SID || ', ' ||rec.SERIAL# || '''immediate';

dbms_output.put_line('the session ' || rec.sid || ',' || rec.serial# || ' waiting for '|| rec.event || ' has being killed;');

      END LOOP;

  end if;

END;

/

spool off;

exit;

EOF

fi

exit 0