5.4 管理 Tablespace

當我們在一個 Tablespace 裡面建立一個 Table 時，會在這個 Tablespace 上面建立一個 Data Segment 給這個 Table 所使用，自Oracle 11g 開始出現了參數 deferred_segment_creation ，預設為 TRUE ，意思是在 Table 建立的當下且沒有任何資料時先不要配置 Data Segment 給它，直到有資料塞進來時再配置 Data Segment ，設為 FALSE 表示在 Table 建立的當下就配置 Data Segment ，這也是過往 Oracle DB 的作法， 11g 開始出現這個參數的用意只是為了節省 Tablespace 的使用空間，因為一個沒有資料的空 Table 在不配置 Data Segment 的情況下不會占用到 Tablespace 的空間。而隨著 Table 資料的增長，起始配置給這個 Table 的 Data Segment 勢必會遇到用完的一天，當 Data Segment 裡面的剩餘空間不足以再容納 Table 新增資料時，就必須從 Tablespace 裡面再挖一段 Extent 進來擴充:

那麼 Data Segment 在擴充的當下要怎麼知道 Tablespace 還有沒有足夠的空間來新增 Extent ? 這個時候就要提到 Tablespace 裡面的 Extent Management 機制，分為 Dictionary Management 與 Local Management。

• Dictionary Management:

Tablespace 裡面的 Free Extent 由系統的 Table SYS.UET$ 與 SYS.FET$ 來管理，已使用的 Extent 記錄在 SYS.UET$ 裡面，Free Extent 則是記錄在 SYS.FET$ ，當 Segment 需要新增 Extent 時便會從 SYS.FET$ 來得知有哪些 Free Extent 可以用， SYS.FET$ 裡面記錄的某一筆 Free Extent 被拿去使用時，便會從 SYS.FET$ 裡面刪除這筆資料，同時會在 SYS.UET$ 新增一筆資料表示這個 Extent 已被使用。這種做法的缺點是會增加系統額外的 I/O 操作，因為除了前台對 Table 資料異動的 I/O 之外，後端也要不斷的對 FET$ 與 UET$ 進行操作，容易影響到整體的效能。

• Local Management:

對於 Free Extent 的管理不使用 UET$ 與 FET$ ，而是以 bitmap 的方式直接在 Tablespace 所屬的 Data File Header 上直接標記有哪些空的 Block 可以做使用， Segment 需要新增 Extent 的時候可以直接從 Data File Header 得知可用空間然後馬上進行擴充，不用再去查找 FET$ 也不用對 FET$ 與 UET$ 進行操作，在流程上省去了很多動作，此種管理方式對整體效能較好。

在建立 Tablespace 的當下使用 extent management 關鍵字就可以選擇要使用哪一種管理方式了:

SQL> create tablespace ts1 datafile '/u01/app/oradata/ts1_01.dbf' SIZE 50M extent management dictionary default storage (initial 50K next 50K minextents 2 maxextents 50); (建立 Extent Management 為 Dictionary 的 Tablespace 起始 segment 50K 不夠的時候以 50k 為單位擴充，最大可擴充 50 個 Extent) SQL> create tablespace ts1 datafile '/u01/app/oradata/ts1_01.dbf' SIZE 50M extent management local;  (建立 Extent Management 為 Local 的 Tablespace)

在 Local Management 下系統會自行決定下個 Extent 要增長的大小，預設以 64K 為單位增長，隨著 Segment 的擴充也有可能增加 Extent 的單位至 1M、8M 或是64M 。我們可以透過設定 Uniform size 來固定每次增加 Extent 的大小，例如每次固定增加 256K:

SQL> create tablespace ts1 datafile '/u01/app/oradata/ts1_01.dbf' SIZE 50M extent management local uniform size 256K;

現在 Oracle 預設的 Extent Management 都是 Local Management ，也就是不指定 Extent Management 時都是用 Local Management ，因為 Local Management 的管理方式與效能較好， Dictionary Management 已經不再使用了。透過 dba_tablespaces 可以得知目前 Tablespace 是使用哪一種 Extent Management:

