-
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.
-
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
-
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.
-
well column WHAT is gonna be constant right? just run a query before to get the job number with what
-
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.
-
have you tried use autonomous transaction? it's kind of fork a process
-
that wont work neither since everything is procedural/sequential in pl/sql have to think something better
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|