壓力測試指令碼

建立指定數目資料庫連線的指令碼.

本文可以任意轉載，轉載時請務必以超連結形式標明文章原始出處和作者資訊及本宣告

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 &