Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.