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

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

我们看如下的查询:

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%，现在我们想回收这个表空间的数据，如何回收处理?

我们可以按照文件查询:

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> 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> 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;

数据库已更改。

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

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

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