表空間和資料檔案的管理

本文概要介紹了Oracle資料庫表空間儲存分配和管理的主要特性及操作。

一、表空間的物理關係和邏輯關係

database：資料庫。
users：使用者，一個資料庫包含多個使用者。
schema：資料庫物件根據使用者與物件的從屬關係組織為不同的模式，一個資料庫使用者所擁有的所有物件稱為一個模式，模式名與使用者名稱相同，一個使用者模式下的所有資料庫物件是透過多個型別的段來儲存的。
tablespace：表空間，從效能和管理上做邏輯劃分，總體上可劃分為系統表空間、撤銷表空間、臨時表空間、使用者表空間。應儘量減少系統表空間的負擔，使用者資料不要放在系統表空間。10g之後多了SYSAUX表空間（輔助系統表空間），主要用於記錄大量的自調整、最佳化分析的資料等。
data file：資料檔案，大塊資料順序訪問用一個，小塊資料隨機訪問用多個。
segment：段，一種儲存結構，不同型別的資料庫物件以不同的段形式存在，如資料段、索引段、臨時段、撤銷段等。
extent：範圍，Oracle表空間內部分配和回收空間的最小單位，由若干連續的資料塊構成。範圍的大小分配由建立表空間時指定，未指定時則採用Oracle的預設儲存引數。
data block：Oracle資料塊，Oracle的最小IO單位，由多個作業系統塊（os block）組成。主塊大小由初始化引數db_block_size指定（一般預設8K），從塊大小在建立表空間時指定。設定db_nk_cache_size引數為1，在SGA中可分配非標準的DB塊記憶體，如db_2k_cache_size、db_4k_cache_size、db_32k_cache_size等。
os block：作業系統塊，大小由作業系統決定。

二、表空間儲存引數的設定

1、檔案屬性的設定

大檔案（bigfile）與小檔案（smallfile）：大檔案是Oracle 10g開始引進的特性，大檔案表空間建立在單個資料檔案上，檔案大小可達32TB。而小檔案表空間由一至多個資料檔案構成。大檔案特性主要為超大型資料庫設計，為了克服在資料變更時Oracle要更新所有資料檔案頭資訊而可能帶來的效率問題。要使用大檔案表空間，在建立表空間時使用bigfile或smallfile關鍵字，create [bigfile | smallfile] tablespace，省略時預設為小檔案表空間。

自動擴充套件（autoextend）：autoextend on | off

檔案狀態資訊：online | offline

表空間的資料檔案可以同時建立多個
create [bigfile | smallfile] tablespace mytbs
datafile 'd:\oradata\mes\mytbs01.dbf' size 100m autoextend on next 10m [maxsize 500m | unlimited],
'd:\oradata\mes\mytbs02.dbf' size 100m autoextend off;

2、空間管理的方式

Oracle提供兩類引數設定：範圍管理，段空間管理。

範圍管理（extent management）：決定範圍分配資訊的儲存位置，有資料字典管理（dictionary）和本地管理（local）兩種方式。前者將表空間的範圍分配資訊儲存於系統表空間的資料字典中，後者將範圍分配資訊以bitmap形式儲存於表空間資料檔案頭部的特殊區域。Oracle推薦並預設使用本地管理的表空間，以提高表空間的操縱效能，Oracle保留資料字典方式管理的表空間只是為了向後相容。如果system表空間是本地管理的，則不允許建立資料字典管理的表空間。

段空間管理（segment space management）：指定段內空間的管理方式，有auto和manual兩種。Oracle推薦使用auto管理方式，manual方式為向後相容所保留，這需要手動設定段空間的使用引數pct_free和pct_used等。

建立表空間時指定範圍管理和段空間管理方式
create [bigfile | smallfile] tablespace mytbs
datafile 'd:\oradata\mes\mytbs01.dbf' size 100m autoextend on next 10m [maxsize 500m | unlimited],
'd:\oradata\mes\mytbs02.dbf' size 100m autoextend off
extent management [local | dictionary] autoallocate | uniform size 512k
segment space management [auto | manual];

為了減少不必要的額外I/O操作，uniform size的最佳大小應為引數db_block_size和引數db_file_multiblock_read_count設定值兩者的乘積。

3、內部儲存引數的設定

建立表空間時如果指定用老式的資料字典範圍管理方式，則可進一步設定預設的儲存引數（default storage），以控制段內空間的範圍分配。
另可透過關鍵字blocksize指定表空間的非標準資料塊大小，否則按標準資料塊大小（db_block_size引數指定）來設定。建立非標準資料塊大小的表空間，需要事先在SGA中分配非標準資料塊的記憶體區域，可透過引數db_nk_cache_size = 1來設定。

