-
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
-
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
-
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
-
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
-
Originally Posted by mahajanakhil198
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
-
Originally Posted by gandolf989
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
-
Originally Posted by mahajanakhil198
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.
-
Originally Posted by gandolf989
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
-
either way, you cannot rollback a specific users transaction - you would need to kill it
fix your application to deal with it
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|