如何收缩表空间中的数据文件?

 

我们知道，我们如果把一些对象删除之后，如何回收表空间中的数据文件:

我们看如下的查询:

[sql] view plaincopyprint?

1. SELECT UPPER(F.TABLESPACE_NAME)"表空间名", 
2. 　　D.TOT_GROOTTE_MB "表空间大小(M)", 
3. 　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", 
4. 　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') ||'%'"使用比", 
5. 　　F.TOTAL_BYTES "已扩展空闲空间(M)", 
6.     (SELECT free_space_mb+free_allocate_mbFROM dba_tablespace_free awhere a.tablespace_name= f.tablespace_name)"总剩余空间", 
7. 　　F.MAX_BYTES "最大块(M)" 
8. 　　FROM (SELECT TABLESPACE_NAME, 
9. 　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, 
10. 　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES 
11. 　　FROM SYS.DBA_FREE_SPACE 
12. 　　GROUP BY TABLESPACE_NAME) F, 
13. 　　(SELECT DD.TABLESPACE_NAME, 
14. 　　ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB 
15. 　　FROM SYS.DBA_DATA_FILES DD 
16. 　　GROUP BY DD.TABLESPACE_NAME) D 
17. 　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME 
18. 　　ORDER BY 3desc; 

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",　　D.TOT_GROOTTE_MB "表空间大小(M)",　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",　　F.TOTAL_BYTES "已扩展空闲空间(M)",    (SELECT free_space_mb+free_allocate_mb FROM dba_tablespace_free a where a.tablespace_name= f.tablespace_name) "总剩余空间",　　F.MAX_BYTES "最大块(M)"　　FROM (SELECT TABLESPACE_NAME,　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES　　FROM SYS.DBA_FREE_SPACE　　GROUP BY TABLESPACE_NAME) F,　　(SELECT DD.TABLESPACE_NAME,　　ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB　　FROM SYS.DBA_DATA_FILES DD　　GROUP BY DD.TABLESPACE_NAME) D　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME　　ORDER BY 3 desc;

查询结果:

其中一个表空间lars01_index分配了3T，但是只用了8%，现在我们想回收这个表空间的数据，如何回收处理?

我们可以按照文件查询:

[sql] view plaincopyprint?

1. select tablespace_name, 
2.        file_name, 
3.        a.file_id, 
4.        bytes / 1024 / 1024 file_size, 
5.        round(a.free_size, 2) free_size, 
6.        autoextensible, 
7.        increment_by next, 
8.        round(maxbytes / 1024 / 1024 / 1024) max_size, 
9.        round(((bytes / 1024 / 1024) - a.free_size) / (bytes / 1024 / 1024) * 100, 
10.              2) used_rate 
11.   from dba_data_files b, 
12.        (select file_id, sum(bytes) / 1024 / 1024 free_size 
13.           from dba_free_space 
14.          group by file_id) a 
15. where b.file_id = a.file_id 
16. and tablespace_name ='LARS01_INDEX' 
17. order by used_ratedesc; 

select tablespace_name,       file_name,       a.file_id,       bytes / 1024 / 1024 file_size,       round(a.free_size, 2) free_size,       autoextensible,       increment_by next,       round(maxbytes / 1024 / 1024 / 1024) max_size,       round(((bytes / 1024 / 1024) - a.free_size) / (bytes / 1024 / 1024) * 100,             2) used_rate  from dba_data_files b,       (select file_id, sum(bytes) / 1024 / 1024 free_size          from dba_free_space         group by file_id) a where b.file_id = a.file_id and tablespace_name ='LARS01_INDEX' order by used_rate desc;

我们针对第一个文件435：

[sql] view plaincopyprint?

1. SQL> selectmax(block_id)from dba_extentswhere file_id=435and tablespace_name='LARS01_INDEX'; 
2.  
3. MAX(BLOCK_ID) 
4. ------------- 
5.       4177033 
6.  
7. SQL> select 4177033*8/1024from dual; 
8.  
9. 4177033*8/1024 
10. -------------- 
11.     32633.0703 

SQL> select max(block_id) from dba_extents where file_id=435 and tablespace_name='LARS01_INDEX';MAX(BLOCK_ID)-------------      4177033SQL> select 4177033*8/1024 from dual;4177033*8/1024--------------    32633.0703

这里最大块是在32G的位置，从这里看到尽快使用率是1%，也就是使用了320M，但是由于这个文件的位置，所以我们要回收的话，是无法直接resize的。

我们查看下文件高水位的分布，按照block_id进行排序:

从这里可以看出有不少对象是在比较高的地方:

把这些对象移动到新的表空间里面:

移除完比较大的block_id之后再次查询:

[sql] view plaincopyprint?

1. SQL> selectmax(block_id)*8/1024from dba_extentswhere file_id=435and tablespace_name='LARS01_INDEX'; 
2.  
3. MAX(BLOCK_ID)*8/1024 
4. -------------------- 
5.           537.070313 

SQL> select max(block_id)*8/1024 from dba_extents where file_id=435 and tablespace_name='LARS01_INDEX';MAX(BLOCK_ID)*8/1024--------------------          537.070313

这里显示:  最大的block_id为537M，也就是这个是可以回收的文件的高水位：

SQL> alter database datafile '+DG_DATA8/oss139/datafile/lars01_index.464.798314649'resize 576M;

数据库已更改。

这个是单个文件的处理回收方法：

如果文件很多，我们可以直接这样查询:

[sql] view plaincopyprint?

1. select a.file#,a.name,a.bytes/1024/1024 Resize,(a.bytes-HWM*a.block_size)/1024/1024 ReleaseMB, 
2. 'alter database datafile '''||a.name||'''resize '||ceil(HWM*a.block_size/1024/1024+30)||'M;' ResizeCmd   ---这里我习惯往前放大30M 
3. from v$datafile a, 
4. (select file_id,max(block_id+blocks-1) HWMfrom dba_extents 
5. group by file_id) b 
6. where a.file#=b.file_id(+) 
7. and (a.bytes-HWM*a.block_size)/1024/1024>100;   ---空闲多余100M以上才回收

from:http://blog.csdn.net/huangchao_sky/article/details/8283733