lock mode is 6 Thanks Mayen Joe-L.Torres@xxxxxxx Nov 12 2008 02:10 PM To Mayen Shah/ITS/Lazard@Lazard NYC cc oracle-l@xxxxxxxxxxxxx Subject RE: Killed session so when you query v$lock under lmode what is it ? 0 = no locks 4=shared mode 6=exclusive lock From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mayen.Shah@xxxxxxxxxx Sent: Wednesday, November 12, 2008 2:00 PM To: Goulet, Richard Cc: oracle-l@xxxxxxxxxxxxx Subject: RE: Killed session Thank you Dick, I really appreciate help I am getting from you and other members. I already checked v$lock. Only lock is on table held by killed session. Thanks Mayen "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx> Nov 12 2008 01:49 PM To Mayen Shah/ITS/Lazard@Lazard NYC cc Subject RE: Killed session Then I'd say your stuck with having to bounce the database. You might want to look around & see if there is a blocking lock. Look for the blocking column in V$LOCK. Dick Goulet Senior Oracle DBA PAREXEL International 978.313.3426 information transmitted in this communication is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please destroy any copies, contact the sender and delete the material from any computer. From: Mayen.Shah@xxxxxxxxxx [mailto:Mayen.Shah@xxxxxxxxxx] Sent: Wednesday, November 12, 2008 1:47 PM To: Goulet, Richard Cc: oracle-l@xxxxxxxxxxxxx Subject: RE: Killed session No distributed transaction involved. Thanks Mayen "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx> Nov 12 2008 12:41 PM To Mayen Shah/ITS/Lazard@Lazard NYC, oracle-l@xxxxxxxxxxxxx cc Subject RE: Killed session First thing I'd look at is if a distributed transaction is in process. Look in dba_2pc_pending as the session may be looking for a foreign database that is where this session started. Otherwise you may have one of those instances where a database restart is the only way you'll clear the locks. Dick Goulet Senior Oracle DBA PAREXEL International 978.313.3426 information transmitted in this communication is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please destroy any copies, contact the sender and delete the material from any computer. From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mayen.Shah@xxxxxxxxxx Sent: Wednesday, November 12, 2008 9:24 AM To: oracle-l@xxxxxxxxxxxxx Subject: Killed session Hi Listers, Oracle 9.2.0.8 on Solaris 9 In one of my production database session was long running. Session was killed last night. Since then session is marked as KILLED in v$session. This killed session is holding locks on few tables and causing further problems. USED_UBLK in v$transaction is constant at 287 since late last night. I identified unix process and killed at os level two hours ago, still session would not go away. (os process is gone already) What else should I be looking at? How do I get rid of KILLED session? I do not have luxury to recycle database. Your help is greatly appreciated. Thanks Mayen Visit our website at http://www.ubs.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mails are not encrypted and cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. This message is provided for informational purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments.