SYSDATE是我们在Oracle开发中经常要用到的一种单行函数(single row function),该函数用以返回当前的日期与时间，常和DUAL伪表一起合作。SYSDATE实际上指的是SYSDATE()函数，但是因为这个函数没有参量，所以这里可以省略()圆括号。另外SYSDATE函数是用于获取数据库所在的操作系统的当前时间值的，所以它和数据库或会话的时区没有关系。
我们可以使用NLS_DATE_FORMAT参数或者TO_CHAR函数来获得我们想要的SYSDATE日期格式，具体的格式代码如下：
| Format Code | Explanation |
|---|---|
| YEAR | Year, spelled out |
| YYYY | 4-digit year |
| MM | Month (01-12; JAN = 01). |
| MON | Abbreviated name of month. |
| MONTH | Name of month, padded with blanks to length of 9 characters. |
| D | Day of week (1-7). |
| DAY | Name of day. |
| DD | Day of month (1-31). |
| DDD | Day of year (1-366). |
| DY | Abbreviated name of day. |
| HH | Hour of day (1-12). |
| HH12 | Hour of day (1-12). |
| HH24 | Hour of day (0-23). |
| MI | Minute (0-59). |
| SS | Second (0-59). |
| SSSSS | Seconds past midnight (0-86399). |
具体的用法如：
syntax: select to_char(sysdate,'FORMAT CODES') from dual; select to_char(sysdate,'YEAR-MON-DAY HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'YEAR-MON-DAY HH24:MI:SS') -------------------------------------------------------------------------------- TWENTY ELEVEN-AUG-WEDNESDAY 21:34:43
与SYSDATE不同，CURRENT_DATE()函数会返回数据库会话所设置的本地时区的当前日期。
我们来具体看一下这2个函数所返回结果的差异:
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- www.askmaclean.com SQL> alter session set nls_date_format='YYYY-MM-DD HH24:mi:ss'; Session altered. SQL> set linesize 200 pagesize 1400; SQL> col sessiontimezone for a20 SQL> select current_date, sysdate,dbtimezone,sessiontimezone from dual; CURRENT_DATE SYSDATE DBTIME SESSIONTIMEZONE ------------------- ------------------- ------ -------------------- 2011-08-24 19:53:09 2011-08-24 19:53:09 +08:00 +08:00 /* 我们调整session的所在时区到+09:00 */ SQL> alter session set time_zone='+09:00'; Session altered. SQL> select current_date, sysdate,dbtimezone,sessiontimezone from dual; CURRENT_DATE SYSDATE DBTIME SESSIONTIMEZONE ------------------- ------------------- ------ -------------------- 2011-08-24 20:56:33 2011-08-24 19:56:33 +08:00 +09:00 /* 可以看到current_date所返回的时间增长了一个小时，而SYSDATE不变 */
另外请注意SYSDATE函数的类型并非是DATE类型，而是其特有的类型:
SQL> create table mytime(t1 date); Table created. SQL> insert into mytime values (sysdate); 1 row created. SQL> commit; Commit complete. SQL> select dump(t1), dump(sysdate), dump( current_date) from mytime; DUMP(T1) -------------------------------------------------------------------------------- DUMP(SYSDATE) -------------------------------------------------------------------------------- DUMP(CURRENT_DATE) -------------------------------------------------------------------------------- Typ=12 Len=7: 120,111,8,24,21,31,59 Typ=13 Len=8: 219,7,8,24,20,32,12,0 Typ=13 Len=8: 219,7,8,24,20,32,12,0 以上可以看到sysdate和current_date都属于TYPE=13，而普通DATE类型为TYPE=12
数据类型type=12代表DATE Valid date range from January 1, 4712 BC to December 31, 9999 AD.
因为sysdate的时钟取自操作系统，所以DBA同样需要关心操作系统本身时区的设置。不当的时区设置可能导致数据库内部的定时作业系统紊乱，主要会影响使用DBMS_JOB调用的一些JOB，DBMS_JOB包不会考虑到时区的因素，而仅仅考虑sydate的值(DBMS_JOB uses the “date” datatype to store the start date/time and does not store/use timezone related information. JOBS who are scheduled are always executed when sysdate => next execution time. )。如果使用DBMS_SCHEDULER则会考虑具体的时区。
此外一些使用夏令时的地区还会面临更复杂的DST问题，详细可以参考ORAganism的文章<Fake lack of TimeZones in DBMS_JOBs>和MOS文档<DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained. [ID 467722.1]> &<Troubleshooting DBMS_SCHEDULER and DBMS_JOB [ID 783357.1] >:
DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained. [ID 467722.1]
Applies to:
Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 11.2.0.2 - Release: 9.0.1 to 11.2
Information in this document applies to any platform.
Purpose
This note gives a overview of how DBMS_SCHEDULER / DBMS_JOB react on DST transitions and what
to know when using timezone information with jobs.
Scope and Application
To be used when DBMS_SCHEDULER jobs run at a wrong time after a DST change or you have output
from jobs selecting from TIMESTAMP WITH LOCAL TIMEZONE columns that is not correct.
If things about timezones are not clear please have first a look at Note 340512.1 Timestamps
& time zones - Frequently Asked Questions.
DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained.
When running trough DBMS_SCHEDULER or DBMS_JOB a job output using TIMESTAMP WITH LOCAL
TIMEZONE gives the wrong time.This happens when you use DBMS_SCHEDULER or DBMS_JOB to run jobs
who select information from TIMESTAMP WITH LOCAL TIMEZONE fields.
The time in a TIMESTAMP WITH LOCAL TIMEZONE that will be returned depends on the SESSIONTIMEZONE.
select sessiontimezone from dual;
If a TIMESTAMP WITH LOCAL TIMEZONE field contains for example "20-DEC-2007 15:15 -08:00" and
your session timezone is also -08:00 you will see 15:15.
If you change the session timezone to (for example) -07:00 then you will see 16:15,
witch is normal and intended.
Jobs using DBMS_SCHEDULER and DBMS_JOB however use a SESSIONTIMEZONE set to UTC (= +00:00 ).
Hence, above example will give 15 + 8 = 23:15 when selected trough a job.
If you use jobs that provide TIMESTAMP WITH LOCAL TIMEZONE then you may need to
included in the job an alter session to change the SESSIONTIMEZONE.
execute immediate 'alter session set time_zone = ''-08:00''';
If you want the job to follow DST rules then you need to use a named timezone that
follows the DST rules you want to use:
execute immediate 'alter session set time_zone = ''US/Pacific''';
There is Enhancement Request 5479680 - INCORPORATE SESSIONTIMEZONE INFORMATION INTO
JOB INFORMATION to a) use the session timezone of the submitting session as timezone and b)
have a way to see with what timezone a job is submitted.
Job runtime time after DST has changed.
When using DBMS_JOB:
DBMS_JOB uses the "date" datatype to store the start date/time and does not store/use timezone
related information. JOBS who are scheduled are always executed when sysdate => next execution time.
This means for DST transitions that:
* Assuming here that during DST the time is going FORWARD from 1:59 am directly to 3:00 am (skip on hour)
- any job scheduled between 2:00 am and 2:59 am will only be started at 3:00 am seen the period between
2:00 am and 2:59 am does not exist. This means ALL jobs scheduled between 2:00 am and 2:59 am are started
at 3:00 am at once.
* Assuming here that during DST the time is going BACK from 2:59 am to 2:00 am (= add one hour) the
jobs will be executed in the "first" 2:00 am - 2:59 am timeslot, if the interval is more then
one hour then the next execution will simply the correct time - aldo the actual "absolute"
interval will be one hour more then normal. If the "interval" is less then one hour then they will
NOT be executed the "second time" time the 2:00 am- 2:59 am timeslot is there because the "next execution time"
will be > 2:59 am. So if you have for example a job that runs every 5 minutes this will NOT be executed
during the "second time" the 2:00 am - 2:59 am timeslot is there until "locale time" becomes 3:00 am (or later).
Please also note that the next execution time is calculated AFTER the job has finished.
A known issue: Note 1328496.1 Ora-01878: Specified Field Not Found In Datetime Or Interval in
Alert.log after DST change When using DBMS_SCHEDULER.
DBMS_SCHEDULER uses a TIMESTAMP WITH TIMEZONE field to store the starting time (and other time related
information) of a job, unlike DBMS_JOB who uses a "date" column who has no concept of timezones.
When checking the various DBMS_SCHEDULER time fields make sure you are using a NLS_TIMESTAMP_TZ_FORMAT
that display's the timezone information
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
or for US customers:
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='YYYY/MM/DD HH24:MI:SS TZR TZD';
By using the "TZR TZD" mask you can quickly see in what format the data is stored/defined:
select start_date, repeat_interval, last_start_date, next_run_date from dba_scheduler_jobs where job_name='';
* If it returns a offset (ex: -08:00) then the job time is defined with a offset.
Timezone offsets are by nature NOT "DST aware" seen they mean a fixed offset from UTC.
So any job using a offset as timezone will run after a DST change at a wrong time.
* If it returns a named timezone (ex: Japan ) then the time is defined with
that timezone name but that timezone has no DST rules.
Some named timezones are also not "DST aware" ( Japan for example) because that region simply does not use DST.
* If it returns a named timezone and a DST prefix (ex: US/Pacific PST )
then the time is defined with that timezone name and that timezone is DST aware.
To have a job that automatically adjusts for DST you need to make sure it's defined
with the proper named timezone name that actually uses DST rules.
The DST information in Oracle is sometimes updated, when using a named timezone please check
Note 412160.1 Updated Time Zones in Oracle Time Zone File patches.
that note has a list of named timezones that changed and so you can see if you need to
update the Oracle DST information.
Please note that it's a good idea to update the Oracle DST definitions when using named
timezones but that applying "a DST patch" will not always solve all scheduler problems.
Most "scheduler dst" problems are due the usage of a offset, not a named timezone, when creating the jobs.
Good to know:
* In oracle the session timezone (select sessiontimezone from dual;) defaults to a offset ( like +05:00),
even if the Operating system "TZ" variable is set to a named TZ,
unless the ORA_SDTZ is set in the client (!) environment (or registry) with a *oracle* TZ name.
* DBMS_SCHEDULER has no relation to or does not use the database timezone.
When you submit a job using DBMS_SCHEDULER Oracle will use a different source to populate the timezone depending on:
a) When start_date is NULL, DBMS_SCHEDULER will determine the time zone for the repeat interval as follows:
1. It will check whether the session time zone is a region name. The session time
zone can be set to a named timezone by either:
* Issuing an ALTER SESSION statement, for example:
SQL> ALTER SESSION SET time_zone = 'Asia/Shanghai';
* Setting the ORA_SDTZ environment variable on the client (!) side.
2. If the session time zone is an absolute offset instead of a region name, the Scheduler
will use the value of the DEFAULT_TIMEZONE Scheduler attribute.
SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','US/Eastern');
PL/SQL procedure successfully completed.
To see the current defined DEFAULT_TIMEZONE Scheduler attribute you can issue:
SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
Session altered.
SQL> select dbms_scheduler.stime from dual;
STIME
---------------------------------------------------------------------------
06/03/2008 11:58:06 US/EASTERN EST
3. If the DEFAULT_TIMEZONE attribute is NULL, the Scheduler will use the time zone of
systimestamp when the job or window is enabled .The timezone returned by systimestamp is always a OFFSET.
Conclusion: make sure your DEFAULT_TIMEZONE Scheduler attribute is set, and check the
sessiontimezone if needed seen this may take precedence.
Note: SELECT DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME FROM DUAL;
is not related to the DEFAULT_TIMEZONE.
DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME provides the TimeZone set on the OS level trough the
TZ variable, it will return the named timezone only if the OS TZ setting is also known in Oracle.
Only when no OS TZ variable is set it will report the DEFAULT_TIMEZONE.
b) When start_date is not NULL then DBMS_SCHEDULER will use:
1. the timezone specified in the start time
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
begin
DBMS_SCHEDULER.DROP_JOB ('GIVETZ_TEST');
end;
/
begin
dbms_scheduler.create_job('GIVETZ_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>'19/11/2007 12:00:00 CET', enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');
end;
/
select start_date, repeat_interval, last_start_date, next_run_date from
dba_scheduler_jobs where job_name='GIVETZ_TEST';
begin
DBMS_SCHEDULER.DROP_JOB ('GIVETZ_TEST');
end;
/
begin
dbms_scheduler.create_job('GIVETZ_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>'19/11/2007 12:00:00 +01:00', enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');
end;
/
select start_date, repeat_interval, last_start_date, next_run_date from
dba_scheduler_jobs where job_name='GIVETZ_TEST';
2. The SESSION timezone when not specified in the start time.
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET TIME_ZONE = '+01:00';
begin
DBMS_SCHEDULER.DROP_JOB ('NODTZ_TEST');
end;
/
begin
dbms_scheduler.create_job('NODTZ_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>'19/11/2007 12:00:00', enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');
end;
/
select start_date, repeat_interval, last_start_date,next_run_date from
dba_scheduler_jobs where job_name='NODTZ_TEST';
ALTER SESSION SET TIME_ZONE = 'CET';
begin
DBMS_SCHEDULER.DROP_JOB ('NODTZ_TEST');
end;
/
begin
dbms_scheduler.create_job('NODTZ_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>'19/11/2007 12:00:00', enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');
end;
/
select start_date, repeat_interval, last_start_date,next_run_date from
dba_scheduler_jobs where job_name='NODTZ_TEST';
3. The SESSION timezone when using CURRENT_TIMESTAMP
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET TIME_ZONE = '+01:00';
begin
DBMS_SCHEDULER.DROP_JOB ('CURRTS_TEST');
end;
/
begin
dbms_scheduler.create_job('CURRTS_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>current_timestamp, enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');
end;
/
select start_date, repeat_interval, last_start_date, next_run_date from
dba_scheduler_jobs where job_name='CURRTS_TEST';
ALTER SESSION SET TIME_ZONE = 'CET';
begin
DBMS_SCHEDULER.DROP_JOB ('CURRTS_TEST');
end;
/
begin
dbms_scheduler.create_job('CURRTS_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>current_timestamp, enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');
end;
/
select start_date, repeat_interval, last_start_date, next_run_date from
dba_scheduler_jobs where job_name='CURRTS_TEST';
4. The SYSTIMESTAMP offset when using SYSTIMESTAMP
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET TIME_ZONE = 'CET';
begin
DBMS_SCHEDULER.DROP_JOB ('SYSTIME_TEST');
end;
/
begin
dbms_scheduler.create_job('SYSTIME_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>systimestamp, enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=00');
end;
/
select start_date, repeat_interval, last_start_date, next_run_date from
dba_scheduler_jobs where job_name='SYSTIME_TEST';
The timezone returned by systimestamp is always a OFFSET.
5. the SESSION timezone when using SYSDATE.
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET TIME_ZONE = '+01:00';
begin
DBMS_SCHEDULER.DROP_JOB ('SYSDATE_TEST');
end;
/
begin
dbms_scheduler.create_job('SYSDATE_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>sysdate, enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=00');
end;
/
select start_date, repeat_interval, last_start_date, next_run_date from
dba_scheduler_jobs where job_name='SYSDATE_TEST';
ALTER SESSION SET TIME_ZONE = 'CET';
begin
DBMS_SCHEDULER.DROP_JOB ('SYSDATE_TEST');
end;
/
begin
dbms_scheduler.create_job('SYSDATE_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>sysdate, enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=00');
end;
/
select start_date, repeat_interval, last_start_date, next_run_date from
dba_scheduler_jobs where job_name='SYSDATE_TEST';
So the best option if to always specify a full start date with a timezone name that uses
the DST rules you expect or to use for example CURRENT_TIMESTAMP and set the session timezone
first to the timezone name that uses the DST rules you expect.
You can also redefine existing jobs if you notice they are defined with a offset.
A example using a explicit named timezone:
begin
dbms_scheduler.set_attribute('SYSDATE_TEST','start_date',to_timestamp_tz
('Europe/London 12:00 20-NOV-2007','TZR HH24:MI DD-MON-RRRR'));
end;
/
select start_date, repeat_interval, last_start_date, next_run_date from dba_scheduler_jobs
where job_name='SYSDATE_TEST';
HOW TO SET UP TIMEZONES ON UNIX =========================================================================== Environment: UNIX Generic Category: Install / Kernel Subject: Timezone Setup Author: Steve Essery Date: 12-Aug-92 Keywords: Timezone, TZ, Sysdate, Time Abstract: How to set up timezones correctly, and some of the effects if this is done incorrectly =========================================================================== Unix has a pretty good idea about timezones and if set up correctly and left alone will do the hard work for you. Unfortunately, this is usually not the case. The usual scenario is as follows. The clocks have gone forward (or backward) and the system clock, for some reason, is now out of step with reality. So the system administrator moves the system clock with the date command. This however, can have untoward side effects with programs that are already running. The root cause is that the machine has not be set up properly to deal with the shifts in time and no one can be bothered to correct the setup. They assume that they have to alter the time by hand, as they do with their own watches. Even more confusion can be caused if the timezone information is not correctly specified with the system then reverting to reporting the time with respect to some hard-to-determine default timezone. With respect to Oracle, these problems are usually noticed because the database is reporting a different time to the system clock. That is, either sysdate or the time reported on the banners of the products are apparently incorrect. These problems can usually be attributed to the incorrect setup of the timezone information. For example, on a System V machine as you will see later, the timezone information can be quite complex and, if incorrect, the system cannot simply fail to return a time; some sort of value must be returned. It is this default action that causes the confusion. If this happens, look at the time being returned. It is usually out of step by a value equal to the timezone's offset from the Greenwich Meridian. Or worse still, the timezone information may be specified correctly with respect to syntax, but may be semantically incorrect, ie setting up the timezone to be 999 hours east of Greenwich, which is legal, but nonsensical. As is normal in the Unix world, distinctions can be drawn between the BSD and System V flavours and their hybrids. A simple statement would be that System V systems use just an environment variable and BSD/System V hybrids use both an environment variable and system files. However, it must be pointed out that these distinctions tend to blur when examined closely as each manufacturer adds their 'personal' touches. For detailed information on the various associated commands you should refer to your system's documentation. Possible entries to look at are timezone, date, ctime, gettimeofday and zic. System V On these machines the TZ environment variable is used to completely specify the timezone, no use is made of timezone rule files. Please refer to your system's documentation for precise syntax details. Typically the TZ environment variable takes the form: stdoffset[dst[offset][,start[/time],end[/time]]] Where the various portions of the string have the following meaning: std and dst Three or more characters that are used to designate the standard (std) or summer (dst) time zones. Only std is required; if dst is missing then summer time does not apply in this zone. offset This is the value which should be added to the local time to arrive at the Coordinated Universal Time (CUT). The offset takes the form: hh[:mm[:ss]] The offset following std is required. If no offset follows dst then summer time is taken to be one hour ahead of standard time. The offset can be signed, positive values are west and negative values east of the Prime Meridian. start and end These indicate when to change to and back from summer time, ie start specifies when summer time begins and end when it ends. The format of these times can take one of the following: Jn The Julian day (1<=n<=365). Leap days are not counted, so February 28 is J59 and March 1 is J60. February 29 can not be referenced. n The zero based Julian day (0<=n<=365). Leap days are counted and February 29 can be referenced. Mm.n.d The nth d day of month m (1<=n<=5, 1<=m<=12, 0<=d<=6) When n is 5 it refers to the last d day of the month m. Day 0 is Sunday. time The time field describes the time when the changes to and from summer time occur. The time is always specified in the current time. If no time is given it defaults to 02:00:00, in addition no leading sign (+ or -) is allowed. NB If the start and end times are not specified then the points when summer time comes in and out of effect depends on the implementation of the operating system. Typically, these days are the first Sunday in April and the last Sunday in October. In addition, the use of Julian days to specify the time changes means that the timezone information must be updated every year. This is because the changes usually happen on a Sunday, and there is no guarantee that the Sunday will fall on the same Julian day, eg the first Sunday in April is the 5th in 1992 and is J96 in 1992, in 1993 it is the 4th, and is J95. The SQL statement, SELECT TO_CHAR(TO_DATE('04-APR-92'),'DDD') FROM DUAL; can be used to determine the Julian day (1<=n<=366). Note that an incorrect use is being made of 'Julian day' as this is not the day's position in the year, but should be in actual fact the number of days since January 1st, 4712 BC! So an example of a simple timezone could be: GMT0BST which is a value that could be used for the United Kingdom. A more complete specification of the timezone is: GMT00:00:00BST-01:00:00,M3.5.0/02:00:00,M10.4.0/02:00:00 which can be simplified by accepting the default values to: GMT0BST,M3.5.0,M10.4.0 Some systems also require the use of quotes around the timezone value. Note that the GMT0BST specification is not accurate enough as the times when summer time goes in and out of effect are typically not the same as the default times. British summer time begins on the last Sunday in March and ends on the first Sunday in October which is later than the 23rd, ie the fourth Sunday in October as it is possible to have five Sundays in October (eg in 1993). Therefore, using GMT0BST as the specification of the timezone could lead to the system clock moving to and from summer time after the changes actually took place. BSD/Sys V Hybrids On these machines (Sun, Dec Risc-Ultrix for example) the TZ environment variable, when set, refers the system to a file containing the rules for the particular timezone chosen. The variable either refers to the file using an absoulte path, eg /usr/lib/zoneinfo/EST, or just to the file which is taken to be relative to the system time information directory. When TZ is not set then a file called localtime is looked for. Usually, this has been created during installation or by the system administrator simply by copying the file which refers to their timezone to localtime. If the pre-generated timezone files do not include your timezone then a set of source files are also provided which can be compiled by the zic command. Details of the format of these source files is not given here as a complete set of timezone source files are provided. However, if the System V format of TZ is used on a hybrid machine then some confusion can result. This stems from the system's behaviour when it cannot find the timezone file specified, ie if TZ is set to PST, then, if the timezone file PST exists (as it usally does) the correct time will be reported. However, if TZ is set to PST8PDT and no such file exists, then the system will revert to some default timezone, possibly determined from the kernel or simply just GMT. In these cases it is necessary to create such a file and this is where some alteration of the timezone source files is necessary. How do we go about creating the new file? It is not simply a case of renaming an existing file, eg if TZ is going to be set to PST8PDT, the file PST cannot be renamed PST8PDT, as this file does not contain information about the PST8PDT timezone. Instead, a LINK is added to the source file, in this case northamerica, which links the new TZ name to an existing zone in the file. In this example the line which needs to be added, usually at the end of the file, is: Link US/Pacific PST8PDT where US/Pacific is the timezone defined in the file and PST8PDT is the name of the file we wish to create. The file can be recompiled using zic and the PST8PDT file will be created. Now, a couple of examples of how the time is determined on some systems. This list is definitely not meant to be exhaustive as it would take too long to check this information against every machine Oracle runs against. Sun (BSD/Sys V) The timezone rule directory is "/usr/share/lib/zoneinfo". The date command will report the system's current time using the value of TZ as the name of the timezone. If TZ is not set at all then date first looks for a file called "localtime". If that doesn't exist, then it looks for the GMT file. And if that doesn't exist, it reports the current system time as GMT. Dec Risc-Ultrix (BSD/Sys V) The timezone rule directory is "/etc/zoneinfo". If the file specified by TZ does not exist (or involves a circular reference, ie TZ=GMT and the file GMT refers to the file GMT,...) or if TZ is not set and the "localtime" file does not exist, then the timezone specified when the Unix kernel was built is used. This value takes the form: x dst y where x is the offset from the prime meridian, and y the amount of daylight saving to apply when summer time is in effect. SCO (Sys V) SCO Unix provides a utility, "/etc/tz" which can be used to construct timezones. The TZ environment variable is set in the "/etc/TIMEZONE" file, as with all System V Release 4 systems. With SCO, this file must exist as it is used during the boot procedure to set the TZ for the system. Please refer to the manual page for timezones for the various formats which can be used. Sequent DYNIX/ptx (Sys V) Again, the TZ environment variable is setup in "/etc/TIMEZONE". This file is read by "/etc/profile" during the login procedure to setup the user's timezone. IBM RS/6000 AIX 3.2.X (Sys V) The TZ environment variable is setup in "/etc/environment", which is used to setup the user's environment on logon. The settings in this file can be overridden by the user's "$HOME/.env" file.Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 11.2.0.0. – Release: 10.2 to 11.2
Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 11.2.0.0. [Release: 9.2 to 11.2]
Information in this document applies to any platform.
Purpose
The aim of this document is to provide a Troubleshooting Guide for the Oracle Job system including:
With References to potential problems and best practices for
Last Review Date
April 17, 2009
Instructions for the Reader
Troubleshooting Details
Oracle Jobqueue and Scheduler Troubleshooting
Instructions to the Reader
This Troubleshooting Guide is provided to assist you in solving Oracle Scheduler and Jobqueue issues in a structured manner. The method is based on Oracle Diagnostic Methodology and helps you to avoid trial and error approach. It guides you through a step-by step method. Diagnostic tools, examples and general questions are included in the document to assist in the different troubleshooting steps.
Step-by-Step approach
ISSUE CLARIFICATION
In the issue clarification section it is important to describe the problem as best as possible. What is the problem you need to solve? At the end of the process you should be able to come back to this section to verify if the root cause was found and the solution was provided.
WHAT IS the problem we are looking at?
Examples of problem descriptions:
STANDARD_ERROR=”Oracle Scheduler error: Config file is not owned by root or is writable by group
or other or extjob is not setuid and owned by root”
Running the shell script from OS level works without problems
ORA-23421: job number <jobid> is not a job in the job
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06512: at “SYS.DBMS_JOB”, line 526
ORA-06512: at “SYS.DBMS_JOB”, line 169
ORA-06512: at line 1
Note: Problems can be caused due to version issues, wrong setups and incorrect usage or other issues. The next steps will help to clarify where the problem exactly resides.
Following questions will help to confirm set up, environment and get good background information surrounding the issue.
ISSUE VERIFICATION
When you have a starting problem description it is time to collect facts in a structured way. First step would be to get an overview of all the facts we have. What information do we have already available?
The answers to the questions below can potentially help you to solve the problem.
Please note that for Oracle Jobs, it is important to distinguish between the usage of the conventional Job queue (DBMS_JOB) and the usage of the Scheduler (DBMS_SCHEDULER). DBMS_JOB is mainly used in Oracle version 9i and lower and has been replaced by the Scheduler, introduced in Oracle version 10.
Note: It is still possible to use DBMS_JOB in Oracle version 10.
Depending on this, questions can be more relevant to the one area, than to the other. For these reasons the following Key / Guidelines can be followed:
(JOB) Questions related to DBMS_JOB issues.
Issues related to the usage of the conventional job queue (dbms_job) for example like incorrect setups, not running jobs, incorrect privileges, spinning jobs, erroring jobs, etc.
(SCHED) Questions related to DBMS_SCHEDULER issues.
Issues related to the usage of the Oracle Scheduler (dbms_scheduler) introduced in Oracle version 10. Providing a much more complex structure with new features and applications than the conventional job system. Issues are for example incorrect setups, not running scheduled jobs, chaining, windows usage, external jobs, erroring jobs, etc.
What is the problem / What is not a problem ?
What 5-digit version of Oracle software reproduces the problem?
What 5-digit version of Oracle software works?
Is this standalone database or RAC database?
What products reproduce the issue? (SQLPLUS, EM ?)
What products do not reproduce the issue?
What are the database parameters in use?
What are the possible errors generated?
What is executed with the job? Execution of PLSQL-Blocks, Procedures, Programs, etc?
What are the exact symptoms?
What job specialties are involved?
(JOB) Intervals etc?
(SCHED) Intervals, Chaining, Windows , External Programs, etc?
When is the problem seen / when is the problem not seen?
Is the problem constant?
Is the problem reported intermittently?
When did the problem start to fail?
Has this ever worked or is this a fresh setup?
Has something been changed in the job setup, OS- and or DB-environment?
Is there any pattern to the failure?
For example in date, time? During peak load? When a batch job is run? When certain SQL statement used? When special parameters used or not used?
How long does the problem last?
When exists, what possible workaround exists or what resolves the issue?
Where is the problem / Where is the problem not?
Which job system is in use, DBMS_JOB and/or DBMS_SCHEDULER?
Problem reproduces with DBMS_JOB or DBMS_SCHEDULER or both?
Reproducible with a small testcase showing the issue?
What (parameter / arguments / attribute) settings, if any, allow the job to work?
Problem reproduces when specific job setup is used, for ex. special intervals, chains, etc?
Problem reproduces when same job setup configured on another Database / Server?
Problem reproduces when commands executed by the job are executed outside the job system?
(JOB) Does the job run manual using dbms_job.run?
(JOB) When possible to configure, does the problem reproduce when the setup is transferred to dbms_scheduler ?
(JOB) Does the Scheduler (dbms_scheduler) work?
(SCHED) Does the job run manual using dbms_scheduler.run_job?
(SCHED) Do the conventional jobs (dbms_job) work?
(SCHED) When possible to configure, does the problem reproduce when the setup is transfered to dbms_job system?
(SCHED) What global attributes are in use when the problem occurs?
What changes (OS-patches, DB-patches, job setup etc ) happened around time first failure seen ?
CAUSE DETERMINATION
The facts listed in the ISSUE VERIFICATION are the starting point for the CAUSE DETERMINATION:
There are 3 main approaches to take here:
Examples
What changes happened around time the first failure / problem was reported?
The output will be a list of potential reasons causing the symptoms: can be a bug, a configuration setting, a conflict with other software, …
CAUSE JUSTIFICATION
Evaluate the causes: check the causes against the facts (the IS and the IS NOT observations). This also includes checking the symptoms of the problem against any bug rediscoveries identifying a problem. List potential assumptions you have made. Determine the most probable cause (often the one with the least assumptions or with the most reasonable assumptions).
For the most probable cause, verify the assumptions and turn them into facts (document them in the Issue Verification part). Some examples:
If this cause would be true then another sequence of actions would result in a different outcome: let’s test this via an extremely simplified internal test case. Please note that in the area of Scheduler / Jobs a testcase will often help solving the issue or finding out a solution with a different job setup, because there are more ways to build up a job structure. A guideline for setting up a simple testcase is shown in
Note 807159.1
If the verification fails, go to the next probable cause and repeat the verification.
If no potential cause stands the justification process, go back to the issue verification and collect more detailed facts. Further diagnostics can be verified. See below in the section Diagnostic Tools.
POTENTIAL SOLUTIONS
A brief description of the corrective actions that will remove the cause of the problem: in some cases there is only one solution linked to the cause. But in many cases, there are more. Example: install a patch to remove the bug from the system, avoid the problem by creating a different job setup, or avoid the problem by setting some parameters.
POTENTIAL SOLUTION JUSTIFICATION
Explain why the proposed solution solves the problem.
Diagnostic Tools
Each area requires a specific diagnostic path to be followed in most cases:
Generic Diagnostics:
To identify the affected job please follow:
Note 744645.1 How to find the job name if a scheduled job fails with ORA-12012
Unexpected behavior or errors may be located in the user commands executed by the job. Jobs can execute different types of commands, like PLSQL-programs, procedures, external shell scripts and more. Depending on the problem it is useful to test the successful execution of these commands outside the jobs to confirm that the problem is located in the job usage.
Problems occurring after changes in a job setup or with a fresh creation of a job setup often implies showing the behavior using a small testcase to reproduce the issue. Even when building up a testcase is often time consuming, it is often a milestone in resolving the issue.
For examples on how to build a testcase please follow:
Note 807159.1 Samplecode and Select Statements to build up simple Testcases for Scheduler / Jobs
According to problem situation combine the SQL commands to build up a simple job structure depending on the objects needed. Replacing large and complex job setups, minimizing the job structure to the only relevant part where the problem is located in will result in a simple testcase, where the behavior / problem can be shown in an easy way and a possible workaround can be found.
Generic Tracing:
When the problem in the job structure can not be found in an easy manner it is often recommended to activate sql tracing with setting the event 10046.
To produce a valuable result set timed_statistics to true and max_dump_file_size to unlimited before executing the job.
For example:
SQL> alter session set max_dump_file_size=unlimited;
SQL> alter session set timed_statistics = true;
SQL> alter session set events ‘10046 trace name context forever, level 4’;
SQL> BEGIN
DBMS_SCHEDULER.drop_job (‘runtest_job’,TRUE);
end;
/
Please note that setting the event into the parameter file, each process will have a trace produced. Optimal tracing should be to enable tracing within a session, reproduce the problem and then disable tracing.
Ensure that only matching traces are uploaded.
Details on how to diagnose the 10046 trace can be found in Note 376442.1 and
Note 39817.1
(SCHED)
For the Scheduler, additional tracing has been introduced with setting the attribute logging_level for the jobs and with using an event to dump further diagnostics for the job coordinator and the job slaves:
Note 461304.1 How to activate tracing for the scheduler
Diagnostics in Scheduling:
Common problems are that jobs do not start at the expected time or won’t start automatically.
As a first diagnosis please confirm that the job runs successfully with an explicit run (dbms_job.run or dbms_scheduler.run_job). This will exclude a configuration problem by the job itself.
When an explicit execution of the affected job is successful then diagnose the automatic scheduling process:
(JOB):
– ensure that when dbms_jobs is used the parameter job_queue_processes has to be set > 0
This will start job slave processes which are responsible for the automatic execution of jobs.
– verify the values in the columns LAST_DATE, NEXT_DATE, FAILURES, BROKEN of DBA_JOBS for the affected job. When sysdate > NEXT_DATE then the next execution is in the past, and the job is not chosen for a future run.
– verify the jobs is in the view DBA_JOBS_RUNNING.
When the job is existent in this view previous run of same job has not completed yet.
(SCHED):
Until version Oracle Database version 11.1 , the setting of the parameter JOB_QUEUE_PROCESSES is not mandatory for the Scheduler. The Job Coordinator will start job slaves on demand, to work on the job queue table. So setting JOB_QUEUE_PROCESSES to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were unaffected and would still run.
Starting with Oracle Database 11g Release 2 (11.2), setting
JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and DBMS_JOB jobs to not
run. This has been implemented to provide the possibility to stop all the jobs in a clean way.
Check the columns STATE, ENABLED, NEXT_RUN_DATE from DBA_SCHEDULER_JOBS.
Collect the information from the Scheduler logs:
DBA_SCHEDULER_JOB_LOG, DBA_SCHEDULER_JOB_RUN_DETAILS
Incorrect scheduling often comes along with timezone changes, DST patches and upgrades:
For DST diagnosis / Timezone issues and Clock Changes follow the guidelines of:
Note 467722.1 DBMS_SCHEDULER And Time Zones ( DST ) Explained.
Note 415912.1 Identifying And Adjusting Incorrect Data After Upgrading Database Time Zone Files Without Using Correct Pre And Post-Install Actions
Upgrade issues:
Note 731678.1 Scheduled jobs do not run as expected after upgrading to either 10.2.0.3 or 10.2.0.4
Further diagnostics may be required to come to a valuable solution.
See the following notes:
@ Common:
@ Unpublished Note 459189.1 How to stop all dbms_scheduler jobs
(JOB)
Note 75070.1 ORA-12012: when executing a job (ORA-6512, ORA-6550)
Examples:
(JOB)
Note 61730.1 Using the DBMS_JOB Package
Note 1068369.6 Example: Using DBMS_JOB.SUBMIT to Execute Jobs at Regular Intervals
Note 312547.1 Advanced use of DBMS_JOB.SUBMIT ‘interval’ Parameter
Note 732939.1 How To Stop User From Creating Dbms_job. User Does Not Have Create Job Privilege
(SCHED)
Note 807159.1 Samplecode and Select Statements to build up simple Testcases for Scheduler / Jobs
Note 727499.1 Sample how to create a job running chains
Note 1300444.1 How to create a event based chained job
Window issues:
Note 742683.1 Scheduled Job Works Does Not Start In Window.
Purging Diagnostics
Note 443364.1 How To Purge DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_WINDOW_LOG
Note 749440.1 Dbms_scheduler.Purge Not Removing Entries from dba_scheduler_job_run_details
Schedule issues:
Note 312547.1 Advanced use of DBMS_JOB.SUBMIT ‘interval’ Parameter
Note 428872.1 Scheduled Jobs Do Not Run After A Re-Start With Repeat_interval => Null.
Issues with Statistic Jobs:
Note 430636.1 GATHER_STATS_JOB Is Not Running
Note 377143.1 How to check what automatic statistics collection is scheduled on 10g
Note 803191.1 Checklist To Diagnose Issues Related To Automatic Run Of
Job GATHER_STATS_JOB
Issues with external Jobs:
http://forums.oracle.com/forums/thread.jspa?threadID=555102
NOTE:389685.1 – Items to Check when Problems Running Executables through the Scheduler
NOTE:976049.1 DBMS_SCHEDULER fails with ORA-27369 and
STANDARD_ERROR=”Launching external job failed: Invalid username or password”
NOTE:1300215.1 ORA-27369: job of type EXECUTABLE failed with exit code: 274667
Please note that this article will guide through the correct analysis for Scheduler/Job Problems.
It is also practicable to go through a list of known issues / bugs which will be listed in
NOTE:1311355.1 Known Issues And Bugs With Using the Scheduler
This article is currently under edit and will be made publish in the near future
Errors