DBMS_REPAIR package

[rlowe]

I have increased the pctfree attribute on a table, and I understand that I must run DBMS_REPAIR.segment_fix_status in order to make this apply. however, when I try to execute, it says that DBMS_REPAIR is not a valid command, etc... Any suggestions?

R

[gpsingh]

Why do u need to run dbms_repair after chainging pctfree.
Just to confirm to u, pctfree change shall affect only new extents to be created and not old ones.

[rlowe]

I was under the impression that by running the DBMS_REPAIR.segment_fix_status, it would be "retroactive" and affect all extents.... was i misinformed?

[gpsingh]

DBMS_REPAIR.segment_fix_status is applied to objects which are created on tablespace
with segment space management as AUTO, which preallot the segment space and hence the
bitmap for this tablespace needs to be fixed for new pctfree.Note that pctused for this
AUTO is ignored if changed.

[jmodic]

I hope you are on Oracle9i, because if you are not then the whole discussion about this topic is useless, and DBMS_REPAIR.SEGMENT_FIX_STATUS is not available in pre-9i databases.

Also I hope your table resides in localy managed tablespace with *segment space management* set to AUTO - if either of this two demands is satisfied then again this discussion is in vain.

In 9i oracle introduced a new way of tracking and marking which blocks are available for additional inserts besides the traditional freelists - the feature is called "automatic space management". Basicaly it tracks how full each block is by maintaining a set of bitmaps in each segment header. So if you changed the value of PCTFREE of a table in LMT with segment space management set to AUTO it might be usefull (although I doubt it is realy necessary in general, but it couldn't hurt) to regenerate those bitmaps by running this procedure.

If your table is in a tablespace with manual segment management then running that procedure will be useless (if not prevented by the sistem in the first place).

But if your system doesn't even recognise a call of DBMS_REPAIR.SEGMENT_FIX_STATUS then I even doubt you are using 9i...

[julian]

I would add that segments that can be auto managed are heap tables, indexes, LOBS and IOTS.

Now Oracle devides all blocks into 4 categories depending on the percentage of free space: FS1 (up to 25% free space), FS2 (25-50), FS3 (50-75) and FS4 (75 up to totally free).

[jmodic]

Originally posted by jmodic
So if you changed the value of PCTFREE of a table in LMT with segment space management set to AUTO it might be usefull (although I doubt it is realy necessary in general, but it couldn't hurt) to regenerate those bitmaps by running this procedure.

I must correct myself. If you use automatic segment space management *it is necessary* to rebuild bitmaps by running DBMS_REPAIR.SEGMENT_FIX_STATUS whenever you change the PCTFREE parameter for a segment. Namely, the status of how full a block is depends on PCTFREE setting (when the data in a block reach the PCTFREE limit, the block is considered 100% full). So if you change the PCTFREE those bitmaps don't reflect true status of block "fullnes", so they must be rebuilt.

[julian]

That's true, and if somebody wonders why this is not done at COMPLE time automatically, it is because of the fact that a FTS is required, which will make the command very slow.