RE: Datafile HWM without querying dba_extents
Date: Mon, 2 Feb 2015 00:06:09 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D92826B9F9_at_EXMBX01.thus.corp>
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: "" <dmarc-noreply_at_freelists.org> (Redacted sender "sharmakdeep_oracle_at_yahoo.com" for DMARC) 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: "" <dmarc-noreply_at_freelists.org> (Redacted sender "sharmakdeep_oracle_at_yahoo.com" for DMARC) 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:
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> 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 - 01:06:09 CET