blocking session

Tags:
Oracle
hi i am new to oracle and i have a question that if a user session that is blocking other sessions and processes from accessing the oracle database so how we can overcome this problem?Could you please explain with example by using a sql command if you can. Would really appreciate. Regards Obaid
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi obaidiqbal:
sorry for me not understanding your question so clearly.
But if there is a session that is no response or done some thing bad to other resource , the best method to stop it is killing it.
You can logging oracle from other Sqlplus session with alter system privilege .
using sql like that
*********************************************
select sid,serial# from
v$session
where
machine=’bad session’s machine name’
*********************************************
There are lots of filed in v$session, you can use other conditions in the where block to identify the bad session.
using the information ( sid, serial#) which is got from that sql, you can kill the bad session by using sql like that.
*********************************************
alter system kill session ‘3,8’;
*********************************************
3 is the value of sid
8 is the value of serial#

Regards
guodong

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Guodong
    Hi obaidiqbal: sorry for me not understanding your question so clearly. But if there is a session that is no response or done some thing bad to other resource , the best method to stop it is killing it. You can logging oracle from other Sqlplus session with alter system privilege . using sql like that ********************************************* select sid,serial# from v$session where machine='bad session's machine name' ********************************************* There are lots of filed in v$session, you can use other conditions in the where block to identify the bad session. using the information ( sid, serial#) which is got from that sql, you can kill the bad session by using sql like that. ********************************************* alter system kill session '3,8'; ********************************************* 3 is the value of sid 8 is the value of serial# Regards guodong
    0 pointsBadges:
    report
  • BCofHP
    A blocking session is one that is holding a lock on a resource (a row in a table) that is required by another session. This is different from a deadly embrace! There are (typically) two ways to handle this. First, get the session to complete normally-- e.g., complete the transaction or log off. Second, you can kill the session. The easiest (and most secure!) method of doing this is through OEM or TOAD or your favorite clone. Find the blocking session, review who/what it is and what it thinks it's doing. If you can't get the session to end normally, kill it. /Bill
    0 pointsBadges:
    report
  • DBRelated
    If you would like to know which session is responsible in a hierarchy manner you can run the script file utllockt located at /rdbms/admin/ folder.Pls go thru the script file and execute it which helps in identifying the parent session holding a lock on the table/view later if u kill the session by getting the serial# from v$session the subsequent sessions are released.Hope this will help u a lot.
    0 pointsBadges:
    report
  • blocking session (Q/A) | Seek The Sun Slowly
    [...] Address: https://itknowledgeexchange.techtarget.com/itanswers/blocking-session/ (0) Comments Read [...]
    0 pointsBadges:
    report
  • blocking session (Q/A) | Seek The Sun Slowly
    [...] Original Address: https://itknowledgeexchange.techtarget.com/itanswers/blocking-session/ [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: