A delete that runs "forever" is either

1) blocked by some other session
2) taking a long time to *locate* the rows
3) taking a long time to *delete* the rows

or some combination of all three.

So, you can do this:

select sid, saddr from v$session where sid = sys_context('USERENV','SID');

to know your session ID, and then run your delete.


Whilst it is running, you can (from another session), so

select status from v$session where sid = ...

- if its "BLOCKED", then its "1", and you can use BLOCKING_SESSION_ID to isolate who is blocking you.

- if its active, then we know is doing some work. So now use SADDR to query v$transaction

select used_urec from v$transaction where ses_addr = ...

if you get no rows, then we havent *found* the rows yet...so its (2)

if you get a row back, then this number is how many 'things' we've touched so far. (This is *not* the number of rows deleted, because we need to delete rows and index entries..so its going to be *more*). But if you get a value back, then its (3).

Hope this helps.