Oracle DBMS_PARALLEL_EXECUTE_INTERNAL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose This package contains APIs to chunk a table into smaller units and execute those chunks in parallel.
AUTHID DEFINER
Dependencies
ALL_TABLES DBMS_PARALLEL_EXECUTE_CHUNKS$ DBMS_STATS
DBA_EXTENTS DBMS_PARALLEL_EXECUTE_EXTENTS DBMS_STATS_INTERNAL
DBA_OBJECTS DBMS_PARALLEL_EXECUTE_INTERNAL DBMS_SYS_ERROR
DBA_PARALLEL_EXECUTE_TASKS DBMS_PARALLEL_EXECUTE_SEQ$ DBMS_SYS_SQL
DBA_SCHEDULER_RUNNING_JOBS USER_PARALLEL_EXECUTE_TASKS DUAL
DBA_TABLES DBMS_PARALLEL_EXECUTE_TASK$ GV$PARAMETER
DBA_USERS DBMS_ROWID PLITBLM
DBMS_ASSERT DBMS_SQL USER_PARALLEL_EXECUTE_TASKS
DBMS_PARALLEL_EXECUTE DBMS_STANDARD  
Documented No
Exceptions
Error Code Reason
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
First Available 11.2.0.1
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvthpexei.plb
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 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

Overload 1
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(owner_name IN VARCHAR2)
RETURN NUMBER;
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(
owner# IN NUMBER,
task   IN VARCHAR2)
RETURN PLSQL RECORD;
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(task IN PLSQL RECORD);
TBD

Related Topics
Built-in Functions
Built-in Packages
DBMS_PARALLEL_EXECUTE
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved