killing session with status snipped [message #390962] |
Tue, 10 March 2009 09:08  |
boukerker
Messages: 11 Registered: January 2008 Location: Paris
|
Junior Member |
|
|
my Dev environment is Oracle 10 and prod is Oracle 9i
I write a procedure to kill snipped session and
I add grant
GRANT SELECT ON SYS.V_$SESSION TO Myuser;
GRANT ALTER SYSTEM TO MyRole;
It work on Oracle 10 but I have error insufficient privilege in Oracle 9i
Thanks.
|
|
|
|
Re: killing session with status snipped [message #390968 is a reply to message #390965] |
Tue, 10 March 2009 09:22   |
boukerker
Messages: 11 Registered: January 2008 Location: Paris
|
Junior Member |
|
|
MyUser and MyRole have the same grant in dev and prod
and procedure is
PROCEDURE KILL_SESSION IS
CURSOR C_SESSION IS
SELECT "SERIAL#" AS SER, SID
FROM SYS.V_$SESSION
WHERE PROGRAM = 'STB01.exe' AND STATUS = 'SNIPED';
nSer NUMBER;
nSid NUMBER;
sRequete VARCHAR2(300);
cur_hdl INTEGER;
rows_processed BINARY_INTEGER;
BEGIN
OPEN C_SESSION ;
LOOP
FETCH C_SESSION INTO nSer, nSid ;
EXIT WHEN C_SESSION%NOTFOUND ;
sRequete := 'ALTER SYSTEM KILL SESSION '''||nSid||','||nSer ||''' IMMEDIATE' ;
-- open cursor
cur_hdl := DBMS_SQL.OPEN_CURSOR;
-- parse cursor
DBMS_SQL.PARSE(cur_hdl, sRequete,dbms_sql.native);
-- execute cursor
rows_processed := DBMS_SQL.EXECUTE(cur_hdl);
-- close cursor
DBMS_SQL.CLOSE_CURSOR(cur_hdl);
END LOOP;
CLOSE C_SESSION;
END KILL_SESSION;
|
|
|
|
|
Re: killing session with status snipped [message #390971 is a reply to message #390970] |
Tue, 10 March 2009 09:33   |
boukerker
Messages: 11 Registered: January 2008 Location: Paris
|
Junior Member |
|
|
user are limited to 1 session and timeout is limited to 10 minute , If the session is with status sniped it's not possible to connect, so I clean sniped session.
I don't know when sniped session are cleaned ?
|
|
|
|
|
Re: killing session with status snipped [message #390979 is a reply to message #390971] |
Tue, 10 March 2009 09:48   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
boukerker wrote on Tue, 10 March 2009 15:33 | user are limited to 1 session and timeout is limited to 10 minute , If the session is with status sniped it's not possible to connect, so I clean sniped session.
I don't know when sniped session are cleaned ?
|
Normally when the user tries to do something in his sniped session.
So, if he's timed out after 10 minutes, his session is sniped. When he tries to do anything, this session is killed.
Means that you should not be worried about this.
|
|
|
|
|
|