Oracle修改时区

Oracle修改时区

 

1. 首先确认会话时区是否正确，会话时区可能和数据库时区不一致

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

 

 

2. 检查数据库是否有这样的字段类型 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

 /

 

COL

--------------------------------------------------------------------------------

OE.ORDERS(ORDER_DATE) -TIMESTAMP(6) 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#;

 

TSLTZCOLUMN

--------------------------------------------------------------------------------

OE.ORDERS.ORDER_DATE

 

3. 查看时区依赖表结构

SQL> desc oe.orders

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ORDER_ID                                  NOT NULL NUMBER(12)

 ORDER_DATE                                NOT NULL TIMESTAMP(6) WITH LOCAL TIME

                                                     ZONE

 ORDER_MODE                                         VARCHAR2(8)

 CUSTOMER_ID                               NOT NULL NUMBER(6)

 ORDER_STATUS                                       NUMBER(2)

 ORDER_TOTAL                                        NUMBER(8,2)

 SALES_REP_ID                                       NUMBER(6)

 PROMOTION_ID                                       NUMBER(6)

 

4. 查看时区依赖表数据

SQL> select ORDER_DATE from oe.orders;

 

ORDER_DATE

---------------------------------------------------------------------------

21-MAR-04 08.18.21.862632 AM

09-JAN-06 12.19.44.123456 PM

09-JAN-06 01.34.13.112233 PM

27-JAN-06 01.22.51.962632 AM

02-FEB-06 05.34.56.345678 PM

03-FEB-06 12.19.11.227550 PM

28-FEB-06 09.03.03.828330 AM

30-MAR-06 02.22.09.509801 AM

30-MAR-06 05.34.50.545196 AM

28-JUL-06 02.22.59.662632 AM

28-JUL-06 03.34.16.562632 AM

..................................

28-JUN-08 11.53.32.335522 AM

15-JUL-08 08.18.23.234567 AM

27-JUL-08 10.59.10.223344 PM

02-AUG-08 01.22.48.734526 AM

 

105 rows selected.

 

5. 处理时区依赖表

(1) 创建临时表进行备份

SQL> create table oe.test1(order_id number,order_date date);

 

Table created.

 

SQL> insert into oe.test1(order_id,order_date) select order_id,order_date from oe.orders;

 

105 rows created.

 

SQL> commit;

 

Commit complete.

 

 

(2) 处理原表oe.orders中的列order_date

SQL> alter table oe.orders drop column order_date;

 

Table altered.

 

SQL> alter table oe.orders add order_date date;

 

Table altered.

 

SQL> update oe.orders a set order_date= (select order_date from oe.test1 b where a.order_id=b.order_id);

 

105 rows updated.

 

SQL> commit;

 

Commit complete.

 

(3) 再次查询是否还存在以上类型的列

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

     /

 

no rows selected

 

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

 

no rows selected

 

 

6. 修改时区，但是查询还是未生效

SQL> alter database set time_zone='+8:00';

 

Database altered.

 

SQL> select dbtimezone from dual;

 

DBTIME

------

+00:00

 

7. 重启数据库，时区生效

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> 

SQL> startup

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             549456976 bytes

Database Buffers          281018368 bytes

Redo Buffers                2371584 bytes

Database mounted.

Database opened.

SQL> 

SQL> select dbtimezone from dual;

 

DBTIME

------

+08:00

 

SQL> select tz_offset(sessiontimezone), tz_offset(dbtimezone) from dual;

 

TZ_OFFS TZ_OFFS

------- -------

+08:00  +08:00

 

8. 删除临时表

SQL> drop table oe.test1 purge;

 

Table dropped.

 

另外：

对于全球化的业务而言，业务必须在多个时区之间正常运转。从9i版本开始，Oracle环境能够知道所使用时区。为了实现这个功能，需要指定数据库所运行的时区以及使用TIMESTAMP WITH TIME ZONE与TIMESTAMP WITH LOCAL TIME ZONE数据类型。前一种具有一个时区指示符，这个指示符说明了其引用的时区。后一种数据类型在存储时会被规范化为数据库时区，但随后在检索时会转换为客户端的时区。普通的DATE和TIMESTAMP数据类型在存储时始终会规范为数据库时区，并且会在查询过程原样显示。

 关于timestamp的几个函数：

 sysdate      数据库服务器操作系统时间，显示不含时区(其实隐含了时区)。

 systimestamp 数据库服务器操作系统时间以及时区

 注意：上述两个函数的返回值不会受到客户端影响。

 

 localtimestamp      根据客户端时区转换成客户端当前时间，但显示并不含时区

 current_timestamp   根据客户端时区转换成客户端当前时间，包含客户端时区

 注意：上述两个函数的返回值和客户端时区设置有关，会转换为客户端时区的时间。