Dml Does Not Run Parallel In Deeper Nesting In Dbms_job And Pl/Sql On 10.2.0.5 (Doc ID 1226655.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.5 to 10.2.0.5 [Release 10.2]
Information in this document applies to any platform.

Symptoms


=== ODM Issue Clarification ===
DML is not executed in parallel when requested from within a job on 10205, same job  used to work in 10204.


--~ PACKAGE declaration
create or replace package useretsting.testcase_a is
procedure run_testcase;
procedure run_testcase_job;
end testcase_a;
/

---------------------------------------------
--~ PACKAGE body
create or replace package body useretsting.testcase_a is
procedure run_testcase is
sqlstmt varchar2(4000);
begin
--~ force parallel query and tracing
execute immediate 'alter session force parallel query';
execute immediate 'alter session set "_px_trace" = "all"';
--~ sql to execute
select sqlstmt into sqlstmt from
(select
'begin INSERT INTO useretsting.endtab
SELECT *
FROM useretsting.btab1 MINUS
SELECT *
FROM useretsting.btab2;
END;' sqlstmt
from dual);
--~ sql execute
execute immediate sqlstmt;
end;
procedure run_testcase_job is
begin
DBMS_JOB.isubmit(333333,'begin testcase_a.run_testcase; end;',next_date => sysdate + 1/17280);
commit;
end;
end testcase_a;
/



--~ run procedure to create job to run problematic insert
conn useretsting/oracle
begin
testcase_allianz.run_testcase_job;
end;
/

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms