了解SYSDATE函数

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.askmac.cn

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';

Posted

in

by

Tags:

Comments

2 responses to “了解SYSDATE函数”

  1. admin Avatar
    admin
    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.
    
  2. admin Avatar
    admin

    Troubleshooting DBMS_SCHEDULER and DBMS_JOB [ID 783357.1]

    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:

    • Jobqueue Issues (dbms_job)
    • Scheduler Issues (dbms_scheduler)

    With References to potential problems and best practices for

    • Setup of jobs
    • Usage of intervals
    • Build up testcases for jobs and scheduler
    • Running/scheduling and nls problems
    • Window groups, chains, (dbms_scheduler)
    • Scheduler tracing
    • Common errors
    • External jobs

    Last Review Date

    April 17, 2009

    Instructions for the Reader

    A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

    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:

    • DBMS_SCHEDULER: Running a OS shell program with 10.2.0.4 Oracle Scheduler erroring with the message ORA-27369: job of type EXECUTABLE failed with exit code: 274662
      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
    • DBMS_JOB: removing a job with version 9.2.0.8 dbms_job.remove(<jobid>) will fail with
      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:

    1. Use your experience to list possible causes. List the assumptions that need to be checked in this case.
    2. Start searching for possible causes in My Oracle Support or other Oracle knowledge bases. Use the facts collected above to refine your search criteria.
    3. Analyze the facts on differences between the working situation and non working situation: Depending on the answers from the questioning above and further investigation from the troubleshooting guides you should be able to list what is different, special, unique between the IS and the IS NOT and also see what is changed and when.

    Examples
    What changes happened around time the first failure / problem was reported?

    • New software / Patches, DST patches installed?
    • DB upgrade done?
    • Created a fresh job environment /setup?
    • Change in the job setup done and could have an impact between a possible working situation and the current situation?
    • What is different between working jobs and those that do not work?

    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 bug XXX then we expect an upgrade from version x to y happened before the symptoms started
    • This looks like same issue as described in note XXX but then we expect to be reproducible on the system when using the example of note XXX.

    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:

    • Collection of the relevant information using selects from the dictionary views, showing the current states, used attributes, timings and logs of the affected job structure.
    • Alert.log, user trace files, Job and Job coordinator trace files from the dump destinations
    • RDA

    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

    Keywords


    DBA_JOBS; DBMS_SCHEDULER

    Errors


    ORA-27369; ORA-6512; ORA-12012; ORA-23421; ORA-6550

     

Leave a Reply to admin Cancel reply

Your email address will not be published. Required fields are marked *