rollback another user's transaction

[mahajanakhil198]

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.

[mahajanakhil198]

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.

[LKBrwn_DBA]

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

[mahajanakhil198]

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?

[gandolf989]

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

[mahajanakhil198]

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.

[gandolf989]

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.

[mahajanakhil198]

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.

[davey23uk]

either way, you cannot rollback a specific users transaction - you would need to kill it

fix your application to deal with it

[hrishy]

Hi

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

DBA_HIST_SQLTEXT

regards
Hrishy