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