您好,登錄后才能下訂單哦!
這篇文章主要為大家展示了“Oracle如何修改時(shí)區(qū)”,內(nèi)容簡而易懂,條理清晰,希望能夠幫助大家解決疑惑,下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“Oracle如何修改時(shí)區(qū)”這篇文章吧。
Oracle修改時(shí)區(qū)
1. 首先確認(rèn)會(huì)話時(shí)區(qū)是否正確,會(huì)話時(shí)區(qū)可能和數(shù)據(jù)庫時(shí)區(qū)不一致
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00
會(huì)話時(shí)區(qū)是北京時(shí)區(qū)
SQL> select dbtimezone from dual;
DBTIME
------
+00:00
DB是世界時(shí)區(qū)
SQL> select tz_offset(sessiontimezone), tz_offset(dbtimezone) from dual;
TZ_OFFS TZ_OFFS
------- -------
+08:00 +00:00
2. 檢查數(shù)據(jù)庫是否有這樣的字段類型 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
--查看時(shí)區(qū)依賴的表
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. 查看時(shí)區(qū)依賴表結(jié)構(gòu)
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. 查看時(shí)區(qū)依賴表數(shù)據(jù)
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. 處理時(shí)區(qū)依賴表
(1) 創(chuàng)建臨時(shí)表進(jìn)行備份
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. 修改時(shí)區(qū),但是查詢還是未生效
SQL> alter database set time_zone='+8:00';
Database altered.
SQL> select dbtimezone from dual;
DBTIME
------
+00:00
7. 重啟數(shù)據(jù)庫,時(shí)區(qū)生效
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. 刪除臨時(shí)表
SQL> drop table oe.test1 purge;
Table dropped.
另外:
對(duì)于全球化的業(yè)務(wù)而言,業(yè)務(wù)必須在多個(gè)時(shí)區(qū)之間正常運(yùn)轉(zhuǎn)。從9i版本開始,Oracle環(huán)境能夠知道所使用時(shí)區(qū)。為了實(shí)現(xiàn)這個(gè)功能,需要指定數(shù)據(jù)庫所運(yùn)行的時(shí)區(qū)以及使用TIMESTAMP WITH TIME ZONE與TIMESTAMP WITH LOCAL TIME ZONE數(shù)據(jù)類型。前一種具有一個(gè)時(shí)區(qū)指示符,這個(gè)指示符說明了其引用的時(shí)區(qū)。后一種數(shù)據(jù)類型在存儲(chǔ)時(shí)會(huì)被規(guī)范化為數(shù)據(jù)庫時(shí)區(qū),但隨后在檢索時(shí)會(huì)轉(zhuǎn)換為客戶端的時(shí)區(qū)。普通的DATE和TIMESTAMP數(shù)據(jù)類型在存儲(chǔ)時(shí)始終會(huì)規(guī)范為數(shù)據(jù)庫時(shí)區(qū),并且會(huì)在查詢過程原樣顯示。
關(guān)于timestamp的幾個(gè)函數(shù):
sysdate 數(shù)據(jù)庫服務(wù)器操作系統(tǒng)時(shí)間,顯示不含時(shí)區(qū)(其實(shí)隱含了時(shí)區(qū))。
systimestamp 數(shù)據(jù)庫服務(wù)器操作系統(tǒng)時(shí)間以及時(shí)區(qū)
注意:上述兩個(gè)函數(shù)的返回值不會(huì)受到客戶端影響。
localtimestamp 根據(jù)客戶端時(shí)區(qū)轉(zhuǎn)換成客戶端當(dāng)前時(shí)間,但顯示并不含時(shí)區(qū)
current_timestamp 根據(jù)客戶端時(shí)區(qū)轉(zhuǎn)換成客戶端當(dāng)前時(shí)間,包含客戶端時(shí)區(qū)
注意:上述兩個(gè)函數(shù)的返回值和客戶端時(shí)區(qū)設(shè)置有關(guān),會(huì)轉(zhuǎn)換為客戶端時(shí)區(qū)的時(shí)間。
以上是“Oracle如何修改時(shí)區(qū)”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對(duì)大家有所幫助,如果還想學(xué)習(xí)更多知識(shí),歡迎關(guān)注億速云行業(yè)資訊頻道!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。