On Mon, May 3, 2010 at 12:49 AM, Stefano Cislaghi <s.cislaghi@xxxxxxxxx>wrote: > Hi all, > > do you use any query or method to discover how much space you can save on a > datafile? I want to say...suppose to shrink your datafile, which is the > minimum size, according to the highest HWM position, you can reach for your > datafile? > > Which is the easiest way to achieve this? > > HWM for a data file cannot be reliably found by querying DBA_SEGMENTS as at least one poster has suggested. That method will work if the objects in the tablespace have never been dropped, rebuilt, or otherwise relocated within the data files. Attached is a script with one way to do it. There are other methods to for doing this as well. Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com
-- hwm_df.sql -- jkstill@xxxxxxxxx -- 11/06/2008 - complete rewrite -- for tablespaces that are completely devoid of objects -- (aka empty) this report will assume 128k is the minimum file size -- this may or not be correct for your implementation -- and probably is not terribly important to worry about @clears @title 'Datafile High Water Marks' 150 col file_name format a50 head 'FILE NAME' col file_size format 999,999,999,999 head 'FILE SIZE|BYTES' col shrink_size format 999,999,999,999 head 'SHRINK TO|SIZE BYTES' col tablespace_name format a15 head 'TABLESPACE' col bytes_saved format 999,999,999,999 head 'BYTES SAVED' col max_block_id format 999999999 head 'MAX BLOCK ID' break on tablespace_name skip 1 break on report compute sum of bytes_saved on tablespace_name compute sum of bytes_saved on report compute sum of file_size on tablespace_name compute sum of file_size on report compute sum of shrink_size on tablespace_name compute sum of shrink_size on report with maxext as ( select file_id, max(block_id) max_block_id from dba_free_space group by file_id ), shrink as ( select t.tablespace_name , f.file_id , f.file_name , m.max_block_id , f.blocks * t.block_size file_size , case when ((nvl(m.max_block_id,f.blocks+1)-1) * t.block_size) >= (128*1024) then(nvl(m.max_block_id,f.blocks+1)-1) * t.block_size else 128*1024 end shrink_size from maxext m , dba_data_files f , dba_tablespaces t where m.file_id(+) = f.file_id and t.tablespace_name = f.tablespace_name and t.contents = 'PERMANENT' ) select s.tablespace_name , s.file_id , s.max_block_id , s.file_name , s.file_size , s.shrink_size , (s.file_size - s.shrink_size) bytes_saved from shrink s order by tablespace_name, file_id /