Oracle SQL script parallel execution

Here's one option.

I wouldn't really CTAS - I'd rather create both tables in advance, and then insert rows into them. Why? Because this approach uses stored procedures which - in order to perform DDL (which is CTAS) - require dynamic SQL. Not that it is impossible to do that; on the contrary, but it is way simpler NOT to use it.

I'd create yet another table (let's call it table_done) which contains only one row with two columns: table_1 and table_2 whose values can be 0 (meaning: data for that table is not ready) or 1 (data ready).

Furthermore, I'd create two stored procedures which look the same; the only difference is that each of them inserts rows into its own table:

create procedure p_insert_1 as
begin
  -- remove old data
  execute immediate 'truncate table table_1';

  -- table_1 data not ready
  update table_done set table_1 = 0;

  -- prepare new data
  insert into table_1 (...) select ...;

  -- table_1 data ready
  insert into table_done (table_1) values (1);
  commit;
end;

The 3rd, "main" procedure, is the one you'd run manually. What would it do? Create two one-time database jobs that run immediately, each of them starting its own p_insert procedure so that they run in parallel. That procedure would then (in a loop) check whether both columns in table_done are set to 1 and - if so - continue execution.

create procedure p_main is
  l_job_1 number;
  l_job_2 number;
  --
  l_t1_done number;
  l_t2_done number;
begin
  dbms_job.submit(l_job_1, 'begin p_insert_1; end;');
  dbms_job.submit(l_job_2, 'begin p_insert_2; end;');

  loop
    select table_1, table_2
      into l_t1_done, l_t2_done
      from table_done;

    if l_t1_done = 1 and l_t2_done = 1 then
       -- exit the loop
       exit;
    else 
       -- tables aren't ready yet; wait 60 seconds and try again
       dbms_lock.sleep(60);
    end if;
  end loop;

  -- process data prepared in table_1 and table_2
end;

That's just a simplified idea; I didn't test it myself so I apologize if there are any errors I made. Also,

• instead of dbms_job, you could choose to use advanced dbms_scheduler
• if you're on 18c (or later), use dbms_session.sleep instead of dbms_lock.sleep
• and so forth