表空间（数据文件shrink）收缩示例

向右看齐 2022-05-23 00:49 297阅读 0赞

正常的数据库管理中会碰到空间不足的情况，一些表空间因为总的磁盘空间有限影响了其正常的扩展，但还有些表空间空余空间很大却不能分给别的表空间使用，这里进行一个表空间的收缩测试。  
以收缩数据文件方式为例，吧数据文件里空余的空间shrink出来给磁盘，供其他表空间扩展使用。  
  
参考  
http://www.oracleonlinux.cn/2011/05/25/10g\_rac\_shrink\_tablespace/  
http://www.34hap.com/database/space/how\_to\_shrink\_datafile.html  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
测试环境oracle10g2  
  
表空间收缩测试  
  
建立测试表空间test  
分配3个数据文件,每个数据文件100M  
  
SQL> create tablespace test datafile 'D:\\oracle\\product\\10.2.0\\rldb\\rldb\\test01.dbf' size 100M;  
表空间已创建。  
SQL> alter tablespace test add datafile 'D:\\oracle\\product\\10.2.0\\rldb\\rldb\\test02.dbf' size 100M;  
表空间已更改。  
SQL> alter tablespace test add datafile 'D:\\oracle\\product\\10.2.0\\rldb\\rldb\\test03.dbf' size 100M;  
表空间已更改。  
\--------------------------------------------------------------------------------------------------------  
建立测试用的表5张  
  
SQL> create table test1 tablespace TEST as select \* from dba\_objects;  
表已创建。  
SQL> create table test2 tablespace TEST as select \* from dba\_objects;  
表已创建。  
SQL> create table test3 tablespace TEST as select \* from dba\_objects;  
表已创建。  
SQL> create table test4 tablespace TEST as select \* from dba\_objects;  
表已创建。  
SQL> create table test5 tablespace TEST as select \* from dba\_objects;  
表已创建。  
  
表1：  
  
SQL> insert into test1 select \* from test1;  
已创建50316行。  
SQL> /  
已创建100632行。  
SQL> /  
已创建201264行。  
SQL> commit;  
提交完成。  
\-----------------------------------------------------------------------------------------------  
查询test表空间用量，每个数据文件用23M左右，加起来有80M左右。  
  
SQL> select file\_id,bytes/1024/1024 from dba\_free\_space where tablespace\_name='TEST';  
  
   FILE\_ID BYTES/1024/1024  
\---------- ---------------  
         8         76.9375  
         6         76.9375  
         7         77.9375  
  
  
采集该表信息  
SQL> exec dbms\_stats.gather\_table\_stats(user,'test1',estimate\_percent=>100);  
PL/SQL 过程已成功完成。  
  
查询表的行数和记录长度用的块多少。  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST1';  
  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     402560       5556  
  
表2：  
  
SQL> insert into test2 select \* from test1;  
已创建402560行。  
SQL> insert into test2 select \* from test2;  
已创建452881行。  
SQL> commit;  
提交完成。  
  
  
SQL> select file\_id,bytes/1024/1024 from dba\_free\_space where tablespace\_name='TEST';  
  
   FILE\_ID BYTES/1024/1024  
\---------- ---------------  
         8         48.9375  
         6         41.9375  
         7         42.9375  
  
SQL> exec dbms\_stats.gather\_table\_stats(user,'test2',estimate\_percent=>100);  
  
PL/SQL 过程已成功完成。  
  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST2';  
  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     905762      12665  
           
表3：  
  
SQL> insert into test3 select \* from test2;  
已创建905762行。  
SQL> insert into test3 select \* from test1;  
insert into test3 select \* from test1  
            \*  
第 1 行出现错误:  
ORA-01653: 表 SYS.TEST3 无法通过 1024 (在表空间 TEST 中) 扩展  
  
表空间不够了，新增加记录失败，先提交前面插入的数据。  
  
SQL> commit;  
提交完成。  
  
