Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question.

Asked: August 12, 2002 - 10:12 am UTC

Last updated: March 11, 2013 - 1:44 pm UTC


Viewed 100K+ times! This question is

You Asked

Hi Tom

When we do alter system kill 'sid, serial#'. Are we killing the user process or the server process? Because sometimes i see that after the session is killed the query is still running in the sessions even shows KILLED sometimes takes days to disappear! Sometimes we got some problems with large batch jobs in testing enviroment, we killed from sql plus but the job keeps on going!
What happens if we kill the server process with kill -9 instead of issueing alter system kill?


and Tom said...

</code> <code>

the job isn't "going on" it is actually "going back" -- it is rolling back.

If the job took an hour to get where it is, it might take even LONGER to "undo" what its done.

You can monitor how the killed session is doing as far as rolling back is concerned in v$transaction via the used_ublk column.

Consider this example. In a schema "big_table" i started a delete against a 1,000,000 row indexed table. Partway through -- I killed it. This is what you'll see (you need to be more selective on the query against v$transaction of course, I just had one active DML session going)

ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;


1 row selected.

it generated that much undo so far...

ops$tkyte@ORA817DEV.US.ORACLE.COM> @showsql

--------------- --------------- ---------- --------------- ---------------
OPS$TKYTE '7,665' ACTIVE 01@ showsql.sql

2 rows selected.

BIG_TABLE(8,1082) ospid = 13903 command = 7 program = sqlplus@aria-dev (TNS V1-V3) dedicated server=13904
Monday 10:24 Monday 10:38 last et = 822
delete from big_table

I kill it and get the message "it is marked for kill". That means the server heard us and is in the process of rolling back but it'll take a while

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter system kill session '8,1082';
alter system kill session '8,1082'
ERROR at line 1:
ORA-00031: session marked for kill

ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, status from v$session where username = 'BIG_TABLE';

-------------------- ----------

1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;


1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /


1 row selected.

we can watch the used_ublk shrink and even use that to estimate when it'll be done

ops$tkyte@ORA817DEV.US.ORACLE.COM> /


1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /


1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, status from v$session where username = 'BIG_TABLE';

-------------------- ----------

1 row selected.

session is still there...

ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;


1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /


1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /


1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, status from v$session where username = 'BIG_TABLE';

-------------------- ----------

1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;


1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /


1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /

no rows selected

now we are done rolling back and...

ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, status from v$session where username = 'BIG_TABLE';

no rows selected


the session is gone

If you kill -9, it might appear that the session is gone "faster" but not really. We still have to roll back (obviously). We might be doing on demand recovery of the blocks which makes it "appear" faster but we still have to recover the transaction. kill -9 is not something I recommend, especially if you use the above technique to see it rolling back.


  (57 ratings)

Is this answer out of date? If it is, please let us know via a Comment


server process

A reader, August 12, 2002 - 11:22 am UTC


So when we kill a session we are actually killing an user process and the server process will be rolled back by PMON and dies away, am I correct?

We had a problem with a scheduled job with dbms_jobs, scheduled to run every 5 minutes however someone killed it 10 days ago and in v$session we still see the session is marked for kill (however process and paddr shows null) and it shows up in dba_jobs_running however if I query v$process there is no process associated to it (expected) but due to this zombie session the job has not run since it was killed 10 days ago! And I really really dont understand why. The way I fixed it is by set the job status to broken, ran the job manually, after job is finished I set BROKEN to N again by using dbms_job

But I really dont understand why a zombie session blocked a scheduled job (and the job shows as it was running for 10 days in dba_jobs_Running)

Tom Kyte
August 12, 2002 - 11:35 am UTC

No, when you kill a session, you kill a session. The process will stay there (that process could have OTHER sessions in it)

A session is not a process, a process is not a session.

A session can use more then one process in its life (shared server, PQ for example)
A process can service more then one session (shared server, connection multi-plexing for example)

Now however, you've changed the entire scope of the problem from a simple "user process" to "we killed the job queue".

You killed the job queue process, it does not end its process, you broke the job queue (snpN) process. It was unable to perform its function in life, running jobs, updating the dba_jobs_running base tables and such.

you should have tried something like:

alter system set job_queue_processes=0;
alter system set job_queue_processes=N;

to restart the snpN processes.

why my condition is not same as what you said

nintyuui, September 09, 2002 - 11:23 pm UTC

SQL> select used_ublk from v$transaction;


after 20 mins

SQL> select used_ublk from v$transaction;


why the rollback cannot be completed?


Tom Kyte
September 10, 2002 - 7:30 am UTC

It was rolled back. I've seen it be off by one or two blocks (even go negative sometimes).

Estimated Time to Rollback

Vivek Sharma, June 10, 2003 - 11:53 am UTC

Dear Tom,

I had read the column in ORACLE Magazine wherein you had answered a query from a user regarding estimated time of Rollback. I have certain query :
1. I noted that the used_ublk was 2861 and after 60 seconds it was 2840. Thus as per the calculation provided by you, it should take approx.135 Minutes to rollback and thus clear the session. Am I right ?

2. Also the query select to_char(flag,'0000000X') from v$transaction gives an error as

ORA-01481: invalid number format model

What could be the problem ?

Thanks and regards

Tom Kyte
June 10, 2003 - 12:43 pm UTC

1) as long as it keeps rolling back at the really slow rate of 20 per minute (which seems really slow)

2) you are on a really old version of Oracle that didn't support "x" as a format perhaps.

muhammad, June 10, 2003 - 3:25 pm UTC

How i will know from the v$transaction table. which sid it belong

Close, but not quite on the mark

Evan, June 25, 2003 - 8:26 am UTC

