【Oracle】 Oracle dbtimezone与OS时区不一致

系统：Linux redhat 6.4
数据库：11.2.0.4.0 RAC

问题：数据库服务器时区+0800   数据库dbtimezone时区 +00:00

客户端windows , cmd sqlplus / as sysdba查询时间 与服务器差两小时。

解决方法：
/查看数据库时区 
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 

重启客户端后查询发现时间一致，完成。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30327022/viewspace-2134097/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30327022/viewspace-2134097/