Executing multiple statements in procedure simultaneously-CodePudding

I want to execute multiple DDL statements present in my procedure to start at once.Say

create or replace procedure test is

a number;
b number;
c number;
d number;
e number;
f number;
g number;
h number;
i number;
j number;

begin

create table t1 as select * from test1 where id between  a and b;

create table t2 as select * from test2 where id between  c and d;

create table t3 as select * from test3 where id between  e and f;

create table t4 as select * from test4  where id between  g and h;

create table t5 as select * from test5 where id between  i and j;


end test;

let's assume this is my proc and I want all the 5 statements to be executed at once and not one after another. How this can be achieved

CodePudding user response：

Statements executed through 1 connection are executed one after the other in the session of the connection. Execution in parallel will require several connections and some way to tell which statement is the next to execute (for example a queue mechanism, in your case it can be a table where you put kind of ID to specify which is the next step (in an autonomous transaction, for other sessions to see the commited data)).

CodePudding user response：

With the package : dbms_scheduler.create_job you can run your statements in parallel. for more information: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/scheduling-jobs-with-oracle-scheduler.html#GUID-D41660D0-D88F-4D9F-8CC8-63D040EDC4E6