如何修改数据库时区

转自：http://space.itpub.net/175005/viewspace-618100

首先确认会话时区是否正确，会话时区可能和数据库时区不一致
SQL> SELECT SESSIONTIMEZONE FROM DUAL;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00

会话时区是北京时区

SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIME
------
+00:00

DB是世界时区

SQL> SELECT TZ_OFFSET(SESSIONTIMEZONE), TZ_OFFSET(DBTIMEZONE) FROM DUAL;

TZ_OFFS TZ_OFFS
------- -------
+08:00 +00:00

1.检查数据库是否有这样的字段类型 TIMESTAMP WITH LOCAL TIME ZONE
SQL>select c.owner || '.' || c.table_name || '(' || c.column_name || ') -'
    || c.data_type || ' ' col
from dba_tab_cols c, dba_objects o
where c.data_type like '%WITH LOCAL TIME ZONE'
    and c.owner=o.owner
   and c.table_name = o.object_name
   and o.object_type = 'TABLE'
order by col
/

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#;

2.检查当前数据库时区
SQL> select * from database_properties where property_name='DBTIMEZONE';
行将被截断

PROPERTY_NAME PROPERTY_VALUE
------------------------------ -------------------------------------------------
DBTIMEZONE 00:00

3.改变数据库时区
SQL> ALTER DATABASE SET TIME_ZONE = '+8:00';

数据库已更改。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 272629760 bytes
Fixed Size                  1248528 bytes
Variable Size              92275440 bytes
Database Buffers          171966464 bytes
Redo Buffers                7139328 bytes
数据库装载完毕。
数据库已经打开。
SQL> select dbtimezone from dual;

DBTIME
------
+08:00

4.检查时间
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual

SQL> SELECT SYSTIMESTAMP FROM dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
23-7月 -09 11.50.25.750000 上午 +08:00