CODEWITHSUNDEEP
oracle-databaseplsql
redsoxlost
redsoxlost

Reputation: 1235

DBMS_PARALLEL_EXECUTE exiting without running task

For large loads, we generally prefer to execute it in parallel and hence Oracle package DBMS_PARALLEL_EXECUTE. Here we are using chunking by row. When I run this, I am getting 

ORA-29495: invalid state for resume task

Task status is CHUNKED and chunks are in UNASSIGNED status.

What is going on here?

declare
    l_sql varchar2(500);
    l_chunk_sql varchar2(500);
    l_task_nm varchar2(50):='k_clearance_1';
    l_try number:=0;
    l_status number;
    l_count number:=0;
begin
    select count(1)
    into l_count
    from user_parallel_execute_tasks
    where task_name=l_task_nm;

    --create task if not exist
    if l_count = 0 then
        --create task
        dbms_parallel_execute.create_task(l_task_nm);
    end if;

    --create chunk

    dbms_parallel_execute.create_chunks_by_rowid(
        task_name=>l_task_nm,
        table_owner=>'RXTEAM',
        table_name=>'RPM_STAGE_CLEARANCE_ID_FIX',
        by_row=>TRUE,
        chunk_size=>10000
    );

    l_sql := 'update XXXXXX krsc
        set krsc.lgcy_extr_tmst = sysdate
        where exists (select 1
                      from rxteam.rpm_stage_clearance_id_fix rscf
                      where rscf.rpm_stage_clearance_id=krsc.rpm_stage_clearance_id
                      and rscf.rowid between :start_id and :end_id)';

    --run task        
    dbms_parallel_execute.run_task(task_name=>l_task_nm,
        sql_stmt=>l_sql,
        language_flag=>DBMS_SQL.NATIVE,
        parallel_level=>40
    );

    l_try := 0;
    l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task_nm);

    while (l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED) 
    loop
        l_try := l_try + 1;
        DBMS_PARALLEL_EXECUTE.resume_task(l_task_nm);
        l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task_nm);
    end loop;

    --dbms_parallel_execute.drop_task(l_task_nm)
end;
/

Upvotes: 1

Views: 3485

Answers (1)

antolis
antolis

Reputation: 11

In my case the problem was a (parse) error in the code of the PL/SQL anonymous block I submitted. I found out by looking in the USER_SCHEDULER_JOB_RUN_DETAILS table. This might help:

SELECT t.task_name, t.sql_stmt, d.errors
    --, d.* -- uncomment to see all job detail information
FROM user_parallel_execute_tasks t
JOIN user_scheduler_job_run_details d ON d.job_name = t.job_prefix||'_1'
WHERE t.status = 'CHUNKED'
ORDER BY t.task_name, d.job_name;

Upvotes: 1

Related Questions