软件开发培训班 >> 编程开发 >> Oracle


Oracle数据库的空间管理技巧

在Oracle数据库中，DBA可以通过观测一定的表或视图来了解当前空间的使用状况，进而做出可能的调整。

一、表空间的自由空间

通过对表空间的自由空间的观察，可以判断分配给某个表空间的空间太多还是不够。请看下列的语句：












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

　　 File Tablespace 
　　  No     _name    Bytes      Used          Free     %free 
　　------ --------- --------  ---------     --------- --------- 
　　  11     IDX_JF   .146E+09 849305600     1.297E+09 60.431806 
　　   9     JFSJTS  2.146E+09 1.803E+09     343793664 16.016961 
 　　 10     JFSJTS  2.146E+09 1.359E+09     787431424 36.685546 
  　　 2     RBS     523239424 359800832     163438592 31.235909 
 　　 12     RBS     1.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    523239424 294912        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表空间的空间量比较困难，因为那要重建数据库。

二、表及索引的扩展

1. 为了防止表或索引被过分扩展，及时实现对数据库的调整，用户应当经常对有关对象进行观察。

我们可以认为，扩展区域大于5个的表或索引为过分扩展(overextended)。请看下面的语句： 

SQL> select substr(segment_name,1,15) 
　　        Segment_name, segment_type, 
　　        substr(tablespace_name,1,10) 
　　        Tablepace_name, extents, Max_extents 
　　from dba_segments 
　　where extents >5 and owner='JFCL' 
　　order by segment_name; 
　　SEGMENT_NAMESEGMENT  TABLEPACE_EXTENTS  MAX_EXTENTS_TYPE 
　　-------------------  -----------------  ---------------- 
　　  CHHDFYB               TABLE JFSJTS         11121 
　　  CHHDFYB_DHHMINDEX     JFSJTS               9121 
　　  DJHZFYB_BF            TABLE JFSJTS         17500 
　　  DJHZFYB_DJHMINDEX     IDX_JF               6500 
　　  DJHZFYB_JZHMINDEX     IDX_JF               7500 
　　  GSMFYB                TABLE JFSJTS         11121 
　　  JFDHTABLE             JFSJTS               14500 
　　  JFDH_DHHM             INDEX IDX_JF         61500 
　　  JFDH_JZHM             INDEX IDX_JF         64500 
　　  XYKFYB                TABLE JFSJTS         7121 
　　  YHDA                  TABLE JFSJTS         6500 
　　  YHDA_BAKTABLE         JFSJTS               6500 
　　  YHHZFYB_12            TABLE JFSJTS         10500 
　　13 rows selected.

通过观察，DBA可以及时发现问题并进行相应的处理。

我们可以利用export卸出表，然后删除表，再利用import命令将表装入，这样，可以将不连续的区域合并成一个连续的空间。

2. 如果用户希望对表的空间设置进行优化，例如，需要改变表EMP的initial参数，可以采用下面的方法：

a. 在将EMP表卸出并删除后执行imp命令时使用indexfile参数：

imp userid=scott/tiger file=emp.dmp indexfile=emp.sql Oracle把表和索引的创建信息写到指定的文件，而不是把数据写回。

b. 打开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参数，根据需要改变它。

c. 在SQL*plus中执行emp.sql。

d. 装入数据： 

mp userid=scott/tiger ignore=y file=emp.dmp
　　

需要注意的是，ignore参数必须设为Y.

3. 可以用下面的语句来观察表或索引距离达到最大扩展的状况，“UNUSE”为距离达到最大扩展的值，在User_extents表中，extent_id是从0开始记数的。 

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

　 　TABLE_NAME    MAXEXTENTS    IN USE    UNUSE 
　　------------  ------------  --------  ------- 
　　    YZPHB          98           1       97 
　　    SHJYB          121          1       120 
　　    SHFYB          121          1       120 
　　    RCHDB          121          1       120 
　　    SJTXDZB        121          1       120 
　　    SJTXDAB        121          1       120 
　　    CHYHB          121          1       120 
　　    JFDH           500          14      486 
　　 8 rows selected.

如果“UNUSE”小到一定的程度，我们就应该加以关注，进行适当的调整处理。

三、关于连续空间

可以用下面的语句来查看数据库中的自由空间： 

SQL> select * from dba_free_space 
　　 where tablespace_name='SFSJTS' 
　　 order by block_id; 
　　 TABLESPACE    FILE_ID    BLOCK_ID    BYTES    BLOCKS_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空间管理对数据库的工作性能有重要影响，其管理方法值得我们认真摸索研究。