增加表空间  
SQL> alter tablespace test add datafile 'D:\\oracle\\product\\10.2.0\\rldb\\rldb\\test04.dbf' size 100M;  
表空间已更改。  
SQL> insert into test3 select \* from test1;  
已创建402560行。  
SQL> commit;  
  
提交完成。  
  
SQL> select file\_id,bytes/1024/1024 from dba\_free\_space where tablespace\_name='T  
EST';  
  
   FILE\_ID BYTES/1024/1024  
\---------- ---------------  
         6          6.9375  
         7          7.9375  
         8          4.9375  
         9         67.9375  
  
SQL> exec dbms\_stats.gather\_table\_stats(user,'test3',estimate\_percent=>100);  
  
PL/SQL 过程已成功完成。  
  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST3  
';  
  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93    1358644      18785  
          
表4；  
  
SQL> insert into test4 select \* from test1;  
已创建402560行。  
SQL> commit;  
提交完成。  
  
SQL> select file\_id,bytes/1024/1024 from dba\_free\_space where tablespace\_name='TEST';  
  
   FILE\_ID BYTES/1024/1024  
\---------- ---------------  
         6           .9375  
         7           .9375  
         8           .9375  
         9         40.9375  
  
SQL> exec dbms\_stats.gather\_table\_stats(user,'test4',estimate\_percent=>100);  
  
PL/SQL 过程已成功完成。  
  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST4';  
  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     452883       6248  
  
表5：  
  
SQL> alter tablespace test add datafile 'D:\\oracle\\product\\10.2.0\\rldb\\rldb\\test05.dbf' size 100M;  
表空间已更改。  
SQL>  insert into test5 select \* from test1;  
已创建402560行。  
SQL>  insert into test5 select \* from test1;  
已创建402560行。  
SQL> commit;  
  
提交完成。  
  
SQL> select file\_id,bytes/1024/1024 from dba\_free\_space where tablespace\_name='TEST';  
  
   FILE\_ID BYTES/1024/1024  
\---------- ---------------  
         6           .9375  
         7           .9375  
         8           .9375  
         9          3.9375  
        10          6.9375  
  
SQL>  exec dbms\_stats.gather\_table\_stats(user,'test5',estimate\_percent=>100);  
  
PL/SQL 过程已成功完成。  
  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST5';  
  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     855444      17257        
          
至此已经创立5个表和5个数据文件，我们检查一下每个数据文件上分布的数据段（表）的情况。  
先查询表空间里的对象，可以看出有的表已经大于100M（单个数据文件  
SQL> select owner,segment\_name,segment\_type,bytes/1024/1024 as Mbtys from dba\_se  
gments where tablespace\_name='TEST';  
  
OWNER      SEGMENT\_NA SEGMENT\_TYPE            MBTYS  
\---------- ---------- ------------------ ----------  
SYS        TEST1      TABLE                      44  
SYS        TEST2      TABLE                     104  
SYS        TEST3      TABLE                     152  
SYS        TEST4      TABLE                      50  
SYS        TEST5      TABLE                     136  
  
  
查看数据文件的里包含的段及其大小  
          
SQL> select SEGMENT\_NAME,PARTITION\_NAME,sum(blocks)   
  2  from dba\_extents where file\_id = 6               
  3  group by SEGMENT\_NAME,PARTITION\_NAME             
  4  order by segment\_name;                           
                                                      
SEGMENT\_NA PARTITION\_NAME                 SUM(BLOCKS)  
\---------- ------------------------------ -----------  
TEST1                                            1920  
TEST2                                            4736  
TEST3                                            4736  
TEST4                                            1024  
TEST5                                             256    
  
  
SQL> select file\_id,SEGMENT\_NAME,PARTITION\_NAME,sum(blocks)  
  2  from dba\_extents where file\_id in (6,7,8,9,10)  
  3  group by file\_id,SEGMENT\_NAME,PARTITION\_NAME  
  4  order by file\_id,SEGMENT\_NAME;  
  
   FILE\_ID SEGMENT\_NA PARTITION\_NAME                 SUM(BLOCKS)  