轉換 Tablespace 為 Dictionary 或是 Local Management 可以使用dbms_space_admin 來達成:

SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');  (將 Tablespace ts1 由 Dictionary Management 轉換為 Local Management) SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2'); (將 Tablespace ts2 由 Local Management 轉換為 Dictionary Management)

不過現在預設都是使用 Local Management ，也不太會有轉換的需求了。

如果要刪除一個 Tablespace ，使用 drop tablespace 命令:

SQL> drop tablespace ts1;  (刪除 tablespace ts1) SQL> drop tablespace ts2 including contents and datafiles; (刪除 tablespace ts1 並且連同相對應的 Data File 也一併刪除)

除此之外，我們還可以使用 alter tablespace 命令來更改 tablespace 的屬性，例如將 Tablespace 更改為唯讀模式 (read-only) 或是 nologging :

SQL> alter tablespace ts1 read only;  (將 tablespace ts1 改為 read-only) SQL> alter tablespace ts1 read write;  (將 tablespace ts1 改為 read-write) SQL> alter tablespace ts2 nologging;  (將 tablespace ts2 改為 nologging) SQL> alter tablespace ts2 logging;  (將 tablespace ts2 改為 logging)

Tablespace 建立起來預設都是 logging 模式，也就是在此 Tablespace 做的任何異動都會記錄到 redo log 當中，建議不要將 Tablespace 更改為 nologging ，因為 nologging 模式下， Tablespace 的異動不會記錄在 redo log ，只要發生異常就無法還原。我們有時候會為了加速 insert 的效能短暫的將 Tablespace 設定為 nologging ，不過在做完這個任務後必須馬上將它改回 logging 並且執行備份，避免造成無法還原的情況。

 

對於 Tablespace 的監控，最常使用的就是 dba_tablespaces 、 dba_free_space 與 dba_data_files 來查看狀態了，而 DBA 則是需要每天觀察 Tablespace 的使用率，我們可以使用下列語法來查詢:

col ts_name format a12 col type format a12 select a.tablespace_name ts_name,c.contents type,           round((a.mbytes - nvl(b.mbytes,0))/a.mbytes * 100,2) "USED(%)",           round(nvl(b.mbytes,0)/a.mbytes * 100,2) "free(%)",           round(nvl(b.mbytes,0),2) "free(MB)",a.mbytes "total(MB)"  from (select tablespace_name,sum(bytes)/1024/1024 mbytes           from dba_data_files         group by tablespace_name) a,        (select tablespace_name,sum(bytes)/1024/1024 mbytes            from dba_free_space         group by tablespace_name) b,           dba_tablespaces c where a.tablespace_name=b.tablespace_name(+)   and a.tablespace_name=c.tablespace_name(+) union select a.tablespace_name ts_name,b.contents type,           round(nvl(b.mbytes,0)/a.mbytes * 100,2) "USED(%)",           round((a.mbytes - nvl(b.mbytes,0))/a.mbytes * 100,2) "free(%)",           round((a.mbytes - nvl(b.mbytes,0)),2) "free(MB)",a.mbytes "total(MB)"   from (select tablespace_name,sum(bytes)/1024/1024 mbytes             from dba_temp_files group by tablespace_name) a,         (select ss.tablespace_name,ts.contents,                   sum((ss.used_blocks*ts.block_size))/1024/1024 mbytes             from gv$sort_segment ss, dba_tablespaces ts           where ss.tablespace_name = ts.tablespace_name             group by ss.tablespace_name,ts.contents) b  where a.tablespace_name=b.tablespace_name  order by ts_name;

上述範例使用的是 dba_data_files 中的 bytes 欄位來計算使用率，如果 Data File 有設定 autoextend 的話，可以將 bytes 欄位更改為 maxbytes 來計算會更加的精確。以下範例是以 maxbytes 計算之:

