delete large number of records with intermediate commits

  • Published on Aug 14 2007
  • # 15,269
  • scripts

Have to delete a large number of records and running out of undo tablespace ‘ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDO’? Script below does a delete with intermediate commits ever ‘x’ records.

declare

  -- commit every 'i_commit' rows
  i_commit   pls_integer := 1000;

  -- row counter, 'i_rowcount' rows deleted
  i_rowcount pls_integer := 0;


begin

  -- define 'infinite' loop
  loop

    -- delete 'i_commit' rows
    delete from obj
    where rownum <= i_commit;

    i_rowcount := i_rowcount + sql%rowcount;

    -- now it's time to exit the loop
    if sql%rowcount = 0 then
       commit;
       exit;
    end if;

    commit;

  end loop;


  -- feedback
  dbms_output.put_line( trim( to_char( i_rowcount, '999999999999')) ||  ' rows deleted') ;

end;
/

Example

[REMI@DB01.REMIDIAN.COM]
SQL> create table obj as select * from dba_objects;

Table created.

[REMI@DB01.REMIDIAN.COM]
SQL> -- get current commit count for this session
[REMI@DB01.REMIDIAN.COM]
SQL> select  s.*, n.name
  2  from  v$mystat s left outer join v$statname n
  3  on  s.statistic# = n.statistic#
  4  where   n.name = 'user commits'
  5  /

    SID STATISTIC#      VALUE NAME
------- ---------- ---------- ----------------------------------------------------------
    130          4          2 user commits

[REMI@DB01.REMIDIAN.COM]
SQL> declare
  2  
  3    -- commit every 'i_commit' rows
  4    i_commit   pls_integer := 1000;
  5  
  6    -- row counter, 'i_rowcount' rows deleted
  7    i_rowcount pls_integer := 0;
  8  
  9  begin
 10  
 11    -- define 'infinite' loop
 12    loop
 13  
 14      -- delete 'i_commit' rows
 15      delete from obj
 16      where rownum <= i_commit;
 17  
 18      i_rowcount := i_rowcount + sql%rowcount;
 19  
 20      -- now it's time to exit the loop
 21      if sql%rowcount = 0 then
 22         commit;
 23         exit;
 24      end if;
 25  
 26      commit;
 27  
 28    end loop;
 29  
 30    -- feedback
 31    dbms_output.put_line( trim( to_char( i_rowcount, '999999999999')) ||  ' rows deleted') ;
 32  end;
 33  /
61963 rows deleted

PL/SQL procedure successfully completed.

[REMI@DB01.REMIDIAN.COM]
SQL> -- get current commit count for this session
[REMI@DB01.REMIDIAN.COM]
SQL> select  s.*, n.name
  2  from  v$mystat s left outer join v$statname n
  3  on  s.statistic# = n.statistic#
  4  where   n.name = 'user commits'
  5  /

    SID STATISTIC#      VALUE NAME
------- ---------- ---------- ----------------------------------------------------------
    130          4         64 user commits

[REMI@DB01.REMIDIAN.COM]
SQL> 

Of course the latter statement would be used on v$sesstat to monitor the progress of the ‘delete loop’.

Monitor user commits
[REMI@DB01.REMIDIAN.COM]
SQL> select  s.*, n.name
  2  from  v$sesstat s left outer join v$statname n
  3  on  s.statistic# = n.statistic#
  4  where   n.name = 'user commits'
  5  and s.sid = <sid>
  6  /
 

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>