\---------- ---------- ------------------------------ -----------  
         6 TEST1                                            1920  
         6 TEST2                                            4736  
         6 TEST3                                            4736  
         6 TEST4                                            1024  
         6 TEST5                                             256  
         7 TEST1                                            1792  
         7 TEST2                                            4736  
         7 TEST3                                            4736  
         7 TEST4                                            1152  
         7 TEST5                                             256  
         8 TEST1                                            1920  
  
   FILE\_ID SEGMENT\_NA PARTITION\_NAME                 SUM(BLOCKS)  
\---------- ---------- ------------------------------ -----------  
         8 TEST2                                            3840  
         8 TEST3                                            5888  
         8 TEST4                                             768  
         8 TEST5                                             256  
         9 TEST3                                            4096  
         9 TEST4                                            3456  
         9 TEST5                                            4736  
        10 TEST5                                           11904  
  
已选择19行。  
  
  
  
现在删除5张表的一部分数据，造成一些数据碎片。  
  
表1：  
SQL> delete test1 where mod(object\_id,3) in (0,1);  
已删除268600行。  
SQL> commit;  
提交完成。  
  
SQL> select count(\*) from test1;  
  COUNT(\*)  
\----------  
    133960  
     
SQL> select file\_id,bytes/1024/1024 from dba\_free\_space where tablespace\_name='TEST';  
   FILE\_ID BYTES/1024/1024  
\---------- ---------------  
         6           .9375  
         7           .9375  
         8           .9375  
         9          3.9375  
        10          6.9375  
  
SQL> exec dbms\_stats.gather\_table\_stats(user,'test1',estimate\_percent=>100);  
PL/SQL 过程已成功完成。  
  
SQL>  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST1';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     133960       5556  
          
表2：  
  
SQL> delete test2 where mod(object\_id,3) in (0,1);  
已删除604350行。  
SQL> commit;  
提交完成。  
  
SQL> select count(\*) from test2;  
  COUNT(\*)  
\----------  
    301412  
  
SQL> select file\_id,bytes/1024/1024 from dba\_free\_space where tablespace\_name='TEST';  
   FILE\_ID BYTES/1024/1024  
\---------- ---------------  
         6           .9375  
         7           .9375  
         8           .9375  
         9          3.9375  
        10          6.9375  
  
SQL> exec dbms\_stats.gather\_table\_stats(user,'test2',estimate\_percent=>100);  
PL/SQL 过程已成功完成。  
  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST2';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     301412      12665  
          
表3：  
SQL> delete test3 where mod(object\_id,3) in (0,1);  
已删除906526行。  
SQL> commit;  
提交完成。  
  
SQL> select file\_id,bytes/1024/1024 from dba\_free\_space where tablespace\_name='TEST';  
   FILE\_ID BYTES/1024/1024  
\---------- ---------------  
         6           .9375  
         7           .9375  
         8           .9375  
         9          3.9375  
        10          6.9375  
  
  
SQL> exec dbms\_stats.gather\_table\_stats(user,'test3',estimate\_percent=>100);  
PL/SQL 过程已成功完成。  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST3';  
  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     452118      18785  
  
  
表4：  
SQL> delete test4 where mod(object\_id,3) in (0,1);  
已删除302177行。  
SQL> commit;  
提交完成。  
  
SQL>  select file\_id,bytes/1024/1024 from dba\_free\_space where tablespace\_name='TEST';  
   FILE\_ID BYTES/1024/1024  
\---------- ---------------  
         6           .9375  
         7           .9375  
         8           .9375  
         9          3.9375  
        10          6.9375  
  
SQL> exec dbms\_stats.gather\_table\_stats(user,'test4',estimate\_percent=>100);  
PL/SQL 过程已成功完成。  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST4';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     150706       6248  
  
表5：  
  
SQL> delete test5 where mod(object\_id,3) in (0,1);  
已删除570777行。  
SQL> commit;  
提交完成。  
  
SQL> select file\_id,bytes/1024/1024 from dba\_free\_space where tablespace\_name='TEST';  
   FILE\_ID BYTES/1024/1024  
