Oracle的時區問題Time Zone

--查詢時區檔案支援的時區
SELECT * FROM V$TIMEZONE_NAMES;
--查詢SESSION中設定的時區
SELECT SESSIONTIMEZONE FROM DUAL;
--在SESSION級別設定時區
ALTER SESSION SET TIME_ZONE = local;
ALTER SESSION SET TIME_ZONE = dbtimezone;
ALTER SESSION SET TIME_ZONE = '-05:00';
ALTER SESSION SET TIME_ZONE = 'Europe/London';
--或者
$ ORA_SDTZ='OS_TZ'
$ export ORA_SDTZ
$ ORA_SDTZ='-05:00'
$ export ORA_SDTZ
--查詢資料庫的時區
SELECT DBTIMEZONE FROM DUAL;
SELECT property_name, property_value
FROM database_properties
WHERE property_name='DBTIMEZONE';
SELECT name, value$
FROM props$
WHERE name='DBTIMEZONE';
--設定資料庫的時區
CREATE DATABASE ...
SET TIME_ZONE='Europe/London';
--或者  （修改後需要重啟）
ALTER DATABASE SET TIME_ZONE = '-05:00';
ALTER DATABASE SET TIME_ZONE = 'Europe/Lisbon';
--查詢在指定時區的當地時間
SELECT TZ_OFFSET('Europe/London') FROM DUAL;
--當前使用的時區檔案，如果查詢的值接近與700，那麼使用的是timezone.dat，如果接近於1300，那麼是timezlrg.dat
SELECT COUNT(*) FROM v$timezone_names;

--檢視當前資料庫系統的時區

   SQL> select dbtimezone from dual;

 DBTIME

 ------

 +00:00

--檢視當前資料庫的時間

SQL> select to_char

  2  (sysdate,'yyyymmdd hh24:mi:ss') from dual;

 

TO_CHAR(SYSDATE,'

-----------------

20121017 16:58:26

--修改時區

SQL> alter database set time_zone='+8:00';

alter database set time_zone='+8:00'

*

ERROR at line 1:

ORA-30079: cannot alter database timezone when database has  TIMESTAMP WITH  LOCAL TIME ZONE columns

主要問題就是因為上面紅色字型標註，即使用了本地時區列，為了查詢這個列，查了不少資料。

--查詢有問題的表

SQL> select u.name || '.' || o.name || '.' || c.name

  2  TSLTZcolumn from sys.obj$ o, sys.col$ c, sys.user$ u

  3  where c.type# = 231 and o.obj# = c.obj# and u.user# = o.owner#;

 

TSLTZCOLUMN

--------------------------------------------------------------------------------

OE.ORDERS.ORDER_DATE  

就是上面這個這個表中的列採用了 timestamp with local timezone

--檢視oe.orders表的屬性

SQL> desc oe.orders

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ORDER_ID                                  NOT NULL NUMBER(12)

  ORDER_DATE                                NOT NULL TIMESTAMP(6) WITH LOCAL TIME

                                                     ZONE

 ORDER_MODE                                         VARCHAR2(8)

 CUSTOMER_ID                               NOT NULL NUMBER(6)

 ORDER_STATUS                                       NUMBER(2)

 ORDER_TOTAL                                        NUMBER(8,2)

 SALES_REP_ID                                       NUMBER(6)

 PROMOTION_ID                                       NUMBER(6)

--刪除此列

SQL> alter table oe.orders drop column order_date;

 

Table altered.

--再次設定時區

SQL> alter database set time_zone='+8:00';

 

Database altered.

SQL> startup force;  

ORACLE instance started.

Total System Global Area  608174080 bytes

Fixed Size                  1220820 bytes

Variable Size             197136172 bytes

Database Buffers          402653184 bytes

Redo Buffers                7163904 bytes

Database mounted.

Database opened.

--再次檢視時區

SQL> select dbtimezone from dual;

DBTIME

------

+08:00

到此時區就已經設定正確了

---

---

About Me

........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在個人微 信公眾號（ DB寶）上有同步更新 ● QQ群號： 230161599 、618766405，微信群私聊 ● 個人QQ號（646634621），微 訊號（db_bao），註明新增緣由 ● 於 2020年11月完成 ● 最新修改時間：2020年11月 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店： ● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用、MySQL、DBA學習班： http://blog.itpub.net/26736162/viewspace-2148098/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ........................................................................................................................ 請掃描下面的二維碼來關注小麥苗的微 信公眾號（ DB寶）及QQ群（230161599、618766405）、新增小麥苗微 信（db_bao）， 學習最實用的資料庫技術。 ........................................................................................................................