oracle job scheduler start date

SQL Navigator
1

As we passed the DST change we found most of our scheduler jobs ran an hour early the first time after the change, then corrected on subsequent runs. Investigating, we found that the jobs’ START_DATE was specified with a time offset rather than a time zone name. When this is the case, those jobs are not DST aware. That’s all standard Oracle stuff and is not a SQL Nav problem in itself.

However …

In creating new or maintaining existing jobs, I have not been able to get SQL Nav to specify a named TZ on the START_DATE. So far I’ve had to resort to manually running dbms_scheduler.set_attribute_null to force the start date to use the scheduler default TZ, which is set at CST6CDT.

Have been working with Oracle for years, but recently started a new job and am pretty new to SQL Nav. Is there a procedural step or a default setting I’m missing, to allow a job setup with SQL Nav to use the scheduler default TZ?

–

Just as an update, it really appears that SQL Nav is doing something with its session vis a vis ‘timestamp with TZ’. Executing the same query from sqlplus on my desktop gives a different result from the executing it within SQL Nav on the same desktop to the same db:

From sqlplus:
SQL> select dbms_scheduler.stime from dual;

STIME

09-NOV-12 01.07.22.124966000 PM US/CENTRAL

SQL>

From SQL Nav

09-NOV-2012 1:06:55.326640000 PM -06:00

and - just for grins - from sqlplus on the database server:

SQL> select dbms_scheduler.stime from dual;

STIME

09-NOV-12 01.06.27.344264000 PM CST6CDT

Message was edited by: estevens_062

Message was edited by: estevens_062

2

Hello,

Sorry for the delay.

I don’t think these issues are related. The second issue I guess is just different display format; try using to_char and see whether it’s different from SQL*Plus.

We don’t supply the time zone info when creating a job, we simply convert the date supplied to timestamp with time zone. We expected it to automatically obtain the default timezone. It appears this is not the case. Thanks for pointing that out. I will raise an ER to add the ability to specify the time zone for the scheduler.

Regards,
Roman

3

Roman,

Thanks for the follow-up.

I may be missing something, but I believe this is more fundamental than simply NLS formatting. In the example below, job 1 was created in SQL Nav. While you can see in the script the creation of job 2 and then the result that compares and shows clearly the difference in time zone. Coming from the same session, we know that any controlling NLS date/time/timestamp format would be the same for both. Also, the sqlplus session shown below was run from the same machine as SQL Nav that created Job 1. I’ll admit I could be missing something, but it looks pretty clear that SQL Nav is depending on TZ information from some source other than what my sqlplus session was using. Both were complete defaults for their respective final environment, and both running on the same desktop.

SQL> select
2 owner
3 , job_name
4 , start_date
5 , last_start_date
6 , next_run_date
7 , repeat_interval
8 from dba_scheduler_jobs
9 where owner=‘ESTEVENS’
10 order by owner, job_name
11 ;

OWNER JOB_NAME START_DATE LAST_START_DATE NEXT_RUN_DATE REPEAT_INTERVAL

ESTEVENS EDS_JOB_1 13-NOV-12 09.05.42.000000 AM -06:00 14-NOV-12 09.05.42.200000 AM -06:00 FREQ=DAILY; INTERVAL=1

1 row selected.

SQL> –
SQL> begin
2 dbms_scheduler.create_job (
3 job_name => ‘estevens.eds_job_2’,
4 job_type => ‘PLSQL_BLOCK’,
5 job_action => ‘begin null;end;’,
6 repeat_interval => ‘FREQ=DAILY; INTERVAL=1’,
7 enabled => TRUE
8 );
9 END;
10 /

PL/SQL procedure successfully completed.

SQL> –
SQL> select
2 owner
3 , job_name
4 , start_date
5 , last_start_date
6 , next_run_date
7 , repeat_interval
8 from dba_scheduler_jobs
9 where owner=‘ESTEVENS’
10 order by owner, job_name
11 ;

