No question at this time
DBA Top 10
1 M. Cadot 22100
2 A. Kavsek 15500
3 M. Hidayathullah ... 11000
4 B. Vroman 7900
5 P. Wisse 6000
6 T. Boles 5400
7 J. Schnackenberg 3300
8 G. Lambregts 2200
8 P. Knibbs 2200
8 K. Pagadala 2200
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48016
Total active users1849
Act. users last 24h10
Act. users last hour0
Registered user hits last week306
Registered user hits last month1339
Go up

Session still in v$session after a good killing
Next thread: dbms_stats.gather_table_stats
Prev thread: Oracle 11.2.0.4 and memory management

Message Score Author Date
I issued an 'alter system kill session' on a sid/s...... Kirt Thomas Aug 11, 2017, 20:15
When a session is killed is default mode, it is ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Michel Cadot Aug 11, 2017, 22:13
Interesting, but then the session should be gone f...... Kirt Thomas Aug 14, 2017, 15:57
>>> <i> Interesting, but then the session should...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Aug 14, 2017, 18:32

Follow up by mail Click here


Subject: Session still in v$session after a good killing
Author: Kirt Thomas, United States
Date: Aug 11, 2017, 20:15, 5 days ago
Os info: AIX 6.1
Oracle info: 11.2.0.3
Message: I issued an 'alter system kill session' on a sid/serial#, and sqlplus returned 'system altered'.

But when I do a select from v$session on that username, I'm returned the same sid/serial# and a status of 'KILLED' (not marked for KILL).

Trying to kill it again returns a 'ORA-00026: missing or invalid session ID' - AND - if I link to v$process (addr=paddr) no rows come back (ie it's not in v$process).

I'm not worried; but I've got a handful of these that have hung on for a couple of weeks - I'd expected PMON to clean this up. I haven't bounced the DB and don't have any plans to.

This is not for every session I kill, but seems to only happen to sessions connecting via sqlnet from a different computer.

Any thoughts? Thanks.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Session still in v$session after a good killing
Author: Michel Cadot, France
Date: Aug 11, 2017, 22:13, 5 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message:
When a session is killed is default mode, it is cleaned up once the possibly ongoing transaction is rolled up and the client has received the message:
ORA-00028: "your session has been killed"
If the session never interacts with the server (for instance a sqlplus session is still alive but the user never tries to execute a statement) then the session is never cleaned up.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Session still in v$session after a good killing
Author: Kirt Thomas, United States
Date: Aug 14, 2017, 15:57, 2 days ago
Message: Interesting, but then the session should be gone form v$session when the client program exits, or the client machine is shut down.

So the sqlplus session is entirely gone. I've seen 'marked for kill' on sessions where the db is waiting on a response from the client (I'll usually just kill the OS process here which I can identify from v$process) - I can't find an OS process for these 'killed' sessions. Thanks.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Session still in v$session after a good killing
Author: Michel Cadot, France
Date: Aug 14, 2017, 18:32, 2 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
>>> Interesting, but then the session should be gone form v$session when the client program exits,

Yes, if it is done by a command to exit.

or the client machine is shut down.

No, it depends on a sqlnet.ora parameter.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here