CODEWITHSUNDEEP
oracle-databaseplsql
notexpert
notexpert

Reputation: 11

Oracle Run same procedure concurrently

How to run same procedure with different parameters at same time in oracle.

Begin
  exec ProcDeleteA(1);
  exec ProcDeleteA(2);
  exec ProcDeleteA(3);
END;

I created procedure ProcDeleteA(tablenumber number). I created 100 temp table with each has 5 million orphan ids in it which needs to be deleted from production table. Each night , I run for three tables ids which are to be deleted from one production table. I want to run same procedure three times concurrently, so that three proc run and complete deleting from production table by 5 A.M in the morning. Any suggestions to run proc concurrently. The temp table numbers are stored in a table. Every day three temp table numbers are inserted and those table ids will be deleted from production table.

Please suggest how to run procedure concurrently? Can it be achieved through pl/SQL or shell script?

I created 100 temp tables with 5 million records in each of the temp table that needs to be deleted from a table in production. Basically i have primary key (ids) of the production table stored in these temp tables. These are orphan ids that application generated which are Pre identified and stored in these temp tables and these ids need to be deleted from production table. so the temp tables are like temptab1, temptab2...temptab100. Basically I want to delete 500 million rows from production table. Everyday I want to run for 3 tables and it will delete the ids from production table. It will take like 30 days to delete 500 million rows. Can't delete during day time and only 15 million per night because the deletions flow through golden gate.

Upvotes: 1

Views: 440

Answers (1)

eaolson
eaolson

Reputation: 15094

You haven't told us much about your actual problem, so I don't understand why you need three instances of this procedure. I also don't understand why you can't run them sequentially.

But you can basically do what you're asking by scheduling a job to run at a given time with DBMS_SCHEDULER.

exec dbms_scheduler.create_job(
    job_name => 'MYJOB1',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin ProcDeleteA(1); end;',
    start_date => SYSDATE,
    repeat_interval => 'FREQ=DAILY',
    enabled => TRUE );

That will run it every day at the current time.

Edit: You need something that will start your procedure, then return, but leave it running. This will spawn a process that immediately starts running, but return control to you, then you can run it again for your other procedures. it won't repeat.

exec dbms_scheduler.create_job(
    job_name => 'MYJOB1',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin ProcDeleteA(1); end;',
    start_date => SYSDATE,
    repeat_interval => NULL,
    enabled => TRUE,
    auto_drop => TRUE );

Are you starting three different instances of your procedure because you think it will be faster than a single one? Depending on what you're doing, it possibly wont.

Upvotes: 2

Related Questions