\---------- ---------------  
         6           .9375  
         7           .9375  
         8           .9375  
         9          3.9375  
        10          6.9375  
  
SQL> exec dbms\_stats.gather\_table\_stats(user,'test5',estimate\_percent=>100);  
PL/SQL 过程已成功完成。  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST5';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     284667      17257  
   
         
检查发现，虽然表删除了数据但是，总体来看表空间使用量并未减少。  
  
SQL> select a.tablespace\_name,total,free,total-free used, (total-free)/total\*100  
 "USED(%)" from  
  2  ( select tablespace\_name,sum(bytes)/1024/1024 total from dba\_data\_files  
  
  3  group by tablespace\_name) a,  
  4  ( select tablespace\_name,sum(bytes)/1024/1024 free from dba\_free\_space  
  5  group by tablespace\_name) b  
  6  where a.tablespace\_name=b.tablespace\_name  
  7  order by (total-free)/total\*100 desc ;  
  
TABLESPACE\_NAME                     TOTAL       FREE       USED    USED(%)  
\------------------------------ ---------- ---------- ---------- ----------  
SYSAUX                                240      .0625   239.9375 99.9739583  
SYSTEM                                480       3.25     476.75 99.3229167  
UNDOTBS1                              430     6.5625   423.4375 98.4738372  
TEST                                  500    13.6875   486.3125    97.2625  
USERS                                   5       1.75       3.25         65  
EXAMPLE                           146.875    69.1875    77.6875  52.893617  
  
已选择6行。  
  
数据文件一直保持原有的使用  
  
SQL> select file\_id,bytes/1024/1024 from dba\_free\_space where tablespace\_name='TEST';  
   FILE\_ID BYTES/1024/1024  
\---------- ---------------  
         6           .9375  
         7           .9375  
         8           .9375  
         9          3.9375  
        10          6.9375  
         
  
以下把删除数据前和数据后的数据库使用对比如下：  
  
表1  
删除前  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST1';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     402560       5556  
删除后  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST1';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     133960       5556  
使用块数不变，但是行数减少。  
  
表2；  
删除前：  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST2';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     905762      12665  
删除后  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST2';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     301412      12665  
          
表3：  
删除前  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST3';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93    1358644      18785  
删除后  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST3';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     452118      18785  
          
表4  
删除前  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST4';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     452883       6248  
删除后  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST4';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     150706       6248  
  
表5：  
删除前  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST5';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     855444      17257  
删除后  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST5';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     284667      17257  
  
看得出来，删除之后数据库并没有释放。  
  
SQL> select file\_id,SEGMENT\_NAME,PARTITION\_NAME,sum(blocks)  
  2  from dba\_extents where file\_id in (6,7,8,9,10)  
  3  group by file\_id,SEGMENT\_NAME,PARTITION\_NAME  
  4  order by file\_id,SEGMENT\_NAME;  
   
   FILE\_ID SEGMENT\_NA PARTITION\_NAME                 SUM(BLOCKS)  
\---------- ---------- ------------------------------ -----------  
         6 TEST1                                            1920  
         6 TEST2                                            4736  
         6 TEST3                                            4736  
         6 TEST4                                            1024  
         6 TEST5                                             256  
         7 TEST1                                            1792  
         7 TEST2                                            4736  
         7 TEST3                                            4736  
         7 TEST4                                            1152  
         7 TEST5                                             256  
         8 TEST1                                            1920  
  
   FILE\_ID SEGMENT\_NA PARTITION\_NAME                 SUM(BLOCKS)  
\---------- ---------- ------------------------------ -----------  
         8 TEST2                                            3840  
         8 TEST3                                            5888  
         8 TEST4                                             768  
         8 TEST5                                             256  
         9 TEST3                                            4096  
         9 TEST4                                            3456  
         9 TEST5                                            4736  
        10 TEST5                                           11904  
  
已选择19行。  
  
   
SQL> /  
  
   FILE\_ID SEGMENT\_NAME         PARTI SUM(BLOCKS)  
