Home » SQL & PL/SQL » SQL & PL/SQL » killing session with status snipped (Oracle 9i Windows server 2003)
killing session with status snipped [message #390962] Tue, 10 March 2009 09:08 Go to next message
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 #390965 is a reply to message #390962] Tue, 10 March 2009 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you didn't grant the role to the user.
Or maybe you didn't do something else.
If you want us to help you you MUST copy and paste your session.

Before Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: killing session with status snipped [message #390968 is a reply to message #390965] Tue, 10 March 2009 09:22 Go to previous messageGo to next message
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 #390969 is a reply to message #390968] Tue, 10 March 2009 09:24 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
AND STATUS = 'SNIPED'

'SNIPPED'?
Re: killing session with status snipped [message #390970 is a reply to message #390968] Tue, 10 March 2009 09:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why kill sniped sessions? They are marked for kill anyway.
Re: killing session with status snipped [message #390971 is a reply to message #390970] Tue, 10 March 2009 09:33 Go to previous messageGo to next message
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 #390976 is a reply to message #390968] Tue, 10 March 2009 09:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If all is the same in both database, they should behave in the same way.
So something is different, find what.

Regards
Michel
Re: killing session with status snipped [message #390978 is a reply to message #390962] Tue, 10 March 2009 09:48 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Oracle will not & does not eliminate SNIPED session unless & until Operating System process/thread goes away.
Re: killing session with status snipped [message #390979 is a reply to message #390971] Tue, 10 March 2009 09:48 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: killing session with status snipped [message #390981 is a reply to message #390979] Tue, 10 March 2009 09:53 Go to previous messageGo to next message
boukerker
Messages: 11
Registered: January 2008
Location: Paris
Junior Member
But when user try to connect from another PC there is an error like this " user Are limited to 1 session" because it not the same session
Re: killing session with status snipped [message #390982 is a reply to message #390981] Tue, 10 March 2009 09:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Then the user should end his previous session cleanly, or you should change your policy
Re: killing session with status snipped [message #390983 is a reply to message #390962] Tue, 10 March 2009 09:59 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>(Oracle 9i Windows server 2003)
This is NOT a supported configuration & V9 has been obsoleted.
Previous Topic: Serialize and Stream Data
Next Topic: Pl/Sql Error
Goto Forum:
  


Current Time: Sat Dec 03 21:56:02 CST 2016

Total time taken to generate the page: 0.03887 seconds