| General Information |
| Note: This package contains APIs to chunk a table into smaller units and execute those chunks in parallel. |
| Source |
{ORACLE_HOME}/rdbms/admin/prvthpexei.plb |
| First Available |
11.2 |
| Dependencies |
| DBA_PARALLEL_EXECUTE_TASKS |
DBMS_PARALLEL_EXECUTE_TASK$ |
| DBA_SCHEDULER_RUNNING_JOBS |
DBMS_ROWID |
| DBA_TABLES |
DBMS_SQL |
| DBA_USERS |
DBMS_SYS_ERROR |
| DBMS_ASSERT |
DBMS_SYS_SQL |
| DBMS_PARALLEL_EXECUTE |
DUAL |
| DBMS_PARALLEL_EXECUTE_CHUNKS$ |
GV$PARAMETER |
| DBMS_PARALLEL_EXECUTE_EXTENTS |
USER_PARALLEL_EXECUTE_TASKS |
| DBMS_PARALLEL_EXECUTE_INTERNAL |
V$PARAMETER |
| DBMS_PARALLEL_EXECUTE_SEQ$ |
|
|
| Exceptions |
| Error Code |
Name |
| ORA-29490 |
MISSING_ROLE |
| ORA-29491 |
INVALID_TABLE |
| ORA-29492 |
INVALID_STATE_FOR_CHUNK |
| ORA-29493 |
INVALID_STATUS |
| ORA-29494 |
INVALID_STATE_FOR_RUN |
| ORA-29495 |
INVALID_STATE_FOR_RESUME |
| ORA-29497 |
DUPLICATE_TASK_NAME |
| ORA-29498 |
TASK_NOT_FOUND |
| ORA-29499 |
CHUNK_NOT_FOUND |
|
| Security Model |
No privileges granted |
| Subprograms |
|
| |
| ASSERT_CHUNK_EXISTS |
| Undocumented |
dbms_parallel_execute_internal.assert_chunk_exists(
owner# IN NUMBER,
task IN VARCHAR2,
chunk IN NUMBER); |
| TBD |
| |
| ASSERT_TASK_EXISTS |
| Undocumented |
dbms_parallel_execute_internal.assert_task_exists(owner# IN NUMBER, task IN VARCHAR2); |
SELECT username, user_id
FROM dba_users
ORDER BY 1;
exec dbms_parallel_execute_internal.assert_task_exists(90, 'HRTASK'); |
| |
| CREATE_CHUNKS_BY_NUMBER_COL |
Chunks the table by the column specified
Overload 1 |
dbms_parallel_execute_internal.create_chunks_by_number_col(
owner# IN NUMBER,
task_name IN VARCHAR2,
table_owner IN VARCHAR2,
table_name IN VARCHAR2,
table_column IN VARCHAR2,
chunk_size IN NUMBER); |
| TBD |
| |
| CREATE_CHUNKS_BY_ROWID |
| Chunks the table by ROWID |
dbms_parallel_execute_internal.create_chunks_by_rowid(
owner# IN NUMBER,
task_name IN VARCHAR2,
table_owner IN VARCHAR2,
table_name IN VARCHAR2,
table_column IN VARCHAR2,
num_rows IN NUMBER); |
| TBD |
| Overload 2 |
dbms_parallel_execute_internal.create_chunks_by_number_col(
owner# IN NUMBER,
task_name IN VARCHAR2,
table_owner IN VARCHAR2,
table_name IN VARCHAR2,
table_column IN VARCHAR2,
num_blocks IN NUMBER); |
| TBD |
| |
| CREATE_CHUNKS_BY_SQL |
| Chunks the table by means of a user-provided SELECT statement |
dbms_parallel_execute_internal.create_chunks_by_sql(
owner# IN NUMBER,
task_name IN VARCHAR2,
sql_stmnt IN CLOB,
by_rowid IN BOOLEAN); |
| TBD |
| |
| CREATE_TASK |
| Creates a task for the identified user |
dbms_parallel_execute_internal.create_task(
owner# IN NUMBER,
task_name IN VARCHAR2,
comment IN VARCHAR2); |
| exec dbms_parallel_execute_internal.create_task(90, 'UWTASK', 'Demo Task'); |
| |
| DEFAULT_PARALLELISM |
| Returns the default degree of parallelism |
dbms_parallel_execute_internal.default_parallelism RETURN BINARY_INTEGER; |
SELECT name, value
FROM v$parameter
WHERE name = 'parallel_threads_per_cpu';
SELECT dbms_parallel_execute_internal.default_parallelism
FROM dual; |
| |
| DROP_ALL_TASKS |
| Undocumented |
dbms_parallel_execute_internal.drop_all_tasks(owner_name IN VARCHAR2); |
| exec dbms_parallel_execute_internal.drop_all_tasks('UWCLASS'); |
| |
| DROP_CHUNKS |
| Undocumented |
dbms_parallel_execute_internal.drop_chunks(owner# IN NUMBER, TASK_NAME IN VARCHAR2); |
| exec dbms_parallel_execute_internal.drop_chunks(90, 'UWTASK'); |
| |
| DROP_TASK |
| Undocumented |
dbms_parallel_execute_internal.drop_task(owner# IN NUMBER, TASK_NAME IN VARCHAR2); |
| exec dbms_parallel_execute_internal.drop_task(90, 'UWTASK'); |
| |
| GENERATE_TASK_NAME |
| Returns a unique name for a task preceded by the indicated prefix |
dbms_parallel_execute_internal.generate_task_name(prefix IN VACHAR2) RETURN VARCHAR2; |
SELECT dbms_parallel_execute_internal.generate_task_name('UW')
FROM dual; |
| |
| GET_RANGE |
| Undocumented |
dbms_parallel_execute_internal.get_range(
owner# IN NUMBER,
task_name IN VARCHAR2,
chunk_id IN NUMBER,
start_rowid IN ROWID,
end_rowid IN ROWID,
start_id IN NUMBER
end_id IN NUMBER);
RETURN BOOLEAN; |
| TBD |
| |
| OWNER_NAME_TO_NUM |
| Given a schema name returns the user# from user$ |
dbms_parallel_execute_internal.owner_name_to_num( |
SELECT dbms_parallel_execute_internal.owner_name_to_num('UWCLASS')
FROM dual; |
| |
| PURGE_PROCESSED_CHUNKS |
| Undocumented |
dbms_parallel_execute_internal.purge_processed_chunks(
owner# IN NUMBER, TASK_NAME IN VARCHAR2); |
| exec dbms_parallel_execute_internal.purge_processed_chunks(90, 'UWTASK'); |
| |
| READ_TASK |
| Undocumented |
dbms_parallel_execute_internal.read_task(... |
| TBD |
| |
| RUN_INTERNAL_WORKER |
| Undocumented |
dbms_parallel_execute_internal.run_internal_worker(
owner# IN NUMBER,
task_name IN VARCHAR2,
job_name IN VARCHAR2) |
| TBD |
| |
| SEQ_NEXT_VAL |
| Returns the next value from sys.dbms_parallel_execute_seq$ |
dbms_parallel_execute_internal.seq_next_val; |
SELECT dbms_parallel_execute_internal.seq_next_val
FROM dual;
SELECT dbms_parallel_execute_internal.generate_task_name('UW')
FROM dual;
SELECT dbms_parallel_execute_internal.seq_next_val
FROM dual; |
| |
| SET_CHUNK_STATUS |
| Undocumented |
dbms_parallel_execute_internal.set_chunk_status(
owner# IN NUMBER,
task_name IN VARCHAR2,
chunk_id IN NUMBER,
status IN NUMBER,
err_num IN NUMBER,
err_msg IN VARCHAR2); |
| TBD |
| |
| STOP_TASK |
| Stops execution of the specified task |
dbms_parallel_execute_internal.stop_task(owner# IN NUMBER, task_name IN VARCHAR2); |
| exec dbms_parallel_execute_internal.stop_task(90, 'UWTASK'); |
| |
| TASK_STATUS |
| Returns the status of the specified task |
dbms_parallel_execute_internal.task_status(owner# IN NUMBER, task_name IN VARCHAR2)
RETURN NUMBER; |
set serveroutput on
DECLARE
x NUMBER;
BEGIN
x := dbms_parallel_execute_internal.stop_task(90, 'UWTASK');
END;
/ |
| |
| UNASSIGN_CHUNKS |
| Unassigns chunks for the specified task |
dbms_parallel_execute_internal.unassign_chunks(owner# IN NUMBER, task_name IN VARCHAR2) |
| exec dbms_parallel_execute_internal.unassign_chunks(90, 'UWTASK'); |
| |
| UPDATE_TASK |
| Undocumented |
dbms_parallel_execute_internal.update_task(... |
| TBD |
