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

I´m sorry Richard. My English is a little poor , therefore, I lose many words while I am writing. I correct my wrong sentence: "Richard has written a big , clear and wonderful explanation!" :-)

I have seen a block dump file and I think that understanding of a dump file is very complicated.

• 2002-08-13 23:03:43.000 Start dump data block from file C:\ORACLE\ORADATA\DB1\USER01.ORA minblk 9 maxblk 9 FILE HEADER: Software vsn=150994944=0x9000000, Compatibility Vsn=134217728=0x8000000 Db Id=1061518437=0x3f457c65, Db Name='DB1' Control Seq=612=0x264, File size=640=0x280 File Number=2, Blksiz=8192, File Type=3 DATA Dump all the blocks in range: buffer rdba: 0x00000009 (0/9) scn: 0x0000.00000000 seq: 0x01 flg: 0x01 tail: 0x00000001 frmt: 0x02 chkval: 0x0000 type: 0x00=unknown Dump of memory from 0x02921014 to 0x02922FFC 2921010 00000000 00000000 00000000 [............] 2921020 00000000 00000000 00000000 00000000 [................] Repeat 508 times 2922FF0 00000000 00000000 00000000 [............] End dump data block from file C:\ORACLE\ORADATA\DB1\USER01.ORA minblk 9 maxblk 9

  ---

  ---

How can I know the space used in the number 9 block? Do I have to know all the datas of this file to know the space used in the block?

Therefore, I am thinking of making the tool with only average amount of free space of the blocks as Howard has commented!

Do you think that this tool will not be useful ? If you had to make a graphical tool about physical storage(blocks, segments, extents......) which is not had by OEM, Which tool would you make? Please, try to give me a clue! I need your opinion because I have to make this tool in order to educational purposes.

Thank you!

"Richard Foote" <Richard.Foote_at_oracle.com> escribió en el mensaje news:3D58AB7E.19C7F531_at_oracle.com...
> Hi Alberto,
>
> Howard's answer is big and clear and mine is just big. Typical ;)
>
> As Srivenu has mentioned and as I alluded to, you can always take a dump
> and see in lovely detail the contents of a block (this can be any
> block).
>
> Therefore, I would question the worth of developing anything that
> details stuff at the block level. Note also with 9i, the fullness of
> blocks statistics can be easily derived with dbms_space.space_usage and
> automatic segment space management.
>
> Then again, I'm not much into GUI pretty looking, colourful displaying
> tools and you might just be able to exploit a huge untapped market.
>
> Then again, maybe not ...
>
> Richard
>
> Alberto Garzas wrote:
> >
> > Thank Howard for your big and clear answer!!!!
> > Thank Richard for your big explanation!!!
> >
> > and then, Its impossible know the amount of used space in the number
'x'
> > block? Only I can know a approximate used of the Oracle block?
> >
> > I am thinking of make a tool which shows the block state of a table and
I
> > think that it will be very difficult or impossible mission. Do you think
> > that I will be able to make that tool?
> >
> > New thank you! Bye!
> > "Richard Foote" <Richard.Foote_at_oracle.com> escribi en el mensaje
> > news:3D57330A.4B2A0313_at_oracle.com...
> > > 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 havent 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 Tue Aug 13 2002 - 18:05:35 CDT