My problem is a oracle session marked "KILLED" for several days that will not go away. Restarting is not an option. I know I have seen an answer. It is just a mater of finding it. Keep up the good work!

Tom Kyte
June 25, 2003 - 6:59 pm UTC

Umm, it'll just sit there.

You can kill the thread/process at the OS level but -- so what? it is not like it is consuming gobs of resources really. It has no locks or anything like that. the process is idle and in a total state of "wait" so it'll be paged out. If it were a shared server connection, there is not even that to concern yourself with.

Be very very very very careful if you go the kill route, or you may be going for that unscheduled shutdown if you kill the wrong thing "by accident"

killed session still holding librarycache locks

Angus McTavish aged 75, June 26, 2003 - 6:56 am UTC

We've had a situation crop up a few times where a killed session has somehow retained library cache locks (as indicated by looking at x$kgllk) and is blocking other sessions and slowly but surely grinding the database to a halt. The only way we've been able to clear it up has been to 'kill -9' the process at the OS level. Then the session disappears from v$session and the locks disappear and everything flips back to normal.
It has only ever happened in these exact circumstances:
A user has been running queries in TOAD and the sql contains a long inlist. Once the inlist goes beyond a certain size the session hangs and 'alter system kill session...' kills the session but does not free the librarycache locks. Other users are blocked, system slows down, kill -9, problem solved. But I haven't been able to get to the bottom of it...
Doesn't happen in Sqlplus, just TOAD

Unable to clear lock on table

Brao, October 02, 2003 - 3:09 pm UTC

I have a stored procedure which updates a error table ERROR during inserts/updates.
Since it took more than 7 hours , we were forced to kill the session.
But I got only Session marked to kill.
But we queried
select used_ublk from v$transaction;
to select the used blocks in the current transaction
it got rolled back.

But when I try to access (insert /delete / drop ) into ERROR I am getting the following

ORA-00054 resource busy and acquired with NOWAIT specified

What could be the reason

Hope I made the points clear.

B rao

Tom Kyte
October 02, 2003 - 6:43 pm UTC

and insert or delete wouldn't return that? can you cut and paste an example from sqlplus of that happening?

Shutdown Abort

A reader, October 06, 2003 - 10:57 am UTC

Why does shutdown abort and re-open seem to "speed" the rollback along somehow.

Tom Kyte
October 06, 2003 - 11:04 am UTC

when you are rolling back in an "up" database -- it is done "nicely" so as to not consume the entire machine (it would appear to be a loss of service otherwise)

when you startup, you sort of want everything to get done asap, so the database can be up -- so it is done with a bit more "vigor".

Also, you can have it do selective block recovery as well -- meaning that the database opens right after the rollforward and as processes hit locked blocks -- the are rolled back on demand -- something we only do during the recovery processing during startup. It gives the appearance of "everything is ready to go" even though it is not.

Session Killed status don't go away

kumar, January 14, 2004 - 8:40 pm UTC

I have tested the session killed status scenarios carefully for the entire day observation. Also tested the V$transaction table for the data in USED_UBLK field. That was showing no records and still the staus of that user was showing killed. When I close that connection (SQLPLUS) then only the list was disappeard from v$session.

So this means even though it is killed the session wasn't released the resources or the UGA+PGA allocation of that session back to the OS, Am I correct Tom?

Tom Kyte
January 15, 2004 - 3:13 am UTC

the OS takes care of people who don't use memory. it pages it out. so the memory allocated by that resource isn't really "relevant" here -- it wasn't real memory unless you had so much free memory that it could afford to let it sit idle.

but yes, the process was there -- it was waiting for the client to send a message to it so it could gracefully say "so sorry, you've been killed, goodbye" instead of the client just reporting "ora-3113 end of file on communication channel" (which would tend to increase support calls by 1,000%)

Tom is a genius

Georges Borlasky, May 12, 2004 - 2:29 pm UTC

Tom is a genius

How to restrict

A reader, June 27, 2004 - 3:55 am UTC

How to set the used_ublk limits?.
Dear tom when we killed a session it shows the status as "killed" untill used_ublk are not come to 0 (optimal value). it takes much time to reach that value and our cpu utilization reaches 100% during that time and other application goes very slow.
can we restrict a session of not using more than defined no of used_ublk.


Tom Kyte
June 27, 2004 - 11:12 am UTC

using resource manager, yes.

</code> <code>

but, I would be asking myself "how did I get into a production environment whereby runaway updates are permitted by my software". this could be a "we granted too much power to too many people" problem more than anything.

Oracle Developer

Dawar, July 14, 2004 - 1:57 pm UTC


I run the following scripts from Oracle 7.3.4. but get no rows selected.

sql> select used_ublk from v$transaction;

sql> no rows selected

sql> select * from v$transaction;

sql> no rows selected

what is it mean?


Tom Kyte
July 14, 2004 - 10:06 pm UTC

means you have no transactions.

do this:

create table t ( x int );
insert into t values ( 1 );

then run those queries.....

why I am getting "?" for termianl?

Dawar, July 14, 2004 - 4:53 pm UTC


select process , sid, terminal , osuser
from v$session;

258 1.00 ? oracle7
260 2.00 ? oracle7
262 3.00 ? oracle7
264 4.00 ? oracle7
266 5.00 ? oracle7
268 6.00 ? oracle7
270 7.00 ? oracle7
272 8.00 ? oracle7
274 9.00 ? oracle7
276 10.00 ? oracle7
179 12.00 pts/5 oracle7
233 14.00 SONIA soalva

why I am getting "?" for termianl?

I also getting "?"from

ps -ef| grep ora

