Oracle Library Delete removes tens of thousands of common heap table data

Requirements: Oracle Database Delete Deletes the normal heap table thousands history.

Effects of direct deletion:

1. An issue that could result in a final deletion failure due to insufficient undo table space;

2. May cause the Undo table space to be overused, affecting the normal operation of other users.

Improvement Scenario: Every 1k row is deleted and submitted once. (so that a big thing is broken up into a number of small things)

Note: To remove all records before 2014 as an example, please modify them according to your actual situation to prevent accidental operation.

Method 1

Declare     cursor [Del_cursor]  is SelectA.*, A.rowid row_id from [table_name]AOrder  byA.rowid;begin      for V_cusor inch [Del_cursor]Loopif V_cusor. [Time_stamp] <To_date ('2014-01-01','YYYY-MM-DD') Then               Delete  from [table_name] whererowID= V_cusor. row_id; End if; ifMoD[Del_cursor]%RowCount, +)=0  Then               Commit; End if; EndLoop; Commit;End;

Description of variables in Method 1:

[del_cursor] CURSOR name [table_name] The table name of the data you want to delete [Time_stamp] the Time field name of the table you are using as the filter condition

Method 2

DeclareMaxRows Number default  +;d elete_ct Number default 0;beginSelect Count(1)/MaxRows intoDelete_ct from [table_name] where[Time_stamp]<to_date ('2014-01-01','yyyy-mm-dd '); forIinch 1.. TRUNC (DELETE_CT)+1LoopDelete [table_name] where[Time_stamp]< to_date ('2014-01-01','yyyy-mm-dd ')        andRowNum<=MaxRows;Commit;EndLoop;End;

Description of variables in Method 2:

[table_name] The table name of the data you want to delete [Time_stamp] the Time field name of the table you are using as the filter criteria

Note

The core of the two approaches is to split a big thing into a number of small things, regardless of which method, it is recommended to first Test in the corresponding test environment before considering whether it can be used in the actual production.

By the way, such a large table should be considered whether the composition of the table can be modified.

Oracle Library Delete removes tens of thousands of common heap table data