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.

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.

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...
:p

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?

Quote:

---

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

Quote:

---

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.

Quote:

---

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.

Quote:

---

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.

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

Thanks all for your feedback.

Hrishy - As of now, I am unaware about mining AWR table. I will read the docs and would use it next time if I fall into this situation.

i doubt its got to be some background process, check program column in v$session. Or it could also be a slave process that is spawned when u run a query in parallel mode. You have to kill the session to free the blocked object, also check v$lock, v$locked_object etc.

HTH

Hi

Take the sql_id you have and get teh value of the sql text from the table
DBA_HIST_SQLTEXT

DBA_HIST_SQLTEXT stores the sql_id and the sql_text as clob.

regards
Hrishy

Check this document.

http://www.filibeto.org/sun/lib/nons..._txnman007.htm

PS: I've personally never tried this.

I have tried solution in the link posted by malay_biswal, but it will only work on distributed transactions.
:cool:

Hrishy--- That's what, the sql_id is showing NULL for blocker session.

Why don't you post the v$session contents for that user, so that we know what this session is for?

gtcol-- Sorry! Actually yesterday, I killed that session at customer site in off production hours. I did it in off production hours because I wasn't sure of the cascading effects on the application.

But I checked the Program column, it was showing the name of application "QuarkDMS.exe". FYI, Everything is hosted on Windows 2003 Server.

Quote:

---

But I checked the Program column, it was showing the name of application "QuarkDMS.exe".

---

Seems like a user session. It could also be a slip-up in your application logic itself if you're sure that this will pile up again and killing it requires application restart. Next time, you can see in detail like program, osuser, sql_id, ROW_WAIT_OBJ#, CLIENT_IDENTIFIER, event, WAIT_CLASS etc in v$session. Also, see v$lock, v$ocked_object etc.

HTH