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 >
Requires Free Membership to View
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 first published in April 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation