9.1 資料型態與字元集

Oracle 資料庫字元集的設定可以分為兩種，一般字元集 (Character Set) 與國際字元集 (National Character Set)，兩者差別只在於對應的是不同資料型態，國際字元集所對應的資料型態是有 “N” 開頭的，例如 NCHAR、NVARCHAR2，而一般字元集是沒有 “N” 的，例如 CHAR、VARCHAR2，透過這種方式，使得 Oracle 資料庫可以利用資料型態的設定來同時使用兩種不同的字元集。

透過查詢 v$nls_valid_values 可以得知目前 Oracle 資料庫可以設定的一般字元集:

SQL> select parameter,value from v$nls_valid_values where parameter='CHARACTERSET';

對於我們中文語系來說，最常使用的莫過於中文字元集，ZHT16BIG5、ZHT16MSWIN950，或是 Unicode 字元集， UTF8 、 AL32UTF8 ，兩者最大的差別在於使用中文字元集時，一個中文字的長度為 2 bytes，而使用 Unicode 時，則為 3 bytes。 UTF8 與 AL32UTF8 同為 Unicode 字元集，差別只在於 UTF8 的最大長度為 3 bytes，而 AL32UTF8 最大可達 4 bytes，因此 AL32UTF8 可容納的字元數量多於 UTF8，可以說是 UTF8 的母字元集。

而國際字元集只提供了兩種設定，AL16UTF16 與 UTF8，預設為 AL16UTF16，差別在於 AL16UTF16 為固定長度，不論是英文字或是中文字都是 2 bytes；UTF8 為變動長度，例如英文字為 1 bytes 而中文字為 3 bytes。

不論是一般字元集或是國際字元集，在資料庫建立的當下就必須決定好要使用哪個，如果之後要更改，可以使用 “alter database character set” 指令來更改，例如將一般字元集改為 AL32UTF8，國際字元集改為 UTF8:

SQL> alter system set cluster_database=false scope=spfile;      (若為 RAC 必須先將參數 cluster_database 設定為 false) SQL> shutdown immediate SQL> startup restrict SQL> alter database character set INTERNAL_USE AL32UTF8;      (一般字元集改為 AL32UTF8) SQL> alter database national character set INTERNAL_USE UTF8;      (國際字元集改為 UTF8) SQL> shutdown immediate SQL> startup

在此必須要特別注意的是，更改資料庫的字元集設定並不會更改資料庫裡面已經存在的字元，它只會影響之後所新增的字元，例如原本設定為 ZHT16BIG5，中文字為 2 bytes，此時將它改為 AL32UTF8 ，已經存在資料庫的中文字並不會自動變成 3 bytes ，而是之後新增的中文字才會變成 3 bytes，因此非常不建議在資料庫建立後再去更改字元集，因為這樣會造成資料庫字集長度的混亂，所以對於 Oracle 管理者來說，字元集在資料庫建立之後就是一個無法更改的設定。

Oracle 文字的資料型態為 CHAR、VARCHAR2 (或是 NCHAR、NVARCHAR2) ，使用上在資料型態後面來指定大小，單位可以使用 byte 或是 char ， byte 表示資料長度以 byte 來計算， char 表示資料長度以一個完整的 “字” 來計算，例如 VARCHAR2(20 byte) ，表示這個文字的資料長度最大為 20 byte，最多可以容納 10 個中文字 (一個中文 2 bytes，以ZHT16BIG5來說)；若是 VARCHAR2(20 char) ，則是以 “字” 為單位，最多就可以容納 20 個 “字” (包含中文或英文) ， 如果不指定單位的話，例如只設定 VARCHAR2(20) ，那麼長度單位就由參數 nls_length_semantics 所決定，預設為 byte。 CHAR 與 VARCHAR2 的差別在於 CHAR 為固定長度而 VARCHAR2 為變動長度，例如 CHAR(10) 設定下，我們只塞了 “scott” 五個字，那麼整個資料會在 “scott” 後面補上五個空白字元讓整體的資料長度為 10 byte，未來所看到的資料就會變成 “scott     “ ；而 VARCHAR2(10) 就不會在後面補空白字元，塞入 “scott” 五個字它的長度就是 5 bytes。

VARCHAR2 最大可設定的長度為 4000 bytes，到了 Oracle 12c 之後，可以將 VARCHAR2 拓展為 32767 bytes (32K)。 Oracle 12c 之後提供了參數 max_string_size ，預設為 STANDARD ，表示 VARCHAR2 長度最大為 4000 ，若設定為 EXTENDED ，則 VARCHAR2 可拓展為 32K，設定時必須將資料庫啟動在 upgrade mode 並執行 32k 的 script:

