remidian.com

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  /