```sql
set lines 199 pagesize 100
set COLSEP ‘|’
col name for a60
select a.file#,
a.name,
round(a.bytes/1024/1024,2) CurrentMB,
round((HWM * a.block_size)/1024/1024,2) ResizeTo,
round((a.bytes - HWM * a.block_size)/1024/1024,2) ReleaseMB
from v$datafile a,
(SELECT file_id, MAX(block_id + blocks - 1) HWM
FROM DBA_EXTENTS
GROUP BY file_id) b
where a.file# = b.file_id(+)
And (a.bytes - HWM * a.block_size) > 0
and rownum < 10
order by ReleaseMB;
```

批量回收数据文件空间语句

```sql
set lines 199 pagesize 100
set COLSEP ‘|’
col RESIZECMD for a80
select a.file#,
round(a.bytes/1024/1024,2) CurrentMB,
round((HWM * a.block_size)/1024/1024,2) ResizeTo,
round((a.bytes - HWM * a.block_size)/1024/1024,2) ReleaseMB,
‘alter database datafile ‘’’ || a.name || ‘’’ resize ’ ||ceil((HWM * a.block_size)/1024/1024)|| ‘m;’ ResizeCmd
from v$datafile a,
(SELECT file_id, MAX(block_id + blocks - 1) HWM
FROM DBA_EXTENTS
GROUP BY file_id) b
where a.file# = b.file_id(+)
And (a.bytes - HWM * a.block_size) > 0
and rownum < 10
order by ReleaseMB;
```