Re: table size in bytes

In article <3l6svq$66d_at_homer.alpha.net> sahmad_at_mfa.com "Saad Ahmad" writes:

: How can I obtain the table size in bytes. I don't want
: to use count(*) * bytes-per-rec * overhead because count(*)
: would take a long time. If I can get count(*) with some
: other method, it could be ok.
:
: I want to make an estimate as to when the table would want
: another extent ...
:

Your best bet is to forget about bytes:
try from SQL*Plus

alter session set events 'immediate trace name blockdump level NNN';

where NNN is the absolute block address of the header block of the table you are interested in. This generates a symbolic block dump of the table header. With a little guess work you can figure out what it all means.

If the table is not one that gets lots of inserts and deletes, then reading the dump and estimating time to next extent is quite easy as you will probably not have to worry about factoring in guesses about free lists.

The bit you will want to check will look roughly like this:

    EXT CTL: nex: 5 cex: 4 ces: 20
    cbk: 15

Meaning:

	Number of EXtents	5
	Current EXtent		4		(starts from 0)
	Current Extent Size	20 blocks
	Current BlocK		15		(starts from 0, I think)

The example shows that there are only 4 free blocks left in the last extent before Oracle will demand a new extent.

If you have entries labelled SEG LST, and XCT LST, these refer to blocks (or rather linked lists of blocks) that have free space that is/will soon be available, so the prediction is harder.

BTW: on a Unix system the absolute block address is:

        power(2,26)*file_id + block_id from dba_segments on VMS systems I think it is

        power(2,24)*file_id + block_id from dba_segments

--
Jonathan Lewis