RE: Query for shrinking datafile

From: D'Hooge Freek <Freek.DHooge_at_uptime.be>
Date: Mon, 3 May 2010 10:36:19 +0200
Message-ID: <4814386347E41145AAE79139EAA398980DFE51BBD1_at_ws03-exch07.iconos.be>

--Find the highwater mark (old query, you will need to modify the query to use the blocksize of the tablespace itself)

column db_block_size new_value _BLOCK_SIZE;

select to_number(value) db_block_size from v$parameter where name = 'db_block_size';

select

   a.tablespace_name,
   a.file_id,
   a.file_name,
   a.bytes/1024/1024 file_mb,

   ((c.block_id+(c.blocks-1)) * &_BLOCK_SIZE) /1024/1024 HWM_MB,    (a.bytes - ((c.block_id+(c.blocks-1)) * &_BLOCK_SIZE))/1024/1024 SAVING_mb from dba_data_files a,

   (select file_id,max(block_id) maximum     from dba_extents
    group by file_id) b,
dba_extents c
where a.file_id = b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by 6;

• find the segments near the highwater mark

set linesize 150

column owner format a20

column file_name format a60
column file_id format 999 heading ID
column high_water_mark format 99G999D99 heading HWM

column segment_type format a15

break on file_id skip 1 on file_name

with
maxext as
( select /*+ MATERIALIZE */

         file_id, owner, block_id, blocks, segment_name, segment_type, ranking   from ( select file_id, owner, block_id, blocks, segment_name, segment_type,

                rank() over
                    ( partition by file_id 
                      order by (block_id + blocks -1) desc
                    ) ranking
         from dba_extents
         where tablespace_name = '&tablespace'
       )

  where ranking <= 5
)
select df.file_name, maxext.file_id, maxext.owner, maxext.segment_name, maxext.segment_type,

       ((maxext.block_id + maxext.blocks - 1) * 8192 / 1024/1024) high_water_mark from maxext, dba_data_files df
where df.file_id = maxext.file_id
order by maxext.file_id, ranking;  

 
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stefano Cislaghi Sent: maandag 3 mei 2010 9:49
To: Oracle L
Subject: Query for shrinking datafile

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?

Thanks
Ste
--

http://www.freelists.org/webpage/oracle-l Received on Mon May 03 2010 - 03:36:19 CDT