Database Reference
In-Depth Information
processed, it will be marked as
PROCESSED_WITH_ERROR
and other columns will contain the detailed error message
indicating the cause of the error. In either case, the session will then retrieve another chunk and process it and so on.
So, eventually these four jobs will have processed all of the chunks and the task will complete.
If any of the chunks failed, you can correct the underlying cause of the error and resume the task. This will cause
it to reprocess the failed chunks. When all complete successfully, you are done.
EODA@ORA12CR1> select *
2 from (
3 select chunk_id, status, start_rowid, end_rowid
4 from dba_parallel_execute_chunks
5 where task_name = 'PROCESS BIG TABLE'
6 order by chunk_id
7 )
8 where rownum <= 5
9 /
CHUNK_ID STATUS START_ROWID END_ROWID
---------- -------------------- ------------------ ------------------
1 PROCESSED AAAEyWAAEAAAAQoAAA AAAEyWAAEAAAAQvCcP
2 PROCESSED AAAEyWAAEAAAAQwAAA AAAEyWAAEAAAAQ3CcP
3 PROCESSED AAAEyWAAEAAAAQ4AAA AAAEyWAAEAAAAQ/CcP
4 PROCESSED AAAEyWAAEAAAARAAAA AAAEyWAAEAAAARHCcP
5 PROCESSED AAAEyWAAEAAAARIAAA AAAEyWAAEAAAARPCcP
You can either retain the task for history or remove it. The following example shows how to remove the task:
EODA@ORA12CR1> begin
2 dbms_parallel_execute.drop_task('PROCESS BIG TABLE' );
3 end;
4 /
PL/SQL procedure successfully completed.
If we review our own application table, we can see that the job was done using parallel 4, and each of the four
processed about the same number of rows:
EODA@ORA12CR1> select session_id, count(*)
2 from t2
3 group by session_id
4 order by session_id;
SESSION_ID COUNT(*)
---------- ----------
22603 2521812
22604 2485273
22605 2529386
22606 2463529
4 rows selected.
If you aren't yet up to Oracle Database 11
g
Release 2 or higher, then you can implement the same sort of
parallelism using the more labor-intensive approach detailed in the next section. However, the new package provides a
rich API (which we've just touched on here) that has much more functionality than the manual implementation does.