default storage(
[initial n K|M]
[next n K|M]
[minextents n]
[maxextents n|unlimited]
[pctincrease n]

initial：段空間分配的第一個範圍的位元組數。當使用者建立模式物件時，Oracle為範圍分配空間。
next：分配下一個範圍的位元組數。
minextents：指定當建立物件時分配範圍的最小數目，預設為1，含義是Oracle僅分配初始範圍，最大值取決於作業系統。如果minextents大於1，則Oracle根據initial、next、pctincrease的值計算下一個範圍的大小。
maxextents：指定Oracle可以分配給一個段結構範圍的最大數目，包括第一個範圍。最小值為1，預設值和最大值取決於作業系統。unlimited表示按需要自動分配範圍數目。
pctincrease：指定第三個和以後的範圍比前一個範圍增大的百分比。早期預設值是50，表示每個擴充套件比前一個大50%，最小值為0，表示第一個範圍之後的所有範圍的大小都相同。pctincrease引數設定大於0，可令表空間中的小段擁有小的範圍，大段擁有大的範圍並使範圍的數目儘可能少。

使用舉例
create tablespace mytbs
datafile 'd:\oradata\mes\mytbs01.dbf' size 100m
blocksize 4096
default storage(
initial 256k
next 256k
minextents 2
maxextents 100
pctincrease 50);

此程式碼建立前需要對非標準資料塊資料緩衝區db_4k_cache_size進行設定
alter system set db_4k_cache_size=1;

之後可看到Oracle實際分配的緩衝區大小
show parameter db_4k_cache_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_4k_cache_size                     big integer 32M

從Oracle 10g開始，建立表空間時自動設定為extent management local autollocate和segment space management auto，此時create tablespace語句將忽略default storage子句。

三、表空間的維護管理

1、更改表空間的狀態

表空間離線，如在做系統恢復、資料檔案移位等操作時。system表空間、預設的臨時表空間、包含活動回滾段的表空間（當前的撤銷表空間）不能離線。
alter tablespace … offline;

offline包括三種模式：
offline normal：預設，所有資料檔案做檢查點，有離線的資料檔案時則無法正常離線。
offline immediate：立即，離線時不做檢查點，用於基於時間點的恢復。
offline temporary：臨時，所有聯機的資料檔案做檢查點，資料檔案可分聯機和離線，未聯機的不管，則恢復時可能需要介質恢復。

表空間聯機
alter tablespace … online;

對於一些用來專門儲存歷史或靜態資料的表空間，可將其設定為只讀狀態，可以防止對資料的意外更新、刪除。只讀表空間不必週期性備份，只需備份一次即可。

表空間設定為只讀
alter tablespace … read only;

表空間設定為預設的可讀寫
alter tablespace … read write;

2、表空間重新命名

alter tablespace … rename to …;

3、刪除表空間

drop tablespace … [including contents [and datafiles]];

當表空間中包含有永久的資料庫物件時，必須顯示指定including contents子句。若要一併刪除磁碟上的作業系統檔案，可同時指定and datafiles子句，否則僅將表空間從資料字典中刪除。

表空間一旦刪除，其中資料將永久丟失，因此在正式刪除表空間前，最好先將其設定為offline狀態，經時間考驗確認不再需要後再刪除。

4、表空間的擴容

三種方法：

1）開啟表空間資料檔案的自動擴充套件屬性。
alter database datafile 'd:\oradata\mes\mytbs01.dbf' autoextend on;

2）手動調整已有資料檔案的大小，使用alter database語句，如
alter database datafile 'd:\oradata\mes\mytbs01.dbf' resize 200m;

對於臨時表空間檔案
alter database tempfile 'd:\oradata\mes\temp01.dbf' resize 200m;

resize也可以將資料檔案尺寸調小，前提是已有的資料能夠被新的大小容納。

3）為表空間新增新的資料檔案，使用alter tablespace語句，如
alter tablespace mytbs add datafile 'd:\oradata\mes\mytbs02.dbf' size 200m;

對於臨時表空間檔案
alter tablespace temp add tempfile 'd:\oradata\mes\temp02.dbf' size 200m;

表空間資料檔案一旦新增，則不能從中去除，除非刪除整個表空間。

5、合併自由空間

基於資料字典管理的表空間，在經過一段時間的使用後，由於不斷的分配和釋放空間，表空間中的碎片會逐漸增多，此時可以對自由空間做合併。
alter tablespace … coalesce;

本地管理的表空間對此做了改善，不需要該操作。

6、臨時表空間

主要用於存放臨時排序資料等，資料首先會存放在PGA記憶體的排序區中，排序區大小由引數sort_area_size指定。
show parameter sort_area_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     65536

建立臨時表空間
create temporary tablespace ... tempfile '...';

修改資料庫預設的臨時表空間
alter database default temporary tablespace ...;

檢視資料庫預設的臨時表空間
col property_value for a30
select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
------------------------------
TEMP

7、撤銷表空間

資料庫當前的UNDO表空間由引數undo_tablespace指定
show parameter undo_tablespace;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1

建立撤銷表空間
create undo tablespace UNDOTBS2 datafile 'd:\oradata\mes\undotbs02.dbf' size 200m autoextend on;

將資料庫的預設UNDO表空間切換到新建立的表空間
alter system set undo_tablespace = UNDOTBS2;

檢視更改結果
show parameter undo_tablespace;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2

四、資料檔案的管理

管理資料檔案需要考慮以下幾方面的問題：

1、資料檔案的數量

作業系統會限制每個過程可同時開啟的檔案數量。
Oracle會限制每個資料庫例項可開啟的資料檔案的數量，該限制由初始化引數db_files決定。
在建立資料庫時，引數maxdatafiles決定了在控制檔案中用於記錄資料檔案的部分的大小，這也限制了資料庫可擁有的資料檔案數量。
少量的大資料檔案要優於大量的小資料檔案，因為這樣可以減少同時開啟的檔案個數，減少對資料檔案頭部的更新數。

2、資料檔案的尺寸

除SYSTEM、SYSAUX、UNDO、TEMP等幾個系統必要的表空間需要保證其大小外，其餘表空間根據資料量需要確定大小，無特殊限制。

3、資料檔案的位置

資料檔案的物理位置將影響資料庫效能。
應考慮將資料檔案和索引檔案分別放在不同的磁碟上以改善效能。
應考慮將資料檔案和日誌檔案放在不同的磁碟上，避免讀寫資料檔案和寫日誌檔案的I/O競爭。
出於安全考慮也應將資料檔案和日誌檔案分磁碟存放，若日誌組有多個日誌成員，這些成員應分別置於不同的物理磁碟上，以保證日誌檔案的安全性。

4、OMF的資料檔案

Oracle管理的資料檔案，由Oracle來管理定義檔名和路徑，不再需要手工指定資料檔案。可以OMF的檔案包括：
Datafiles
Tempfiles
Redo log files
Control files
Block change tracking files

透過設定引數db_create_file_dest來指定目標檔案位置
alter system set db_create_file_dest='d:\oradata';

不再需要指定資料檔案
create tablespace …;
此時Oracle在指定路徑下的<SID>\DATAFILE\下自動建立了資料檔案，如d:\oradata\mes\DATAFILE\O1_MF_MYTBS_FV8S2RLX_.DBF，預設大小100M，且autoextend屬性為unlimited。

可以自定義檔案大小
create tablespace ... datafile size 200m;

增加資料檔案
alter tablespace ... add datafile;

五、資料檔案的維護操作

1、為表空間新增資料檔案
alter tablespace mytbs add datafile 'd:\oradata\mes\mytbs02.dbf' size 200m;

2、改變資料檔案大小，這裡的操作同前面描述的給表空間擴容的操作類似。

1）透過允許或禁止資料檔案的自動擴充套件
alter database datafile 'd:\oradata\mes\mytbs01.dbf' autoextend on;

2）透過手工改變資料檔案的大小
alter database datafile 'd:\oradata\mes\mytbs01.dbf' resize 200m;

3、改變資料檔案的可用性

1）資料檔案聯機
alter database datafile 'd:\oradata\mes\mytbs01.dbf' online;

2）資料檔案離線
alter database datafile 'd:\oradata\mes\mytbs01.dbf' offline [drop];

資料庫開啟時被離線的資料檔案，在回到聯機狀態時，需要介質恢復。對於非歸檔模式的資料庫來說，離線需使用drop選項，離線後如果進行了日誌切換，檔案將無法恢復。

4、重定位資料檔案

方法1：重定位表空間的資料檔案，需要表空間離線。

1）alter tablespace … offline;
2）將資料檔案複製到新的位置，按需要重新命名
3）alter tablespace … rename datafile '…' to '…';
或 alter database rename file '…' to '…';
4）alter tablespace … online;

方法2：重定位資料庫檔案，適用於不能離線的表空間，該方法同樣適用於對聯機日誌檔案的重定位。

1）啟動資料庫到mount狀態
2）複製或移動資料庫檔案到新位置，按需要重新命名
3）alter database rename file '…' to '…';
4）開啟資料庫

六、沒有備份情況下資料檔案的恢復實驗

在歸檔模式下實驗：

1）建立一個表空間mytbs（mytbs01.dbf）
2）在mytbs表空間內建立一張表t1（insert into）
3）shutdown immediate
4）手工刪除表空間mytbs的資料檔案
5）startup
6）將資料檔案mytbs01.dbf離線
7）alter database open;
8）alter database create datafile '…\mytbs01.dbf';
9）recover datafile '…\mytbs01.dbf';
10）將資料檔案mytbs01.dbf聯機
11）檢查資料是否恢復