Oracle資料庫的空間管理技巧

在oracle資料庫中，DBA可以透過觀測一定的表或檢視來了解當前空間的使用狀況，進而作出可能的調整決定。

---- 一.表空間的自由空間

---- 透過對錶空間的自由空間的觀察，可用來判斷分配給某個表空間的空間是太多還是不夠。請看下列的語句

SQL > select a.file_id "FileNo",a.tablespace_name
"Tablespace_name",
2 a.bytes "Bytes",a.bytes-sum(nvl(b.bytes,0)) "Used",
3 sum(nvl(b.bytes,0)) "Free",
4 sum(nvl(b.bytes,0))/a.bytes*100 "%free"
5 from dba_data_files a, dba_free_space b
6 where a.file_id=b.file_id(+)
7 group by a.tablespace_name ,
8 a.file_id,a.bytes order by a.tablespace_name;

File Tablespace
No _nameBytes Used Free %free
------ --------- -------- --------- --------- ---------
11IDX_JF .146E+09 849305600 1.297E+09 60.431806
9 JFSJTS 2.146E+09 1.803E+09 343793664 16.016961
10JFSJTS 2.146E+09 1.359E+09 787431424 36.685546
2 RBS523239424 359800832 163438592 31.235909
12RBS1.610E+09 1.606E+09 3104768 .19289495
8 RBSJF 3.220E+09 2.716E+09 504356864 15.662396
7 SFGLTS 2.146E+09 1.228E+09 918159360 42.776014
6 SFSJTS 2.146E+09 1.526E+09 620093440 28.889457
1 SYSTEM 523239424 59924480 463314944 88.547407
3 TEMP 523239424294912 522944512 99.943637
4 TOOLS 15728640 12582912 314572820
5 USERS 7340032 81927331840 99.888393

12 rows selected.

---- 可以看出，在FileNo為12的表空間RBS中，只有0.19%的分配空間未被使用，這個比例太小了，而在SYSTEM及TEMP等表空間中，高達80%以上的空間未被利用，對於生產型資料庫，這個表空間的設定有些偏高。

---- 關於自由空間的管理，有下面的一些建議：

利用Export及Import命令卸出和裝入表空間可以釋放大量的空間，從而緩解增加另外的資料檔案的要求。

如果包含具有高插入(insert)和更新(update)活動的表的表空間中自由空間的比重下降到了15%以下，要為此表空間增加更多的空間。

對於一個基本是靜態表資料的表空間，如果有多於20%的自由空間，則可以考慮減少分配給它的檔案空間量。

減少SYSTEM表空間的空間量比較困難，因為那要重建資料庫。
---- 二 表及索引的擴充套件
---- A.為了防止表或索引被過分擴充套件，及時實現對資料庫的調整，使用者應當經常對有關物件進行觀察。

---- 我們可以認為，擴充套件區域大於5個的表或索引為過分擴充套件(overextended)。請看下面的語句：

SQL > select substr(segment_name,1,15)
Segment_name,segment_type,
2 substr(tablespace_name,1,10)
Tablepace_name,extents,Max_extents
3from dba_segments
4where extents >5 and owner=’JFCL’
5order by segment_name;

SEGMENT_NAMESEGMENT TABLEPACE_
EXTENTS MAX_EXTENTS
_TYPE
-------------- --------- ----------
CHHDFYB TABLE JFSJTS 11121
CHHDFYB_DHHMINDEX JFSJTS9121
DJHZFYB_BF TABLE JFSJTS 17500
DJHZFYB_DJHMINDEX IDX_JF6500
DJHZFYB_JZHMINDEX IDX_JF7500
GSMFYB TABLE JFSJTS 11121
JFDHTABLE JFSJTS 14500
JFDH_DHHM INDEX IDX_JF 61500
JFDH_JZHM INDEX IDX_JF 64500
XYKFYB TABLE JFSJTS7121
YHDATABLE JFSJTS6500
YHDA_BAKTABLE JFSJTS6500
YHHZFYB_12 TABLE JFSJTS 10500

13 rows selected.