col ts_name format a12 col type format a12 select a.tablespace_name ts_name,c.contents type,  round((a.rmbytes - nvl(b.mbytes,0))/decode(a.mbytes,0,32767,a.mbytes) * 100,2) "USED(%)",  100 - round((a.rmbytes - nvl(b.mbytes,0))/decode(a.mbytes,0,32767,a.mbytes) * 100,2) "free(%)",  round(nvl(b.mbytes,0),2) "free(MB)",a.rmbytes "total(MB)" from  (select tablespace_name,sum(maxbytes)/1024/1024 mbytes,sum(bytes)/1024/1024 rmbytes from dba_data_files    group by tablespace_name) a,  (select tablespace_name,sum(bytes)/1024/1024 mbytes from dba_free_space    group by tablespace_name) b,   dba_tablespaces c where a.tablespace_name=b.tablespace_name(+)   and a.tablespace_name=c.tablespace_name(+) union select a.tablespace_name ts_name,b.contents type,  round((a.rmbytes - nvl(b.mbytes,0))/decode(a.mbytes,0,32767,a.mbytes) * 100,2) "USED(%)",  100 - round((a.rmbytes - nvl(b.mbytes,0))/decode(a.mbytes,0,32767,a.mbytes) * 100,2) "free(%)",  round((a.rmbytes - nvl(b.mbytes,0)),2) "free(MB)",a.rmbytes "total(MB)" from  (select tablespace_name,sum(maxbytes)/1024/1024 mbytes,sum(bytes)/1024/1024 rmbytes    from dba_temp_files group by tablespace_name) a,  (select ss.tablespace_name,ts.contents,sum((ss.used_blocks*ts.block_size))/1024/1024 mbytes    from gv$sort_segment ss,dba_tablespaces ts   where ss.tablespace_name=ts.tablespace_name   group by ss.tablespace_name,ts.contents) b where a.tablespace_name=b.tablespace_name order by ts_name;

這邊要注意的是，如果 Data File 沒有設定為 autoextend 屬性，則 dba_data_files 裡面的 maxbytes 欄位會顯示為 0 :

5.3 Tablespace 介紹

Tablespace(表格空間) 是 Oracle 資料庫用來存放資料的一個虛擬空間，我們可以將 Tablespace 視為一個盒子，資料庫裡面可以建立多個盒子然後把資料分門別類的放在這裡面。 Tablespace 大致上可以分為兩種類型， Small File Tablespace 與 Big File Tablespace。

• Small File Tablespace: 

表示一個 Tablespace 可以由一個或多個實體的 Data File 所組成，一個 Small File Tablespace 最多可以有 (2 的 10 次方) -1 個 Data File，而每個 Data File 最多可以容納 (2 的 22 次方) 個 Block ， Block以預設大小 8k 來換算的話，一個 Data File 最大可以到 32G。早期由於單一檔案大小受到作業系統限制，只有一個檔案大小的 Tablespace 是不足以容納所有的資料，也因此 Oracle 讓 Tablespace 能夠由多個 Data File 來組成，藉以擴充 Tablesapce 的空間。

Small File Tablespace 的好處是，因為是由多個 Data File 所組成，所以可以把這些 Data File 分別存放在不同的磁碟上，這樣就可以達到 I/O 分散的目的；另外一個好處是，當其中一個 Data File 有所損壞時，這個時候只會造成存放在這個 Data File 上面的資料無法使用或查詢， Tablespace 裡面其餘的資料還是可以做使用，不會因為單一個 File 損壞而造成整個 Tablespace 損毀。而缺點是 DBA 必須時常監控 Tablespace 以及所屬的 Data File 使用率，如果空間不足的話必須手動再為它增加 Data File。

• Big File Tablespace: 

從 Oracle 10g 開始出現的新功能，表示一個 Tablespace 只能有一個 Data File 所組成，而這個 Data File 最大可以容納 (2 的 32 次方) 個 Block ，以預設大小 8k 來換算的話，一個 Data File 最大可以到 32T 。這種類型的 Tablespace 最大的缺點當然是只有一個 Data File ，若此 File 損壞就會造成整個 Tablespace 無法使用，而過大的單一 Data File 也有可能造成備份的效能不好；然而它的好處當然是可以減少 DBA 的負擔，只要作業系統上的硬碟空間足夠，基本上不太需要去關注 Tablespace 的空間使用率。

