Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_SCHEDULER job does not run
icon2.gif  DBMS_SCHEDULER job does not run [message #265182] Wed, 05 September 2007 12:06 Go to next message
Koolwant
Messages: 49
Registered: June 2007
Location: new jersey
Member
Hi

I am running dbms_scheduler job on 10.2.0.1 platform.
Logged in as SYS as sysdba
The job is as follows
 begin
 dbms_scheduler.create_job(
 job_name => 'event_based_job',
 job_type => 'PLSQL_BLOCK',
 job_action => 'begin
 insert into scheduler values (s1.nextval);commit;end;',
 start_date => systimestamp,
 repeat_interval => 'freq=minutely;interval=1;');
 end;
 /


I did create sequence and table scheduler also before running.
Now the problem is I'm checking scheduler table , it does have any new enteries,as according to JOB scheduled it should add a new row into table after every 1 minute.

Is there anything I'm missing.?

Any Suggestions definately appreciated.

Regards

Re: DBMS_SCHEDULER job does not run [message #265185 is a reply to message #265182] Wed, 05 September 2007 12:18 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
SQL> CREATE TABLE TEST
  2  (SNO NUMBER(5));

Table created.

SQL> CREATE SEQUENCE S1;

Sequence created.

SQL>  begin
  2  dbms_scheduler.create_job(
  3  job_name => 'event_based_job',
  4  job_type => 'PLSQL_BLOCK',
  5   job_action => 'begin
  6  insert into TEST  values (s1.nextval);commit;end;',
  7   start_date => systimestamp,
  8    repeat_interval => 'freq=minutely;interval=1;');
  9   end;
 10  /

PL/SQL procedure successfully completed.

SQL>  SELECT JOB_NAME,ENABLED
  2  FROM ALL_SCHEDULER_JOBS;

JOB_NAME                       ENABL
------------------------------ -----
EVENT_BASED_JOB                FALSE<<<<<<<<<<<<<<<<<
AUTO_SPACE_ADVISOR_JOB         TRUE
GATHER_STATS_JOB               TRUE
FGR$AUTOPURGE_JOB              FALSE
PURGE_LOG                      TRUE

SQL> EXEC dbms_scheduler.ENABLE (NAME=>'event_based_job');
SQL> SELECT JOB_NAME,ENABLED
  2  FROM ALL_SCHEDULER_JOBS;

JOB_NAME                       ENABL
------------------------------ -----
EVENT_BASED_JOB                TRUE <<<<<<<<<<<<<<<<<<
AUTO_SPACE_ADVISOR_JOB         TRUE
GATHER_STATS_JOB               TRUE
FGR$AUTOPURGE_JOB              FALSE
PURGE_LOG                      TRUE

SQL> SELECT * FROM TEST;

       SNO
----------
         1

SQL> /

       SNO
----------
         1

SQL> /

       SNO
----------
         1
         2

SQL>

[Updated on: Wed, 05 September 2007 12:19]

Report message to a moderator

Re: DBMS_SCHEDULER job does not run [message #265186 is a reply to message #265182] Wed, 05 September 2007 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

- Never ever use SYS but for maintenance purpose (startup, shutdown, backup, recover)
- SYS is special
- SYS is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS" and you'll see the immediate answer)
- SYS does not act like any other user
- When you use SYS Oracle deactivates some code path and activates others
- Whatever you do with SYS will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS for anything that can be done by another user.
Use SYS ONLY for something that can't be done by someone else.


Regards
Michel

Re: DBMS_SCHEDULER job does not run [message #265187 is a reply to message #265182] Wed, 05 September 2007 12:27 Go to previous messageGo to next message
Koolwant
Messages: 49
Registered: June 2007
Location: new jersey
Member
Thanks DreamzZ, that worked.

and Michel thanks for giving me a TIP, That makes my day.

Regards

Re: DBMS_SCHEDULER job does not run [message #265188 is a reply to message #265187] Wed, 05 September 2007 12:34 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
YOU WELCOME Wink
Previous Topic: Count difference
Next Topic: Correct script using indexes
Goto Forum:
  


Current Time: Fri Feb 14 16:38:01 CST 2025