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 < 1000Then 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 ;
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 first published in April 2003