至於選擇 Small File 或是 Big File 哪種類型的 Tablespace 比較好，這並沒有一定的標準，主要還是看 DBA 的管理習慣而定，有些人習慣使用 Small File Tablespace ，而有些人為了管理上的方便選擇了 Big File 。然而值得注意的是， Tablespace 在建立的當下就必須選擇是要使用 Small File 或是 Big File ，一旦建立了就無法更改這個屬性。

Tablespace 建立的預設選項都是 Small File ，例如建立一個名為 TBS1 、大小為 100M 的 Tablespace:

SQL> create tablespace tbs1 datafile '/u01/app/oradata/orcl/tbs1_01.dbf' size 100M;

當 Tablespace 空間不足時可以為它增加 Data File 或是先將原本的 File Resize 擴充:

SQL> alter database datafile '/u01/app/oradata/orcl/tbs1_01.dbf' resize 512M; (將原本的 file resize) SQL> alter tablespace tbs1 add datafile '/u01/app/oradata/orcl/tbs1_02.dbf' size 512M; (為原本的 Tablespace 增加 File)

另外也可以為 Data File 設定是否自動增長的選項，例如先建立 Data File大小為 100M 的 Tablespace ，當空間不足時請以 50M 為單位自動增長至 512M :

SQL> create tablespace tbs2 datafile '/u01/app/oradata/orcl/tbs2_01.dbf' size 100M autoextend on next 50M maxsize 512M;

如果不設定 next 的話預設是以 100M 為單位增長，不設定 maxsize 的話以 Small File Tablespace 來說，最大為 32G 。

如果要建立 Big File Tablespace 的話，則在建立的同時必須加上 bigfile 關鍵字，例如建立一個名為 btbs1 的 Big File Tablespace:

SQL> create bigfile tablespace btbs1 datafile '/u01/app/oradata/orcl/btbs1.dbf' size 100M autoextend on;

Tablespace 以種類來區分的話可以分為永久(Permanent Tablespace) 、還原(Undo Tablespace) ，與暫存(Temporary Tablespace)。 

Permanent Tablespace 即是一般儲存資料的 Tablespace ，像是 System Tablespace 、 Users Tablespace ，或是其他使用者自行建立的 Tablespace 例如上述例子中的 tbs1 、 tbs2 、 btbs1 都是屬於 Permanent Tablespace。

Undo Tablespace 只提供資料庫裡面的 Rollback Segment 所使用，不能在上面建立其他物件。

Temporary Tablespace 提供 Global Temporary Table 或是 SQL 執行過程中的排序行為所使用。

建立 Undo Tablespace 必須加上 undo 關鍵字:

SQL> create undo tablespace undotbs1 datafile '/u01/app/oradata/orcl/undotbs1_01.dbf' size 100M;

建立 Temporary Tablespace 則是須使用 temporary 與 tempfile 關鍵字:

SQL> create temporary tablespace temp1 tempfile '/u01/app/oradata/orcl/temp1_01.dbf' size 100M;

在資料庫建立完成時，預設會產生這幾個 Tablespace:

1. SYSTEM: 用來存放資料庫系統物件，為 sys 與 system 使用者所使用。由於是存放系統物件，因此 SYSTEM Tablespace 絕對不可以損壞，一但有問題會造成整個資料庫無法使用。

2. SYSAUX: 用來存放 AWR 相關的資料，可以由 v$sysaux_occupants 來查詢 SYSAUX 存放了哪些項目。 AWR 為 Oracle 10g 開始才有的新功能， Oracle 10g 之前的版本不會有 SYSAUX Tablespace 。

3. UNDOTBS1: 為 Undo Tablespace ，也視為系統 Tablespace 的一部分，Undo Tablespace 損壞也有可能會造成資料庫無法正常運行。

4. TEMP: 為 Temporary Tablespace 。

5. USERS: 系統預設的 Permanent Tablespace 。

一般來說，使用者要建立屬於自己的系統物件時會再建立新的 Tablespace 客製化所使用，很少會使用預設的 USERS 來當作應用系統(application) 所使用的 Tablespace 。

5.2 資料庫實體結構

