BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'kill_exceeded_dedic',
job_type => 'PLSQL_BLOCK',
job_action => '
for c in (
select * from (
select sid,serial# from v$session where server=''DEDICATED'' and type=''USER''
and machine!=sys_context(''USERENV'', ''HOST'') and lower(machine) not in (''workgroup\ex'') order by logon_time desc
) where rownum<5
)
loop
begin
execute immediate ''alter system disconnect session '''''' || c.sid || '','' || c.serial# || '''''' immediate'';
exception when others then null;
end;
end loop;',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=1;',
enabled => TRUE
);
END;
/ BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'kill_exceeded_dedic',
job_type => 'PLSQL_BLOCK',
job_action => '
for c in (
select * from (
select sid,serial#,machine,logon_time from v$session where server=''DEDICATED'' and type=''USER''
and machine!=sys_context(''USERENV'', ''HOST'') and lower(machine) not in (''workgroup\ex'')
and (sid,serial#) not in (
select * from (select sid,serial# from v$session where server=''DEDICATED'' and type=''USER''
and machine!=sys_context(''USERENV'', ''HOST'') and lower(machine) not in (''workgroup\ex'') order by logon_time) where rownum<6
)
)
)
loop
begin
execute immediate ''alter system disconnect session '''''' || c.sid || '','' || c.serial# || '''''' immediate'';
exception when others then null;
end;
end loop;',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=1;',
enabled => TRUE
);
END;
/ BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'kill_dedic',
job_type => 'PLSQL_BLOCK',
job_action => '
for c in (select sid,serial#,machine,logon_time from v$session where server=''DEDICATED'' and type=''USER'' and lower(machine) not in (''workgroup\ex'',''kk''))
loop
begin
execute immediate ''alter system disconnect session '''''' || c.sid || '','' || c.serial# || '''''' immediate'';
exception when others then null;
end;
end loop;',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=1;',
enabled => TRUE
);
END;
/