oracle7 260 1 0 Jul 09 ? 0:01 ora_dbwr_witsprd
oracle7 262 1 0 Jul 09 ? 0:02 ora_lgwr_witsprd
oracle7 264 1 0 Jul 09 ? 0:00 ora_ckpt_witsprd
oracle7 266 1 0 Jul 09 ? 0:00 ora_smon_witsprd
oracle7 268 1 0 Jul 09 ? 0:01 ora_db01_witsprd
oracle7 270 1 0 Jul 09 ? 0:01 ora_db02_witsprd
oracle7 272 1 0 Jul 09 ? 0:00 ora_db03_witsprd
oracle7 274 1 0 Jul 09 ? 0:00 ora_db04_witsprd
oracle7 276 1 0 Jul 09 ? 0:00 ora_db05_witsprd
oracle7 278 1 0 Jul 09 ? 0:01 ora_db06_witsprd
oracle7 345 1 0 Jul 09 ? 0:00 /disk/app/oracle/product/7.3.4/bi
n/r30mtsm name=repwitsprd
oracle7 18546 18457 0 12:39:24 pts/6 0:00 sqlplus
oracle7 18350 18348 0 11:42:24 pts/4 0:00 -csh
oracle7 18547 18546 0 12:39:30 ? 0:00 oraclewitsprd (DESCRIPTION=(LOCAL
oracle7 18457 18455 0 12:13:10 pts/6 0:00 -csh
oracle7 17714 17712 0 08:49:54 pts/3 0:00 -csh
oracle7 18124 1 0 10:15:42 ? 0:01 oraclewitsprd (LOCAL=NO)
oracle7 17919 17879 0 09:15:16 pts/5 0:00 sqlplus
oracle7 17879 17877 0 09:14:54 pts/5 0:00 -csh
oracle7 17920 17919 0 09:15:26 ? 0:00 oraclewitsprd (DESCRIPTION=(LOCAL
oracle7 18741 18350 0 13:48:14 pts/4 0:00 grep ora

Tom Kyte
July 15, 2004 - 11:34 am UTC

ask your OS vendor?

Session killed but unable to compile that object

Laxman Kondal, April 14, 2005 - 1:59 pm UTC

Hi Tom

I was debugging a package and session got hung-up and finally I had to kill the session and close the application - TOAD where I was debugging.

And now its over 24 hours and still can not compile that object from TOAD, PL/SQL Developer or SqlPlus and after 5 minutes error comes 'ORA-04021: timeout occurred while waiting to lock object '.

I can compile all other objects except this one which got hung-up.

Is there any way without bouncing db to over come this?


Is there any way to recompile th
lkondal@OPS> SELECT sid||','||serial# username,
2 status,
4 osuser,
5 client_info,
6 TO_CHAR(logon_time,'DD-MON-YY HH:MI:SSSS') loged_on
7 FROM v$session
8 WHERE osuser = 'lkondal';

--------- ---------- ---------------- -------- ----------- ---------------
21,10778 INACTIVE T.O.A.D. lkondal 14-APR-05 12:06:070
24,11815 INACTIVE PL/SQL Developer lkondal 14-APR-05 11:41:161
28,34869 ACTIVE SQL*Plus lkondal 14-APR-05 01:04:000
34,42849 KILLED TOAD.exe lkondal 13-APR-05 02:58:212

no rows selected.

Elapsed: 00:00:00:00
lkondal@OPS> SELECT used_ublk FROM v$transaction;

no rows selected

Elapsed: 00:00:00:00
lkondal@OPS> alter package p_exp_imp compile;
alter package p_exp_imp compile
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object OPS.P_EXP_IMP



1 row selected.

Elapsed: 00:00:00.00
lkondal@OPS> alter package p_dataset compile;

Package altered.

Elapsed: 00:00:00.03

Tom Kyte
April 14, 2005 - 2:00 pm UTC

so you have a dba around? they should know how to kill the connection from the OS level. That killed session is waiting for toad (which is dead) to tell it to do something.

Kill OS Process

Vinayak, April 15, 2005 - 12:34 am UTC

To see which OS process to kill:

a) On a Unix platform:

FROM v$process
(SELECT paddr FROM v$session WHERE sid=<> AND serial#=<>);

% kill <spid>

b) On a Windows platform:

SELECT spid, osuser, s.program
FROM v$process p, v$session s
WHERE p.addr=s.paddr
AND s.sid=<> AND s.serial#=<>;

Then use orakill at the DOS prompt:
c:>orakill sid thread

Oracle 9205 Max Availability

A reader, April 19, 2005 - 4:59 pm UTC


If i issue this command,

kill -HUP ora_lgwr_ORACLESID

Will this cause corruption in the database or on the standby site.

Tom Kyte
April 19, 2005 - 7:48 pm UTC

better not.

(meaning it better not cause corruption).

holding file

A reader, April 20, 2005 - 12:04 am UTC

Have you seen that before? Consider this. A session is generating gobs of data on the /tmp (instrumetation) and suddenly /tmp filesystem gets full. So, something such as ls -lt | pg goes wrong. I've tried to find out what's going on, but no clue for where start. Do you have any idea?

Tom Kyte
April 20, 2005 - 6:50 am UTC

"goes wrong" what goes wrong with ls -lt | pg?


Vidya Sagar, May 11, 2005 - 7:55 am UTC

New Database has been created and Listener has been started, sqlplus has been connected as test/test@tstsvr. After some time[ like after 1 hr], sqlplus gets disconnected and $sqlplus test/test@testsvr doesnt work showing ERROR: ORA-03113: end-of-file on communication channel,
where as $sqlplus test/test works [i.e. when no SID is specified]. This has been a problem while trying to connect through Client. Can you please tell me the reason for this.

Tom Kyte
May 11, 2005 - 8:00 am UTC

sounds like you have a firewall in place and it is timing out connections

user/pass@database -> used network
user/pass -> did not use network

ask your network guys what is the networking policy in place

Using Kill -9

Michael, May 11, 2005 - 11:56 am UTC

The way it was explained to me years ago is that when you do and alter system kill session 'nnn,nnn' then pmon goes to work cleaning up that session, rolling back uncommitted work, etc. However, this can take a long time depending on how much rolling back and how many other demands are being made on pmon(on a busy box, it could be a lot). As observed earlier, sometimes sessions would be marked as killed for a very long time.

On the other hand, kill -9 essentially pulls the rug out from under the Oracle session by killing the shadow process, forcing PMON to dedicate all of its might to rolling back as quickly as possible, not just when it has the time to do so. The explanation was that doing kill -9 prioritized the rollback/cleanup operation as #1 on its to do list, everything else came later on that list. It seemed sensible to me, because when a kill -9 was used on a process that was running too long and doing a lot of dml, it *appeared* that new logon requests were handled more slowly than usual, for a minute or two anyway. Does that make any sense from an internals perspective?

Also, a warning for kill -9 lovers. NEVER use this on a process that has allocated PQ slaves...the main process will go away, but not the PQ slave processes, and nobody else will be able to allocate them. I found out the hard way. Use the alter system command for the parent process, and the PQ slaves will be de-allocated.

Tom Kyte
May 11, 2005 - 12:29 pm UTC

no, pmon doesn't with alter system kill session -- the session does it. and does it in bits so as to not overwhelm the system .

Also, it normally takes longer to ROLLBACK than it did to get to do the work in the first place.

Until the CLIENT gets the ora-28, the session will remain in the killed state after it is done.

on the other hand, kill -9 makes pmon do it. pmon will do it much like the session would have but can use parallel and even fast start (block level recovery upon demand) if possible.

Thank you.

Sagar, May 30, 2005 - 1:59 pm UTC

How to increase extents of an existing tablespace and what if its a temporary tablespace in oracle 7.1

Tom Kyte
May 30, 2005 - 2:11 pm UTC

well, tablespaces really don't have "extents", they can (in dictionary managed which you obviously have) supply a DEFAULT max extents for segments created therein.

Are you asking "how do I make a tablespace bigger"? If so, you can

alter tablespace t add datafile .....

(in 7.1, there wasn't any resize of datafiles yet)

Ok but

Sagar, May 30, 2005 - 2:47 pm UTC

I have run the query - select * from sys.user_tablespaces; the output is

tbsp_name init_ext next_ext min_ext max_ext %incr status

RBTHREE 1048576 1048576 2 50 0 INVALID
USAGEINDX 1048576 1048576 2 50 0 INVALID
RBS 2097152 1048576 2 249 0 INVALID
TMP 20480 20480 1 249 50 INVALID
TEMP 20480 20480 1 249 50 ONLINE,
My query is
1. Can i increase the init_extent and next_extent values against a particular tablespace as the TEMP TABLESPACE already has enough size.
2. Can the status of these Invalid tablespaces be changed to ONLINE.

Tom Kyte
May 30, 2005 - 3:25 pm UTC

as stated, those will be the defaults for newly created segments in those tablespaces -- nothing more.

tkyte@ORA8IW> alter tablespace users default storage ( maxextents 512 );
Tablespace altered.

but it won't change ANY EXISTING segments, it only affects stuff you create into the future and provides nothing more then the DEFAULT value (segments are free to override this)

Now, as I recall, the maxextents of a segment in 7.1 was limited by the size of the database block (smaller blocks -> smaller max extents was possible). So, you probably have a 2 or 4k blocksize there and 249 could well be "your limit" (I believe 512 was the limit with an 8k block). But we are going back well over a decade so....

You might just want to drop that temp and rebuild it with better extent sizes. 20k isn't very "big" after all.

2) depends, why are the offline in the first place? you can use alter tablespace to online it, but if it is offline because the file went missing, well, then it won't online.

kill -6 vs. kill -9

A reader, May 30, 2005 - 3:36 pm UTC

I use to use kill -6 instead of kill -9
(thought I read some recommondation about that)

which one is preferrable ?

Tom Kyte
May 30, 2005 - 8:08 pm UTC

well, 6 should be "abort" and 9 is "kill, unblockable" -- they should have the same (nasty) effect.

I use 9 myself, you are killing it.

Thank you Tom, that helped me.

Sagar, June 01, 2005 - 2:21 am UTC

As it is on 7.1 as suggested by you, I have added a datafile against the tablespace. That has worked out.
Another Query
select * from testa A, testb B where A.chtest1 = B.chtest1;
A and B are parent and child tables. I want only one row to be displayed against each multiple retrieval, is that possible using count or so, pls suggest.

Tom Kyte
June 01, 2005 - 8:55 am UTC

unless you have some way of identifying the child row you want, in 7.1, your options are extremely limited.

How to find out spid of the killed process

Parikshit Paul, June 15, 2005 - 10:37 am UTC

Hi Tom,
Some sessions were killed in the database, but they are still in v$session with the status as "killed". These processes do not have any entry in v$transaction
I issued the following query :
select * from v$transaction, v$session where addr=taddr
and sid in (select sid from v$session where status='KILLED')
and no rows were returned
...which means no rollback is going on for the processes.
I queried in v$session_wait for the sids and they were waiting for "SQL*Net message from client"
How can I find out the spid of these processes so that i can kill them from os ?

Tom Kyte
June 15, 2005 - 3:33 pm UTC

if you plan on killing the os process, that should be done "first", the connection between the session and the process is "gone".

short of querying up the spid and program from v$process where addr is not in the paddr's of V$session and looking at each 

ops$tkyte@ORA9IR2> select spid, program from v$process where addr not in ( select paddr from v$session ) and spid is not null
  2  /
------------ ------------------------------------------------
8731         oracle@localhost.localdomain (S000)
8733         oracle@localhost.localdomain (D000)
15870        oracle@localhost.localdomain (TNS V1-V3)
4 rows selected.

15870 is a "candidate" -- s000 and d000 are the shared server and dispatcher.  Since people can be connected to the database but not have a session, it is only a candidate. 

test case

A reader, June 15, 2005 - 7:57 pm UTC

Can you please kindly show a test case where "people can be connected to the database but not have a session"

Tom Kyte
June 16, 2005 - 3:38 am UTC

kill them, there you go :)

but using OCI, you would find that the act of "logging in" has at least two steps:

a) connect - create physical circut between client and database
b) create one (or more) sessions on that connection.

