Re: Datafile HWM without querying dba_extents
- From: "Deepak Sharma" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "sharmakdeep_oracle@xxxxxxxxx" for DMARC)
- To: "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, "dmarc-noreply@xxxxxxxxxxxxx" <dmarc-noreply@xxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 31 Jan 2015 21:21:29 +0000 (UTC)
Thanks for sharing your thoughts, esp. getting the TS dump (will give it a
try).
As for the dba_free_space, I have 2 datafile cases as below (T- Used, x -
Empty) :
Case1:
01234567890123456789
xxTTxxxxTTxxxxxxxxxx
Case2:
01234567890123456789
xxTTxxxxxxxxxxxxxxTT
In Case1, the contiguous free space for max block_id (per dba_free_space) would
begin at BlkId 10
In Case2, the max blockid for free chunk would begin at BlkId 5, but we really
cannot shrink that Datafile, since the last blocks are already used.
So, the MAX(block_id) for a given File_Id, in dba_free_space may not
necessarily point to the free blocks at the 'End' of a datafile.
Regards,
Deepak
On Saturday, January 31, 2015 4:58 AM, Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx> 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.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Deepak Sharma [dmarc-noreply@xxxxxxxxxxxxx]
Sent: 31 January 2015 05:57
To: oracle-l@xxxxxxxxxxxxx
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.
Other related posts:
