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

Hi Alberto,

Just to add to what Howard has already mentioned.

Note that the AVG_SPACE value in dba_tables gives you the average amount of free space available in a block. This can be used to quickly determine how much space you are potentially 'wasting' in your table.

If you want to determine the amount of free/used space in specific blocks or range of blocks, you could easily write you're own code (if performing block dumps doesn't turn you on). Basically, you need to group by the relevant components of the rowid for the tables of interest, using say dbms.rowid, to give you a listing for each unique combination of object number, relative file number and block number (if your database has fewer than 1022 datafiles, you can simplify and ignore the object number, the relative file number should be unique). By summing the lengths of all the columns of all the rows in each of these groupings (plus adding the block overhead as mentioned by Howard) you can approximately calculate the amount of used space per block in the table(s) in question. Any "missing" block numbers in a particular range suggests there are no rows in that range, and this would need to be catered for.

You can then scan through the report and get a reasonable indication of the row to block distribution and determine fragmentation or otherwise of your targeted tables.

Should be fun :)

Richard
"Howard J. Rogers" wrote:
>
> 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!
> >
> >