建立指定数目数据库连接的脚本.

本文可以任意转载，转载时请务必以超链接形式标明文章原始出处和作者信息及本声明

http://blog.itpub.net/post/11/3357

created by husthxd

-- 1.kill session方式
-- 用于记录连接信息的表:
drop table test_connect
/
create table test_connect
(sid number,
serial number,
flag char(1)
)
/

-- 批量kill session的脚本
set heading off
set linesize 200
set pagesize 0

spool kill_session.sql

select 'alter system kill session '||''''||to_char(sid)||','||to_char(serial)||''''||';'
from test_connect
/

spool off

@kill_session.sql

delete test_connect
/

host rm kill_session.sql
host del kill_session.sql

-- 也可以写存储过程kill session
/*
create or replace procedure kill_session
is
  cursor c_conn is
    select sid,serial from test_connect where flag = '0';
  sid number;
  serial number;
begin
  for c1 in c_conn loop
    sid := c1.sid;
    serial := c1.serial;
    -- 动态执行alert system kill session需要的权限?
    execute immediate 'alter system kill session '||''''||to_char(sid)||''''||','||''''||to_char(serial)||'''';
  end loop;
end;
/
*/

-- 2.设置标志方式
drop table exit_flag
/
create table exit_flag
(flag char(1)
)
/
insert into exit_flag values('0')
/
commit
/

-- 测试脚本
-- test_connect.sql
declare
  v_flag char(1);
begin
    /*
    insert into test_connect
    select s.sid,s.serial#,'1'
    from v$mystat m,v$session s
    where m.sid = s.sid and m.statistic# = 1;
    commit;
    */
    while 1=1 loop
      select flag into v_flag from exit_flag;
      if v_flag = '1' then
        exit;
      end if;
    end loop;
end;
/
   
#shell 脚本
#test_connect.sh
#note : 赋值时的等号不能用空格隔开
export x=1
while test $x -lt 100
do
  connect.sh
  x=`expr $x + 1`
done

#created by hexiaodong
#This script is used to create connection to db server
#connect.sh

sqlplus -s $USERPW @test_connect.sql &