oracle之time zone时区timestamp with time zone相关的概念之一

结论

1，数据库测试环境为10.2.0.5
2, dba_tab_cols可以获取所有数据类型的信息，共计383种数据类型
3，timestamp with time zone默认值6个粒度，可取范围为0-9
4，与此相关的参数为dbtimezone,sessiontimezone
5, v$timezone_names存储时间区信息，共计392个时区
6, 可以在创建数据库时create database以set time_zone指定数据库时区
   也可以在创建完库后用alter database调整数据库时区
7，调整数据库时区有2种方式，一种为set time zone 'hh:mi'   
   一种为直接指定数据库时区名称（源于v$timezone_names）
8,调整后数据库时区不能马上生效，必须重启库方可生效
9，调整数据库时区后，已存在的相关数据不会进行更新调整，所以必须要导出然后调整数据库时区，再导入方可
10，一般ORACLE不建议调整数据库时区
11，timestamp with local time zone是timestamp变体，它的列数据不会存储时区信息，也就是每次查询数据，会基于客户端会话的时区配置，显示相关数据
12，如果没有显式指定数据库时区，数据库会使用操作系统的时区，但是如果操作系统时区不是一个合理的数据库时区，数据库则会使用默认的时区UTC，且UTC的取值范围为-12:00 to +14:00

问题

1，时区的概念是什么
2，创建数据库时应使用什么样的时区
3，如果选择时区错误，会对数据库造成什么影响
4，如何修改或调整数据库时区
5，会话时区与数据库时区对于数据库中相关数据的影响
6，其它相关的一些资料，主要是哪些官方手册
7，数据库升级与时区相关的问题处理
8，调整数据库时区对于现存数据到底有何影响

测试

SQL> select * from v$version where rownum=1;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi


SQL> select sessiontimezone,dbtimezone from dual;


SESSIONTIMEZONE                                    DBTIMEZONE
-------------------------------------------------- --------------------------------------------------
-05:00                                             -04:00


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


TZ_OFFS TZ_OFFS
------- -------
-05:00  -04:00




SQL> select owner,data_type,count(*) from dba_tab_cols where lower(data_type) like '%time zone%' group by owner,data_type;


OWNER                          DATA_TYPE                                            COUNT(*)
------------------------------ -------------------------------------------------- ----------
DBSNMP                         TIMESTAMP(6) WITH TIME ZONE                                 1
SYS                            TIMESTAMP(6) WITH TIME ZONE                               150
WMSYS                          TIMESTAMP(0) WITH TIME ZONE                                 2
SYS                            TIMESTAMP(0) WITH TIME ZONE                                 4
SYS                            TIMESTAMP(9) WITH TIME ZONE                                 1


SQL> select count(distinct data_type) from dba_tab_cols;


COUNT(DISTINCTDATA_TYPE)
------------------------
                     383


SQL> select distinct data_type from dba_tab_cols where lower(data_type) like '%varchar%';


DATA_TYPE
--------------------------------------------------
NVARCHAR2
VARCHAR2


---默认存储精度为6，可取范围为0-9
SQL> create table t_timestamp_time_zone(a timestamp with time zone);


Table created.


SQL> desc t_timestamp_time_zone;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  TIMESTAMP(6) WITH TIME ZONE       




 SQL> alter table t_timestamp_time_zone modify a timestamp(9) with time zone;


Table altered.


SQL> desc t_timestamp_time_zone;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  TIMESTAMP(9) WITH TIME ZONE


 SQL> alter table t_timestamp_time_zone modify a timestamp(0) with time zone;


Table altered.


SQL> alter table t_timestamp_time_zone modify a timestamp(10) with time zone;
alter table t_timestamp_time_zone modify a timestamp(10) with time zone
                                                     *
ERROR at line 1:
ORA-30088: datetime/interval precision is out of range




SQL> select tzname,tzabbrev from v$timezone_names where tzname='UTC';


TZNAME                                                           TZABBREV
---------------------------------------------------------------- ----------------------------------------------------------------
UTC                                                              GMT




SQL> select count(*),count(distinct tzname) from v$timezone_names;


  COUNT(*) COUNT(DISTINCTTZNAME)
---------- ---------------------
      1458                   392


SQL> select tzname,TZABBREV from v$timezone_names where tzname='UTC';


TZNAME                                                           TZABBREV
---------------------------------------------------------------- ----------------------------------------------------------------
UTC                                                              GMT




SQL> select tzname,TZABBREV from v$timezone_names where tzname='US/Mountain';


TZNAME                                                           TZABBREV
---------------------------------------------------------------- ----------------------------------------------------------------
US/Mountain                                                      LMT
US/Mountain                                                      MST
US/Mountain                                                      MWT
US/Mountain                                                      MDT




---经查官方手册，可以采用2种方式调整数据库时区，可见修改后时间还是原有的值
SQL>  select dbtimezone from dual;


DBTIMEZONE
--------------------------------------------------
-04:00


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


Database altered.


SQL>  select dbtimezone from dual;


DBTIMEZONE
--------------------------------------------------
-04:00


---可见数据库时区调整后要重启数据库方会新值生效
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.


Total System Global Area  176160768 bytes
Fixed Size                  2094832 bytes
Variable Size             163580176 bytes
Database Buffers            4194304 bytes
Redo Buffers                6291456 bytes
Database mounted.
Database opened.
SQL>  select dbtimezone from dual;


DBTIMEZONE
--------------------------------------------------
+05:00




---官方说，对于time zone数据类型的数据，即使你更新了数据库时区，原数据也不会进行对应调整，只能你导出数据，然后调整数据库时区，再把原始数据导入即可
所以，一定不要调整数据库时区




---官方建议数据库时间采用UTC，因为这种时区性能好，指定数据库时区还有第2种方式，即直接指定数据库时区名称，源于v$timezone_names,下面测试下第2种方式指定数据库时区
SQL>  select dbtimezone from dual;


DBTIMEZONE
--------------------------------------------------
+05:00


SQL> 
SQL> 
SQL> select tzname,TZABBREV from v$timezone_names where tzname='UTC';


TZNAME                                                           TZABBREV
---------------------------------------------------------------- ----------------------------------------------------------------
UTC                                                              GMT




SQL> alter database set time_zone='UTC';


Database altered.


SQL> select dbtimezone from dual;


DBTIMEZONE
--------------------------------------------------
+05:00


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.


Total System Global Area  176160768 bytes
Fixed Size                  2094832 bytes
Variable Size             163580176 bytes
Database Buffers            4194304 bytes
Redo Buffers                6291456 bytes
Database mounted.
Database opened.
SQL> select dbtimezone from dual;


DBTIMEZONE
--------------------------------------------------
UTC


SQL> 


---另外官方说，如果没有显式指定数据库时区，数据库会使用操作系统的时区，但是如果操作系统时区不是一个合理的数据库时区，数据库则会使用默认的时区UTC，且UTC的取值范围为-12:00 to +14:00




---再看下timestamp with local time zone,官方说它是timestamp的一种变体，它不同于timestamp with time zone,其时区信息不会写入数据库，准确来说就是不会存储到对应的列中，每当你查询相关数据时，
会基于会话的时区信息，显示数据，其默认粒度为6，同timestamp with time zone,取值范围为0-9
SQL> create table t_local_timezone(a timestamp with local time zone);


Table created.


SQL> desc t_local_timezone;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  TIMESTAMP(6) WITH LOCAL TIME
                                                     ZONE

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1868603/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1868603/