ODI Load Plan got stuck at Index Creation Step: Oracle BIAPPS 11g

The reason why it is getting stuck is because of the PARALLEL_LEVEL parameter value passed greater than 1 in (DBMS_PARALLEL_EXECUTE.RUN_TASK ) DBMS package.

Sample parameters passed as below :

DBMS_PARALLEL_EXECUTE.RUN_TASK('CREATE_INDEXES_17813500', l_sql_stmt, DBMS_SQL.NATIVE, parallel_level => 2) ;

ODI Load Plan Screenshot:

There are two possible reason when your load plan will get stuck up.

01. If the below DB parameter is 0 and parallel_level => {more than 1}, then your load plan will get stuck.

SQL> show parameter job_queue_processes 

NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 

job_queue_processes integer 0 

02. If your dbms_scheduler was disabled, kindly enable the same.


Execution of PL/SQL package (DBMS_PARALLEL_EXECUTE.RUN_TASK) with below parameters: