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

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