【TABLESPACE】怎么去降低数据文件的高水位呢（BLOCK_ID）

千家信息网最后更新 2025年02月02日【TABLESPACE】怎么去降低数据文件的高水位呢（BLOCK_ID）
http://blog.itpub.net/29487349/viewspace-2143418/ 这边文章说了怎么收缩表空间，那么有的时候会出现以下情况，也就是我们查询表空间空间空闲很多，但执行该文章中查询可以resize的空间却很小，这是为什么呢？ 好，下面我们来看一看。

实例，Oracle11.2.0.4 for redhat6.7 x64

查询表空间使用状况

点击(此处)折叠或打开

1. TABLESPACE_NAME TOTAL FREE % Free
   
2. ------------------------- ---------- ---------- ----------
   
3. SYSAUX 520 27 5
   
4. SYSTEM 750 9 1
   
5. TEST 200 111 56 --- TEST表空间剩余111M（测试，使用数据较小）
   
6. UNDOTBS1 75 1 1
   
7. USERS 76.25 3 4

执行 查询可以resize的大小

点击(此处)折叠或打开

1. Smallest
   
2. Size Current Poss.
   
3. FILE_NAME Poss. Size Savings
   
4. -------------------------------------------------- -------- -------- --------
   
5. /backup/oradata/mytest/test01.dbf 161 200 39
   
6. --------
   
7. sum 39 也就是该数据文件可以释放39M，那么这什么怎么回事呢

我们通过以下语句看看：

点击(此处)折叠或打开

1. SQL> select owner ownr, segment_name name,PARTITION_NAME, segment_type type, extent_id exid, file_id fiid, block_id blid, blocks blks
   
2. 2 from dba_extents
   
3. 3 where tablespace_name ='TEST'
   
4. 4 order by block_id
   
5. 5 /
   
6. ………………………………
   
7. 
   
8. SCOTT OBJPART_ID INDEX 21 5 4352 128
   
9. SCOTT OBJPART_ID INDEX 22 5 4480 128
   
10. SCOTT OBJPART_ID INDEX 23 5 4608 128
    
11. SCOTT OBJPART_ID INDEX 24 5 4736 128
    
12. SCOTT OBJPART_ID INDEX 25 5 4864 128
    
13. SCOTT OBJPART_ID INDEX 26 5 5120 128
    
14. SCOTT OBJ_PART P20141231 TABLE PA 0 5 9600 1,024
    
15. SCOTT OBJ_PART P20141231 TABLE PA 1 5 10624 1,024
    
16. SCOTT OBJ_PART P20141231 TABLE PA 2 5 19584 432 --查出 该分区表block_id比较大，也就是占据数据文件的位置最尾端（相对其他数据块））

那么我们可以通过move table方式降低该分区表的位置，也就移动到考前点的位置，此处请注意关于分区表相关的表、索引情况，请根据业务、数据库来做判断

执行下面命令：

点击(此处)折叠或打开

1. SQL> alter table scott.obj_part MODIFY PARTITION p20141231 shrink space; 该命令并没有降低数据文件中block_id大小，字面来理解，它只是负责收缩，降低该分区表的高水位。 该命令无需重建索引
   
2. 
   
3. Table altered.
   
4. 
   
5. 
   
6. 
   
7. SQL> alter table scott.obj_part move partition p20141231 tablespace test; --move 移至，移到表空间最前面的空闲block中，需要重建索引
   
8. 
   
9. Table altered.

再次查看表空间resize情况

点击(此处)折叠或打开

1. SCOTT OBJPART_ID INDEX 23 5 4608 128
   
2. SCOTT OBJPART_ID INDEX 24 5 4736 128
   
3. SCOTT OBJPART_ID INDEX 25 5 4864 128
   
4. SCOTT OBJPART_ID INDEX 26 5 5120 128
   
5. SCOTT OBJ_PART P20141231 TABLE PA 0 5 5248 1,024
   
6. SCOTT OBJ_PART P20141231 TABLE PA 1 5 6272 1,024
   
7. SCOTT OBJ_PART P20141231 TABLE PA 2 5 7296 1,024 --位置已前移
   
8. 
   
9. 
   
10. SQL> select file_name,
    
11. 2 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
    
12. 3 ceil( blocks*&&blksize/1024/1024) currsize,
    
13. 4 ceil( blocks*&&blksize/1024/1024) -
    
14. 5 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
    
15. 6 from dba_data_files a,
    
16. 7 ( select file_id, max(block_id+blocks-1) hwm
    
17. 8 from dba_extents where tablespace_name='TEST'
    
18. 9 group by file_id ) b
    
19. 10 where a.file_id = b.file_id(+) and tablespace_name='TEST';
    
20. 
    
21. Smallest
    
22. Size Current Poss.
    
23. FILE_NAME Poss. Size Savings
    
24. -------------------------------------------------- -------- -------- --------
    
25. /backup/oradata/mytest/test01.dbf 65 200 135
    
26. --------
    
27. sum 135 --剩余空间变大

再次执行相关alter …… resize操作即可。 当执行表、索引相关move、rebuild时，请了解业务及相关结构，任何操作都有风险，注意备份及操作规范。