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 Oracle 11g修改open_links参数的方式--并发CentOS 下 Oracle 10g 安装 + 配置全过程图解相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）