OWNER JOB_NAME START_DATE LAST_START_DATE NEXT_RUN_DATE REPEAT_INTERVAL

ESTEVENS EDS_JOB_1 13-NOV-12 09.05.42.000000 AM -06:00 14-NOV-12 09.05.42.200000 AM -06:00 FREQ=DAILY; INTERVAL=1
ESTEVENS EDS_JOB_2 13-NOV-12 09.06.03.088292 AM CST6CDT 13-NOV-12 09.06.03.100000 AM CST6CDT FREQ=DAILY; INTERVAL=1

2 rows selected.

SQL> spool off

4

Thank you for the additional info. I think the difference can be explained easily. When creating the job manually, you didn’t specify the start date; you can do the same in SQL Nav by leaving the start date field blank.

Would you be satisfied if we just enhance the job scheduler by adding an option to specify the time zone?

Regards,
Roman

5

Well, it’s not really a matter of being “satisfied”. I’m just wanting to make sure I understand how the product is dealing with this and that I’m not overlooking something.

I thought I had tested deselecting the start date and start time when defining a new job. The wizard didn’t allow it, but the main screen does have a check box next to both elements, so I deselected them both. But the result was the same, the job defines with GMT offset instead of using the client or default named time zone. On this listing, EDS_JOB_4 was the result of that test:

SQL> select
2 owner
3 , job_name
4 , start_date
5 , last_start_date
6 , next_run_date
7 --, repeat_interval
8 from dba_scheduler_jobs
9 where owner=‘ESTEVENS’
10 order by owner, job_name
11 ;

OWNER JOB_NAME START_DATE LAST_START_DATE NEXT_RUN_DATE

ESTEVENS EDS_JOB_1 01-NOV-12 12.00.00.000000 AM US/CENTRAL 14-NOV-12 01.00.00.700000 PM US/CENTRAL
ESTEVENS EDS_JOB_2 13-NOV-12 09.43.00.041152 AM CST6CDT 13-NOV-12 09.43.00.000000 AM CST6CDT
ESTEVENS EDS_JOB_3 13-NOV-12 03.14.47.619354 PM US/CENTRAL 19-NOV-12 01.00.47.600000 PM US/CENTRAL
ESTEVENS EDS_JOB_4 15-NOV-12 08.23.42.000000 AM -06:00 22-NOV-12 08.23.42.400000 AM -06:00

4 rows selected.

SQL> spool off

On the flip-side, I was doing some head-to-head comparison with Oracle’s own SQL Developer. It does correctly specify the timezone (with no special action or selection on the part of the user) but unconditionally enforce case sensitivity on the name of the new job. So, SQL Nav stumbles on the time zone but correctly defaults object names to upper case, while Oracle’s SQL Developer correctly handles TZ but allows users to accidentally create mixed case object names …

Here’s a test result that clarifies how the two products handle timezone. notice the job names, indicating where they were created, and in the case of the SQL Nav, with and without specified start date.

SQL> select
2 owner
3 , job_name
4 , start_date
5 , last_start_date
6 , next_run_date
7 --, repeat_interval
8 from dba_scheduler_jobs
9 where owner=‘ESTEVENS’
10 order by owner, job_name
11 ;

OWNER JOB_NAME START_DATE LAST_START_DATE NEXT_RUN_DATE

ESTEVENS EDS_SQLNAV_WITHOUT_S 15-NOV-12 08.47.48.000000 AM -06:00 22-NOV-12 08.47.48.700000 AM -06:00
TARTDATE

ESTEVENS EDS_SQLNAV_WITH_STAR 15-NOV-12 08.47.12.000000 AM -06:00 22-NOV-12 08.47.12.000000 AM -06:00
T_DATE

ESTEVENS eds_sqldeveloper_job 15-NOV-12 08.46.21.303940 AM AMERICA/CHICAGO 19-NOV-12 08.46.21.300000 AM AMERICA/CHICAGO

3 rows selected.

SQL> spool off

Message was edited by: estevens_062