1. 將資料庫啟動在 upgrade mode  SQL> shutdown immediate  SQL> startup upgrade 2. 更改參數 max_string_size  SQL> alter system set max_string_size=EXTENDED scope=spfile; 3. 以 sysdba 身分執行 32k script  SQL> @?/rdbms/admin/utl32k.sql 4. 重啟資料庫至正常模式  SQL> shutdown immediate  SQL> startup 5. 重新編譯無效物件  SQL> @?/rdbms/admin/utlrp.sql

在此必須要注意的是，一旦max_string_size 更改為 EXTENDED 之後，就無法再還原回 STANDARD 了。

Oracle 數值資料型態可為 NUMBER 或是 INTEGER，例如 NUMBER(4) 表示最大長度為4的數字，例如 9999 ； 而 NUMBER(4,2) 表示最大長度為 4 包含兩個小數位，例如 99.99 。 INTEGER 則表示是整數的數字型態，沒有小數的設定。

日期的資料型態可為 DATE 或是 TIMESTAMP，差別在於 TIMESTAMP 預設可以顯示到秒位數小數點後的六位。日期顯示的格式由參數 nls_date_format 所設定，預設為 DD-MON-RR ，我們可以經由調整 nls_date_format 的設定讓日期格式的可讀性更高:

除了上述常用的資料型態之外，還有一些特殊的資料型態可以使用，例如CLOB (NCLOB) ， 文字型態的大型物件、 BLOB ，二進位(Binary) 型態的大型物件、 BIGFILE ，將檔案(Binary File)儲存在資料庫外部的資料型態、 XMLTYPE ， 存放 XML 格式的資料型態，到了 Oracle 12c 之後更是可以將 JSON 格式的資料型態直接存放在 Oracle 資料庫當中。

在了解字元集與資料型態之後，我們就可以依據應用系統的需求來建立資料庫物件。

8.2 Oracle Undo Segment

Oracle Undo Segment 主要的用途是用來存放變更前的資料，當有 DML 行為產生時，DML 所做的變更會記錄到 Redo Log，而這些變更前的資料會暫存在 Undo Segment :

 

存放在 Undo Segment 中的變更前資料，我們將它稱作資料的 “Before Image”，最主要的用途是為了達成資料讀取的一致性，避免讀取到已經變更但尚未 commit 的資料，例如 EMP 這個 table 裡面有一筆 empno=7369、sal=4000 的資料，此時 session1對這筆資料進行變更，將 sal 改為 5000 :

在資料更改的同時，會把 empon=7369、sal=4000 這一筆變更前的資料存入 Undo Segment 中，此時當有 session2 來查詢 empno=7369 這筆資料時，由於之前的變更 (sal=5000) 尚未 commit，所以 session2 查到的會是從 Undo Segment 回傳的 sal=4000 這筆資料。

Undo Segment 除了用來維持資料讀取的一致性外，在做資料庫還原的過程當中也是扮演了重要的角色。當資料庫在進行還原的過程中，需要藉由 Redo Log / Archive log 來將資料還原到最新的狀態，假如資料庫是因為異常中止(crash)而需要進行還原，此時的 Redo Log 裡面就會包含已經 commit 的交易與尚未 commit 的交易，資料庫沒辦法分辨 Redo Log 裡面那些是已 commit 的交易哪些是未 commit，所以會將 Redo Log 裡面的交易通通應用(apply)到資料庫中，然後再藉由檢查 Undo Segment 將那些尚未 commit 的交易進行 rollback，以此達到資料回復到 crash 前的狀態。

Undo Tablespace 這個概念是從Oracle 9i 開始有的，Undo Tablespace 裡面只能存放 Undo Segment ，早期(Oracle 8i)以前稱作 rollback segment 。在 Oracle 8i 以前必須要由 DBA 自行管理所有的 rollback segment，由 ”create rollback segment” 這個命令來建立 rollback segment ，並且設定參數 rollback_segments 來指定所有使用的 rollback segment。到了 Oracle 9i 之後，將 rollback segment 進行自動化管理，DBA 只需要建立一個 Undo Tablespace，隨後的 rollback segment 都交由資料庫自動進行管理。這個設定與三個參數有關，undo_management、undo_retention與undo_tablespace:

• undo_tablespace: 設定undo tablespace為哪個tablespace。

• undo_retention: 設定undo tablespace裡面的before image 保留時間，單位為秒。

• undo_management: 設定 rollback segment的管理方式，AUTO表示自動管理，MANUAL表示手動管理，設定為MANUAL將回到Oracle 8i 之前的管理方式，預設為AUTO。

在 Undo Segment 為自動管理的設定下，我們還是可以透過查詢 dba_rollback_segs 來查看目前所使用的 rollback segments :

只有 SYS 這個使用者所使用的 rollback segment 存放在 SYSTEM 這個 tablespace，其餘使用者所使用的都會是由 Undo Tablespace 自行所產生 “_SYSSMU” 開頭的 rollback segments。

