database - How to control parallel execution in Oracle DB -

i'm trying execute script developed data transformation in oracle.

i have table large amount of data , want split data table based on condition , delete affected rows original table.

my approach use create table select second table, same first table condition inverted. truncated original table , moved data original temporary table. so:

create table data_reject select a, b, c table_original in (select criteria aux_table) / 

then

create table data_aux select a, b, c table_original not in (select criteria aux_table) / 

finally do

truncate table data_original / 

and

insert table_original (a, b, c) select a, b, c table_aux / 

my problem placed parallel hint on select , create table commands, , appears 1 statement issued, , next 1 starts executing without waiting first statement end execution.

this leads original table losing data before script has time populate other 2 tables.

this script executed command

alter session enable parallel dml 

how can prevent 1 command starting before previous 1 ends execution?

this script looks like: -- create temp table create table id_loc_rejected_temp parallel 32 nologging id_hier (select hierarchy7_key id id_hierarchy), loc_hier (select hierarchy6_key loc loc_hierarchy)

     select /* + parallel (a, 32) */             id_key,             loc_key        sls       a.id_key not in (select key merch_hier)      union       select /* + parallel (a, 32) */             id_key,             loc_key        sls       a.loc_key not in (select store loc_hier)      union       select /* + parallel (a, 32) */             id_key,             loc_key        id_loc_rejected_previous   /    insert debug_msg values(sysdate, 'first filter. inserted ' || sql%rowcount || ' rows')   /   commit   /    --add more filters   insert /* + append parallel (id_loc_rejected_temp, 32) */     id_loc_rejected_temp(id_key,                                 loc_key)                    store_repl               (select /* + parallel (t, 32) */ id_key id, loc_key store srep t)          select /* + parallel (a, 32) */                id_key,                loc_key           sls          (a.id_key, a.loc_key) not in (select id, store store_repl)         union          select /* + parallel (a, 32) */                id_key,                loc_key           inv          (a.id_key, a.loc_key) not in (select id, store store_repl)    /    insert debug_msg values(sysdate, 'second filter. inserted ' || sql%rowcount || ' rows')   /   commit   /    -- remove duplicates   create table id_loc_rejected parallel 32 nologging   select /* + parallel (a, 32) */ distinct id_key, loc_key id_loc_rejected_temp   /    insert debug_msg values(sysdate, 'transformed temp table final table. inserted ' || sql%rowcount || ' rows')   /   commit   /    drop table id_loc_rejected_temp   /    insert debug_msg values(sysdate, 'dropped id loc rejected temp table')   /    -- create rejected data tables   create table sls_rejected parallel 32 nologging         select /* + parallel (a, 32) */                id_key,                id_level,                loc_key,                loc_level,                date1,                ticket,                units           sls          (a.id_key, a.loc_key) in (select id_key, loc_key id_loc_rejected)   /    insert debug_msg values(sysdate, 'sales rejected created. inserted ' || sql%rowcount || ' rows')   /   commit   /    -- inventory reject table   create table inv_rejected parallel 32 nologging         select /* + parallel (a, 32) */                id_key,                id_level,                loc_key,                loc_level,                date1,                ticket,                units           inv          (a.id_key, a.loc_key) in (select id_key, loc_key id_loc_rejected)   /    insert debug_msg values(sysdate, 'inventory rejected created. inserted ' || sql%rowcount || ' rows')   /   commit   /    -- stockouts reject table   create table oos_rejected parallel 32 nologging         select /* + parallel (a, 32) */                id_key,                id_level,                loc_key,                loc_level,                date1,                ticket,                flag           oos          (a.id_key, a.loc_key) in (select id_key, loc_key id_loc_rejected)   /    insert debug_msg values(sysdate, 'stockout rejected created. inserted ' || sql%rowcount || ' rows')   /   commit   /    -- store replenishment reject table   create table stg_ro_st_re_rejected parallel 32 nologging         select /* + parallel (a, 32) */                id_key,                loc_key,                review           srep          (a.id_key, a.loc_key) in (select id_key, loc_key id_loc_rejected)   /    insert debug_msg values(sysdate, 'store replenishment parameters rejected created. inserted ' || sql%rowcount || ' rows')   /   commit   /    -- now, create temp tables hold rest of data (what want keep)   -- sales temp table   create table sls_tmp parallel 32 nologging         select /* + parallel (a, 32) */                id_key,                id_level,                loc_key,                loc_level,                date1,                ticket,                ticket           sls          (a.id_key, a.loc_key) not in (select id_key, loc_key id_loc_rejected)   /    insert debug_msg values(sysdate, 'sales temp created. inserted ' || sql%rowcount || ' rows')   /   commit   /    -- store replenishmment temp table   create table stg_ro_store_repl_tmp parallel 32 nologging         select /* + parallel (a, 32) */                id_key,                loc_key,                review           srep          (a.id_key, a.loc_key) not in (select id_key, loc_key id_loc_rejected)   /    insert debug_msg values(sysdate, 'store replenishment temp created. inserted ' || sql%rowcount || ' rows')   /   commit   /    -- truncate original tables , reinsert    -- final sales   truncate table sls   /    insert /* + append parallel (sls, 32) */     sls(id_key,                         id_level,                         loc_key,                         loc_level,                         date1,                         ticket,                         ticket)               (                  select /* + parallel (a, 32) */                         id_key,                         id_level,                         loc_key,                         loc_level,                         date1,                         ticket,                         ticket                    sls_tmp               )   /    insert debug_msg values(sysdate, 'sales reinserted. inserted ' || sql%rowcount || ' rows')   /    commit   /   drop table sls_tmp   / 

the problem select statements in script. of them had blank lines organization inside , these lines made sqlplus think had execute 2 commands instead of one.

this made long commands fail completely, while next commands had nothing or failed too.

the other problem sql%rowcount inside insert, made of inserts being performed while others lagged behind.

finally, broke script apart, executing 1 step @ time , worked fine.