利用shell脚本定时清理某个sql_id的session

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