undo_retention 預設為900，表示資料的 before image 只保留在 Undo Tablespace 裡面15分鐘，當 Undo Tablespace 空間不足且有新的 before image 需要放進來時，便會把較舊的 before image 清除掉，讓新的 before image 放進來，若是此時有查詢需要使用 Undo Data 但是 before image 卻被清掉了，這時候就會出現 ”ora-01555:snapshot too old” 的錯誤訊息，必須考慮將 undo_retention 參數加大或是增加 undo tablespace 的空間來避免這個錯誤。透過 dba_undo_extents 可以查詢目前 Undo Tablespace 裡面各個 Undo Segment 的狀態:

• ACTIVE: 表示這個 Undo Segment正在被使用中。

• EXPIRED: 表示這個Undo Segment裡面的before image已經超出undo_retention的時間了，標示這區段的before image可以被清除。

• UNEXPIRED: 表示這區段的 Undo Segment 沒有 session 在使用它，而且也還未超出 undo_retention 的時間，等到它超過 undo_retention 時，狀態就會變為 EXPIRED。

當 Undo Tablespace 的空間不足時且 Undo Tablespace 所使用的 Data File 又沒有設定 autoextend 屬性時，標示為 EXPIRED 或是 UNEXPIRED 的 Segment 都有可能被清除，此時可以為 Undo Tablespace 加上 GUARANTEE 屬性，表示 Undo Tablespace 裡面的 before image 一定會保留到 undo_retention 這麼長的時間不被清掉:

SQL> alter tablespace undotbs1 retention guarantee;

 

透過查詢 dba_tablespaces 裡面的 retention 可以得知目前 Undo Tablespace 是否具有 GUARANTEE 屬性:

 

值得注意的是，當設定為 GUARANTEE 屬性時，如果 Undo Tablespace 空間不足且無法 autoextend ，那麼就會因空間不足而導致作業失敗。

建立 Undo Tablespace 只需要加入 UNDO 關鍵字，其餘操作與一般 Tablespace 無異:

SQL> create undo tablespace undotbs02 datafile '/oradata/undotbs02.dbf' size 1024m;

更改 Undo Tablespace 只需要將參數 undo_tablespace 變更就可以，例如將 UNDOTABS1 更改為 UNDOTABS2 :

SQL> alter system set undo_tablespace='UNDOTBS2';

這個參數可以在線上動態更改，更改完畢後，新的 session 就會使用新的 Undo Tablespace 了，但是舊的 Undo Tablespace 不建議在線上馬上刪除(Drop)，原因是在線上有可能有舊有的session仍舊在使用，且尚未 EXPIRED 的 before image 也有可能會被使用，建議將資料庫重啟後再將舊有的Undo Tablespace進行刪除的動作。

8.1 Oracle Redo Log

Oracle Redo Log 簡單來說就是用來記錄交易資訊的 Transaction Log，用途在於紀錄每一筆異動資料的紀錄，藉由這些交易紀錄，在進行資料庫還原時就可以將資料回復到之前最新的狀態。Redo Log 的內容會記錄每一筆交易的 Transaction ID、DML與DDL的SQL Statement，以及 Checkpoint/SCN 的資訊，在進行資料庫還原時，需要藉由 Redo Log 所記錄的SCN來恢復每個階段的交易，資料庫在 Apply 完所有 Redo Log 的交易資訊後，所有 Data File 的SCN便會一致，達成資料還原以及資料的完整與一致性。

在 Oracle 資料庫當中，Redo Log 是以 Group 的方式存在，每一個 Redo Log Group 可以有一個或多個 Redo Log File，在同一個 Group 裡面的 Redo Log File 稱為 Redo Log File Member，且每一個 Member 的內容都是相同的，建立多個 Member 的目的是可以互相作為備份，避免當有 Redo Log File 損壞而造成交易資訊的遺失。Redo Log Group 是以循環的方式來使用:

例如資料庫目前有四個 Redo Log Group，當 Redo Log Group 1 的 Redo Log File 被寫滿時，便會切換至下一個 Redo Log Group 2 繼續寫交易資訊，Group 2 被寫滿時再切換至 Group 3，同理 Group 3 寫滿後切換至 Group 4，而當 Group 4 被寫滿之後，便會把 Group 1 的內容清空然後循環回 Group 1 開始再寫交易資訊，如此不斷的循環。那麼把 Group 1 的內容清空，交易資訊不是會不見嗎 ? 為了避免這個問題，在 Redo Log 寫滿的時候會把所有的內容複製一份出來，而這些被複製出來的內容就稱作 Archive Log，如此就可以避免因為 Redo Log Group 的循環而造成交易資訊的遺失。

當交易發生時，並不會直接寫入到Redo Log，而是會先暫存在Log Buffer，然後再透過LGWR(Log Writer) 這個 Background Process寫入Redo Log:

