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
[@more@]

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

转载于:http://blog.itpub.net/23682146/viewspace-1055048/