Re: Query for shrinking datafile

From: Jared Still <>
Date: Mon, 3 May 2010 08:36:01 -0700
Message-ID: <>

On Mon, May 3, 2010 at 12:49 AM, Stefano Cislaghi <>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: Home Page:


Received on Mon May 03 2010 - 10:36:01 CDT

Original text of this message