oracle增加支持时区,【Oracle】 Oracle dbtimezone与OS时区不一致

//需要将这些列删除后，才能更改，下面我们来查找这些列：

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重启客户端后查询发现时间一致，完成。