找出所有文件最小可resize尺寸

--找出所有文件最小可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