Home » SQL & PL/SQL » SQL & PL/SQL » dbms_job to run for every 30 seconds daily
dbms_job to run for every 30 seconds daily [message #357931] Fri, 07 November 2008 05:23 Go to next message
yashora
Messages: 39
Registered: August 2006
Member
Hello,

I created a job to run DAILY for every 30 seconds. I used the following code to do the task.

declare
v_job number;
begin
dbms_job.submit(v_job, 'execute_this_proc;',SYSDATE + 30/86400,'SYSDATE + 30/86400');
dbms_output.put_line(v_job);
commit;
end;

Will the above code help me to run the job daily for every 30 seconds starting today? And moreover, sometimes the above job broken status was set to "Y" and the failures it is showing is 16. Why this failure comes into picture? though there is no wrong in the procedure code? I used dbms_job.broken(jobno,false) package to set the broken status to 'N'. But not happening. Can any one suggest me the code changes that needs to be done on the above procedure to make it error free.

Your support on this is highly appreciated.

Regards
Jagdish
Re: dbms_job to run for every 30 seconds daily [message #357933 is a reply to message #357931] Fri, 07 November 2008 05:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your post is quite confusing.
DBMS_JOB cannot specift how long a job runs for. It will start a job at a given time, but it will not intervene to end that job.

If you want the process to end after 30 seconds, you'll have to include code in the proess itself to do that.
Re: dbms_job to run for every 30 seconds daily [message #357936 is a reply to message #357931] Fri, 07 November 2008 05:36 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Depends on what you mean with "DAILY for every 30 seconds"

Right now it will run every 30 seconds for all eternity as long as the database is up.

The "broken Y" means something in the procedure isn't working. When it didn't work for 16 times in a row the job is set to broken and isn't executed anymore.

Have a look at the alert.log or the background dump / user dump directories for log files about what is going wrong.

Aside from that, needing to run a job every 30 seconds smells somewhat like a flawed design to begin with.

What are you actually trying to do with the job? Is it CERTAIN that the job will always finish in 30 seconds?
Re: dbms_job to run for every 30 seconds daily [message #357950 is a reply to message #357931] Fri, 07 November 2008 06:31 Go to previous messageGo to next message
yashora
Messages: 39
Registered: August 2006
Member
Hello Thomas/JRowbottom,

Its interesting to share a couple of things with the experts like you.

Scenario is something like:

From Cognos Planning certain tables would be loaded daily in "abc" schema at their convenince. After all the tables loaded in our database, they would DROP AND CREATE a table with the publish status set to 'Y'. And my schema name ("XYZ") is different from "ABC" schema within the same database. To see the publish status "Y" my job should run for every 30 seconds to continue certain procedures run in my user schema.

Can you please suggest me accordingly on that as to how to achieve this task? I know i am somewher doing a flaw design.

Regards
Yashora
Re: dbms_job to run for every 30 seconds daily [message #357965 is a reply to message #357950] Fri, 07 November 2008 07:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are you saying that the Cognos Planning system signals that it has finished it's load by dropping a table and then recreating it?
That's poor design beyond my ability to believe....
However, it might just work in you advantage.
It is possible to create a database trigger that fires when DDL operations are performed (Documentation here )

If you created a trigger that fired whenever this table was created, then the trigger could perform whatever process you want your job to perfrom, and you'd have the added advanage f not loading the server down with a job that fired every 30 seconds.
Previous Topic: Data Dictionary table for script (DDL)
Next Topic: CUSROR%FOUND
Goto Forum:
  


Current Time: Tue Dec 06 04:13:45 CST 2016

Total time taken to generate the page: 0.05693 seconds