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