\---------- -------------------- ----- -----------  
         6 TEST1                             1920  
         6 TEST2                             4736  
         6 TEST3                             4736  
         6 TEST4                             1024  
         6 TEST5                              256  
         7 TEST1                             1792  
         7 TEST2                             4736  
         7 TEST3                             4736  
         7 TEST4                             1152  
         7 TEST5                              256  
         8 TEST1                             1920  
  
   FILE\_ID SEGMENT\_NAME         PARTI SUM(BLOCKS)  
\---------- -------------------- ----- -----------  
         8 TEST2                             3840  
         8 TEST3                             5888  
         8 TEST4                              768  
         8 TEST5                              256  
         9 TEST3                             4096  
         9 TEST4                             3456  
         9 TEST5                             4736  
        10 TEST5                            11904  
  
已选择19行。  
  
现在查询可收回的空间有多少：  
  
SQL> select FILE\_ID,BLOCK\_ID,shrink\_mb  
  2  from (select FILE\_ID,BLOCK\_ID,BYTES/1024/1024 shrink\_mb,  
  3  row\_number() over(partition by file\_id order by BLOCK\_ID desc) rn  
  4  from dba\_free\_space where file\_id in (6,7,8,9,10) ) t  
  5  where rn =1 ;  
  
   FILE\_ID   BLOCK\_ID  SHRINK\_MB  
\---------- ---------- ----------  
         6      12681      .9375  
         7      12681      .9375  
         8      12681      .9375  
         9      12297     3.9375  
        10      11913     6.9375  
  
和剩余的数据文件空间一样，虽然删除了但是并没有可以收回的空间，因为占用的数据块并没有释放。  
  
查询每个数据文件空闲块和起始位置  
   
SQL> select FILE\_ID,BLOCK\_ID start\_block,BLOCK\_ID+BLOCKS-1 end\_block,BLOCKS  
  2  from dba\_free\_space  
  3  where file\_id in (6,7,8,9,10)  
  4  order by 1;  
   
   FILE\_ID START\_BLOCK  END\_BLOCK     BLOCKS  
\---------- ----------- ---------- ----------  
         6       12681      12800        120  
         7       12681      12800        120  
         8       12681      12800        120  
         9       12297      12800        504  
        10       11913      12800        888  
  
  
先对数据段（表）进行整理，收缩这些表。  
表1：  
SQL> alter table test1 enable row movement;  
表已更改。  
SQL> alter table test1 shrink space;  
表已更改。  
  
SQL> exec dbms\_stats.gather\_table\_stats(user,'TEST1',estimate\_percent=>100);  
PL/SQL 过程已成功完成。  
  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST1';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     133960       1652        
          
对比以前数据快减少了，空间收缩了。  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST1';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     133960       5556  
        
表2：  
  
SQL> alter table test2 enable row movement;  
表已更改。  
SQL> alter table test2 shrink space;  
表已更改。  
  
SQL> exec dbms\_stats.gather\_table\_stats(user,'TEST2',estimate\_percent=>100);  
PL/SQL 过程已成功完成。  
  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST2';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     301412       3711  
          
对比之前数据快减少了，空间收缩了。                 
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST2';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     301412      12665  
          
表3：  
SQL> alter table test3 enable row movement;  
表已更改。  
SQL> alter table test3 shrink space;  
表已更改。  
  
SQL> exec dbms\_stats.gather\_table\_stats(user,'TEST3',estimate\_percent=>100);  
PL/SQL 过程已成功完成。  
  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST3';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     452118       5565  
          
对比之前数据快减少了，空间收缩了。       
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST3';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     452118      18785  
          
表4：  
SQL> alter table test4 enable row movement;  
表已更改。  
  
SQL> alter table test4 shrink space;  
表已更改。  
  
SQL> exec dbms\_stats.gather\_table\_stats(user,'TEST4',estimate\_percent=>100);  
PL/SQL 过程已成功完成。  
  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST4';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     150706       1858  
  
