Oracle dbtimezone与os时区不一致的解决办法

//查看数据库时区 
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 


oracle DBTIMEZONE时区调整

select dbtimezone from dual ;  --查看数据库时区
select sessiontimezone from dual ;  --查看会话时区

1.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#;

  执行此句，如果能查询到数据进行第2步操作，否则转到3

2.执行下列语句

  --创建一个临时表

  create table ttt(
     order_id number ,
     oeder_Date date 
  );

  --导入数据

  insert into ttt(order_id,oeder_date) select order_id,order_date from oe.orders

  --修改oe.orders表

  alter table oe.orders drop column order_date
  alter table oe.orders add order_date date

  --将临时表中的数据恢复到oe.orders

  update oe.orders a set order_date = (select oeder_Date from ttt b where a.order_ID = b.order_id);

  --删除临时表

  drop table ttt

3.修改 DBTIMEZONE

  alter database set time_zone='+08:00';

4.查看DBTIMEZONE

  select DBTIMEZONE from dual ;