Thank you

Sagar, June 16, 2005 - 2:24 am UTC

Hi Tom,

We have a view which constitutes of near about a combination of 15 tables. When we are retrieving this view we get: ORA-01630:max extents(121) reached in temp seg in tbsp temp. then we said


now we get: ORA-01652:unable to extend temp segment by 2560 in tbsp temp,
then we said


now we get: ORA-01652:unable to extend temp segment by 92169 in tbsp temp
Can you suggest in this context 

Tom Kyte
June 16, 2005 - 3:43 am UTC

you are running out of space, make temp bigger.

Query on Oracle to MS SQL Server 2000

Sagar, June 20, 2005 - 4:17 am UTC

Hi Tom,
We have a transaction where in we transfer data from Oracle 9 to MS SQL Server2000, while doing this when we write Begin Transaction and End Transaction statements, we get this message where as without these it has no problems, but without them our transaction cannot be complete.
Message is as below.
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [Oracle][ODBC]MTS support not available, could not load ORAMTS.DLL]

Tom Kyte
June 20, 2005 - 10:17 am UTC

sorry, I know less than nothing about ODBC and OLE DB.

I'll have to refer you to support for this one (or the forums on

Linking process to session

Tracy Tupman, June 21, 2005 - 6:37 am UTC

A session was killed 4 days ago. It is still in v$session.
SQL> select username, status, logon_time from v$session where status = 'KILLED';