对比之前数据快减少了，空间收缩了。          
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST4';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     150706       6248  
          
表5：  
SQL> alter table test5 enable row movement;  
表已更改。  
SQL> alter table test5 shrink space;  
表已更改。  
  
SQL> exec dbms\_stats.gather\_table\_stats(user,'TEST5',estimate\_percent=>100);  
PL/SQL 过程已成功完成。  
  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST5';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     284667       3505  
          
对比之前数据快减少了，空间收缩了  
SQL> select avg\_row\_len,num\_rows,blocks from user\_tables where table\_name='TEST5';  
AVG\_ROW\_LEN   NUM\_ROWS     BLOCKS  
\----------- ---------- ----------  
         93     284667      17257  
          
5张表都收缩完了，  
以下是每个数据文件数据库空闲和起始位置  
SQL> select FILE\_ID,BLOCK\_ID start\_block,BLOCK\_ID+BLOCKS-1 end\_block,BLOCKS  
  2  from dba\_free\_space  
  3  where file\_id=6;  
  
   FILE\_ID START\_BLOCK  END\_BLOCK     BLOCKS  
\---------- ----------- ---------- ----------  
         6        1569       2952       1384  
         6        3977       7432       3456  
         6        9097      11912       2816  
         6       12169      12800        632  
  
SQL> select FILE\_ID,BLOCK\_ID start\_block,BLOCK\_ID+BLOCKS-1 end\_block,BLOCKS  
  2  from dba\_free\_space  
  3  where file\_id=7;  
  
   FILE\_ID START\_BLOCK  END\_BLOCK     BLOCKS  
\---------- ----------- ---------- ----------  
         7        1545       2824       1280  
         7        3793       7304       3512  
         7        8873      11784       2912  
         7       12041      12800        760  
  
SQL> select FILE\_ID,BLOCK\_ID start\_block,BLOCK\_ID+BLOCKS-1 end\_block,BLOCKS  
  2  from dba\_free\_space  
  3  where file\_id=8;  
  
   FILE\_ID START\_BLOCK  END\_BLOCK     BLOCKS  
\---------- ----------- ---------- ----------  
         8        1673       2952       1280  
         8        3977       6536       2560  
         8        8201      12168       3968  
         8       12529      12800        272  
  
SQL> select FILE\_ID,BLOCK\_ID start\_block,BLOCK\_ID+BLOCKS-1 end\_block,BLOCKS  
  2  from dba\_free\_space  
  3  where file\_id=9;  
  
   FILE\_ID START\_BLOCK  END\_BLOCK     BLOCKS  
\---------- ----------- ---------- ----------  
         9           9       4104       4096  
         9        4361       7560       3200  
         9        8969      12800       3832  
  
SQL> select FILE\_ID,BLOCK\_ID start\_block,BLOCK\_ID+BLOCKS-1 end\_block,BLOCKS  
  2  from dba\_free\_space  
  3  where file\_id=10;  
  
   FILE\_ID START\_BLOCK  END\_BLOCK     BLOCKS  
\---------- ----------- ---------- ----------  
        10        1409      12800      11392  
  
  
可以看得出来空闲数据块都不连续。  
  
  
我们看一下表空间的数据文件的空余使用量。   
  
SQL> select file\_id,bytes/1024/1024 from dba\_free\_space where tablespace\_name='TEST';  
  
   FILE\_ID BYTES/1024/1024  
\---------- ---------------  
         6         10.8125  
         6              27  
         6              22  
         6          4.9375  
         7              10  
         7         27.4375  
         7           22.75  
         7          5.9375  
         8              10  
         8              20  
         8              31  
  
   FILE\_ID BYTES/1024/1024  
\---------- ---------------  
         8           2.125  
         9              32  
         9              25  
         9         29.9375  
        10              89  
  
已选择16行。  
  
可以看出空余的量很多了，但是都是分散的。  
  
