1 2 Previous Next 23 Replies Latest reply: May 3, 2012 5:01 AM by user522961 Go to original post RSS
      • 15. Re: DELETE is running since
        user522961
        Hi Karan,
        would you please considere this :

        SQL> select event,wait_Class, seconds_in_wait,state from v$session_wait where sid

        EVENT
        ----------------------------------------------------------------
        WAIT_CLASS SECONDS_IN_WAIT
        ---------------------------------------------------------------- ---------------
        STATE
        -------------------
        enq: TX - row lock contention
        Application 12624
        WAITING

        Thank you.
        • 16. Re: DELETE is running since
          amardeep.sidhu
          SQL> select event,wait_Class, seconds_in_wait,state from v$session_wait where sid

          EVENT
          ----------------------------------------------------------------
          WAIT_CLASS SECONDS_IN_WAIT
          ---------------------------------------------------------------- ---------------
          STATE
          -------------------
          enq: TX - row lock contention
          Application 12624
          WAITING
          You aren't doing anything. Just waiting on a TX row lock. The rows you want to do DML on are already held by someone else. Check select sid,blocking_session from v$session where sid=<sid> and see who is blocking you. Then see what that session is doing.
          • 17. Re: DELETE is running since
            Karan
            You see ? Thats what i told you to do and there you have the answer... And Amardeep has already given you a perfect answer for this..

            Regards
            Karan
            • 18. Re: DELETE is running since
              user522961
              OK, Thanks.
              It is blocked by another session which is inactive :
              SQL> select sid,blocking_session from v$session where sid=99;
              
                     SID BLOCKING_SESSION
              ---------- ----------------
                      99              105
              
              SQL> select osuser, sid,status from v$session where program like '%TOAD%';
              
              OSUSER                                SID STATUS
              ------------------------------ ---------- --------
              USER1         99 ACTIVE
              USER2        105 INACTIVE
              Then what to do with session 105 that is inactive ?
              • 19. Re: DELETE is running since
                amardeep.sidhu
                select username,event,status,state,last_call_et,module,program columns also. If it is inactive since long time and is doing nothing, you may want to kill it (after confirming that it is a user session).

                It is indeed a user session being run from Toad.

                Edited by: amardeep.sidhu on May 3, 2012 1:03 PM
                • 20. Re: DELETE is running since
                  user522961
                  Ok,
                  SQL> select username,event,status,state,last_call_et,module from v$session where SID=105;
                  
                  USERNAME
                  ------------------------------
                  EVENT                                                            STATUS
                  ---------------------------------------------------------------- --------
                  STATE               LAST_CALL_ET
                  ------------------- ------------
                  MODULE
                  ------------------------------------------------
                  SYSTEM
                  SQL*Net message from client                                      INACTIVE
                  WAITING                    20007
                  TOAD.exe
                  Do you suggest :
                  alter system kill session '105,nnn';

                  ??
                  Regards.
                  • 21. Re: DELETE is running since
                    user522961
                    Ok, I killed session for SID=105. And now no blocking session :
                     
                    SQL> select sid,blocking_session from v$session where sid=99; 
                    
                           SID BLOCKING_SESSION 
                    ---------- ---------------- 
                            99 
                    There should be a column in v$session showing that other session (SID=99) is doing its job. Do you know this column ?

                    Thank you.
                    • 22. Re: DELETE is running since
                      amardeep.sidhu
                      Same SQL

                      select username,event,status,state,last_call_et,module from v$session where SID=<sid>;
                      • 23. Re: DELETE is running since
                        user522961
                        thanks to all.
                        Delete of about 120000 is now finished successfully.

                        Realy thanks to all.
                        1 2 Previous Next