//查看數據庫時區?
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 DBTIMEZONE時區調整

select dbtimezone from dual ; ?--查看數據庫時區
select sessiontimezone from dual ; ?--查看會話時區

1.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#;

? 執行此句，如果能查詢到數據進行第2步操作，否則轉到3

2.執行下列語句

? --創建一個臨時表

? create table ttt(
? ? ?order_id number ,
? ? ?oeder_Date date?
? );

? --導入數據

? insert into ttt(order_id,oeder_date) select order_id,order_date from oe.orders

? --修改oe.orders表

? alter table oe.orders drop column order_date
? alter table oe.orders add order_date date

? --將臨時表中的數據恢復到oe.orders

? update oe.orders a set order_date = (select oeder_Date from ttt b where a.order_ID = b.order_id);

? --刪除臨時表

? drop table ttt

3.修改 DBTIMEZONE

? alter database set time_zone='+08:00';

4.查看DBTIMEZONE

? select DBTIMEZONE from dual ;
??

總結

以上是生活随笔為你收集整理的Oracle dbtimezone与os时区不一致的解决办法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。