Oracle DBMS_PARALLEL_EXECUTE_INTERNAL
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose This package contains APIs to chunk a table into smaller units and execute those chunks in parallel.
AUTHID DEFINER
Dependencies
DBA_PARALLEL_EXECUTE_TASKS DBMS_PARALLEL_EXECUTE_CHUNKS$ DBMS_SYS_ERROR
DBA_SCHEDULER_RUNNING_JOBS DBMS_PARALLEL_EXECUTE_EXTENTS DBMS_SYS_SQL
DBA_TABLES DBMS_PARALLEL_EXECUTE_SEQ$ DUAL
DBA_USERS DBMS_PARALLEL_EXECUTE_TASK$ GV$PARAMETER
DBMS_ASSERT DBMS_ROWID USER_PARALLEL_EXECUTE_TASKS
DBMS_PARALLEL_EXECUTE DBMS_SQL V$PARAMETER
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 PL/SQL RECORD;
TBD
 
RUN_INTERNAL_WORKER
Undocumented dbms_parallel_execute_internal.run_internal_worker(
owner#    IN NUMBER,
task_name IN VARCHAR2,
job_name  IN VARCHAR2);
 
 
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 PL/SQL Record);
TBD

Related Topics
DBMS_PARALLEL_EXECUTE
Packages

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-2014 Daniel A. Morgan All Rights Reserved