Problem solve Get help with specific problems with your technologies, process and projects.

Avoid 'cannot extend rollback segment' error when deleting rows

Here is how delete as many rows as you like without having to worry about RBS size.

You can delete as many rows as you like without having to worry about RBS size. One method is very low-tech:

Delete from tbl_x where rownum < 1000
Then run it as many times as you need to delete all the rows.

The other method is more sophisticated and flexible. Build a cursor to select the rows you want to delete and then in an anonymous PL/SQL block, loop through the cursor, and delete one row at a time. Keep track of the number of rows processed, and after a predetermined limit, commit the records deleted to that point. (I believe that the best performance can be obtained if you retrieve the row_id in the cursor and sort by row_id. This allows you to do a full table scan and only touch a block once, during the execution of the program.)

The program below was modified to remove sensitive information, but the basic structure is intact. I accidentally removed the code that checks for what mode you are running in (update/read-only) but you should be able to figure it out. Since I hate getting code that doesn't work, I tested it after the mods, and it still runs on Oracle 8.1.7.

-- turn on printing
set serveroutput on size 999999;

-- display start/end time + instance name
Select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') "Start Date / Time",
       substr(vi.host_name,1,15) "Host", vi.instance_name "Instance",
       vd.name "Dbname",substr(User,1,12) "User",
       vd.created "Instance Created", chr(13), chr(10)from Dual, V$Database vd, v$instance vi ;


-- The real work is done here
declare
        tc_num       number(6)    := 0;  -- counter for # of recs read
        COMMIT_COUNT number(6)    := 0;  -- count # of recs that have not yet been committed

        vc_Processing_Mode  varchar2(9) := 'READ-ONLY';
        vc_Commit_Mode      varchar2(9) := 'NO COMMIT';
        vc_var_out   varchar2(41) := 'Pr_PUB_XrefLog Delete Program #1';

        cursor csr_TD is
                select
                       Division,
                       rowid
                  from table_1

                  where division in ('ABc', 'Def')

                 order by   rowid,
                      Division
                                         ;

begin

        dbms_output.put_line(vc_var_out||' Beginning');
        dbms_output.put_line('Running in '||vc_processing_Mode||'with '||vc_commit_mode||'s');

        For csr_TD_Rec in csr_TD    Loop

            tc_num := tc_num + 1 ;
            COMMIT_COUNT := COMMIT_COUNT + 1 ;


            Delete from table_1    T

                  where   T.rowid = csr_td_rec.rowid ;

            IF COMMIT_COUNT > 1000 THEN
                 COMMIT;
                 COMMIT_COUNT := 1;
                 dbms_output.put_line('COMMIT AT..'|| tc_num);
            END IF;

        End Loop;

        IF vc_COMMIT_Mode = 'COMMIT'
                                      THEN
           commit;

        END IF;


        dbms_output.put_line(vc_var_out||' Finished');

        dbms_output.put_line('Records processed........'|| to_char(tc_num,'999999') );


end;


--Select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') "END   Date / Time",
--       substr(vi.host_name,1,15) "Host", vi.instance_name "Instance",
--       vd.name "Dbname",substr(User,1,12) "User",
--       vd.created "Instance Created", chr(13), chr(10)from Dual, V$Database vd, v$instance vi ;

Reader Feedback

Jim L. writes: Good tip. The script included in this tip, should always be ordered by rowid to reduce ORA-01555 (snapshot too old errors). It could still conceivably fall over with the dreaded ORA-01555, if there are any migrated rows or indexes involved. To ensure this does not happen then I would close and reopen the cursor after every commit.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was last published in April 2003

Dig Deeper on Oracle error messages

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close