當發生以下的現象時，便會觸發LGWR將資料寫入Redo Log:

• 交易被 commit 時。

• Log Buffer 以使用 1/3 時。

• 發生 checkpoint 時。

由以上可知實際上交易資訊存放在 Log Buffer 時間並不長，因此Log Buffer實際上不需要設定太大，一般使用預設的32M就已經足夠。

除了等待 Redo Log寫滿之後切換，我們也可以透過 “alter system switch logfile” 這個命令來手動強制切換一個Redo Log Group。透過 v$log 與 v$logfile 可以知道目前所有Redo Log Group 的狀態:

SQL> select a.thread#,a.group#,a.sequence#,b.member,a.bytes/1024/1024 mb,a.status         from v$log a,v$logfile b      where a.group#=b.group# order by 1,2

Redo Log Group 會有四種狀態，ACTIVE、INACTIVE、CURRENT與UNUSED。CURRENT表示目前交易正在寫入這個 Group 的 Redo Log File；ACTIVE表示這個 Group 正在等待 Checkpoint；INACTIVE表示目前為閒置狀態，可以被使用；UNUSED表示這是一個新的 Group，裡面的 Redo Log File還從未被使用。

由 Redo Log Group的狀態欄可以得知，必須為 INACTIVE狀態的Group才可以重複再被使用，如果交易量太大導致Redo Log Group 切換速度過快，這時候可能造成所有的Group都為ACTIVE狀態，此時前端的交易就會產生等待的情形，直到有Group變為INACTIVE時，前端交易才有辦法再進行下去，這個時候在資料庫的 alert log 當中可能就會看到 “checkpoint not complete” 字樣，表示Redo Log 切換的速度趕不上交易產生的速度，這個時候就必須要增加 Redo Log Group，提供多一點的Group來切換，或者是增加Redo Log File的大小，減少切換的頻率來避免大交易量的問題。

Redo Log Group 或是Redo Log File Member可以在線上進行新增或刪除的動作，只要是狀態為 INACTIVE的Group就可以進行刪除，使用 alter database命令進行操作:

SQL> alter database add logfile group 4 '/oradata/ORA11/redo04.log' size 200M;      (新增 group 4 且大小為 200M) SQL> alter database add logfile member '/oradata/ORA11/redo04b.log' to group 4;      (為 group 4 添加一個 Redo Log File) SQL> alter database drop logfile group 5;      (刪除 Redo Log Group 5) SQL> alter database drop logfile member '/oradata/ORA11/redo05b.log';      (刪除這一個 Redo Log File)

Redo Log File 的大小在建立後就無法更改，如果有變更 Redo Log 大小的需求，必須將他刪除後再重建才行。早期資料庫預設的Redo Log Size為50M，這大多時候是不足的，而現在版本的資料庫已經把預設值改為200M了。

Oracle 資料庫預設是 No archive log Mode，也就是說在預設的狀態下，Redo Log 的交易資訊會被覆蓋掉，當資料庫發生問題時便無法由備份還原到最新的狀態，只能還原到備份當下的時間點，而中間的交易資料便遺失了，所以一般來說都會將資料庫設定為 archive log Mode，以確保資料能夠被完全的保護，我們可以簡單的在 sqlplus 裡面使用 archive log list 命令來檢查目前設定是否為 archive log Mode:

在設定 archive log Mode 之前必須先設定兩個參數 log_archive_dest_1 與 log_archive_format，log_archive_dest_1 設定 archive log 存放的位置，可以指定一個或多個位置，log_archive_dest_n 可以從 1 ~ 31，最多可以設定 32 個 archive log 位置；log_archive_format 則是設定產出的 archive log 的格式(檔名)，比較要注意的是，log_archive_format設定上必須包含 %s、%t與 %r 三個參數，%s表示交易的 sequence number、%t表示instance的thread number，%r則表示為 resetlogs number。

將log_archive_dest_1 設定為 use_db_recovery_file_dest 表示將 archive log 存放在 recovery area 裡面，如果是這個設定，那麼就還要先設定兩個參數db_recovery_file_dest與db_recovery_file_dest_size來指定 recovery area 的位置與大小，log_archive_dest_1 也可以設定在任何一個指定的目錄，例如設定將 archive log 存放至 /u01/app/arch 底下:

SQL> alter system set log_archive_dest_1='location=/u01/app/arch';

設定完參數之後，便可以將資料庫設定為 archive log Mode，在 mount 模式下使用 alter database archivelog 指令便可以設定為 archive log Mode；如果要設定為 No archive log Mode，則是使用 alter database noarchivelog 指令:

設定完 archive log Mode之後，可以馬上使用 “alter system switch logfile”，手動切換一個 Redo Log Group來檢查 archive log 是否有產生在指定的位置，驗證設定是否正確。