Re: Detecting xxx fragmentation/corruption?
- From: "Dennis Williams" <oracledba.williams@xxxxxxxxx>
- To: mwf@xxxxxxxx
- Date: Thu, 8 May 2008 19:46:51 -0500
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
Other related posts:
