Delete statement causing UNDO tablespace to reach limit.

xbox360dp
xbox360dp used Ask the Experts™
on
Gurus,

I need to delete orphan data from a table with 682 million records.

When execute the following delete statement I receive "ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2':

DELETE FROM afl WHERE NOT EXISTS
      ( SELECT 1 FROM fty WHERE ftykeyi = afl.aflftykeyi );
COMMIT;

I know that I can continue to add datafiles. Is there a better way to write the statement or any other suggestions?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Delete inside a loop that commits every XXX rows.

something like this (untested, just typed in):

declare
    rows_deleted number;
begin

loop
     DELETE FROM afl WHERE NOT EXISTS
       ( SELECT 1 FROM fty WHERE ftykeyi = afl.aflftykeyi ) and rownum<100000;
     rows_deleted := sql%rowcount;
     commit;
exit when rows_deleted>0;
end loop;
end;
/
Most Valuable Expert 2011
Top Expert 2012
Commented:
If all of the deletes must happen in a single transaction, then no, you must add more undo space.

If you can break the deletes into multiple transactions then try something like this...

If 100000 rows is too big then make it smaller,  try to pick as large of a number as your system can support.

BEGIN
    LOOP
        DELETE FROM afl
              WHERE NOT EXISTS
                        (SELECT 1
                           FROM fty
                          WHERE ftykeyi = afl.aflftykeyi)
                AND ROWNUM < 100000;

        EXIT WHEN SQL%ROWCOUNT = 0;
        COMMIT;
    END LOOP;

    COMMIT;
END;
Most Valuable Expert 2011
Top Expert 2012

Commented:
ha - same idea at the same time

but...

you don't want your delete count > 0,  if you do that then the deletes will stop after one batch
Geert GOracle dba
Top Expert 2009

Commented:
off course that will happen

how many records would remain after the delete

it might be a lot faster to
create table tmp_table as select * from yourtable
where conditioning = remaining records

copy constraints and triggers

drop the old table

and rename the new table to the old table
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>you don't want your delete count > 0,

Yes, that was a typo.  It should be "=0".

I was thinking that but my fingers typed something else...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial