系统：Linux redhat 6.4
数据库：11.2.0.4.0 RAC
问题：数据库服务器时区+0800 数据库dbtimezone时区 +00:00
客户端windows , cmd sqlplus / as sysdba查询时间 与服务器差两小时。
解决方法：
/查看数据库时区
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
重启客户端后查询发现时间一致，完成。
数据库：11.2.0.4.0 RAC
问题：数据库服务器时区+0800 数据库dbtimezone时区 +00:00
客户端windows , cmd sqlplus / as sysdba查询时间 与服务器差两小时。
解决方法：
/查看数据库时区
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
重启客户端后查询发现时间一致，完成。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30327022/viewspace-2134097/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
