rollback another user's transaction
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: rollback another user's transaction

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    rollback another user's transaction

    Is it possible to rollback another user's last uncommited transaction. It has blocked other sessions. I only know the sid and serial# of blocking session. I dont want to kill this session as it will impact the apllication and would require application restart. For the time being, I am able to live with this. But soon the issues will crop up. It's kind of urgent requirement. I googled and found some rollback work force '25.32.87' but couldn't find its meaning. How will I use it to rollback the transaction of session I am wiling to rollback? Also dba_2pc_pending has no rows.

    dba_blockers shows one row as discussed above. dba_waiters has 2 rows.
    lucky

  2. #2
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    I also traced the blocking session but it is not generating any trace file as it seems to be stopped at a point and doesn't execute any SQL. So, no trace files are generated.
    lucky

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool Tough luck...

    Tough luck...You will need to kill that session.

    Besides if it's not doing anything why be concerned about any effect on the application? The session is already DEAD but does not know it -- KILL, KILL, KILL...
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Can I find the SQL statement because of which the other sessions are waiting. I tried using SQL_ID from v$session to find the offending SQL statement but SQL_ID column value for blocking session is NULL. Had it not been NULL, I could have mapped it to SQL_ID of v$SQL.

    I can't simply kill this session because with DB session, more than application sessions are linked. Actually, the application product has been designed in such a way. Even If I know the OS user, I can't trace his session as OSUSER column shows the same value for all rows in v$session view.

    Is there any other way to find the SQL because of which other sessions are blocked?
    Last edited by mahajanakhil198; 12-18-2009 at 01:32 PM.
    lucky

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Quote Originally Posted by mahajanakhil198 View Post
    Is there any other way to find the SQL because of which other sessions are blocked?
    SYS@outreach AS SYSDBA> select * from dba_blockers;

    no rows selected

    Elapsed: 00:00:00.09
    SYS@outreach AS SYSDBA> select * from dba_Waiters;

    no rows selected

    Elapsed: 00:00:00.01
    this space intentionally left blank

  6. #6
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by gandolf989 View Post
    SYS@outreach AS SYSDBA> select * from dba_blockers;

    no rows selected

    Elapsed: 00:00:00.09
    SYS@outreach AS SYSDBA> select * from dba_Waiters;

    no rows selected

    Elapsed: 00:00:00.01
    seem

    seems u haven't got it exactly. i want to find the blocking SQL, not the SQL to find the blocked sessions.
    lucky

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Quote Originally Posted by mahajanakhil198 View Post
    seem
    seems u haven't got it exactly. i want to find the blocking SQL, not the SQL to find the blocked sessions.
    It gives you the sid of the blocking session which you can use to query v$session, which gives you the current sql id of what they are running which you can use to find the blocking sql.
    this space intentionally left blank

  8. #8
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by gandolf989 View Post
    It gives you the sid of the blocking session which you can use to query v$session, which gives you the current sql id of what they are running which you can use to find the blocking sql.
    Man, that's what I posted. Why is it(SQL_ID column of v$session) showing NULL value?

    That's why, wanted to know any alternative way to find blocking SQL.
    lucky

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    either way, you cannot rollback a specific users transaction - you would need to kill it

    fix your application to deal with it

  10. #10
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    If you are on 10g and have AWR liscence you can mine the AWR table to get this

    DBA_HIST_SQLTEXT

    regards
    Hrishy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width