查看每个数据文件里表占用的数据块     
SQL> select file\_id,SEGMENT\_NAME,PARTITION\_NAME,sum(blocks)                
  2  from dba\_extents where file\_id in (6,7,8,9,10)                    
  3  group by file\_id,SEGMENT\_NAME,PARTITION\_NAME                      
  4  order by file\_id,SEGMENT\_NAME;                                    
                                                                       
   FILE\_ID SEGMENT\_NAME         PARTI SUM(BLOCKS)                      
\---------- -------------------- ----- -----------                      
         6 TEST1                              536                      
         6 TEST2                             1280                      
         6 TEST3                             1920                      
         6 TEST4                              512                      
         6 TEST5                              256                      
         7 TEST1                              512                      
         7 TEST2                             1224                      
         7 TEST3                             1824                      
         7 TEST4                              512                      
         7 TEST5                              256                      
         8 TEST1                              640                      
                                                                       
   FILE\_ID SEGMENT\_NAME         PARTI SUM(BLOCKS)                      
\---------- -------------------- ----- -----------                      
         8 TEST2                             1280                      
         8 TEST3                             1920                      
         8 TEST4                              616                      
         8 TEST5                              256                      
         9 TEST4                              256                      
         9 TEST5                             1408                      
        10 TEST5                             1400                      
                                                                       
已选择18行。                                                           
  
  
计算查看每个数据文件可以收回的空间大小                                                                       
SQL>                                                                   
SQL> select FILE\_ID,BLOCK\_ID,shrink\_mb                                 
  2  from (select FILE\_ID,BLOCK\_ID,BYTES/1024/1024 shrink\_mb,          
  3  row\_number() over(partition by file\_id order by BLOCK\_ID desc) rn  
  4  from dba\_free\_space where file\_id in (6,7,8,9,10) ) t             
  5  where rn =1 ;                                                     
                                                                       
   FILE\_ID   BLOCK\_ID  SHRINK\_MB                                       
\---------- ---------- ----------                                       
         6      12169     4.9375                                       
         7      12041     5.9375                                       
         8      12529      2.125                                       
         9       8969    29.9375                                       
        10       1409         89                                       
   
除了文件10外其他收回的量都比较小，这个应该是因为数据文件高水位线的原因，数据文件只能收缩最高水位线以上的数据块，在文件最高水位线以下各个分布的空闲块无法收回。  
  
问题来了，怎么能让数据文件里高水位线以下的很多空闲数据库合在一起呢？让他们连续呢？  
  
网上看到的一个计算生成收缩数据文件的查询。                                                                    
  
