• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

performance and tunning

Dear Sir
                 I am confused... Please Help
             I found db_block_size parameter in my database ,the value is 4096.

but when i quired ..select block_id,bytes from user_free_space
The results are confusing for me.Results are

block_id                 bytes        
888222                  24899584
115140                  13299712
102467                  20929782

            I am confused that a how a block of size 4k is going to have such a large ammount of space?? please clearify this scenerio
Best Wishes

Naveen Mathur
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
these are adjacent blocks, starting at the given block_id
hg1010Author Commented:
Its ok, that these are adjacent blocks, starting at the given block_id but how come it possible to have such large values in data blocks ,yet the block size is 4k
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, your database seems to have a size of free space of at least 57MB, which are in these 3 parts.
as your data files are in blocks of 4K, you have size/4K = number of blocks in all these files.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Try this query

select block_id,bytes/4096 from user_free_space ;

Please read Oracle documentation on USER_FREE_SPACE:

Column                                         Description
TABLESPACE_NAME                        Name of the tablespace containing the extent
FILE_ID                                          File identifier number of the file containing the extent
BLOCK_ID                                      !!!STARTING!!!  block number of the extent
BYTES                                           Size of the extent (in bytes)
BLOCKS                                         Size of the extent (in Oracle blocks)
RELATIVE_FNO                              Relative file number of the file containing the extent

block_id is identifier of block, where free space starts.\
bytes is length of continous free space (free space extent).

block_id                 bytes        
888222                  24899584

block 888222 is the first block of free area of 24899584 bytes,
or block 888222 is the first of 6079 free blocks (block 888222 and 6078 blocks following),
or blocks from 888222 until 894301 are free.

JacekMycha said it well.

just some high level picture:

dba_free_space registers information about FREE EXTENTs in each tablespace.

What is free extent? It is a set of contiguous free blocks, not a single block.

When Oracle tries to allocate a new extent to an object, it will search for those free extents or contiguous free blocks first, it will try to coalesce those free spaces (not move) or extend the datafile.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now