系統：Linux redhat 6.4
資料庫：11.2.0.4.0 RAC

問題：資料庫伺服器時區+0800 資料庫dbtimezone時區 +00:00

客戶端windows , cmd sqlplus / as sysdba查詢時間與伺服器差兩小時。

解決方法：
/檢視資料庫時區
SQL> select dbtimezone from dual;
DBTIMEZONE
----------
+00:00

//檢視當前時間和時區
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------------
12-4月 -11 02.39.49.421000 下午 +08:00

//修改資料時區
SQL> alter database set time_zone='+8:00';
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns
/從錯誤提示，可以看出資料庫中一些表的列的資料型別為:TIMESTAMP WITH LOCAL TIME ZONE
//需要將這些列刪除後，才能更改，下面我們來查詢這些列：

SQL>
select u.name || '.' || o.name || '.' || c.name TSLTZcolumn from sys.obj$ o, sys.col$ c, sys.user$ u where c.type# = 231 and o.obj# = c.obj# and u.user# = o.owner#;

TSLTZ_COLUMN
--------------------------------------------------------------------------------
OE.ORDERS.ORDER_DATE

//是oe使用者下orders表下的列order_date

SQL> desc oe.orders;
Name Type Nullable Default Comments
------------ --------------------------------- -------- ------- -----------------------------------------------------------
ORDER_ID NUMBER(12) PRIMARY KEY column.
ORDER_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE column, NOT NULL constraint.
ORDER_MODE VARCHAR2(8) Y CHECK constraint.
CUSTOMER_ID NUMBER(6)
ORDER_STATUS NUMBER(2) Y 0: Not fully entered, 1: Entered, 2: Canceled - bad credit,-
3: Canceled - by customer, 4: Shipped - whole order, -
5: Shipped - replacement items, 6: Shipped - backlog on items, -
7: Shipped - special delivery, 8: Shipped - billed, 9: Shipped - payment plan,-
10: Shipped - paid
ORDER_TOTAL NUMBER(8,2) Y CHECK constraint.
SALES_REP_ID NUMBER(6) Y References hr.employees.employee_id.
PROMOTION_ID NUMBER(6) Y Sales promotion ID. Used in SH schema

SQL> alter table oe.orders drop column order_date; --刪除列
Table altered

SQL> alter database set time_zone='+8:00'; --修改時區
Database altered

SQL> shutdown immediate; --關閉資料庫
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup; --重啟資料庫
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 7877988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

SQL> select dbtimezone from dual; --檢視更新後的時區
DBTIMEZONE
----------
+08:00

重啟客戶端後查詢發現時間一致，完成。

【Oracle】 Oracle dbtimezone與OS時區不一致

相關文章