RE: Datafile HWM without querying dba_extents

From: Jonathan Lewis <>
Date: Sat, 31 Jan 2015 10:56:15 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D92826B22A_at_EXMBX01.thus.corp>

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.

Jonathan Lewis

From: [] on behalf of Deepak Sharma [] Sent: 31 January 2015 05:57
Subject: Datafile HWM without querying dba_extents

In order to resize a datafile to release space at the end, we need to find whatever the last block_id that is at the start of that free contiguous space.

Problem is that we have a very large database such that querying dba_extents to find the last block is probably not an option. The standard query(ies) that make use of dba_extents runs for hours at stretch and also sometimes fails with a 'snapshot too old' (just gives up).

Is there an alternative to using dba_extents?

For example, if the datafile size is 100mb and the last 10mb is vacant, I want to know the block_id of where that 10mb begins.

Received on Sat Jan 31 2015 - 11:56:15 CET

Original text of this message