SQL> select a.file\#,a.name,a.bytes/1024/1024 CurrentMB,  
  2         ceil(HWM \* a.block\_size)/1024/1024 ResizeTo,  
  3         (a.bytes - HWM \* a.block\_size)/1024/1024 ReleaseMB,  
  4         'alter database datafile '''||a.name||''' resize '||  
  5         ceil(HWM \* a.block\_size/1024/1024) || 'M;' ResizeCMD  
  6  from v$datafile a,  
  7       (select file\_id,max(block\_id+blocks-1) HWM  
  8         from dba\_extents  
  9         group by file\_id) b  
 10  where a.file\# = b.file\_id(+)  
 11  and (a.bytes - HWM \*block\_size)>0  
 12  order by 5 ;  
  
FILE\# NAME                                                CURRENTMB   RESIZETO RELEASEMB RESIZECMD  
\-----------------------------------------------------------------------------------------------------------------------------------------------  
    4 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\USERS01.DBF              5       3.251.75    alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\USERS01.DBF' resize 4M;  
    8 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST03.DBF             100     97.8752.125   alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST03.DBF' resize 98M;  
    1 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\SYSTEM01.DBF           480   477.06252.9375  alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\SYSTEM01.DBF' resize 478M;  
    6 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST01.DBF             100    95.06254.9375  alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST01.DBF' resize 96M;  
    7 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST02.DBF             100    94.06255.9375  alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST02.DBF' resize 95M;  
    3 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\SYSAUX01.DBF           250   240.43759.5625  alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\SYSAUX01.DBF' resize 241M;  
    9 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST04.DBF             100    70.062529.9375 alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST04.DBF' resize 71M;  
    5 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\EXAMPLE01.DBF      146.875    80.187566.6875 alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\EXAMPLE01.DBF' resize 81M;  
   10 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST05.DBF             100              1189 alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST05.DBF' resize 11M;  
  
已选择9行。  
  
  
  
如果只是想对某个表个间的datafile resize,可采用:  
  
SQL> select a.file\#,a.name,a.bytes/1024/1024 CurrentMB,                          
  2         ceil(HWM \* a.block\_size)/1024/1024 ResizeTo,                         
  3         (a.bytes - HWM \* a.block\_size)/1024/1024 ReleaseMB,                  
  4         'alter database datafile '''||a.name||''' resize '||                 
  5         ceil(HWM \* a.block\_size/1024/1024) || 'M;' ResizeCMD                 
  6  from v$datafile a,                                                          
  7       (select file\_id,max(block\_id+blocks-1) HWM                             
  8         from dba\_extents where file\_id in                                    
  9                (select b.file\#  From v$tablespace a ,v$datafile b            
 10                  where a.ts\#=b.ts\# and a.name='TEST')                        
 11         group by file\_id) b                                                  
 12  where a.file\# = b.file\_id(+)                                                
 13  and (a.bytes - HWM \*block\_size)>0                                           
 14  order by 5                                                                  
 15  ;                                                                           
                                                                                 
FILE\# NAME                                                CURRENTMB   RESIZETO         RELEASEMB RESIZECMD                                                              
    8 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST03.DBF             100     97.8752.125    alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST03.DBF' resize 98M;                                                                    
    6 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST01.DBF             100    95.06254.9375   alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST01.DBF' resize 96M;                                                                    
    7 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST02.DBF             100    94.06255.9375   alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST02.DBF' resize 95M;                                                                    
    9 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST04.DBF             100    70.062529.9375  alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST04.DBF' resize 71M;                                                                    
   10 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST05.DBF             100             1189   alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST05.DBF' resize 11M;          
  
  
根据以上生产的执行语句执行对数据文件的收缩操作  
  
SQL> alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST03.DBF' resize 98M;  
数据库已更改。  
  
SQL> alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST01.DBF' resize 96M;  
数据库已更改。  
  
SQL> alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST02.DBF' resize 95M;  
数据库已更改。  
  
SQL> alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST04.DBF' resize 71M;  
数据库已更改。  
  
SQL> alter database datafile 'D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST05.DBF' resize 11M;  
数据库已更改。  
  
收缩成功无报错  
  
查看数据文件大小  
SQL> select file\_id,file\_name,bytes/1024/1024 from dba\_data\_files where tablespace\_name='TEST';  
  
   FILE\_ID FILE\_NAME                                          BYTES/1024/1024  
\---------- -------------------------------------------------- ---------------  
         6 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST01.DBF                   96  
         7 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST02.DBF                   95  
         8 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST03.DBF                   98  
         9 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST04.DBF                   71  
        10 D:\\ORACLE\\PRODUCT\\10.2.0\\RLDB\\RLDB\\TEST05.DBF                   11  
  
数据文件成功收缩至目标大小。  
  
查看数据正常  
SQL> select count(\*) from test1;  
  
  COUNT(\*)  
\----------  
    133960  
  
SQL> select count(\*) from test2;  
  
  COUNT(\*)  
\----------  
    301412  
  
SQL> select count(\*) from test3;  
  
  COUNT(\*)  
\----------  
    452118  
  
SQL> select count(\*) from test4;  
  
  COUNT(\*)  
\----------  
    150706  
  
SQL> select count(\*) from test5;  
  
  COUNT(\*)  
\----------  
    284667  
  
  
至此测试完成，数据文件收缩完毕。  
结论，数据文件收缩大大小取决于高水位线（HWM）的位置，前期对数据表文件里所在表的shrink有一定的作用，但是如果表shrink的部分在高水位线一下则无明显效果。  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#