This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Apr 1, 2013 7:25 AM by ranit B RSS

Reg : Background Jobs -

ranit B Expert
Currently Being Moderated
Hi Experts,

I've few procedures which together form a complete process.
Say like -
/* 
     - Login into an UNIX box 
     - connect to the Database
     - sudo su - orabatch 
*/
     sqlplus /
     
     EXEC A.proc_a1;
     EXEC B.proc_b1;
     EXEC A.proc_a2;
     EXEC B.proc_b1; -- "same as 2nd"
     EXEC C.proc_c1;
This complete process (5 procs) takes around 3-4 hrs. So, we run this from UNIX box so that even after the machine gets shutdown the process will be continuing at the background.
But, I want to avoid the UNIX box thing and make a way where we will do everything inside a SQL file and running this SQL file (may be from SQL*Plus) will do the same. (Even if system gets shutdown, task would be running at background)

Also, doing <tt>sudo su - orabatch</tt> executes as a super user, so it can execute all procs w/o changing user.

<tt>JOBS</tt> and <tt>SCHEDULERS</tt> are coming to my mind. Will that help running procs in BG?

Help much appreciated. Thanks!
- Ranit
  • 1. Re: Reg : Background Jobs -
    jeneesh Guru
    Currently Being Moderated
    Cant you make a main procedure which will call these procedures?

    And then schedule it using dbms_scheduler..
    procedure main_proc is
    begin
      A.proc_a1;
      B.proc_b1;
      A.proc_a2;
     ....
    end;
  • 2. Re: Reg : Background Jobs -
    Manik Expert
    Currently Being Moderated
    By machine I guess you are referring to your pc on which you start those procs..

    You can use oracle dbms_scheduler here, create a job bundle all these procs together and run the job, this way you will be safe even if your machine is switched off.

    Cheers,
    Manik.
  • 3. Re: Reg : Background Jobs -
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi.
    <tt>JOBS</tt> and <tt>SCHEDULERS</tt> are coming to my mind. Will that help running procs in BG?
    Yes you should use jobs which will execute on certain time that you specify. Also this will take less time if your procedure are using different tables since all the procedure will run on same time and complite as possible as and unlike script they will not wait for complation of the first procedure.

    For sample scheduer you can refer below link...

    http://www.oracle-base.com/articles/10g/scheduler-10g.php
  • 4. Re: Reg : Background Jobs -
    ranit B Expert
    Currently Being Moderated
    jeneesh wrote:
    Cant you make a main procedure which will call these procedures?

    And then schedule it using dbms_scheduler..
    procedure main_proc is
    begin
    A.proc_a1;
    B.proc_b1;
    A.proc_a2;
    ....
    end;
    In which user (A/B/C) should I create the main_proc?
    From where should I call main_proc?

    Since, the procs are in different Users they don't have execute permissions.
    If I make it in a SQL script, I can easily connect/disconnect to users when the proc execution is needed.
  • 5. Re: Reg : Background Jobs -
    jeneesh Guru
    Currently Being Moderated
    ranit B wrote:
    In which user (A/B/C) should I create the main_proc?
    From where should I call main_proc?

    Since, the procs are in different Users they don't have execute permissions.
    If I make it in a SQL script, I can easily connect/disconnect to users when the proc execution is needed.
    Have a user with all the privileges..Either A or B or C or a DBA user..

    What is the benefit of doing Connect and Disconnect, instead of having a privileged user?

    There is no point in handling a db procedure at OS level..

    And to run it in back ground, your only option in PL/SQL is jobs..
  • 6. Re: Reg : Background Jobs -
    sybrand_b Guru
    Currently Being Moderated
    Just grant execute on the B procedure to user A and don't disconnect/connect.

    -----------
    Sybrand Bakker
    Senior Oracle DBA
  • 7. Re: Reg : Background Jobs -
    Manik Expert
    Currently Being Moderated
    grant execute,debug on A.proc_a1 to X; --- debug optional....
    grant execute,debug on B.proc_b1 to X;
    grant execute,debug on C.proc_c1 to X;
    ...

    And make use of that user X to create job and run all those at once using a job in dbms_scheduler..

    Cheers,
    Manik.
  • 8. Re: Reg : Background Jobs -
    Purvesh K Guru
    Currently Being Moderated
    ranit B wrote:
    In which user (A/B/C) should I create the main_proc?
    From where should I call main_proc?

    Since, the procs are in different Users they don't have execute permissions.
    If I make it in a SQL script, I can easily connect/disconnect to users when the proc execution is needed.
    As you demonstrated, you are, probably, executing the batches using the Root user (which in fact is a bad idea). And if you decide to move on Schedulers, you will still face the problem of possessing necessary Execute permissions on procedures belonging to each user.

    Hence, if this has to be a scheduled activity, only then, IMHO, should you move to Schedulers. But if it is not, then encapsulating, as Jeneesh suggested, it into a stored program should suffice and you can then execute the stored program, as per your need.

    But, having a User, that has execute privlieges on these procedures would give you great benefit.
  • 9. Re: Reg : Background Jobs -
    Ramin Hashimzadeh Expert
    Currently Being Moderated
    create one user, which have grant to execute all of your procedures and under this user create one main procedure which is call other procedures, then :
      begin
    
        DBMS_SCHEDULER.CREATE_PROGRAM(program_name   => ' myprogram',
    
                                      program_type   => 'STORED_PROCEDURE',
    
                                      program_action => 'myMainProc',
    
                                      enabled        => true);
    
                                      
    
        DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'mySchedule',
    
                                       start_date => systimestamp,
    
                                       repeat_interval => 'FREQ=DAILY; BYHOUR=1');
    
                                          
    
        DBMS_SCHEDULER.CREATE_JOB(job_name      => 'myJob',
    
                                  program_name  => 'myprogram',
    
                                  schedule_name => 'mySchedule',
    
                                  enabled       => true);
    
      end; 
  • 10. Re: Reg : Background Jobs -
    ranit B Expert
    Currently Being Moderated
    Really really thanks for all the inputs/ideas guys. Much appreciated!

    But what I'm thinking now is to create a PERL script which will contain all these procs, and we'll execute the scripts as a super-user like <tt>sudo su - orabatch</tt>

    In this way, things will be more systematic and even no permission issues.

    What you all say?
  • 11. Re: Reg : Background Jobs -
    Manik Expert
    Currently Being Moderated
    There is a lot of difference in running perl script to call your pl/sql program and to run something within oracle (via scheduled job)
    You decide which is better.

    Cheers,
    Manik.
  • 12. Re: Reg : Background Jobs -
    Purvesh K Guru
    Currently Being Moderated
    ranit B wrote:
    Really really thanks for all the inputs/ideas guys. Much appreciated!

    But what I'm thinking now is to create a PERL script which will contain all these procs, and we'll execute the scripts as a super-user like <tt>sudo su - orabatch</tt>

    In this way, things will be more systematic and even no permission issues.

    What you all say?
    Sorry if it offends you, but, IMHO, it looks like you are just getting past the current restrictions than to fix those.

    You have an option of granting execute permissions on the procedure and Insert/Update/Delete on specific tables (if the procedure does any), and you are all set to accomplish the task. Moreover, since you are already using the Root user to execute these procedures, what stops you from doing so in SQL Jobs/manual executions?
  • 13. Re: Reg : Background Jobs -
    ranit B Expert
    Currently Being Moderated
    Manik,

    It is not just about executing few Oracle Procs... It has a lot of other UNIX related things following it.
    Few server restart commands and then executing few other shell scripts.

    So, again creating JOBS will complicate things.

    The current process does everything properly, just that I want to make things bit more automated.
  • 14. Re: Reg : Background Jobs -
    ranit B Expert
    Currently Being Moderated
    Purvesh,

    This is an existing setup and works pretty well.
    Giving grants will be an extra action which in my case can be avoided.

    Anyways thanks for the inputs.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points