OraFAQ Forum: Server Administration » Not able to kill the session

Home » RDBMS Server » Server Administration » Not able to kill the session (Oracle 11g [11.2.0.1.0])

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Not able to kill the session [message #520568]

Tue, 23 August 2011 09:37

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

Hi All,

My disk is completely full so i decided to drop temporary tablespace and create a new one but unfortunately the query to drop temp tablespace hang in between to whic I come to know that there were some sessions which were using it for sort purpose.

check out the queries below with results:

SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
  2  a.username,a.osuser, a.status
  3  FROM v$session a,v$sort_usage b
  4  WHERE a.saddr = b.session_addr;

TABLESPACE                        SEGFILE#    SEGBLK#     BLOCKS        SID    SERIAL# USERNAME               OSUSER                         STATUS
------------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ --------
TEMP                                     0    5515776        128         76      19519 NI_EXT_STR             nibansal                       INACTIVE
TEMP                                     0    5456512        128        289       4221 VSL9                   visingh                        KILLED
TEMP                                     0    5438976        128        290      39365 SCOTT1                 NBANSAL                        KILLED
TEMP                                     0    5456384        128        497       1926 AN13                   akaushal                       KILLED

SQL>
SQL> alter system kill session '76,19519' immediate;
alter system kill session '76,19519' immediate
*
ERROR at line 1:
ORA-00030: User session ID does not exist.


SQL>

Was able to killed three of the four sessions but not the last one.

Thanks
Deepak

Report message to a moderator

Re: Not able to kill the session [message #520569 is a reply to message #520568]

Tue, 23 August 2011 09:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

OS Name & version?

Report message to a moderator

Re: Not able to kill the session [message #520571 is a reply to message #520569]

Tue, 23 August 2011 09:50

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

OS NAME= Windows xp
Database Version = Oracle 11g R2 [11.2.0.1.0]

SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
  2  a.username,a.osuser, a.status
  3  FROM v$session a,v$sort_usage b
  4  WHERE a.saddr = b.session_addr;

TABLESPACE                        SEGFILE#    SEGBLK#     BLOCKS        SID    SERIAL# USERNAME               OSUSER                         STATUS
------------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ --------
TEMP                                     0    5515776        128         76      19519 NI_EXT_STR             nibansal                       INACTIVE
TEMP                                     0    5456512        128        289       4221 VSL9                   visingh                        KILLED
TEMP                                     0    5438976        128        290      39365 SCOTT1                 NBANSAL                        KILLED
TEMP                                     0    5456384        128        497       1926 AN13                   akaushal                       KILLED

SQL>
SQL> alter system kill session '76,19519' immediate;
alter system kill session '76,19519' immediate
*
ERROR at line 1:
ORA-00030: User session ID does not exist.


SQL> ALTER SYSTEM DISCONNECT SESSION '76,19519' IMMEDIATE;
ALTER SYSTEM DISCONNECT SESSION '76,19519' IMMEDIATE
*
ERROR at line 1:
ORA-00030: User session ID does not exist.

Thanks
Deepak

Report message to a moderator

Re: Not able to kill the session [message #520573 is a reply to message #520571]

Tue, 23 August 2011 09:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>OS NAME= Windows xp
>Database Version = Oracle 11g R2 [11.2.0.1.0]

nice UNSUPPORTED combination of OS & Oracle versions!

terminate threads at OS level

Report message to a moderator

Re: Not able to kill the session [message #520574 is a reply to message #520573]

Tue, 23 August 2011 09:59

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

Boss Sorry, its just that I have got frustrated with this error I am getting again n again.

OS= windows 7
Database = Oracle 11g R2 [11.2.0.1.0]

Regards
Deepak

Report message to a moderator

Re: Not able to kill the session [message #520576 is a reply to message #520574]

Tue, 23 August 2011 10:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Boss Sorry, its just that I have got frustrated with this error I am getting again n again.
We can't make any change to eliminate this error.
So how can we assist you now?

bounce the DB & make sure TEMP tablespace has valid OS file associated with it.

Report message to a moderator

Re: Not able to kill the session [message #520577 is a reply to message #520568]

Tue, 23 August 2011 10:06

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Once again

Make sure that lines of code and result do not exceed 80 characters when you format.

Regards
Michel

Report message to a moderator

Re: Not able to kill the session [message #520578 is a reply to message #520576]

Tue, 23 August 2011 10:10

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

I have made the tempfile of the tablespace offline.

Quote:

bounce the DB & make sure TEMP tablespace has valid OS file associated with it.

Means shutdown and startup.

Thanks
Deepak

Report message to a moderator

Re: Not able to kill the session [message #520579 is a reply to message #520576]

Tue, 23 August 2011 10:13

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

If its regarding the shutdown and startup of the database then I can not as it is being used by several other users.

Thanks
Deepak

Report message to a moderator

Re: Not able to kill the session [message #520580 is a reply to message #520579]

Tue, 23 August 2011 10:17

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

When I checked for the process used by the user, I got the following result.

SQL> SELECT s.inst_id,
  2         s.sid,
  3         s.serial#,
  4         p.spid,
  5         s.username,
  6         s.program
  7  FROM   gv$session s
  8         JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
  9  WHERE  s.username='NI_EXT_STR';

   INST_ID        SID    SERIAL# SPID      USERNAME           PROGRAM
---------- ---------- ---------- --------- ------------------ -----------
         1         76      20049 3380      NI_EXT_STR         sqlplus.exe

But checking on the system there is no such process which is running.

Thanks
Deepak

Report message to a moderator

Re: Not able to kill the session [message #520589 is a reply to message #520580]

Tue, 23 August 2011 10:51

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I would have thought that an easier way to do this would be
ALTER TABLESPACE TEMP SHRINK SPACE;

Report message to a moderator

Re: Not able to kill the session [message #520605 is a reply to message #520589]

Tue, 23 August 2011 12:15

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

I have already created an another tablespace and made it default tablespace for the users now I have to delete the previous tablespace having 10G of tempfile.

What can I do for that as I am not able to kill the session which I have mentioned above.

Thanks
Deepak

Report message to a moderator

Re: Not able to kill the session [message #520616 is a reply to message #520579]

Tue, 23 August 2011 13:09

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

dsharma wrote on Tue, 23 August 2011 08:13

If its regarding the shutdown and startup of the database then I can not as it is being used by several other users.

Tell the users that you need them to logoff briefly, then shutdown and startup.

Or don't use immediate:

alter system kill session '76,19519';

I believe it should then mark the session for kill and kill it when it can.

Report message to a moderator

Re: Not able to kill the session [message #520626 is a reply to message #520616]

Tue, 23 August 2011 14:00

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

I have tried all the option for killing the session mam, tried without immediate option as well but then as well I am getting the same error "user session id doesnot exist"

Regards
Deepak

Report message to a moderator

Re: Not able to kill the session [message #520640 is a reply to message #520626]

Tue, 23 August 2011 14:56

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

What is your username? You are not trying to kill your own session are you? You appear to be running the queries and attempts to kill the session from SQL*Plus.

Report message to a moderator

Re: Not able to kill the session [message #520645 is a reply to message #520640]

Tue, 23 August 2011 15:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>OS= windows 7
>Database = Oracle 11g R2 [11.2.0.1.0]
I seriously doubt you upgraded DB Server from XP to Windows 7 in a few minutes.
Don't appreciate being deceived.

Report message to a moderator

Re: Not able to kill the session [message #520648 is a reply to message #520645]

Tue, 23 August 2011 15:24

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

Please post the results of the following:

host wmic os get name

Report message to a moderator

Re: Not able to kill the session [message #520667 is a reply to message #520640]

Tue, 23 August 2011 20:07

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

No mam, the process sqlplus that is being shown is on different machine on which there is client installed the one of the developers was accessing the database.
So the process(session) I am trying to kill is not mine.

As far as the command you specified me to run below are the results Mam.

C:\Users\Administrator>host wmic os get name
'host' is not recognized as an internal or external command,
operable program or batch file.

C:\Users\Administrator>cd...

C:\Users\Administrator>cd..

C:\Users>cd..

C:\>cd Windows\System32

C:\Windows\System32>host wmic os get name
'host' is not recognized as an internal or external command,
operable program or batch file.

C:\Windows\System32>hostname.exe
localhost

C:\Windows\System32>

Quote:

BlackSwan
>OS= windows 7
>Database = Oracle 11g R2 [11.2.0.1.0]
I seriously doubt you upgraded DB Server from XP to Windows 7 in a few minutes.
Don't appreciate being deceived.

I admitted that it was by mistake I wrote that my OS was Windows XP.

Thanks n Regards
Deepak

Report message to a moderator

Re: Not able to kill the session [message #520669 is a reply to message #520667]

Tue, 23 August 2011 20:17

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

I thought you would be running it from SQL*Plus. The host command is used to run operating system commands from SQL*Plus. If you are running it from the operating system, then just run the following and post the results.

wmic os get name

Report message to a moderator

Re: Not able to kill the session [message #520689 is a reply to message #520669]

Wed, 24 August 2011 01:19

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

Result from the SQL prompt:

SQL> host wmic os get name
Node - LOCALHOST
ERROR:
Description = Provider failure

Result from the command prompt.

C:\Users\Administrator>wmic os get name
Node - LOCALHOST
ERROR:
Description = Provider failure

Thanks
Deepak

Report message to a moderator

Re: Not able to kill the session [message #520792 is a reply to message #520689]

Wed, 24 August 2011 12:57

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

Please post the results of either

host ver

from SQL*Plus or

ver

from the operating system.

Report message to a moderator

Previous Topic:	V$session_Longops issue
Next Topic:	my update sql dees not appear in v$session_longops

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 03:19:48 CDT 2024