---- 透過觀察， DBA可以及時發現問題並進行相應的處理。
---- 我們可以利用export卸出表，然後刪除表，再利用import命令將表裝入，這樣，可以將不連續的區域合併成一個連續的空間。

---- B.如果使用者希望對錶的空間設定進行最佳化，例如，需要改變表EMP的initial引數，可以採用下面的方法：

---- 1.在將EMP表卸出並刪除後執行imp命令時使用indexfile引數：

---- imp userid=scott/tiger file=emp.dmp indexfile=emp.sql oracle把表和索引的建立資訊寫到指定的檔案，而不是把資料寫回。

---- 2.開啟emp.sql檔案：

REM CREATE TABLE "SCOTT"."EMP" ("EMPNO"
NUMBER(4, 0), "ENAME"
REM VARCHAR2(10), "JOB" VARCHAR2(9),
"MGR" NUMBER(4, 0), "HIREDATE" DATE,
REM "SAL" NUMBER(7, 2), "COMM" NUMBER
(7, 2), "DEPTNO" NUMBER(2, 0))
REM PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255 LOGGING STORAGE(INITIAL
REM 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS
121 PCTINCREASE 50 FREELISTS
REM 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USER_DATA" ;
REM ... 14 rows


---- 對它進行編輯，去除"REM"等資訊，找到Initial引數，根據需要改變它。
---- 3.在SQL*plus中執行emp.sql。

---- 4.裝入資料：

---- imp userid=scott/tiger ignore=y file=emp.dmp

---- 需要注意的是，ignore引數必須設為Y.

---- C.可以用下面的語句來觀察表或索引距離達到最大擴充套件的狀況，“UNUSE”為距離達到最大擴充套件的值，在User_extents表中，extent_id是從0開始記述數的。

SQL >select a.table_name "TABLE_NAME",max
(a.max_extents) "MAXEXTENTS" ,
2 max(b.extent_id)+1 "IN USE", MAX
(a.max_extents)-(max(b.extent_id)+1) "UNUSE"
3 from user_tables a, user_extents b
4where a.table_name=b.segment_name
5 group by a.table_name ORDER BY 4;

TABLE_NAME MAXEXTENTS IN USEUNUSE
---------- ----------- -------- ---------
YZPHB 98 1 97
SHJYB 121 1 120
SHFYB 121 1 120
RCHDB 121 1 120
SJTXDZB121 1 120
SJTXDAB121 1 120
CHYHB 121 1 120
JFDH 50014 486
8 rows selected.

---- 如果“UNUSE"小到一定的程度，我們就應該加以關注，進行適當的調整處理。
---- 三 關於連續空間

---- 可以用下面的語句來檢視資料庫中的自由空間：

SQL > select * from dba_free_space
where tablespace_name=’SFSJTS’
2 order by block_id;

TABLESPACE FILE_ID BLOCK_ID BYTESBLOCKS
_NAME
----------- --------- --------------
SFSJTS 6 133455 1064960 130
SFSJTS 6 133719 1032192 126
SFSJTS 6 133845 1064960 130
SFSJTS 6 135275 1064960 130
SFSJTS 6 135721 606208 74
SFSJTS 6 139877 901120 110
SFSJTS 6 143497 737280 90
SFSJTS 6 220248 737280 90
SFSJTS 6 246228 491520 60
SFSJTS 6 261804 1064960 130

10 rows selected.

---- 我們可以透過命令的結果來估計相鄰自由空間的真正數量。對每一行，用起始快的id(BLOCK_ID)加上自由塊(BLOCKS)的數量，如果其和與下一行的塊id(BLOCK_ID)相等，則此兩行是連續的。如上例第二行和第三行，133719+126=133845，而1338456+130!=135275,所以從block_id為133719開始，有126+130=256個block的連續空間。
---- 在oracle資料庫的後臺，系統監視器(SMON)週期性地合併自由空間相鄰的塊，以得到更大的連續塊。而DBA可以用SQL命令來完成這個工作：

---- alter tablespace tablespace_name coalesce;

---- oracle空間管理對資料庫的工作效能有重要影響，其管理方法值得我們認真摸索研究。