Delete statement causing UNDO tablespace to reach limit.

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?
xbox360dpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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;
/
0
sdstuberCommented:
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;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
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
0
Geert GOracle dbaCommented:
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
0
slightwv (䄆 Netminder) 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...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.