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

最新推荐文章于 2024-08-22 15:36:33 发布

原创最新推荐文章于 2024-08-22 15:36:33 发布 · 5.8k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #os #database #sql #数据库 #timezone

Oracle PL/SQL编程专栏收录该内容

103 篇文章

订阅专栏

本文介绍如何解决Oracle数据库中因存在TIMESTAMP WITH LOCAL TIME ZONE类型字段而导致无法直接修改数据库时区的问题。通过删除相关字段后成功将时区设置为'+8:00'。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

参考：http://www.itpub.net/312046.html