OraFAQ Forum: SQL & PL/SQL » Oracle Scheduler Timing Issue

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Scheduler Timing Issue

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Scheduler Timing Issue [message #601146]

Sun, 17 November 2013 07:13

VIP2013
Messages: 91
Registered: June 2013

Member

Hi All, I am using Release 11.2.0.3.0 of oracle. We migrated all of our jobs to scheduler recently.
and i just found that our job has been running in different times. All the database scheduled job are running 5hr(Perhaps EST) gap and application jobs are showing to be executed ~8hrs(perhaps PST) of gap.

So i verified that my DBA_SCHEDULER_GLOBAL_ATTRIBUTE shows EST5EDT and my expectation was GMT. Now below are my issues

1.The JOBS from application side those submitted to scheduler , should also show the same EST5EDT time on ACTUAL_START_DATE, but these are showing PST time.

2.Again even if i set this time to GMT in my local. And rerun the job(from database schedule but not application), it shows same EST5EDT time on ACTUAL_START_DATE column of dba_scheduler_job_run_details.

BEGIN
DBMS_SCHEDULER.get_scheduler_attribute (
attribute => 'default_timezone',
value => 'GMT');
END;
/

Report message to a moderator

Re: Oracle Scheduler Timing Issue [message #601147 is a reply to message #601146]

Sun, 17 November 2013 07:29

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

From Oracle docs:

Repeating jobs and windows that use the calendaring syntax need to know which time zone to use for their repeat intervals. They normally retrieve the time zone from start_date, but if no start_date is provided (which is not uncommon), they retrieve the time zone from the default_timezone Scheduler attribute.

This means that if you create a DBMS_SCHEDULER job and specify start date, Oracle will not use DBMS_SCHEDULER.DEFAULT_TIMEZONE. It will derive it from start date. And if you didn't specify time zone in start date Oracle will use session timezone. Use SET_ATTRIBUTE to change job START_DATE to start date with desired time zone.

For example:

Code: [Select all] [Show/ hide]

SQL> BEGIN
  2      DBMS_SCHEDULER.CREATE_JOB(
  3                                job_name             => 'job1',
  4                                job_type             => 'PLSQL_BLOCK',
  5                                job_action           => 'BEGIN NULL; END;',
  6                                start_date           => SYSDATE + 1,
  7                                repeat_interval      => 'FREQ=DAILY',
  8                                enabled              =>  TRUE
  9                               );
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> SELECT  START_DATE
  2    FROM  USER_SCHEDULER_JOBS
  3    WHERE JOB_NAME = 'JOB1'
  4  /

START_DATE
---------------------------------------------------------------------------
18-NOV-13 08.45.35.000000 AM -05:00

SQL> ALTER SESSION SET TIME_ZONE = 'PST'
  2  /

Session altered.

SQL> BEGIN
  2      DBMS_SCHEDULER.DROP_JOB(
  3                              job_name             => 'job1'
  4                             );
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2      DBMS_SCHEDULER.CREATE_JOB(
  3                                job_name             => 'job1',
  4                                job_type             => 'PLSQL_BLOCK',
  5                                job_action           => 'BEGIN NULL; END;',
  6                                start_date           => SYSDATE + 1,
  7                                repeat_interval      => 'FREQ=DAILY',
  8                                enabled              =>  TRUE
  9                               );
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> SELECT  START_DATE
  2    FROM  USER_SCHEDULER_JOBS
  3    WHERE JOB_NAME = 'JOB1'
  4  /

START_DATE
---------------------------------------------------------------------------
18-NOV-13 08.45.35.000000 AM PST

SQL>

SY.

[Updated on: Sun, 17 November 2013 07:51]

Report message to a moderator

Re: Oracle Scheduler Timing Issue [message #601148 is a reply to message #601147]

Sun, 17 November 2013 07:41

VIP2013
Messages: 91
Registered: June 2013

Member

Got it. But even if i have set the default_timezone to 'GMT' and i am triggering the same scheduler manually. its still populating the EST5EDT time in DBA_SCHEDULER_JOB_RUN_DETAILS dictionary. Why its happening.

Note - here i am not providing the start_date to the scheduler signature. Again i am validating the job run time from dba_scheduler_job_run_details dictionary by looking to the field 'REQ_START_DATE' and 'ACTUAL_START_DATE'. So i am expecting respective timezone should reflect in these fields too.

[Updated on: Sun, 17 November 2013 07:57]

Report message to a moderator

Re: Oracle Scheduler Timing Issue [message #601149 is a reply to message #601148]

Sun, 17 November 2013 07:53

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

What means "triggering"? Are you creating a new job, runiing existing job ...?

SY.

Report message to a moderator

Re: Oracle Scheduler Timing Issue [message #601150 is a reply to message #601149]

Sun, 17 November 2013 07:58

VIP2013
Messages: 91
Registered: June 2013

Member

here i am not providing the start_date to the scheduler signature. Again i am validating the job run time from dba_scheduler_job_run_details dictionary by looking to the field 'REQ_START_DATE' and 'ACTUAL_START_DATE'. So i am expecting respective timezone should reflect in these fields too.

Triggering means i am running the job using DBMS_SCHEDULER.RUN_JOB();

Report message to a moderator

Re: Oracle Scheduler Timing Issue [message #601153 is a reply to message #601150]

Sun, 17 November 2013 08:53

VIP2013
Messages: 91
Registered: June 2013

Member

can anybody help me , why even after changing the global scheduler attribute('default_timezone'), changes not getting reflected during execution in dba_scheduler_job_run_details

Report message to a moderator

Re: Oracle Scheduler Timing Issue [message #601154 is a reply to message #601150]

Sun, 17 November 2013 09:00

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

VIP2013 wrote on Sun, 17 November 2013 08:58

here i am not providing the start_date to the scheduler signature. Again i am validating the job run time from dba_scheduler_job_run_details dictionary by looking to the field 'REQ_START_DATE' and 'ACTUAL_START_DATE'. So i am expecting respective timezone should reflect in these fields too.

I don't know if it is a bug or is documented, but default timezone is not dynamic. You need to restart DBMS_SCHEDULER AQ for default time zone to take effect. Basically, you need to restart DB. For example:

Code: [Select all] [Show/ hide]

SQL> ALTER SESSION SET TIME_ZONE = 'PST'
  2  /

Session altered.

SQL> SELECT SESSIONTIMEZONE FROM DUAL
  2  /

SESSIONTIMEZONE
---------------------------------------------------------------------------
PST

SQL> DECLARE
  2      v_val VARCHAR2(20);
  3  BEGIN
  4      DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
  5                                             attribute => 'default_timezone',
  6                                             value     => 'EST'
  7                                            );
  8      DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE(
  9                                             attribute => 'default_timezone',
 10                                             value     => v_val
 11                                            );
 12      DBMS_OUTPUT.PUT_LINE('default_timezone is ' || v_val);
 13  END;
 14  /
default_timezone is EST

PL/SQL procedure successfully completed.

SQL> BEGIN
  2      DBMS_SCHEDULER.DROP_JOB(
  3                              job_name => 'job1'
  4                             );
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2      DBMS_SCHEDULER.CREATE_JOB(
  3                                job_name             => 'job1',
  4                                job_type             => 'PLSQL_BLOCK',
  5                                job_action           => 'BEGIN NULL; END;',
  6                                start_date           => NULL,
  7                                repeat_interval      => 'FREQ=DAILY',
  8                                enabled              =>  TRUE
  9                               );
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> SELECT  NEXT_RUN_DATE
  2    FROM  USER_SCHEDULER_JOBS
  3    WHERE JOB_NAME = 'JOB1'
  4  /

NEXT_RUN_DATE
---------------------------------------------------------------------------
17-NOV-13 10.49.05.200000 PM PST <-- And still it is PST

SQL> connect scott as sysdba
Enter password:
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3423965184 bytes
Fixed Size                  2260000 bytes
Variable Size            2751464416 bytes
Database Buffers          654311424 bytes
Redo Buffers               15929344 bytes
Database mounted.
Database opened.
SQL> connect scott
Enter password:
Connected.
SQL> set serveroutput on
SQL> BEGIN
  2      DBMS_SCHEDULER.DROP_JOB(
  3                              job_name => 'job1'
  4                             );
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2      DBMS_SCHEDULER.CREATE_JOB(
  3                                job_name             => 'job1',
  4                                job_type             => 'PLSQL_BLOCK',
  5                                job_action           => 'BEGIN NULL; END;',
  6                                start_date           => NULL,
  7                                repeat_interval      => 'FREQ=DAILY',
  8                                enabled              =>  TRUE
  9                               );
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> SELECT  NEXT_RUN_DATE
  2    FROM  USER_SCHEDULER_JOBS
  3    WHERE JOB_NAME = 'JOB1'
  4  /

NEXT_RUN_DATE
---------------------------------------------------------------------------
17-NOV-13 10.50.53.100000 PM EST <-- Now it is EST

SQL>

SY.

[Updated on: Sun, 17 November 2013 09:03]

Report message to a moderator

Re: Oracle Scheduler Timing Issue [message #601243 is a reply to message #601154]

Mon, 18 November 2013 22:12

VIP2013
Messages: 91
Registered: June 2013

Member

Thanks a lot. Appreciate your help.

Report message to a moderator

Previous Topic:	select Pivot
Next Topic:	list box in a cell of a excel sheet

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 08 15:55:48 CDT 2025