![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcuYjNyADO2MjN10CNwkzMyMDN2EDOxkDM5EDMy0CM4YzM5MTMvwVOwkTMwIzLcBDO2MTOzEzLcd2bsJ2Lc12bj5ycn9Gbi52YugTMwIzZtl2Lc9CX6MHc0RHaiojIsJye.png)
--找出所有文件最小可resize尺寸
SELECT a.file_id,
CEIL( ( NVL( hwm,1 ) * blksize ) / 1024 / 1024 ) smallest_M,
CEIL( blocks * blksize / 1024 / 1024 ) currsize_M,
CEIL( blocks * blksize / 1024 / 1024 ) -
CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) savings_M,
'alter database datafile ''' || file_name || ''' resize ' ||
CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) || 'm;' cmd
FROM DBA_DATA_FILES a,
( SELECT file_id, MAX( block_id + blocks - 1 ) hwm FROM DBA_EXTENTS
GROUP BY file_id ) b, (
SELECT TO_NUMBER( value ) blksize FROM V$PARAMETER
WHERE name = 'db_block_size' )
WHERE a.file_id = b.file_id(+)
AND CEIL( blocks * blksize / 1024 / 1024 ) - CEIL( ( NVL( hwm, 1 ) * blksize ) / 1024 / 1024 ) > 0
ORDER BY 5 desc;
作者:九命猫幺
博客出处:http://www.cnblogs.com/yongestcat/
欢迎转载,转载请标明出处。
如果你觉得本文还不错,对你的学习带来了些许帮助,请帮忙点击右下角的推荐