Friday, February 17, 2012

How to check possible shrinkage for datafiles

SELECT file_name , Round(bytes/1024/1024) file_size_MB, 
Nvl ( Round ( ( blocks-hwm + 1 ) *bytes / blocks / 1024 / 1024 ) , 
Round ( ( blocks ) *bytes / blocks / 1024 / 1024 ) ) shrinkage_possible_mb , 
Nvl ( Round ( hwm *bytes / blocks / 1024 / 1024 ) , 0 ) size_after_shrinkage_mb 
FROM dba_data_files a , 
( SELECT file_id , max ( block_id + blocks ) hwm FROM dba_extents GROUP BY file_id ) b 
WHERE a.file_id =b.file_id ( + ) AND
      a.tablespace_name = 'USERS'

No comments: