Oracle DBMS_PARALLEL_EXECUTE_INTERNAL
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose This package contains APIs to chunk a table into smaller units and execute those chunks in parallel.
AUTHID DEFINER
Data Types CREATE TABLE "SYS"."DBMS_PARALLEL_EXECUTE_TASK$" SHARING=METADATA (
"TASK_OWNER#"                NUMBER NOT NULL ENABLE,
"TASK_NAME"                  VARCHAR2(128) NOT NULL ENABLE,
"CHUNK_TYPE"                 NUMBER NOT NULL ENABLE,
"STATUS"                     NUMBER NOT NULL ENABLE,
"TABLE_OWNER"                VARCHAR2(128),
"TABLE_NAME"                 VARCHAR2(128),
"NUMBER_COLUMN"              VARCHAR2(128),
"CMT"                        VARCHAR2(4000),
"JOB_PREFIX"                 VARCHAR2(128),
"STOP_FLAG"                  NUMBER,
"SQL_STMT"                   CLOB,
"LANGUAGE_FLAG"              NUMBER,
"EDITION"                    VARCHAR2(130),
"APPLY_CROSSEDITION_TRIGGER" VARCHAR2(130),
"FIRE_APPLY_TRIGGER"         VARCHAR2(10),
"PARALLEL_LEVEL"             NUMBER,
"JOB_CLASS"                  VARCHAR2(128),
CONSTRAINT "PK_DBMS_PARALLEL_EXECUTE_1"
PRIMARY KEY ("TASK_OWNER#", "TASK_NAME")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
LOB ("SQL_STMT") STORE AS BASICFILE (
TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

This object is a heap table but is referenced in this package as a RECORD.
Dependencies
ALL_TABLES DBA_USERS DBMS_STANDARD
DBA_EXTENTS DBMS_PARALLEL_EXECUTE DBMS_SYS_ERROR
DBA_OBJECTS DBMS_PARALLEL_EXECUTE_CHUNKS$ DUAL
DBA_PARALLEL_EXECUTE_TASKS DBMS_PARALLEL_EXECUTE_SEQ$ GV$PARAMETER
DBA_SCHEDULER_RUNNING_JOBS DBMS_PARALLEL_EXECUTE_TASK$ PLITBLM
DBA_TABLES DBMS_ROWID USER_PARALLEL_EXECUTE_TASKS
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
Security Model Owned by SYS with no privileges granted

Direct access to some objects in this package is prevented by means of an Accessible By clause.
Source {ORACLE_HOME}/rdbms/admin/prvthpexei.plb
Subprograms
ASSERT_CHUNK_EXISTS DROP_TASK RUN_INTERNAL_WORKER
ASSERT_TASK_EXISTS GENERATE_TASK_NAME SEQ_NEXT_VAL
CREATE_CHUNKS_BY_NUMBER_COL GET_BLOCKS_NUM_ROWS SET_CHUNK_STATUS
CREATE_CHUNKS_BY_ROWID GET_RANGE SET_TASK_STATUS
CREATE_CHUNKS_BY_SQL INSERT_CHUNKS_ROW STOP_TASK
CREATE_TASK IS_STOP_FLAG_SET TASK_STATUS
DEFAULT_PARALLELISM OWNER_NAME_TO_NUM UNASSIGN_CHUNKS
DROP_ALL_TASKS PURGE_PROCESSED_CHUNKS UPDATE_TASK
DROP_CHUNKS READ_TASK  
 
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;

DEFAULT_PARALLELISM
-------------------
                  4
 
DROP_ALL_TASKS
Undocumented dbms_parallel_execute_internal.drop_all_tasks(owner_name IN VARCHAR2);
exec dbms_parallel_execute_internal.drop_all_tasks('UWCLASS');

PL/SQL procedure successfully completed.
 
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;

DBMS_PARALLEL_EXECUTE_INTERNAL.GENERATE_TASK_NAME('UW')
--------------------------------------------------------
UW1
 
GET_BLOCKS_NUM_ROWS (new 21c)
Undocumented dbms_parallel_execute_internal.get_blocks_num_rows(
table_owner IN  VARCHAR2,
table_name  IN  VARCHAR2,
blocks      OUT NUMBER,
num_rows    OUT NUMBER)
DECLARE
 blksOut NUMBER;
 rowsOut NUMBER;
BEGIN
  dbms_parallel_execute_internal.get_blocks_num_rows(USER, 'TAB$', blksOut, rowsOut);
  dbms_output.put_line(blksOut);
  dbms_output.put_line(rowsOut);
END;
/
dbms_parallel_execute_internal.get_blocks_num_rows(USER, 'TAB$', blksOut, rowsOut);
*
ERROR at line 5:
ORA-06550: line 5, column 3:
PLS-00904: insufficient privilege to access object GET_BLOCKS_NUM_ROWS
 
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
 
INSERT_CHUNKS_ROW (new 21c)
Undocumented dbms_parallel_execute_internal.insert_chunks_row(
owner#      IN NUMBER,
task_name   IN VARCHAR2,
status      IN NUMBER,
start_id    IN NUMBER,
end_id      IN NUMBER,
start_rowid IN UROWID,
end_rowid   IN UROWID);
TBD
 
IS_STOP_FLAG_SET (new 21c)
Undocumented dbms_parallel_execute_internal.is_stop_flag_set(
owner# IN NUMBER,
task   IN VARCHAR2)
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 user_id
FROM dba_users
WHERE username = 'UWCLASS';

 USER_ID
--------
     110

SELECT dbms_parallel_execute_internal.owner_name_to_num('UWCLASS')
FROM dual;

DBMS_PARALLEL_EXECUTE_INTERNAL.OWNER_NAME_TO_NUM('UWCLASS')
-----------------------------------------------------------
                                                        110
 
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 dbms_parallel_execute_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;

SEQ_NEXT_VAL
------------
           1

SELECT dbms_parallel_execute_internal.generate_task_name('UW')
FROM dual;

DBMS_PARALLEL_EXECUTE_INTERNAL.GENERATE_TASK_NAME('UW')
-------------------------------------------------------
UW2

SELECT dbms_parallel_execute_internal.seq_next_val
FROM dual;

SEQ_NEXT_VAL
------------
           3
 
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
 
SET_TASK_STATUS (new 21c)
Undocumented dbms_parallel_execute_internal.set_task_status(
owner#     IN NUMBER,
task       IN VARCHAR2,
new_status IN NUMBER);
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(110, '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.task_status(110, '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(110, 'UWTASK');
 
UPDATE_TASK
Undocumented dbms_parallel_execute_internal.update_task(task IN dbms_parallel_execute_task$);
TBD

Related Topics
Built-in Functions
Built-in Packages
DBMS_PARALLEL_EXECUTE
What's New In 21c
What's New In 23c

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