Re: Datafile HWM without querying dba_extents
Date: Sun, 1 Feb 2015 08:43:34 -0500
Message-Id: <126BC98C-2357-4993-A5F9-2158F047EE7D_at_gmail.com>
Simple example that should quickly identify all datafiles that could be shrunk.
select df.file_name, (df.bytes-f.bytes)/1024/1024 + 1 resize_to_mb
from dba_free_space f, dba_data_Files df
where f.file_id = df.file_id
and f.block_id+f.blocks = df.blocks
> On Feb 1, 2015, at 12:53 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>
> Your case 2 comment is correct - but if you do a resize datafile aimed at the highest starting block and it fails you know that you can't shrink the file. Alternatively, if you check the start block and block count and find that that doesn't take you to the end of file then you know that you can't resize the file downwards.
>
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com <http://jonathanlewis.wordpress.com/>
> _at_jloracle
> From: Deepak Sharma [sharmakdeep_oracle_at_yahoo.com <mailto:sharmakdeep_oracle_at_yahoo.com>]
> Sent: 31 January 2015 21:21
> To: Jonathan Lewis; dmarc-noreply_at_freelists.org <mailto:dmarc-noreply_at_freelists.org>; oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
> Subject: Re: Datafile HWM without querying dba_extents
>
> 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_at_jlcomp.demon.co.uk <mailto:jonathan_at_jlcomp.demon.co.uk>> 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 <http://jonathanlewis.wordpress.com/>
> _at_jloracle
> From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org>] on behalf of Deepak Sharma [dmarc-noreply_at_freelists.org <mailto:dmarc-noreply_at_freelists.org>]
> Sent: 31 January 2015 05:57
> To: oracle-l_at_freelists.org <mailto: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-lReceived on Sun Feb 01 2015 - 14:43:34 CET