oracle em 时区更改不了,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