RE: Datafile HWM without querying dba_extents

From: Neil Chandler <>
Date: Sun, 1 Feb 2015 15:46:31 +0000
Message-ID: <DUB113-W207021CF9A5FC14C602E5853F0_at_phx.gbl>

Occam's Razor:
Much of the Oracle data dictionary design is rather old, and I have had some notable success in the past querying an unruly dictionary by using the equally old RULE hint. Forget the fact it's de-supported from 11G - give it a go. It is still being actively used by Oracle for internal queries in, so what do they know :-) You just may be pleasantly surprised.
Neil C.

Subject: Re: Datafile HWM without querying dba_extents From:
Date: Sat, 31 Jan 2015 09:20:38 -0500
CC:; To:

Thanks Jonathan, this is too simple, I canít wait to rewrite my resize_tablespace.sql script. Itís funny how you head down a road and never look back. Iíve been using a script that joins dba_extents, db_data_files and dba_tablespaces for some time. It works great but time consuming on large tablespaces with many segments/extents. This approach turns the job upside down and I suspect will be much faster to derive the same result. Kenny

On Jan 31, 2015, at 5:56 AM, Jonathan Lewis <> wrote: On second thoughts, why are you querying dba_extents to find where last used block id is ? If all you want to do is shrink the datafile then querying user_free_space (ordered by file id and block id) will allow you to find the starting block of the highest free area in file. You only need to query dba_extents if you think you've got a lot of space lower down the file and think that moving a couple of small objects might be sufficient to clear the way to releasing it.                                                

Received on Sun Feb 01 2015 - 16:46:31 CET

Original text of this message