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]