Learn how to a build a cloud-first strategyRegister Now


performance and tunning

Posted on 2006-05-21
Medium Priority
Last Modified: 2008-03-04
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
Question by:hg1010
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16731741
these are adjacent blocks, starting at the given block_id

Author Comment

ID: 16731861
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
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16731876
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.
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

LVL 16

Expert Comment

ID: 16731878
Try this query

select block_id,bytes/4096 from user_free_space ;


Accepted Solution

JacekMycha earned 1000 total points
ID: 16733573
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.

LVL 19

Expert Comment

ID: 16734682
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.


Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question