Oracle dbtimezone與os時區不一致的解決辦法

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0   
Connected as sys  
          //查看數據庫時區   -
 

SQL> select dbtimezone from dual;  
DBTIMEZONE  
----------  
+00:00  
          //查看當前時間和時區  
SQL> select systimestamp from dual;  
SYSTIMESTAMP  
--------------------------------------------------------------------------------  
12-4月 -11 02.39.49.421000 下午 +08:00  
          //我明明是在東8區，怎么數據庫時區顯示為0呢？  
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 tsltz_column  
  2  from sys.obj$ o,sys.col$ c,sys.user$ u  
  3  where c.type#=231 and  
  4        o.obj#=c.obj# and  
  5        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  
          //關閉數據庫  
          //SHUTDOWN is not an SQL command, it is an SQL*Plus command.  
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 

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]