Friday, February 24, 2012

Check the total possible shrinkage for tablespace

SELECT SUM(shrinkage_possible_mb) FROM 
(SELECT NVL ( ROUND ( ( blocks-hwm + 1 ) * bytes / blocks / 1024 / 1024 ) , 
              ROUND ( ( blocks ) * bytes / blocks / 1024 / 1024 ) ) shrinkage_possible_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 = 'DATA01'
);

No comments: