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>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 1 Feb 2015 18:57:58 +0000 (UTC)
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 sizeSQL> 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@xxxxxxxxxxxxxxxxxx> 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
@jloracle
From: "" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender
"sharmakdeep_oracle@xxxxxxxxx" for DMARC)
Sent: 31 January 2015 21:21
To: Jonathan Lewis; dmarc-noreply@xxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx> wrote:
#yiv1377546528 #yiv1377546528 #yiv1377546528 BODY
{direction:ltr;font-family:Tahoma;color:#000000;font-size:10pt;}#yiv1377546528
P {margin-top:0;margin-bottom:0;}
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:
