Question – How can you have a single file in a single tablespace showing multiple free extents when there are no objects using any space in that file ? For example, from an 11.1.0.7 database:
SQL> select 2 * 3 from user_free_space 4 where 5 tablespace_name = 'TEST_8K' 6 order by 7 file_id, block_id 8 ; TABLESPACE_N FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------ ---------- ---------- ---------- ---------- ------------ TEST_8K 3 128 1048576 128 3 TEST_8K 3 256 1048576 128 3 TEST_8K 3 384 1048576 128 3 TEST_8K 3 512 130023424 15872 3 4 rows selected.
The answer in this case is simple – here’s what I did just before running my query:
SQL> create table t1(n1 number); Table created. SQL> create table t2(n1 number); Table created. SQL> create table t3(n1 number); Table created. SQL> drop table t1; Table dropped. SQL> drop table t2; Table dropped. SQL> drop table t3; Table dropped. SQL>
This test is on 11.1.0.7; test_8k is my default tablespace, uses an 8KB block size (did you spot the clue) with 1MB uniform extents. The segments created immediately and I haven’t purged my recyclebin. Because I’ve dropped the tables Oracle includes their space in the “free space” views, but because I need to be able to flash them back into existence the segments can’t be coalesced into the adjacent free space, and they will also be reported in dba_segments.
Here’s a harder one – there are NO objects in this tablespace, and nothing hiding in the recyclebin:
SQL> select 2 * 3 from user_free_space 4 where 5 tablespace_name = 'TEST_2K' 6 order by 7 file_id, block_id 8 ; TABLESPACE_N FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------ ---------- ---------- ---------- ---------- ------------ TEST_2K 7 512 58720256 28672 7 TEST_2K 7 29184 58720256 28672 7 TEST_2K 7 57856 58720256 28672 7 TEST_2K 7 86528 32505856 15872 7
And here’s the statement I executed (as SYS) just before I ran the query – so no chance that there’s anything hidden in the file:
create tablespace test_2k datafile 'C:\ORACLE\ORADATA\d11g\d11g\test_2k.dbf' SIZE 200M reuse blocksize 2k extent management local uniform size 4k segment space management manual ;
Answer: 26th Feb
I’m sitting in Munich airport and boarding starts in 10 minutes, so just enough time to give an answer. I’ll start with a block dump of block 3 of the data file.
Block dump from disk: buffer tsn: 22 rdba: 0x01c00003 (7/3) scn: 0x0b86.06d63ae4 seq: 0x01 flg: 0x04 tail: 0x3ae41e01 frmt: 0x02 chkval: 0x4e8a type: 0x1e=KTFB Bitmapped File Space Bitmap Hex dump of block: st=0, typ_found=1 Dump of memory from 0x0C112200 to 0x0C112A00 C112200 0000621E 01C00003 06D63AE4 04010B86 [.b.......:......] C112210 00004E8A 00000007 00000200 00000000 [.N..............] C112220 00000000 00003800 00000000 00000000 [.....8..........] C112230 00000000 00000000 00000000 00000000 [................] Repeat 123 times C1129F0 00000000 00000000 00000000 3AE41E01 [...............:] File Space Bitmap Block: BitMap Control: RelFno: 7, BeginBlock: 512, Flag: 0, First: 0, Free: 14336 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 ... 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000
This is a locally managed tablespace, so the third block of the file is the first bitmap space management block for the tablespace. The tablespace uses a 2KB block size (so the amount of space in the block for the bit map is slight under 2KB) and a 4KB uniform extent size (so each bit in the map represents 2 blocks/4KB in the tablespace).
As you can see from line 15 of the dump, the first bit represents the extent starting at block 512, and there are 14,336 bits available (1,792 bytes), so the bitmap in the next block would start at block 512 + 2 * 14,336 = 512 * 28,672 = 29,184. This lines up exactly with the first chunk of free space reported in dba_free_space above.
As one of the comments indicated – the code that populates x$ktfbfe is probably called once for each bitmap space management block, and it doesn’t seem to bother trying to “coalesce” two free space fragments identified by adjacent bitmap blocks.
Footnote (years later):
I’ve just discovered that I had already mentioned this phenomenon a few months ago with an example using a block size of 8KB.
Hi Jonathan,
that is because of free space management, right?
Few blocks contains bitmaps for space management.
Regards
GregG
Comment by goryszewskig — February 25, 2013 @ 6:59 pm GMT Feb 25,2013 |
GregG,
There’s something in what you say, but:
512 + 28672 = 29184
29184 + 28672 = 57856
57856 + 28672 = 86528
So I don’t seem to have left any gaps for space management blocks.
Comment by Jonathan Lewis — February 25, 2013 @ 7:22 pm GMT Feb 25,2013 |
You are right, no gaps but somehow Oracle did not decide to show 1 row only .
I think that small block size and extend size plays important role here.
Instead of guessing I’ll just wait for Your explanations :).
Regards
GregG
Comment by goryszewskig — February 25, 2013 @ 8:12 pm GMT Feb 25,2013 |
GregG,
The phenomenon happens for all block sizes – but at larger block sizes you need a larger file to see it.
Comment by Jonathan Lewis — February 25, 2013 @ 8:20 pm GMT Feb 25,2013 |
Maybe the small block size limits the maximum free space size that Oracle can store in some headers ?
Comment by Pierre — February 25, 2013 @ 9:54 pm GMT Feb 25,2013 |
It seems depend on size of File Space Bitmap Block and uniform size, in your case 28672 block = 14336 extents and require 14336 / 8 = 1792 bytes per File Space Bitmap Block.
Comment by Valentin Nikotin — February 25, 2013 @ 10:16 pm GMT Feb 25,2013 |
Yep. you are right! :) I’ve tested on 400m and got 8 rows :)
Comment by Sayan Malakshinov — February 25, 2013 @ 10:44 pm GMT Feb 25,2013 |
Moreover, i found Jonathan’s answer to this question :)
Comment by Sayan Malakshinov — February 26, 2013 @ 5:29 am GMT Feb 26,2013 |
Actually “28672 block = 14336 extents and require 14336 / 8” makes sense only if we use uniform extents, more correctly to use “units” instead of “extents”.
Comment by Valentin Nikotin — February 26, 2013 @ 1:38 pm GMT Feb 26,2013 |
Hmm, in trace i see queries to recyclebin$. What does it means? Some objects was created and dropped? Or was initialized recyclebin for this tablespace?
Comment by Sayan Malakshinov — February 25, 2013 @ 10:23 pm GMT Feb 25,2013 |
Since we are using locally managed tablespace, It is storing the information about the space available in a file in the bitmaps in the file header. One unit of information contains information about one extent. If the information can fit in the space allocated in the file header, all the free space appears as contiguous. As the size of the file increases and extent size decreases , the units of the information to be stored increases and more space is allocated beyond the file header. Until any objects are created in the tablespace, the bits corresponding to all the extents are not set and appear as free space. As objects are created in the tablespace, corresponding bits are set and free space available decreases (Starting block_id rises).
This is the conclusion I have reached after trying with block/extent/file sizes.
I don’t know if it is correct.
Comment by Anju Garg — February 26, 2013 @ 6:44 am GMT Feb 26,2013 |
As I understand, free space for each datafile calculated by each freespace bmb separately, because each freespace bmb contains a header and bitmap, so bitmaps in these blocks are not continuous(although located in a row). Number of bmb depends on datafile size.
Comment by Sayan Malakshinov — February 26, 2013 @ 7:20 am GMT Feb 26,2013 |
I guess it’s just internal implementation of x$ktfbfe that provides access to bitmap blocks.
Comment by Valentin Nikotin — February 26, 2013 @ 8:54 am GMT Feb 26,2013 |
Hi Jonathan,
I can´t understand the above operation:
512 + 2 * 14,336
If an extent consists of two blocks, and there is only a bitmap control header for each extent, it means there is 14336 bits in the first block of the extent but the second block should be full available (except its own control information, of course). I mean:
Block 1, extent Nth Block 2, extent Nth
——————————————————– + ——————————————————-
bitmap header: 256 B | 14336 bits=1792B + Full 2048 B – block header(22B for example)
———————————————————+——————————————————–
So, If it is correct, the next bit map should be at: 1792 B + 2048B, isn´t?.
Best regards.
Comment by Richard. — March 4, 2013 @ 11:27 am GMT Mar 4,2013 |
Richard,
I’m not quite sure I understand your comments about, but I think you are mixing up the bitmaps for the extents and the bitmaps for the tablespace as you work through the numbers.
Each block in the bitmap for the tablespace allows us to map 14,336 extents.
In this version of Oracle, the first extent of the tablespace starts after a 1MB file header section (which, of course, includes the tablespace bitmaps).
Comment by Jonathan Lewis — March 10, 2013 @ 11:55 am GMT Mar 10,2013 |
[…] you have an object in the recyclebin and each extent of that object is listed as free space (see this article and the footnote here). It’s only for dictionary managed tablespaces that dba_free_space […]
Pingback by RTFM | Oracle Scratchpad — May 1, 2017 @ 12:55 pm BST May 1,2017 |
[…] when there are no segments allocated, but with no gaps between chunks – if Oracle has to “grow” the bitmap for a file then the separate chunks of the bitmap report their freespace […]
Pingback by Lost Space | Oracle Scratchpad — November 16, 2022 @ 12:56 pm GMT Nov 16,2022 |