Re: Datafile HWM without querying dba_extents
Date: Mon, 2 Feb 2015 08:57:42 -0600
Message-ID: <CAMNhnU0wGYLvJuNeJNpzkrWquKPR_sqgowyC2SYFrgupTPOJ1g_at_mail.gmail.com>
Going off the top of my head here, I would likely just try to shrink the datafile by "X" GB's at a time until it won't shrink anymore in a loop and move on to the next datafile, until tablespace is X% free.
On Sun, Feb 1, 2015 at 6:06 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>
> My oversight - it's a side effect of being able to pick file sizes that
> don't have to be exactly aligned with possible extent sizes; one of the
> side effects is described here:
> http://jonathanlewis.wordpress.com/2012/05/31/lmt-headers
> <https://jonathanlewis.wordpress.com/2012/05/31/lmt-headers/>
>
> The difference in your case is 7 x 8KB blocks - so your tablespace is
> either system-allocated extents or uniform with extent size greater than
> 64KB.
>
> You need to tweak the code either to check that the difference between
> last block of the file and the derived last of block of the free space is
> less than the minimum extent size; or you could do something with
> "user_bytes" and knowing the size of the file space header.
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
> ------------------------------
> *From:* Deepak Sharma [sharmakdeep_oracle_at_yahoo.com]
> *Sent:* 01 February 2015 18:57
> *To:* Jonathan Lewis; oracle-l_at_freelists.org
>
> *Subject:* Re: Datafile HWM without querying dba_extents
>
> *I tried this in our Dev DB for 2 files (50 and 51). I was able to
> resize both of these by 1 byte to begin with, which means that there is
> Free Space at the end of the file.*
>
>
> *The problem I see is in the calculation to determine whether the Free
> Space chunk for Max(block_id) was indeed at the end of the file (by
> comparing the extent size with total file size). *
>
> *What am I missing? It could be that the extent size calculation for
> these 2 files are different. I know that they are in 2 separate tabespaces
> using LMT as well as ASSM.*
>
> =============
> FILE:50
> =============
> SQL> select bytes from dba_data_files where file_id=50;
>
> BYTES
> ----------
> 53477376
>
> 1 row selected.
>
> SQL> alter database datafile 50 resize 53477375;
>
> Database altered.
>
> -- Resize it back to its original size
> SQL> alter database datafile 50 resize 53477376;
>
> Database altered.
>
> SQL> select file_id, block_id, bytes, blocks from dba_free_space where
> file_id=50
> 2 and block_id = (select max(block_id) from dba_free_space where
> file_id=50);
>
> FILE_ID BLOCK_ID BYTES BLOCKS
> ---------- ---------- ---------- ----------
> 50 6409 983040 120
>
> 1 row selected.
>
> SQL> select 6408*8192 + 120*8192 from dual;
>
> 6408*8192+120*8192
> ------------------
> 53477376 * <=== MATCHES the FILE_SIZE*
>
> 1 row selected.
>
> =============
> FILE:51
> =============
> SQL> select bytes from dba_data_files where file_id=51;
>
> BYTES
> ----------
> 840294400
>
> 1 row selected.
>
> SQL> alter database datafile 51 resize 840294399;
>
> Database altered.
>
> -- Resize it back to its original size
> SQL> alter database datafile 51 resize 840294400;
>
> Database altered.
>
> SQL> select file_id, block_id, bytes, blocks from dba_free_space where
> file_id=51
> 2 and block_id = (select max(block_id) from dba_free_space where
> file_id=51);
>
> FILE_ID BLOCK_ID BYTES BLOCKS
> ---------- ---------- ---------- ----------
> 51 75625 220725248 26944
>
> 1 row selected.
> SQL> select 75624*8192 + 26944*8192 from dual;
>
> 75624*8192+26944*8192
> ---------------------
> 840237056* <=== DOES NOT MATCH the FILE_SIZE*
>
> 1 row selected.
>
>
> On Saturday, January 31, 2015 11:55 PM, 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
> _at_jloracle
> ------------------------------
> *From:* Deepak Sharma [sharmakdeep_oracle_at_yahoo.com]
> *Sent:* 31 January 2015 21:21
> *To:* Jonathan Lewis; dmarc-noreply_at_freelists.org; 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:
>
> 0123456789*0*123456789
> xxTTxxxxTTxxxxxxxxxx
>
> Case2:
>
> 0123*4*567890123456789
> 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> 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
> _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-lReceived on Mon Feb 02 2015 - 15:57:42 CET