DanBrother的部落格

conn /@DB1 as sysdba
set serveroutput on size 1000000
set linesize 200
set feedback off
set trimspool on trimout on
spool c:\dispatcher_control\dispatcher_control_run.sql
declare
type disp_no_type is table of varchar2(4)
index by binary_integer;
disp disp_no_type;

script1 varchar2(1000);
script2 varchar2(1000);
script3 varchar2(1000);
v_shut_counter number(1) default 0;
v_max_disp number(2) default 0;
v_new_disp_no number(2) default 0;

begin
 
 -- DISPATCHER(S) for shutting down IMMEDIATE
 for rec in (select name from v$dispatcher
             where accept = 'NO'
             ) loop
     dbms_output.put_line('exec dbms_lock.sleep(120)');
     script1:='alter system shutdown immediate '''||rec.name||''';';
     dbms_output.put_line(script1);
 end loop;

v_shut_counter:=0;
 -- DISPATCHER(S) for shutting down
 for rec in (
            select distinct d.name as dispatcher_name
            from v$process p, v$session s, v$dispatcher d
            where p.addr=s.paddr
            and p.addr not in (select paddr from v$bgprocess)
            and s.last_call_et >= 50000 and s.logon_time < sysdate-1
            and s.status = 'INACTIVE' and s.server <> 'DEDICATED' and p.addr = d.paddr
            and accept = 'YES'
           ) loop
     v_shut_counter:=v_shut_counter+1;
     disp(v_shut_counter):=rec.dispatcher_name;
 end loop;

 if v_shut_counter = 0 then
    return;  -- exit
 end if;

 select value
 into v_max_disp
 from v$parameter
 where name ='mts_max_dispatchers';

 if v_shut_counter < v_max_disp then
    v_new_disp_no:=v_shut_counter+3;
    if v_new_disp_no > v_max_disp then
       v_new_disp_no:=v_max_disp;
    end if;

    script2:='alter system set mts_dispatchers=''(address=(protocol=tcp))(dispatchers='||v_new_disp_no||')(connections=500)(sessions=1000)'';';
    dbms_output.put_line(script2);  
    for i in 1..v_shut_counter  loop
        dbms_output.put_line('exec dbms_lock.sleep(5)');   
        script3:='alter system shutdown '''||disp(i)||''';';
        dbms_output.put_line(script3);
    end loop;
 end if;
end;
/
spool off
@c:\dispatcher_control\dispatcher_control_run.sql