isitdevops.com/databases

set serveroutput on

DECLARE

l_sql_stmt VARCHAR2(1000);

l_try NUMBER;

l_status NUMBER;

BEGIN

— Create the TASK

DBMS_PARALLEL_EXECUTE.CREATE_TASK (‘blds_convert’);

— Chunk the table by ROWID

DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(‘blds_convert’,’SAPISU’,’SWW_CONTOB’, true, 5);

— Execute the DML in parallel

l_sql_stmt := ‘update /*+ ROWID (dda) */ SAPISU.SWW_CONTOB e SET e.LOGSYS = ”AAA-100” WHERE e.LOGSYS = ”JBA-100” and rowid BETWEEN :start_id AND :end_id’;

— l_sql_stmt := ‘update /*+ ROWID (dda) */ SWW_CONTOB e SET e.LOGSYS = “AAA-100″‘;

dbms_output.put_line(‘SQL: ‘ || l_sql_stmt );

DBMS_PARALLEL_EXECUTE.RUN_TASK(‘blds_convert’, l_sql_stmt, DBMS_SQL.NATIVE, parallel_level => 10);

— dbms_output.put_line(‘start: ‘ || start_id || ‘end ‘ || end_id );

— If there is an error, RESUME it for at most 2 times.

L_TRY := 0;

L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(‘blds_convert’);

dbms_output.put_line(‘OUT: ‘ || L_TRY || ‘Status ‘ || L_status );

WHILE(L_TRY

LOOP

L_TRY := L_TRY + 1;

DBMS_PARALLEL_EXECUTE.RESUME_TASK(‘blds_convert’);

L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(‘blds_convert’);

END LOOP;

— Done with processing; drop the task

DBMS_PARALLEL_EXECUTE.DROP_TASK(‘blds_convert’);

END;

 

/