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 bytesFixed Size 7877988 bytesVariable Size 145488364 bytesDatabase Buffers 25165824 bytesRedo Buffers 524288 bytesDatabase mounted.Database opened. //查看时区SQL> select dbtimezone from dual;DBTIMEZONE----------+08:00

参考：http://www.itpub.net/312046.html