CREATE OR REPLACE PROCEDURE kill_time ( v_time in number )
AS
l_serial CHAR (100);
l_sid CHAR (100);
l_sid_serial CHAR (20);
l_count NUMBER (10, 0);
sqlstmt VARCHAR2 (4000);
CURSOR session_cur
IS
SELECT SID, SERIAL#, SID || ',' || SERIAL# AS SID_SERIAL
FROM V$SESSION
WHERE TYPE <> 'BACKGROUND' AND
STATUS='ACTIVE' AND
COMMAND=3 AND USERNAME NOT IN ('SYS', 'SYSTEM')
AND
LAST_CALL_ET/60 > V_TIME order by LAST_CALL_ET desc ;
BEGIN
l_count := 0;
insert into sayed.sess_kill_tab ( select sysdate, username, sql_id, round(last_call_et/60,3) from v$session
where TYPE <> 'BACKGROUND' and
status='ACTIVE' and
command=3 and username not in ('SYS', 'SYSTEM')
and
last_call_et/60 > v_time);
OPEN session_cur;
LOOP
FETCH session_cur
INTO l_sid, l_serial, l_sid_serial;
EXIT WHEN session_cur%NOTFOUND;
sqlstmt := 'alter system kill session ' || '''' || trim(l_sid_serial)|| '''';
DBMS_OUTPUT.put_line (sqlstmt);
EXECUTE IMMEDIATE sqlstmt;
l_count := l_count +1;
END LOOP;
CLOSE session_cur;
commit;
DBMS_OUTPUT.put_line ('*** ' ||l_count||' Sessions Killed ***');
END kill_time;
/
Oracle Hidden Parameters
Hidden Parameter Instance Level
=============================
select
ksppinm name,
ksppstvl value
from
x$ksppi a,
x$ksppsv b
where
a.indx=b.indx and
substr(ksppinm,1,1) = '_'
order by 1;
Hidden Parameter Session Level
=============================
select
a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from
x$ksppi a,
x$ksppcv b,
x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
order by a.ksppinm;