Oracle 資料庫主要是由實體結構的檔案所組成，包含 Control File 、 Data File 、 Temp File 、 Redo Log / Archive Log 、 Parameter File ， 以及 Password File，其中 Parameter File 已於 4.5 章節詳述過，其餘檔案的功能分述如下:

Control File: 為 Oracle 資料庫最核心的檔案，紀錄著此資料庫的重要資訊，包括資料庫名稱 、 SCN Number 、 Data File 與 Redo Log 存放位置，以及備份資訊…等，若資料庫缺少了 Control File ，那麼就無法開啟。由於 Control File 如此重要，一般都建議建立兩個 Control File 並將它們存放在不同的磁碟上互為備份，資料庫要使用幾個 Control File 由參數 control_files 所決定，值得注意的是，如果使用多個 Control File ，則這些 Control File 的內容以及時間點必須都要一致才行。我們可以從參數檔或是 v$controlfile 裡面來得知目前資料庫的 Control File 的訊息。

Data File: 為 Tablesapce 的實體架構，是所有資料庫物件存放的實體位置，可以由 v$datafile 或是 dba_data_files 來查詢 Data File 的資訊，建議使用 dba_data_files ，因為它可以揭露 Data File 與 Tablespace 之間的關聯。

Temp File: 為 Temporary Tablespace 所使用，主要是用來存放 Temporary Table 或是 SQL 執行時的排序所用，可以由 v$tempfile 或是 dba_temp_files 來得知目前 temp file 資訊。

Redo Log / Archive Log: Redo Log 為資料庫的交易日誌檔，資料庫所產生的交易紀錄都會透過 Log Writer(LGWR) 寫入 Redo Log。

每一個 Redo Log Group 底下可以建立一個或多個 Redo Log File ，而且 Redo Log Group 是不斷循環來使用，例如現在有 3 個 Redo Log Group ，第一個 Group 寫滿後切到第二個 Group 繼續寫，第二個寫滿後換寫第三個 Group ，而第三個 Group 寫滿後又會把第一個 Group 資料清除然後從第一個 Group 開始寫資料，由於 Redo Log Group 是循環使用，當一個 Group 被重複使用時，之前存放在此 Group 的交易紀錄就會被刪除，為了保留這些交易紀錄，在這個 Group 被覆寫之前，必須先把這些紀錄複製一份出來保存，這些被複製出來的交易紀錄就是 Archive Log 。 Archive Log 存放的位置由參數 log_archive_dest_n 所決定，n 表示從 1 至 31 的數字，表示Archive Log 可以同時存放一個或多個副本，最多可以存放 31 個副本，一般資料庫有開啟 Archive Log Mode 都只有先設定 log_archive_dest_1 而已。至於 Redo Log 的資訊則是由 v$log 以及 v$logfile 來得知:

SQL> select a.thread#,a.group#,b.member,a.bytes/1024/1024 mb,a.status        from v$log a,v$logfile b       where a.group#=b.group#;

Password File: 為 sys 使用者作為遠端登入時的認證所使用，由於 sys 為資料庫最高權限使用者，具有 sysdba 權限，因此 Client 端使用具有 sysdba 權限的使用者都必須要透過 Password File 的認證，除此之外，資料庫參數 remote_login_passwordfile 必須設定為 exclusive 或是 shared 才能允許 sys 從遠端登入。 Password File 必須放置在 $ORACLE_HOME/dbs 底下 (Oracle 12c 之後可以存放在 ASM) ，並且檔案格式必須為 orapw<SID> ，如果其中一項條件不符合，那麼就無法進行認證。 Password File 在資料庫建立的同時會自動產生，如果遺失了，可以透過 orapwd 命令來產生，例如產生密碼為 welcome1 的 Password File:

[oracle@db11gr2 dbs]$ orapwd file=orapworcl password=welcome1

透過 v$pwfile_users 可以得知目前資料庫有哪些使用者必須使用 Password File 進行認證:

到了 Oracle 12.2 版本之後更可以使用 v$passwordfile_info 來查詢目前資料庫所使用的 Password File 位置。

資料庫實體架構是資料實際存放的位置， DBA 必須要保護好這些檔案以及定期的備份來確保資料庫能正確的運行。