Re: Query for shrinking datafile

Hi Stefano,

I'm using these two queries when shrinking datafiles...

1) From Tim Hall

http://www.oracle-base.com/dba/monitoring/min_datafile_size.sql


2) I got from my compilation of scripts..

SELECT /*+rule*/ 'ALTER DATABASE DATAFILE '''||FILE_NAME||''' RESIZE
'||CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 )||'M;',
CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) -
CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
FROM DBA_DATA_FILES A,
( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM
FROM DBA_EXTENTS
GROUP BY FILE_ID ) B
WHERE A.FILE_ID = B.FILE_ID(+)





- Karl Arao
karlarao.wordpress.com
karlarao.tiddlyspot.com




On Mon, May 3, 2010 at 3:49 PM, 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?
>
>
> Thanks
> Ste
>
>

Other related posts: