Start/fork a Background Process and Proceed....
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Start/fork a Background Process and Proceed....

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577

    Arrow

    Hi,

    We have a daily process which is executed by our users.
    Every evening after the stock market closes one of our users loads the stock_price table with todays stock_price data.

    Basically he/she just has to hit a submit button which executes a stored procedure and the data is loaded from a various sources into the tables.

    Now I want to execute another process say a refresh of a Materialized view from within this procedure but dont want the user to wait till the time its over.

    As far as the user is concerned his job is done. He doesnt want to wait for the refresh of MV to be done.

    Is there a way where I can fork out another process or something to refresh the MV and still proceed with the execution of the current stored procedure.

    I hope i was clear enough.

    Please suggest.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    instead of running dbms_mview.refresh put that into a job so intead of waiting dbms_mview to finish you just wait for dbms_job which is instantaneous and job will be running in background

  3. #3
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by pando
    instead of running dbms_mview.refresh put that into a job so intead of waiting dbms_mview to finish you just wait for dbms_job which is instantaneous and job will be running in background

    Thanks Pando,

    Whats the best way to do this.

    CASE 1
    --------

    Create the Job, Run it immediate and remove the Job. In this way we dont have to hard code the Job Number.

    CASE 2
    --------
    Create the Job and Run it immediate by Hard coding the job number .

    or any other suggestion.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well column WHAT is gonna be constant right? just run a query before to get the job number with what

  5. #5
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Thanks Pando,

    I tried with dbms_job but it still takes the same amount of time.

    Here is what I did.

    I created a stored procedure like

    create or replace procedure codb_refresh_stockprice_mv as

    Begin

    DBMS_MVIEW.REFRESH('MV_MAXPRICEDATE_STOCKPRICE','C','',TRUE,False,0,0,0,False);
    Execute Immediate 'Analyze Table stock_price compute statistics';

    end;
    /

    Then I created a Job as

    VARIABLE jobno number;
    dbms_job.submit(:jobno,'codb_refresh_stockprice_mv;',sysdate,null,false);

    SQL> print jobno

    JOBNO
    ----------
    75

    I executed the job as
    begin
    dbms_job.run(75);
    end;
    /

    Its taking the same amount of time as before.

    Another thing is that the Job vanished after executing.

    I dont see the job anymore in user_jobs. I saw it before running it.


    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    have you tried use autonomous transaction? it's kind of fork a process

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    that wont work neither since everything is procedural/sequential in pl/sql have to think something better

  8. #8
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by pando
    have you tried use autonomous transaction? it's kind of fork a process


    I dont think we can use that.

    Take a look at

    http://metalink.oracle.com/metalink/...T&p_id=65961.1
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well your only choice is dbms_job, instead of haveing scheduled have to submit the job at run time
    look

    http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_CRITERIA:3533747242374,%7Bfork%7D%20and%20%7Bprocess%7D

  10. #10
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Thanks pando,

    Issue resolved.

    It was taking a long time with dbms_job because I was executing
    dbms_job.run ().

    Thanks again
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width