ORACLE中的時區
1. How to check the session time zone
?
=======================================
Answer
------
The new SESSIONTIMEZONE built-in SQL function returns the value of the current
session's time zone.
SQL> SELECT SESSIONTIMEZONE FROM DUAL;
SESSIONTIMEZONE
---------------
+01:00
2. How to set the session time zone ?
=====================================
Answer
------
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
3. How to check the database time zone ?
========================================
Answer
------
The DBTIMEZONE SQL function returns the value of the database time zone.
SQL> SELECT DBTIMEZONE FROM DUAL;
DBTIMEZONE
--------------
Europe/Lisbon
@ 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.
4. How to set the database time zone ?
======================================
Answer
------
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抯 O/S timezone offset.
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 ?
============================================
Answer
------
SQL> SELECT * FROM v$timezone_names;
TZNAME TZABBREV
------------------------------ ----------
Pacific/Tahiti LMT
Poland LMT
US/Pacific PST
Europe/Zurich CET
...
The result will depend on which timezone file is currently in use.
See Q&A 7-8 for further details.
6. How to retrieve the time zone offset corresponding to a time zone region ?
=============================================================================
The new 9i TZ_OFFSET() SQL function returns the time zone offset displacement
to the input time zone region.
SQL> SELECT TZ_OFFSET('Europe/London') FROM DUAL;
TZ_OFFS
-------
+01:00
The returned offset depends on the date this statement is executed.
For example, in the 'US/Pacific' time zone, it may return '-07:00' or '-08:00'
whether daylight saving is in effect or not.
SQL> SELECT TZ_OFFSET(SESSIONTIMEZONE), TZ_OFFSET(DBTIMEZONE) FROM DUAL;
returns the time zone offset corresponding to the time zone set for session
and database.
TZ_OFFS TZ_OFFS
------- -------
+01:00 +00:00
7. How to use a larger set of defined time zones ?
==================================================
2 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_MACHINESOFTWAREORACLEHOMEIDORA_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%oracorezoneinfotimezlrg.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 !
8. Which timezone-set is currently used ?
=========================================
Beside checking the UNIX ORA_TZFILE parameter or the Windows registry subkey,
you can easily check whether you are using the large or the default timezone
file with the following query:
SQL> SELECT COUNT(*) FROM v$timezone_names;
If the default-smallest timezone region file is in use, it will return:
COUNT(*)
----------
616 (in 9.0.1.x and 9.2.x)
or
COUNT(*)
----------
1250 (in 9.0.1.x and 9.2.x)
if the largest file is in use.
9. Can customers rely on Oracle time zones ?
============================================
Timezone.dat and timezlrg.dat are based on information maintained by the U.S. Navy
Observatory.[@more@]
=======================================
Answer
------
The new SESSIONTIMEZONE built-in SQL function returns the value of the current
session's time zone.
SQL> SELECT SESSIONTIMEZONE FROM DUAL;
SESSIONTIMEZONE
---------------
+01:00
2. How to set the session time zone ?
=====================================
Answer
------
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
3. How to check the database time zone ?
========================================
Answer
------
The DBTIMEZONE SQL function returns the value of the database time zone.
SQL> SELECT DBTIMEZONE FROM DUAL;
DBTIMEZONE
--------------
Europe/Lisbon
@ 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.
4. How to set the database time zone ?
======================================
Answer
------
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抯 O/S timezone offset.
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 ?
============================================
Answer
------
SQL> SELECT * FROM v$timezone_names;
TZNAME TZABBREV
------------------------------ ----------
Pacific/Tahiti LMT
Poland LMT
US/Pacific PST
Europe/Zurich CET
...
The result will depend on which timezone file is currently in use.
See Q&A 7-8 for further details.
6. How to retrieve the time zone offset corresponding to a time zone region ?
=============================================================================
The new 9i TZ_OFFSET() SQL function returns the time zone offset displacement
to the input time zone region.
SQL> SELECT TZ_OFFSET('Europe/London') FROM DUAL;
TZ_OFFS
-------
+01:00
The returned offset depends on the date this statement is executed.
For example, in the 'US/Pacific' time zone, it may return '-07:00' or '-08:00'
whether daylight saving is in effect or not.
SQL> SELECT TZ_OFFSET(SESSIONTIMEZONE), TZ_OFFSET(DBTIMEZONE) FROM DUAL;
returns the time zone offset corresponding to the time zone set for session
and database.
TZ_OFFS TZ_OFFS
------- -------
+01:00 +00:00
7. How to use a larger set of defined time zones ?
==================================================
2 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_MACHINESOFTWAREORACLEHOMEIDORA_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%oracorezoneinfotimezlrg.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 !
8. Which timezone-set is currently used ?
=========================================
Beside checking the UNIX ORA_TZFILE parameter or the Windows registry subkey,
you can easily check whether you are using the large or the default timezone
file with the following query:
SQL> SELECT COUNT(*) FROM v$timezone_names;
If the default-smallest timezone region file is in use, it will return:
COUNT(*)
----------
616 (in 9.0.1.x and 9.2.x)
or
COUNT(*)
----------
1250 (in 9.0.1.x and 9.2.x)
if the largest file is in use.
9. Can customers rely on Oracle time zones ?
============================================
Timezone.dat and timezlrg.dat are based on information maintained by the U.S. Navy
Observatory.[@more@]
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/18921899/viewspace-1016705/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle的時區問題Oracle
- Oracle修改時區Oracle
- 轉:Oracle的時區問題Oracle
- 關於oracle時區Oracle
- docker中的MySQL修改時區DockerMySql
- MySQL中的時區設定MySql
- Oracle的時區問題Time ZoneOracle
- oracle中in和exists的區別Oracle
- Oracle資料庫時區Oracle資料庫
- ORACLE 時間與時區(Time and Time Zone)Oracle
- oracle中distinct和group by的區別Oracle
- oracle中 DG和GG的區別Oracle
- Oracle中truncate和delete的區別Oracledelete
- (轉)ORACLE 中IN和EXISTS的區別Oracle
- Oracle中latch和lock的區別Oracle
- Oracle OCP(17):管理不同時區的資料Oracle
- Oracle中Date和Timestamp的區別Oracle
- oracle中rownum和rowid的區別Oracle
- Oracle rman中recover和restore的區別:OracleREST
- Oracle中的Connect、session、process的區別OracleSession
- oracle收集直方圖時 method_opt中引數 auto跟skewonly的區別Oracle直方圖
- 修改主機時區對Oracle的影響分析Oracle
- PHP中如何處理時區PHP
- Oracle中的臨時表用法彙總Oracle
- 遊戲開發中不同時區下的時間問題遊戲開發
- Oracle中sys和system的區別小結Oracle
- 在Oracle中session和process的區別(轉)OracleSession
- Oracle中truncate和delete的區別(例項)Oracledelete
- oracle中char與varchar2的區別Oracle
- MYSQL和ORACLE時區設定比較MySqlOracle
- 測試修改作業系統時間&時區對oracle的影響作業系統Oracle
- 修改Docker容器的時區為中國時區Docker
- Oracle中User和Schema的區別和聯絡Oracle
- 主題:Oracle中Null與空字串''''的區別OracleNull字串
- 理解ES6中的TDZ(暫時性死區)
- 深入Oracle的left join中on和where的區別詳解Oracle
- oracle中merge的用法,以及各版本的區別 CreateOracle
- Oracle中定位資料表的最近DML時間Oracle