server process
A reader, August 12, 2002 - 11:22 am UTC
Hi
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)
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;
USED_UBLK
----------
1
after 20 mins
SQL> select used_ublk from v$transaction;
USED_UBLK
----------
1
why the rollback cannot be completed?
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
Vivek
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
June 10, 2003 - 7:53 pm UTC
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!
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.
Thanks
B rao
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.
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?
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.
thanks
June 27, 2004 - 11:12 am UTC
using resource manager, yes.
</code>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96521/dbrm.htm#16978 <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
Tom,
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?
cheers,
Dawar
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
Tom,
select process , sid, terminal , osuser
from v$session;
PROCESS SID TERMINAL OSUSER
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
=YES)(ADDRESS=(PROTOCOL=beq)))
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
=YES)(ADDRESS=(PROTOCOL=beq)))
oracle7 18741 18350 0 13:48:14 pts/4 0:00 grep ora
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?
Thanks.
Regards.
Is there any way to recompile th
lkondal@OPS> SELECT sid||','||serial# username,
2 status,
3 MODULE,
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';
SERNAME STATUS MODULE OSUSER CLIENT_INFO LOGED_ON
--------- ---------- ---------------- -------- ----------- ---------------
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
lkondal@OPS> SELECT SYSDATE FROM DUAL;
SYSDATE
---------
14-APR-05
1 row selected.
Elapsed: 00:00:00.00
lkondal@OPS> alter package p_dataset compile;
Package altered.
Elapsed: 00:00:00.03
lkondal@OPS>
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:
SELECT spid
FROM v$process
WHERE addr IN
(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
Tom,
If i issue this command,
kill -HUP ora_lgwr_ORACLESID
Will this cause corruption in the database or on the standby site.
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?
Thanks,
April 20, 2005 - 6:50 am UTC
"goes wrong" what goes wrong with ls -lt | pg?
ORA-03113
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.
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.
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
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.
May 30, 2005 - 3:25 pm UTC
1)
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 ?
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.
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 ?
Thanks
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 /
SPID PROGRAM
------------ ------------------------------------------------
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"
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
SQL> ALTER TABLESPACE TEMP DEFAULT STORAGE (INITIAL 1M NEXT 10M);
now we get: ORA-01652:unable to extend temp segment by 2560 in tbsp temp,
then we said
SQL> ALTER TABLESPACE TEMP ADD DATAFILE '/ORACLE_HOME/DBS/TEMP05.DBF' SIZE 600M REUSE;
now we get: ORA-01652:unable to extend temp segment by 92169 in tbsp temp
Can you suggest in this context
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]
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 otn.oracle.com)
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,
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.
Thanks
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?
Best,
SQL> select sid, serial#, paddr from v$session where status='KILLED';
SID SERIAL# PADDR
---------- ---------- ----------------
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;
VERSION
-----------------
8.1.7.4.0
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.
However,
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 9.2.0.5.0
Thanks,
Peter
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?
Thanks,
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.
Regards,
Suvendu
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;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bi
PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for Solaris: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production
SQL>
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.
Sean
Session Memory info..
Mark Zuraski, December 14, 2006 - 12:06 am UTC
Oracle 9i (Release 2 on unix)..
Tom,
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..
Thanks.
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
Hi
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
USERNAME USED_UBLK USED_UREC STATUS
------------------------------ ---------- ---------- --------
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
USERNAME USED_UBLK USED_UREC STATUS
------------------------------ ---------- ---------- --------
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
USERNAME USED_UBLK USED_UREC STATUS
------------------------------ ---------- ---------- --------
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 ??
SELECT SID, SERIAL#, status, OSUSER, PROGRAM FROM V$SESSION where username='SOURCE_DATA_MART'
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
SELECT SID, SERIAL#, status, OSUSER, PROGRAM FROM V$SESSION where username='SOURCE_DATA_MART'
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
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 10.2.0.2 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?
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...
v$process
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?
Thanks,
Russell
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.
v$process
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:
SELECT DISTINCT "A1"."OWNER" FROM "ALL_OBJECTS" "A1" WHERE "A1"."OBJECT_TYPE"<>'SYNONYM'
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?
Thanks,
Dainee
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.
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.
sys@DB.WORLD>
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 AskTom.com.
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(9.2.0.1) 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.
Thanks
Pratik
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';
USED_UBLK
----------
42254
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
Concurrency
Network
Network
Idle
Network
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.
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 11.2.0.2.(Most 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
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).
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"
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.
Regards,
Rizwan
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
Tom,
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.