SQL> ARNOLD       KILLED     17-JUN-05

but the session is not joined to a process...

SQL> select s.sid,s.serial#,p.spid,s.username from   v$session s,v$process p where s.paddr=p.addr and s.status = 'KILLED';

SQL> no rows selected

However, there are 2 processes with no session...

SQL> SELECT spid,username,terminal,program from v$process where not exists (select 1 from v$session where paddr = addr);

SQL>                                                                 PSEUDO
19410     oracle@livedb (TNS V1-V3)

2 rows selected.

In the OS this command,  ps -ef | grep "Jun 17"

gives us this:

oracle 19410     1  0   Jun 17 ?       125:02 oracleTOM (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))

Which coincidentally has the same process id as the one returned from the last sql command. Is there any way of knowing whether this process is the same one that was being used by the killed session? I have had instances where a killed session has retained library cache locks and the only way to free them was to kill the associated os process, but to do that you have to be sure you have the right process...


Killed session not releasing locks

Kuldeep, August 23, 2005 - 8:37 am UTC

Dear Tom,

I had killed a session which was holding the lock of my one table. Now the status of session in v$session is 'KILLED' and this session is still holding the locks of that table.

I am continuously querying the v$transaction dict view. The value in column "used_ublk" was reduced to 141 from 542 in 2 hours but after that (around 4 hours passed) it is still 141.

It is not changing, it seems due to some reason rolling back process of transaction has halted. What should I do now? What dict view I should query to know more about this process/rolling back transaction.

Please help.

Thanks and regards,

Killed Session

kuldeep, August 26, 2005 - 7:12 am UTC

This is in continuation of my above posting. Now 4 days has been passed when I killed the session. The status of above session is still killed and USED_UBLK in v$transaction view is still that 141, this also has not changed.

Do I have any option other than bouncing the DB.

Thanks and regards,

Tom Kyte
August 26, 2005 - 8:48 am UTC

depends on your OS, I'll ask you to contact support and get their assistance with this please.

muhammad, January 17, 2006 - 1:19 pm UTC

In my case , its show mark for killed in v$session and in v$transaction used_ublk is increasing.


killed sessions not showing in v$process

Jianhui, April 07, 2006 - 6:01 pm UTC

Hi Tom,
Why are sessions marked as killed not showing in v$process anymore, however the server processes are still there from OS level?


SQL> select sid, serial#, paddr  from v$session where status='KILLED';

---------- ---------- ----------------
        60      41802 0000000B01C42B88
       413      48653 0000000B01C42B88
       510      42132 0000000B01C42B88
       946      23666 0000000B01C42B88
      1064      55278 0000000B01C42B88
      1492       6472 0000000B01C42B88
      1938      44074 0000000B01C42B88
      2412         19 0000000B01C42B88
      3119      38272 0000000B01C42B88

9 rows selected.
SQL>select spid from v$process p, v$session s where s.status='KILLED' and s.paddr=p.addr

no rows selected

SQL> select version from v$instance;



ORA-04021: timeout occurred while waiting to lock object

Peter, June 15, 2006 - 10:54 am UTC

Hi Tom,

