Rick If you are waiting on rollback, this script (written by Steve Adams) will help you find out how long the rollback will take.
set serveroutput on set feedback off prompt prompt Looking for transactions that are rolling back ... prompt declare cursor tx is select s.username, t.xidusn, t.xidslot, t.xidsqn, t.used_ublk from sys.v_$transaction t, sys.v_$session s where t.used_ublk > 1 and s.saddr = t.ses_addr; user_name varchar2(30); xid_usn number; xid_slot number; xid_sqn number; used_ublk1 number; used_ublk2 number; begin open tx; loop fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1; exit when tx%notfound; if tx%rowcount = 1 then sys.dbms_lock.sleep(10); end if; select sum(used_ublk) into used_ublk2 from sys.v_$transaction where xidusn = xid_usn and xidslot = xid_slot and xidsqn = xid_sqn; if used_ublk2 < used_ublk1 then sys.dbms_output.put_line( user_name || '''s transaction ' || xid_usn || '.' || xid_slot || '.' || xid_sqn || ' will finish rolling back at approximately ' || to_char( sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24, 'HH24:MI:SS DD-MON-YYYY' ) ); end if; end loop; if user_name is null then sys.dbms_output.put_line('No transactions appear to be rolling back.'); end if; end; / Babu ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, February 25, 2002 3:13 PM > I created a procedure to remove up to 2.4 million records out of a 13+ million record table with bulk binds and forall. Figured that would be the fastest way. While doing this delete, other processes were accessing the table, but not the rows that were being deleted. Things went fine. > > Eventually, records that were being deleted were being updated, then the probelms started. The job that did the big delete was killed in favor of deleteing the records in smaller batches. However, whenever I try to delete or update some of the records that were affected, the process hangs, like it's waiting on a commit or rollback from a previous transaction. I have bounced the db and this is still occuring. > > Is there a way to check if the blocks are indeed waiting for a commit or rollback and provide that as needed? > > Thank you > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babu Nagarajan INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).