Time_zone

1. How to check  time zone ?

a db:

SQL> select dbtimezone from dual;

DBTIME
------
+00:00

@ Note that the database timezone is also included in both database_properties and props$ views:
@ SQL> SELECT property_name, property_value FROM database_properties WHERE property_name='DBTIMEZONE';
@ and
@ SQL> SELECT name, value$ FROM props$ WHERE name='DBTIMEZONE';
@ Be aware that you should not rely on these views because in case of db time zone
@ change, these views reflect the new db time zone too early: they should reflect
@ it only after database shutdown and restart.

SQL> SELECT property_name, property_value
  2  FROM database_properties
  3  WHERE property_name='DBTIMEZONE';

PROPERTY_NAME
------------------------------
PROPERTY_VALUE
------------------------------------------
DBTIMEZONE
00:00

SQL>
SQL> SELECT name, value$
  2  FROM props$
  3  WHERE name='DBTIMEZONE';

NAME
------------------------------
VALUE$
------------------------------------------
DBTIMEZONE
00:00

 

b session:

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
----------------------------------------------------
+08:00

2 built-in  SQL  function

SESSIONTIMEZONE returns the time zone of the current session. The return type is a time zone offset (a character type in the format '[+|]TZH:TZM') or a time zone region name, depending on how the user specified the session time zone value in the most recent ALTER SESSION statement.

DBTIMEZONE returns the value of the database time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement.

3 How to set the session time zone ?
The session time zone can be set to:
- O/S local time zone
- Database time zone
- An absolute offset
- A named region

1). The first method consists to use one of the following ALTER SESSION SET TIME_ZONE statements:
SQL> ALTER SESSION SET TIME_ZONE = local;
SQL> ALTER SESSION SET TIME_ZONE = dbtimezone;
SQL> ALTER SESSION SET TIME_ZONE = '-05:00';
SQL> ALTER SESSION SET TIME_ZONE = 'Europe/London';

2). As an alternative, the operating system environment variable ORA_SDTZ can also be used to set the session time zone:
ORA_SDTZ = 'OS_TZ' | 'DB_TZ' | '[+ | -] hh:mm' | 'timezone_region'

Example:
$ ORA_SDTZ='OS_TZ'
$ export ORA_SDTZ

$ ORA_SDTZ='-05:00'
$ export ORA_SDTZ

4. How to set the database time zone ?

Note that the database time zone is only relevant for TIMESTAMP WITH LOCAL TIME ZONE columns.

1). At creation time
SQL> CREATE DATABASE ...
SET TIME_ZONE='Europe/London';
If not specified with the CREATE DATABASE statement, the database time zone defaults to the server timezone offset.

CREATE DATABASE [ database ]
  { USER SYS IDENTIFIED BY password
  | USER SYSTEM IDENTIFIED BY password
  | CONTROLFILE REUSE
  | MAXDATAFILES integer
  | MAXINSTANCES integer
  | CHARACTER SET charset
  | NATIONAL CHARACTER SET charset
  | SET DEFAULT
      { BIGFILE | SMALLFILE } TABLESPACE
  | database_logging_clauses
  | tablespace_clauses
  | set_time_zone_clause
  }... ;

2). After database creation, use the ALTER DATABASE SET TIME_ZONE statement and then shut down and restart the database.
SQL> ALTER DATABASE SET TIME_ZONE = '-05:00';
SQL> ALTER DATABASE SET TIME_ZONE = 'Europe/Lisbon';
The change will not take effect until the database is bounced.

5. How to list the valid time zone regions ?

TZ_OFFSET returns the time zone offset corresponding to the argument based on the date the statement is executed. You can enter a valid time zone name, a time zone offset from UTC (which simply returns itself), or the keyword SESSIONTIMEZONE or DBTIMEZONE. For a listing of valid values for time_zone_name, query the TZNAME column of the V$TIMEZONE_NAMES dynamic performance view.Timezone region names are needed by the daylight savings feature. The region names are stored in two time zone files. The default time zone file is a small file containing only the most common time zones to maximize performance. If your time zone is not in the default file, then you will not have daylight savings support until you provide a path to the complete (larger) file by way of the ORA_TZFILE environment variable.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> SELECT count(*) FROM v$timezone_names;

  COUNT(*)
----------
      1393

---check whether you are using the large

SQL> SELECT distinct tzname FROM v$timezone_names where rownum<30;

TZNAME
----------------------------------------------------------------
Africa/Ceuta
Africa/Johannesburg
Africa/Cairo
Africa/Khartoum
Africa/Algiers
Africa/Casablanca
Africa/Djibouti
Africa/Freetown

已选择8行。

SQL>  SELECT TZ_OFFSET('Africa/Ceuta'), TZ_OFFSET('Africa/Johannesburg'),TZ_OFFSET('Africa/Cairo'), TZ_OFFSET('Africa/Khartoum'),TZ_OFFSET('Africa/Algiers'), TZ_OFFSET('Africa/Casablanca') FROM DUAL;

TZ_OFFS TZ_OFFS TZ_OFFS TZ_OFFS TZ_OFFS TZ_OFFS
------- ------- ------- ------- ------- -------
+01:00  +02:00  +02:00  +02:00  +01:00  +00:00

SQL>  SELECT TZ_OFFSET(SESSIONTIMEZONE), TZ_OFFSET(DBTIMEZONE) FROM DUAL;

TZ_OFFS TZ_OFFS
------- -------
+08:00  +00:00

6. How to use a larger set of defined time zones ?

different time zone files contain for each zone the offset from UTC, the transition times for daylight savings and abbreviation :

- ORACLE_HOME/oracore/zoneinfo/timezone.dat
This is the default and contains the most commonly used time zones. This is the smallest file.

- ORACLE_HOME/oracore/zoneinfo/timezlrg.dat
This file contains the larger set of defined time zones and can be used if you require time zones that are not defined in the default time zone file.

To enable the use of this larger time zone data file :

1). Shutdown the database

2).1 On UNIX platforms :
set the ORA_TZFILE environment variable to the full pathname of the location for the timezlrg.dat. By default, this should be $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat:

$ setenv ORA_TZFILE $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

2).2 On Windows systems :
You should add an ORA_TZFILE registry subkey for each of your Oracle Homes with the Windows Registry Editor:
Start -> Run...
Type "regedit", and click "ok"
Add or edit the following registry entry:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID\ORA_TZFILE
where ID is the unique number identifying the Oracle home.

Set the ORA_TZFILE to the full pathname of the location for the timezlrg.dat:
By default, the value should be %ORACLE_HOME%\oracore\zoneinfo\timezlrg.dat

3). Restart the database

Once the larger timezlrg.dat is used, it must continue to be used unless the user is sure that none of the non-default zones are used for data that is stored in the database. Also, all databases that share information should use the same time zone data file.

Be aware that you can neither create nore alter these timezone definition files !

----end----

refence：

http://hi.baidu.com/edeed/blog/item/2e4e304eb06ee6cfd1c86adf.html

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13750068/viewspace-718873/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13750068/viewspace-718873/