Re: Detecting xxx fragmentation/corruption?

Peter,

Tablespace fragmentation - You might take a look at LMT with uniform extents. My understanding is that this prevents tablespace fragmentation. This might be easier than spending a lot of time yourself, unless you have more free time than most DBAs do.

Stored procedures are stored in the system tablespace. They aren't usually considered a "physical" object, usually their size is much smaller than tables or indexes. You may have noticed that you have little control over their storage.

For indexes, you could probably get a list of the blocks where the index resides and then look to see if there are gaps. There might even be a way to walk down the index entries in the same way as your procedure walks the rows of a table.

If you are fascinated by fragmentation, you might consider looking into TABLE fragmentation.

Corruption - you realize there is a difference between fragmentation and corruption? Just asking since you touched on both topics in the same posting.

Usually table corruption is considered the most critical. Indexes you can rebuild. You should have a source copy of a stored procedure somewhere, but if you get table corruption, you have a problem. Fortunately with high-quality modern software like Oracle and quality storage devices with redundancy like RAID, this is a pretty rare problem. Take a look at DB_BLOCK_CHECKSUM and DB_BLOCK_CHECKING.

The best way I've found to check a table for corruption is to export it. This reads the entire table and if export can't read a block, you get an error. If it is a small table I keep the export as a "just in case". Large tables can be exported to /dev/null.

And while you're spending spare time on this topic, look up the instructions for how to deal with a corrupted block and study them.

Dennis Williams

--
http://www.freelists.org/webpage/oracle-l