Batch clear a table of data, TB-level data.

There is a requirement to clean up 8TB of data and keep only one month's data. The existing data volume needs to be cleaned up from 2010 to the end of 2018, so a general cleaning script has been written. This table is a large table containing BLOBs without partitions. It consists of three columns, file_no,BLOB, and create_time column, where file_no is the primary key and create_time is non-empty.

The script is as follows:

create or replace procedure proc_batch_delete(PI_table_name in varchar2, -- table name which will delete data

PI_where_condition in varchar2, -- delete sql condition

PI_thread_count in varchar2, -- thread number of the sql which use parallel hint

PI_commit_count in varchar2) is -- per delete count and commit count

v_delete integer;

v_sql varchar2(2000);

begin

dbms_output.put_line('Delete table is ' || PI_table_name || ';');

dbms_output.put_line('Where condition is ' || PI_where_condition || ';');

dbms_output.put_line('Number of threads is ' || to_char(PI_thread_count) || ';');

dbms_output.put_line('Commit count is ' || to_char(PI_commit_count) || ';');

dbms_output.put_line('Now start to batch delete. Timestamp is ' || to_char(sysdate, 'yyyy-mm-dd hh34:mi:ss'));

v_delete := 0;

execute immediate 'alter session enable parallel dml';

v_sql := 'delete /+ parallel('||PI_table_name || ' ' || PI_thread_count ||') / from '

|| PI_table_name ||' where '|| PI_where_condition || 'and rownum