Re: What space of a Oracle block is being used?

You can't find out exactly. No view gives you that information directly.

You can compute an average, though.

Analyze table EMP compute statistics;
Select * from dba_tables where table_name='EMP';

Now, find the BLOCKS column. That tells you how many blocks do contain data, or might once have contained data.

Find the NUM_ROWS value, and multiply it by AVG_ROW_LEN. That gives you the size (in bytes) the table might be expected to occupy, were each block packed 100% full, and there was no Oracle overhead in each block. You then need to take into account that overhead, and PCTFREE.

Compare the 100% optimum with the actual BLOCKS, and that gives you an idea of how sparsely or densely populated your blocks really are.

As an example. I use 8K blocks (and you should stop using 2K blocks as soon as possible, too). For table TEST, here's what DBA_TABLES reports:

BLOCKS: 404
NUM_ROWS : 29260
AVG_ROW_LEN: 96 So 29260 x 96 = 2808960 bytes optimal storage.

8192 bytes per block. Less 88 bytes for Oracle overhead = 8104 useable bytes. Less 10% PCTFREE = 7293 truly useable bytes per block.

2808960 optimal bytes / 7293 useable bytes = 386 blocks.

Optimally, this table would use 386 blocks. It's actualy using 404. That's 18 'extra' blocks... which is about 5% 'extra'. So I would guess that my blocks were about 95% full to the PCTFREE level, and given that PCTFREE is 10% empty space already, it means my blocks are about 85% physically full.

Regards
HJR "Alberto Garzas" <NOSPAMalberto.garzas_at_wanadoo.es> wrote in message news:aj70v5$1su$1_at_news.wanadoo.es...
> Hello group!
>
> I think that my question is easy but I havenīt solved yet.
>
> If I have a block which has 2Kb size, what percent of these 2Kb are used
or
> what percent of this 2Kb are free in a concrete moment?
>
> Thank you!
>
>
Received on Sun Aug 11 2002 - 20:39:26 CDT