Question

DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL is called properly but START_ID IS NULL

I call DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL in different configuration and users. At my personel computer, it works well. below code assigns start_id to 1. But at business computer, it can not assign START_ID. I mean START_ID is null. I think there is a missing Grant.

begin
--sys.dbms_parallel_execute.drop_task(task_name => 'TASK_TEST');
sys.dbms_parallel_execute.create_task(task_name => 'TASK_TEST');
sys.DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL('TASK_TEST','SELECT 1 column_1, 2 column_2 FROM sys.dual',false);
end;

select start_id from sys.dba_parallel_execute_chunks;
--NULL ( at business computer/environment ) --WRONG result.

select start_id from sys.dba_parallel_execute_chunks;
--1 (at my personel computer/ Virtual Machine ) --EXPECTED result is OK.

Expected value is 1. I run that code in different computer. Which grant is missing ? Or where should I focus to eleminate the problem

 2  34  2
1 Jan 1970

Solution

 0

I was running the above create_chunk query in business environment by using DIFFERENT SCHEMA(USER) because of missing grants on business.

I was checking start_id via sys.dba_parallel_execute_chunks. This view is masking "start_rowid, end_rowid, start_id, end_id" by using CASE WHEN USERENV('SCHEMAID')=c.taskowner THEN c.start_id ELSE NULL END. I don't know why Oracle masks these values. So, when I check the source table ( sys.dbms_parallel_execute_chunks$)of sys.dba_parallel_execute_chunks view. I see the values.

Short answer : values are assigned and it can be seen from that main table sys.dbms_parallel_execute_chunks$

2024-07-24
iltermutlu