오라클 프로시져 패러럴 실행
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
SET SERVEROUTPUT ON
DECLARE
l_task_name user_parallel_execute_tasks.task_name%TYPE;
l_sql_stmt user_parallel_execute_tasks.sql_stmt%TYPE;
BEGIN
l_task_name := 'px_update';
l_sql_stmt := 'UPDATE t SET col = expr WHERE rowid BETWEEN :start_id AND :end_id';
dbms_parallel_execute.create_task(task_name => l_task_name);
dbms_parallel_execute.create_chunks_by_rowid(
task_name => l_task_name,
table_owner => user,
table_name => 'T',
by_row => FALSE,
chunk_size => 128
);
dbms_parallel_execute.run_task(
task_name => l_task_name,
sql_stmt => l_sql_stmt,
language_flag => dbms_sql.native,
parallel_level => 4
);
WHILE (dbms_parallel_execute.task_status(task_name => l_task_name)
NOT IN (
dbms_parallel_execute.chunking_failed,
dbms_parallel_execute.finished,
dbms_parallel_execute.finished_with_error,
dbms_parallel_execute.crashed
))
LOOP
dbms_lock.sleep(1);
END LOOP;
CASE dbms_parallel_execute.task_status(task_name => l_task_name)
WHEN dbms_parallel_execute.chunking_failed THEN dbms_output.put_line('chunking_failed');
WHEN dbms_parallel_execute.finished THEN dbms_output.put_line('finished');
WHEN dbms_parallel_execute.finished_with_error THEN dbms_output.put_line('finished_with_error');
WHEN dbms_parallel_execute.crashed THEN dbms_output.put_line('crashed');
END CASE;
dbms_parallel_execute.drop_task(task_name => l_task_name);
END;
/