l_task VARCHAR2 (30) := 'task_del_dfupricepatric';
l_sql_stmt VARCHAR2 (32767);
l_try NUMBER;
l_status NUMBER;
BEGIN
BEGIN
DBMS_PARALLEL_EXECUTE.drop_task (l_task);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);
DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid (
task_name => l_task,
table_owner => 'SCOTT',
table_name => 'DFUPRICERIC',
by_row => FALSE,
chunk_size => 10000);
l_sql_stmt :=
'DELETE FROM scpomgr.dfupricepetric WHERE startdate < sysdate -671
and rowid BETWEEN :start_id AND :end_id';
DBMS_PARALLEL_EXECUTE.run_task (task_name => l_task,
sql_stmt => l_sql_stmt,
language_flag => DBMS_SQL.NATIVE,
parallel_level => 100);
COMMIT;
l_try := 0;
l_status := DBMS_PARALLEL_EXECUTE.task_status (l_task);
WHILE (l_try < 2 AND l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
LOOP
l_try := l_try + 1;
DBMS_PARALLEL_EXECUTE.resume_task (l_task);
l_status := DBMS_PARALLEL_EXECUTE.task_status (l_task);
END LOOP;
END;
/
No comments:
Post a Comment