<rt id="bn8ez"></rt>
<label id="bn8ez"></label>

  • <span id="bn8ez"></span>

    <label id="bn8ez"><meter id="bn8ez"></meter></label>

    Decode360's Blog

    業精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

      BlogJava :: 首頁 :: 新隨筆 :: 聯系 ::  :: 管理 ::
      302 隨筆 :: 26 文章 :: 82 評論 :: 0 Trackbacks
    ORACLE中的時區
    ?
    ?發表人:jss001 | 發表時間: 2009年二月24日, 03:42

    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_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%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.




    -The End-

    posted on 2009-01-29 21:01 decode360-3 閱讀(500) 評論(0)  編輯  收藏 所屬分類: Oracle
    主站蜘蛛池模板: 亚洲最大天堂无码精品区| 91亚洲国产成人久久精品网站 | 国产成人免费a在线视频色戒| 亚洲av永久无码精品三区在线4 | 亚洲成a人片7777| 亚洲一级毛片免费看| 亚洲精品中文字幕麻豆| 最近免费中文字幕大全高清大全1| 亚洲AV成人一区二区三区AV| 国产精品网站在线观看免费传媒 | 边摸边吃奶边做爽免费视频网站| 国产一级淫片a免费播放口之| 特级毛片免费播放| 亚洲熟妇av一区二区三区| 大地资源网高清在线观看免费| 亚洲Av无码精品色午夜| 麻豆高清免费国产一区| 亚洲中文字幕无码爆乳app| 日本19禁啪啪无遮挡免费动图| 猫咪免费人成网站在线观看入口| 亚洲午夜AV无码专区在线播放 | 久久夜色精品国产亚洲AV动态图| 99久久99久久精品免费观看| 亚洲视频一区二区三区四区| 又大又粗又爽a级毛片免费看| 国产99久久久国产精免费| 久久伊人久久亚洲综合| 最新欧洲大片免费在线| 阿v免费在线观看| 亚洲视频在线免费观看| 浮力影院第一页小视频国产在线观看免费 | 日本高清免费网站| 国产精品免费观看视频| 亚洲精品在线不卡| 国产免费牲交视频| 一区二区免费视频| mm1313亚洲国产精品无码试看| 亚洲情综合五月天| 女人18毛片水真多免费播放| 国产免费久久精品丫丫| 亚洲人妖女同在线播放|