一、环境准备
数据库中创建存放日志的路径:
create directory LOG_DIR as '/home/oracle/clean_log';
grant read,write on directory LOG_DIR to GAPS41;
二、创建日志存储过程
create or replace procedure gaps41.PUT_INTO_FILE(table_name in varchar2,
l_delete in number,
column_name in varchar2,
min_data in varchar2,
s_time in varchar2,
e_time in varchar2,
flag in varchar2) is
file_handle utl_file.file_type;
file_name varchar2(20);
begin
file_name := to_char(sysdate, 'yyyymmdd') || '.log';
file_handle := utl_file.fopen('LOG_DIR', file_name, 'a');
if flag = '0' then
utl_file.put_line(file_handle, '--------------------------------- START TIME :'|| s_time || ' ---------------------------------');
utl_file.put_line(file_handle,'Delete table GAPS41.' || table_name || ' by column ' ||column_name || ' :');
utl_file.put_line(file_handle, 'Totally ' || l_delete || ' records deleted!');
utl_file.put_line(file_handle, 'The column min number: ' || min_data );
utl_file.put_line(file_handle, '--------------------------------- FINISH TIME:' || e_time || ' ---------------------------------');
utl_file.put_line(file_handle, '');
utl_file.put_line(file_handle, '');
utl_file.put_line(file_handle, '');
utl_file.put_line(file_handle, '');
else if flag = '1' then
utl_file.put_line(file_handle, '--------------------------------- START TIME :'|| s_time || ' ---------------------------------');
utl_file.put_line(file_handle,'');
utl_file.put_line(file_handle,'Truncate table GAPS41.' || table_name || ' Complated !');
utl_file.put_line(file_handle,'');
utl_file.put_line(file_handle, '--------------------------------- FINISH TIME:' || e_time || ' ---------------------------------');
utl_file.put_line(file_handle, '');
utl_file.put_line(file_handle, '');
utl_file.put_line(file_handle, '');
utl_file.put_line(file_handle, '');
end if;
end if;
utl_file.fflush(file_handle);
utl_file.fclose(file_handle);
end;
/
三、创建批量删除的存储过程
CREATE OR REPLACE PROCEDURE GAPS41.DEL_TAB_BY_DATES(del_date in varchar2,
tname in varchar2,
column_name in varchar2,
date_format in varchar2,
commit_l in number) AS
BEGIN
DECLARE
dd varchar2(30);
tn varchar2(30);
cn varchar2(30);
min_d varchar2(300);
m_d varchar2(20);
dtf varchar2(30);
c_rowid varchar2(300);
cu_sql clob;
d_sql clob;
s_time varchar2(30);
e_time varchar2(30);
l_delete number := 0;
type refcursor is ref cursor;
c1 refcursor;
C_COMMIT CONSTANT PLS_INTEGER := commit_l;
BEGIN
dd := del_date;
tn := tname;
cn := column_name;
dtf := date_format;
s_time := to_char(sysdate, 'yyyy-mm-dd hh24:mm:ss');
cu_sql := 'SELECT ROWID ROW_ID FROM GAPS41.' || tn || ' where ' || cn || ' <to_char(sysdate-' || dd || ','''||dtf||''')';
--dbms_output.put_line(cu_sql);
open c1 for cu_sql;
LOOP
fetch c1
into c_rowid;
exit when c1%NOTFOUND;
d_sql := 'DELETE FROM GAPS41.' || tn || ' WHERE ROWID = ''' ||c_rowid || '''';
--dbms_output.put_line(d_sql);
execute immediate d_sql;
IF (MOD(C1%ROWCOUNT, C_COMMIT) = 0) THEN
COMMIT;
DBMS_LOCK.SLEEP(3);
END IF;
END LOOP;
l_delete := l_delete + C1%ROWCOUNT;
close c1;
COMMIT;
min_d :='select min('||cn ||') from GAPS41.'||tn;
execute immediate min_d into m_d;
e_time := to_char(sysdate, 'yyyy-mm-dd hh24:mm:ss');
GAPS41.PUT_INTO_FILE(tn, l_delete,cn,m_d,s_time, e_time,0);
END;
END;
/
四、创建执行删除的存储过程
CREATE OR REPLACE PROCEDURE GAPS41.DO_CLEAN(days in varchar2,
lines in number) is
BEGIN
DECLARE
d varchar2(20);
l number;
t clob;
cursor t1 is
select distinct table_name tn,
decode(table_name,
'IBPS_COMM_RECV',
'SENDDATE',
'IBPS_COMM_SEND',
'SENDDATE',
'IBPS_PAY_RELAT_REG',
'PLTDATE',
'IBPS_PAY_TRANS_REG',
'WKDT',
'IBPS_TXNET',
'TXNETGDT',
'UPSS_COREBKSERIAL',
'PLTDATE') cn
from dba_tab_columns
where TABLE_NAME IN ('IBPS_CHKBUSI_TMP_REG',
'IBPS_COMM_RECV',
'IBPS_COMM_SEND',
'IBPS_CORECHK_TEMP',
'IBPS_PAY_RELAT_REG',
'IBPS_PAY_TRANS_REG',
'IBPS_TXNET',
'UPSS_COREBKSERIAL')
AND OWNER = 'GAPS41';
BEGIN
d := days;
l := lines;
for t_name in t1 loop
if t_name.tn IN ('IBPS_CHKBUSI_TMP_REG', 'IBPS_CORECHK_TEMP') then
t := 'truncate table GAPS41.' || t_name.tn;
execute immediate t;
--dbms_output.put_line(t);
GAPS41.PUT_INTO_FILE(t_name.tn,
'0',
'',
'0',
to_char(sysdate, 'yyyy-mm-dd hh24:mm:ss'),
to_char(sysdate, 'yyyy-mm-dd hh24:mm:ss'),'1');
else
if t_name.tn in ('IBPS_PAY_TRANS_REG', 'IBPS_TXNET') then
GAPS41.DEL_TAB_BY_DATES(d, t_name.tn, t_name.cn, 'yyyy-mm-dd', l);
else
GAPS41.DEL_TAB_BY_DATES(d, t_name.tn, t_name.cn, 'yyyymmdd', l);
end if;
end if;
end loop;
END;
END;
/
五、按清理协议清楚历史数据
5.1 调用现有的存储过程清楚数据
--使用DO_CLEAN
declare i number;
begin
for i in reverse 369 .. 360 loop
GAPS41.DO_CLEAN(i,'10000');
end loop;
end;
/
--使用DEL_TAB_BY_DATES
exec GAPS41.DEL_TAB_BY_DATES(180,'IBPS_PAY_TRANS_REG','WKDT','yyyy-mm-dd','10000');
5.2 使用DBMS_PARALLEL清楚历史数据
CREATE OR REPLACE PROCEDURE Parallel_exec_delete(table_name in varchar2,
column_name in varchar2,
day_num in number,
date_type in varchar2,
parallel_level in number,
chunk_size in number) is
begin
declare
tn varchar2(50);
cn varchar2(10);
dn number;
dtp varchar2(15);
vc_task varchar2(100);
vc_sql varchar2(2000);
n_try number;
n_status number;
p_level number;
ck_size number;
file_handle utl_file.file_type;
file_name varchar2(50);
begin
tn := table_name;
cn := column_name;
dn := day_num;
dtp := date_type;
p_level := parallel_level;
ck_size := chunk_size;
file_name := to_char(sysdate, 'yyyymmdd') || '_parallel_delete.log';
file_handle := utl_file.fopen('LOG_DIR', file_name, 'a');
utl_file.put_line(file_handle,'Begin task =====================');
utl_file.put_line(file_handle,'Table : '||tn);
utl_file.put_line(file_handle,'Column : '||cn);
utl_file.put_line(file_handle,'Time : '||to_char(sysdate, 'hh24:mi:dd'));
utl_file.fflush(file_handle);
--utl_file.fclose(file_handle);
--Define the Task
vc_task := tn||'_'||cn||'_DELETE';
dbms_parallel_execute.create_task(task_name => vc_task);
--Define the Spilt
dbms_parallel_execute.CREATE_CHUNKS_BY_ROWID(task_name => vc_task,
table_owner => 'GAPS41',
table_name => tn,
by_row => true,
chunk_size => ck_size);
vc_sql := 'delete /*+ ROWID(dda) */ from GAPS41.'||tn||' where rowid between :start_id and :end_id AND '||cn||' <to_char(sysdate-' || dn || ','''||dtp||''')';
--Run the task
dbms_parallel_execute.run_task(task_name => vc_task,
sql_stmt => vc_sql,
language_flag => dbms_sql.native,
parallel_level => p_level);
utl_file.put_line(file_handle,'===================== Task Finished ' ||to_char(sysdate, 'hh24:mi:dd'));
utl_file.put_line(file_handle,'');
utl_file.fflush(file_handle);
utl_file.fclose(file_handle);
--Controller
n_try := 0;
n_status := dbms_parallel_execute.task_status(task_name => vc_task);
while (n_try<2 and n_status != dbms_parallel_execute.FINISHED) loop
dbms_parallel_execute.resume_task(task_name => vc_task);
n_status := dbms_parallel_execute.task_status(task_name => vc_task);
end loop;
--Deal with Result
dbms_parallel_execute.drop_task(task_name => vc_task);
end;
end;
/
--执行清除
begin
Parallel_exec_delete('IBPS_PAY_TRANS_REG','WKDT',90,'yyyy-mm-dd',20,10000);
end;
/
查看parallel执行状态
select task_name, chunk_type, JOB_PREFIX from user_parallel_execute_tasks where task_name like '%_DELETE';
select status, count(*) from user_parallel_execute_chunks WHERE task_name like '%_DELETE' group by status;
select chunk_id, task_name, status, start_rowid, end_rowid from user_parallel_execute_chunks where rownum<10;
select sum(bytes)/1024/1024 from dba_undo_extents where status='ACTIVE';
select owner, job_name, JOB_ACTION, SCHEDULE_TYPE, state, last_start_date from dba_scheduler_jobs where job_name like 'TASK$_%';