--找出所有文件最小可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;
转载于:https://www.cnblogs.com/yongestcat/p/11543479.html