RE: Datafile HWM without querying dba_extents

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
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.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Deepak Sharma [dmarc-noreply_at_freelists.org] Sent: 31 January 2015 05:57
To: oracle-l_at_freelists.org
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.

--
http://www.freelists.org/webpage/oracle-l

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