I met the oracle error. ORA-04021 when compiling a procedure (let's call the procedure as PROC_A).
After checking the v$sqlarea and v$session
I found the there are three sessons doing the similar thing like
INSERT /*+ APPEND */ INTO "SYS"."ORA_TEMP_1_DS_138797...

Then I asked our DBA to kill the three sessions.

the oracle error is still there even thought I waited for 10 hours.

then, I check the V$access, v$session_wait, and v$enqueue_stat

select sid, object from v$access where object = 'PROC_A';
-- 36, PROC_A
select sid, event, seconds_in_wait, state
from v$session_wait WHERE sid=36;
-- 36 enqueue 84506 WAITING

select * from v$enqueue_stat where total_wait# > 500;
PS 12955 84718529
TC 1090 110877

PS, Parallel Slave Synchronization
TC, Temporary Segment (also TableSpace)

How do I unlock this?

The oracle is Oracle9i Enterprise Edition Release


kill session,

A reader, July 08, 2006 - 10:27 am UTC

I understand in your comment in the beggining of this thread that kill -9 is something which you don't recommend.

I have seen a situation when we kill a long running transaction, the status will be KILLED. The rollback would be happening and the table into which we were inserting or deleting will be locked until the rollback is complete.

Sometimes we don't have that much time to wait till the rollback is complete. So we use kill -9 and instantly the session is gone and also all the locks held on the table are released. We found this is a very convenient way for us.

How were the locks got released after we said kill -9?
What are the cons in using kill -9 (I know its pros)?

You said the Oracle would be still doing rollback, under what SID the rollback transaction would be happening?


v$session STATUS='KILLED', some different behaviour Oracle 10g R1

Suvendu, November 15, 2006 - 9:29 am UTC

Hi Tom,

I'm trying to find PID of a killed a session, but didn't get it from v$process though it showing in v$session. After that, I have read out some your follow up in this site, that v$session.STATUS='KILLED' will remain there as long as v$transaction.USED_UBLK not coming to ZERO, but in this case it is showing ZERO, but WHY status is still 'KILLED'.

Is there any change on Oracle 10g ?

Kindly, provide your input on this. And, correct me If I'm wrong.

Thanking you.


SQL>  select username, sid, serial#, status from v$session where status='KILLED';

USERNAME                              SID    SERIAL# STATUS
------------------------------ ---------- ---------- --------
INSSTG_LAM                            135         51 KILLED

SQL> select pid from v$process where username='INSSTG_LAM' and serial#='51';

no rows selected

SQL> select USED_UBLK from v$transaction;

no rows selected

SQL> select * from v$version;

Oracle Database 10g Enterprise Edition Release - 64bi
PL/SQL Release - Production
CORE      Production
TNS for Solaris: Version - Production
NLSRTL Version - Production


Tom Kyte
November 16, 2006 - 2:57 am UTC

it stays there until the client session does something so we can tell the client session "sorry, you are dead, goodbye"

not a bug, it is the way it works.

How to know the os id from another host?

Sean, November 17, 2006 - 5:53 pm UTC

Hi Tom,

We have a Unix server A which has all the shell scripts which call database on Unix Server B. When we want to kill one session, we found the session came from Unix server A (by looking at machine column of v$session), but OS id in v$process is the id from Server B. Are there any ways to know which script in Server A uses the database session in Server B? ( Oracle 9207, Solaris 9 )

Thanks so much for your help.


Session Memory info..

Mark Zuraski, December 14, 2006 - 12:06 am UTC

Oracle 9i (Release 2 on unix)..


I saw in your query how you showed used undo blocks goes down.
How can you see the %age of memory a session is using..
Do you have a showsession query that can show memory info per session..
I guess I am trying to come up with a query that can show how expensive a session really is..


Tom Kyte
December 15, 2006 - 8:05 am UTC

you'll be hard pressed to define "expensive"

but everything about a session is in the v$ views - v$sesstat for example.

USED_UBLK keeps on in creasing even the session is KILLED

A reader, December 19, 2006 - 9:22 am UTC


I just killed a session however instead of going away it is there and keeps on using rollback!

  1  select username, b.USED_UBLK, b.USED_UREC, a.status
  2   from v$transaction b, v$session a
  3  where addr = '0000000404868BF0'
  4* and b.addr = a.taddr

------------------------------ ---------- ---------- --------
APPS                                  382      36644 KILLED

SQL> r
  1  select username, b.USED_UBLK, b.USED_UREC, a.status
  2   from v$transaction b, v$session a
  3  where addr = '0000000404868BF0'
  4* and b.addr = a.taddr

------------------------------ ---------- ---------- --------
APPS                                  383      36672 KILLED

SQL> r
  1  select username, b.USED_UBLK, b.USED_UREC, a.status
  2   from v$transaction b, v$session a
  3  where addr = '0000000404868BF0'
  4* and b.addr = a.taddr

------------------------------ ---------- ---------- --------
APPS                                  385      36870 KILLED

SQL> r
  1  select username, b.USED_UBLK, b.USED_UREC, a.status
  2   from v$transaction b, v$session a
  3  where addr = '0000000404868BF0'
  4* and b.addr = a.taddr

How can this be???

How to Kill these kind of sessions

Saravanan Ramalingam, April 19, 2007 - 3:56 am UTC

Hi Tom,

How to go ahead with this scenario to kill the session ??

SID SERIAL# STATUS   OSUSER                         PROGRAM                     
--- ------- -------- ------------------------------ ----------------------------
125   41539 KILLED   192078                         SQL Developer               
133   46999 KILLED   192078                         SQL Developer               
146      94 INACTIVE 01HW030996\Administrator       sqlplus.exe                 
3 rows selected

SELECT spid, osuser, s.program fROM v$process p, v$session s WHERE p.addr=s.paddr AND s.sid=133 AND s.serial#=46999
0 rows selected
SID SERIAL# STATUS   OSUSER                         PROGRAM                     
--- ------- -------- ------------------------------ ----------------------------
125   41539 KILLED   192078                         SQL Developer               
133   46999 KILLED   192078                         SQL Developer               
146      94 INACTIVE 01HW030996\Administrator       sqlplus.exe                 
3 rows selected
select used_ublk from v$transaction
0 rows selected

Tom Kyte
April 19, 2007 - 7:20 am UTC

they are killed.

zombie threads with nothing in v$session

Russell H, June 20, 2007 - 1:47 pm UTC

Oracle version on Windows Server 2003 (32 bit)

Every few days or so, we have a stiuation where the database stops accepting new connections, though v$session shows only about 25 sessions. The windows task manager shows a high thread count, usually around 250 threads. I have been resorting to re-starting the database when this occurs, because I cannot identify sessions associated with the threads.

Seems like this may be an issue for support, but are there any other places you can suggest that I can look that might help diagnose this?
Tom Kyte
June 20, 2007 - 2:19 pm UTC

peek into v$process

could be your client - you can CONNECT without having a SESSION. Could be a connection pool problem...


RussellH, December 28, 2007 - 1:55 pm UTC

Following on from the previous post, v$process had about 150 processes, while v$session had a small number of sessions (about 20).

Do you have any suggestions on how to investigate how these processes (threads) could have been created and not killed?


Tom Kyte
December 28, 2007 - 3:51 pm UTC

they might not be a problem at all.

In Oracle a connection can have 0, 1 or more sessions on it. A session may use 1 or more connections in it's life.

they could be backgrounds (shared servers), they could be connections without a session, they could be quite innocent. You'd have to show why they are a problem before we could comment further.


RussellH, December 28, 2007 - 5:16 pm UTC

Thank you for the quick reply.

I'm not sure the high process count is the problem, but when I sample the v$process table and the applications are running normally, I see 30-40 processes. However, when the database (listener) stops accepting new connections, I see the high process count in v$process and the high thread count in the oracle process. I was expecting to see a high session count in v$session, perhaps due to applications not closing connections, but I don't see that.

The applications are web applications using ODP.NET and Oracle Provider for OLE DB to connect to the database. Both providers have connection pooling enabled. The connections are dedicated server connections (not shared).

The developers are reviewing the application code to make sure that connections are properly closed, and connection related objects are disposed.

The problem is that something is preventing new database sessions from being created.

Problem with "select" statement marked for kill

Dainee, June 11, 2008 - 12:48 pm UTC

Hi Tom,
I noticed that the following statement was blocking sessions so I kill it via alter system kill session:

The sql was called via a dblink and I killed the sessions on both sides. However, it is still sitting in the v$session table with status KILLED.

Any idea why?

Tom Kyte
June 11, 2008 - 4:03 pm UTC

well, that statement wasn't blocking other sessions, that just reads.

the transaction that was currently executing that statement might have done some stuff (inserts/updates/deletes - whatever) that were blocking others.

it is normal for a session to enter the "killed" status when you kill it. It is waiting for the client (the initiator of the statement) to acknowledge that it was "killed"

custom message

A reader, June 23, 2008 - 8:25 am UTC

Hi Tom,

Can we send custom message instead of Oracle message to the client when his session is killed.
Tom Kyte
June 23, 2008 - 8:40 am UTC

no, the client will get an ora-28 "you have been killed" message. The client is free to display whatever they like after that, but they will get the ora-28

A reader, July 23, 2008 - 1:21 pm UTC

I am not sure what happened to these sessions. See below

sys@DB.WORLD> select sid,serial# from v$session where sid=462;
       SID    SERIAL#

---------- ----------

       462       2945

sys@DB.WORLD> alter system kill session '462,2945' immediate;

alter system kill session '462,2945' immediate


ERROR at line 1:

ORA-00030: User session ID does not exist.


One way it¿s showing that sid is existing but while killing it¿s say session ID does not exists.
This is 2nd time I am getting such kind of issues.Last time I restarted the database instance to resolve this issue.
Please suggest me on this how to ressolve this kind of issue.

Pratik, May 29, 2009 - 5:33 am UTC

Hello Tom,

We always learn something new whenever we surf
Thanks lot for this..

Back to the main point...
We are also facing similar problem as mention in above post(By>>A reader from Rochester,USA ).

we have oracle( on windows machine, when I try to kill one session I got error like:

ORA-00030: User session ID does not exist.

I think this session entry might be clean up by the PMON process after some time.
But can you explain why this type of error occurs and what is reason for this error.


Tom Kyte
May 29, 2009 - 8:35 am UTC

[tkyte@dellpe ~]$ oerr ora 30
00030, 00000, "User session ID does not exist."
// *Cause:  The user session ID no longer exists, probably because the
//          session was logged out.
// *Action: Use a valid session ID.

I don't know what else to say on that one? You tried to kill a session that doesn't exist anymore.

killing session at sql levle

kair, May 11, 2010 - 2:32 pm UTC

Hi Tom,

In my case i have killed the session at sql level , but when i checked the USED_UBLK it is not getting reduced.

SQL>  select used_ublk from v$transaction where ADDR='00000003A88BBFE8';


it is same when i killed that session.

later i tried to see for the spid of the session .

to my surprise there is no os level process running .

how to handle this situation.

actually this session is blocking one table, for which other users are getting time wait error.

Resolution to Killing /Compiling Lock Objects after Parallel Query Batch Operation.

Anil, November 18, 2010 - 6:42 am UTC

I had a Parallel Pipeline Package Job that got killed.
There are rows in v$session with 'KILLED' and also following data in v$session_event for the killed sid.

Event Total Total Time
Waits Timeouts Waited

log file switch completion 4 0 6
latch: In memory undo latch 1 0 0
TCP Socket (KGAS) 5495 0 3908
SQL*Net message to client 22 0 0
SQL*Net message from client 22 0 2194
SQL*Net more data from client 1 0 0

with Wait Classes as below in that order


Now the issue ?
Not able to compile the package using parallel/pipelined fns as it timeouts due to lock on the object.

I saw the dba_ddl_locks and it shows the above package + dreaded Type 18 + some sys and other dependant packages in there.

My Question ?
Is there a way or utility that Oracle has to clean this dangling/holded piped txn as it uses TCP KGAS evt so that the 'KILLED' gets removed from v$session and also locked objects also gets released so that it can be compiled, without going into the usual oracle server shutdown and startup way ?

Also would request you to include this as a new Question if it passes your test.

Thx in advance,
Anil Bishnoie

kill sessions

pranav, March 28, 2012 - 4:31 pm UTC

Hi Tom,

Can you please let me know how to terminate active/inactive sessions of particular users automatically. As part of maintenance we have to do this daily after hours.
Oracle version: 11gR1

Thank you very much for your help.
Tom Kyte
March 29, 2012 - 6:56 am UTC

you can write a custom job of your own to use alter system kill/disconnect session (query v$session for sessions)

seems like a very very very strange requirement, I'd be interested in hearing why you feel you need to do this - especially to active sessions.

kill sessions

pranav, March 29, 2012 - 2:43 pm UTC

Thank you very much for your time Tom.

We are locking users during off hours. Requirement is to prevent them locking some of our nightly jobs. If those users are already logged in before lock script run we are planning to terminate their sessions after running the lock script.

We prepared custom script for this.

No DCD,Still server session terminates after two hour.

Pradeep, April 18, 2012 - 10:32 pm UTC

The information in documentation and in MOS (ex Doc ID 257650.1)says if a client process lost contact with server (due to a firewall time out) the server session will NOT get terminated.(as it have no way to know client is lost).

However our observation is two hours since the client is lost, server session also getting removed.(There is no DCD set). Observed in 10205 and of the time this happens and in few case it does not happen too). There no documentation on this behavior I could find. So wondering if you have some inputs. Sample Server session trace is below.

[17-APR-2012 14:14:14:777] nsrdr: recving a packet
[17-APR-2012 14:14:14:777] nsprecv: entry
[17-APR-2012 14:14:14:777] nsprecv: reading from transport...
[17-APR-2012 14:14:14:778] nttrd: entry
[17-APR-2012 16:14:14:940] ntt2err: entry
[17-APR-2012 16:14:14:951] ntt2err: soc 11 error - operation=5, ntresnt[0]=517, ntresnt[1]=104, ntresnt[2]=0

Tom Kyte
April 19, 2012 - 5:19 am UTC

that would indicate there was a conversation going on between the client and server that got interrupted.

For example - if the client submitted a long running request - a long running stored procedure....

and then the firewall timed them out - before the server responded...

and then eventually when the server goes to respond - they discover "no one is home"

idle server session

A reader, April 19, 2012 - 2:07 pm UTC

Tom, Thanks a lot for your reply.
However for testing the session was just opened and No SQL was run before client session was detached.

It reproduces whenvever tested like:

1.Open an sqlplus connection from a client machine.(NO SQL even executed).
2.Bringdown the network interface on client(to simulate a network detach).
3.After two hours server session disappear.

So Here the server session has nothing to return to client(which I know of).

Tom Kyte
April 20, 2012 - 3:49 pm UTC

that does sound like dead client detection, please do verify your sqlnet.ora on the server - make sure you find all sqlnet.ora's - make sure you are looking at the one that the database is actually using.

Also, it can be an implementation feature of the tcp/ip stack you are using on your server as well. All it would have to do it timeout the connection after some period of time - that is an option as well.

Thanks Tom.

A reader, April 25, 2012 - 1:48 pm UTC

Thanks Tom for your excellent answers (as always).
We suspect the behavior is related to tcp_keepalive_time = 7200 seconds (2 hours)
(which falls under second point in your last response).

Thanks again.

killing session and dcd

rizwan, March 10, 2013 - 3:29 pm UTC

Hi Tom ,

In below case will the session disappear ?

1.Open an sqlplus connection from a client machine.(NO SQL even executed).
2.Note down this connection on the database and kill the session

DCD is set in this case . Will DCD work here and remove the session entry ? (Assuming i don't go to the client to get "your session has been killed message"

Tom Kyte
March 11, 2013 - 9:33 am UTC

if the DCD is never touched again, it will leave the session in a sniped state (killed but connected).

if the DCD attempts to access the database for whatever reason, it will get the oar-28 your session has been killed and will then go away.

so, it depends.

you can disconnect the session as well.

killing session and dcd

rizwan, March 11, 2013 - 12:48 pm UTC

Hi tom ,

This is regarding by above question . By DCD you mean client ? By DCD i mean Dead client detection set using sqlnet.expire_time parameter

What if the client is never touched again ? Will oracle leave the session forever ? Why cannot Dead client detection remove that session even if the
client is never touched again ? I am aware that if tcp/ip send socket failure message than oracle will remove the session but why not DCD can do that ?

Hope my question is clear and thanks for the impact you have made in my database career.


Tom Kyte
March 11, 2013 - 1:44 pm UTC

Oh, sorry, for some reason I translated DCD into DAD and was thinking mod_plsql. acronyms did it again....

dead client detection is when the server pings the client and disconnects the session automagically if the client is "gone". Here the client would not be gone.

kill session is designed to be graceful - so the client gets an ora-28. DCD cannot do that gracefully.

if you want to be un-graceful, disconnect the session.

A reader, March 06, 2014 - 6:15 am UTC

Great demo

A reader, September 24, 2014 - 10:16 pm UTC

Your straightforward demo was excellent! I had a session that took a very long time to die. I didn't understand why. The 2 queries below allowed me to figure out why it was taking so long. It was indeed, still rolling back.