Database Dictionary View Query

All,

I have been writing some table/index analysis scripts recently, and am now looking to make them backward compatible with Server version 7.

I am just curious about data available within the DBA_TABLES view.

In my current report, I look at how much free space is available on a table segment's freelist by calculating:

DBA_TABLES.AVG_SPACE_FREELIST_BLOCKS x
DBA_TABLES.NUM_FREELIST_BLOCKS

Unfortunately, in the V7 dictionary this information is not available.

Does anyone know if it is calculable from data stored elsewhere in the dictionary, or via a PL/SQL util package?

Furthermore, if we try looking at the number of blocks visible in DBA_SEGMENTS and DBA_TABLES, I get the followig results:

'blocks' assigned under DBA_SEGMENTS differs from 'blocks' in DBA_TABLES for the same segment. Neither does it appear to add up to 'blocks' + 'empty_blocks'.

Anyone any enlightenment to shed on any of the above?

:-)

- Tony

the data in dba_tables is gathered from statistics periodically collected through "Analyze table ..." or "DBMS_STATS ..." etc.

Size according to DBA_SEGMENTS is correct all the time.

Hmmm,

It seems a little silly to have snapshot or estimation values in DBA_TABLES when the correct value is obtainable all the time from DBA_SEGMENTS.

I thought about whether it was gathered from an analyze on my way home from work... but figured Oracle would be a bit more intelligent than that...

Hey-ho. Perhaps there is a method in that madness.

Thanks for the info.

- Tony.

Quote:

---

Originally posted by AJW_ID01
Hey-ho. Perhaps there is a method in that madness.

---

Of course there is. BLOCKS columns in DBA_SEGMENTS and DBA_TABLES counts two totaly different things, hence the numbers in those two columns are different.

BLOCKS in DBA_SEGMENTS shows the number of database blocks uccupied by table segment (the sum of blocks occupied by all table extents. It doesn't matter if there was ever any data in those blocks, it simply shows how much space was grabbed by that segment up untill now.

BLOCKS in DBA_TABLES shows number of database blocks *below the high watter mark* (in other words, number of blocks from the beginning of the segment up untill the last block that ever contained any row data). And as slimdave said, this column(s) in DBA_TABLES are populated during analyze process, because even if BLOCKS in DBA_SEGMENTS remain unchanged, the BLOCKS in DBA_TABLES may be changing all the time.

This is what I initially thought would be the case.

DBA_SEGMENTS reporting blocks in all allocated extents.
DBA_TABLES reporting the overall usage over time of the blocks in those extents.

Then, of course, I became confused as to why:

BLOCKS (which I believed would be those below HWM) and
EMPTY_BLOCKS (which I believed would be those above HWM)

did not seemingly add up to the total within blocks from all extents.

I looked at various different table segments, from a test (non-changing) environment, immediately after a full analyze.

Is EMPTY_BLOCKS referring to something completely different?

On the other issue, is there any way from the V7 Server to calculate the number of blocks on the freelist, and the avg space available within them (as contained within the V8 DBA_TABLES dictionary view)?

Quote:

---

Originally posted by AJW_ID01
Then, of course, I became confused as to why:

BLOCKS (which I believed would be those below HWM) and
EMPTY_BLOCKS (which I believed would be those above HWM)

did not seemingly add up to the total within blocks from all extents.

---

If you add BLOCKS and AMPTY_BLOCKS from DBA_TABLES and compare this to blocks in DBA_SEGMENTS, what is the difference? AFAIK, the difference between those two numbers shopuld be 1 block (which, if I'm not mistaken, is the segment header block, which is not counted neither in BLOCKS nor in EMPTY_BLOCKS).

So, yes, BLOCKS shows the number of blocks below (and including the one with) HWM, while EMPTY_BLOCKS shows the number of blocks above HWM. The sum of those two numbers should be equal to BLOCKS.DBA_SEGMENTS-1.

Ah,

The differences I was getting were somewhat less marginal than 1 block.

I'll go and take a look at everything again.

Thanks for all the comments.

:-)

- Tony.