Re: Query for shrinking datafile
- From: Karl Arao <karlarao@xxxxxxxxx>
- To: s.cislaghi@xxxxxxxxx
- Date: Mon, 3 May 2010 23:48:25 +0800
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:
