Best Way to Detect and Correct Table fragmentation with Toad for Oracle?

Toad for Oracle
1

Best Way to Detect and Correct Table fragmentation with Toad for Oracle?

2

What do you mean by "Table fragmentation"? Spaces in blocks due to deletes and inserts? Are you sure it's really a problem? If there is no observable problem my advice is to just leave it alone.

If you really want to proceed, one technique is a simple script to move the data out and back.

Create table Blah as select * from YOUR_TABLE;
Truncate table YOUR_TABLE;
Insert into YOUR_TABLE Select * from BLAH;
Commit;

This won't work in 100% of cases. Triggers and Foreign Key constraints might complicate matters.

Another technique is main menu: Database -> Optimize -> DBMS Redefinition wizard. You don't actually have to redefine anything, just let it rebuild your table as-is.

-John

3

And if you want to detect fragmentation of objects at a tablespace level, the TableSpace Map can give you some good info...
Database->Diagnose->Tablespace Map from the pull-down menus...

image
image1574×705 66.6 KB

4

Gary is right, but that is also something you shouldn't try to correct unless there is an actual problem. It's very easy to do more harm than good when trying to de-fragment a table/tablespace. Most of the time it isn't really a problem (other than perhaps a little wasted space)

5

Thanks for your replies, what I mean by fragmentation is from spaces in blocks due to deletes and inserts. We are after possible better performance, I have read many mixed reviews on the performance improvements by defragmenting, we want to test if it helps on some processes.

6

There is no reason to bother about this unless your data file space is constrained and running out. I recommend the below article for an explanation. Its author, Connor MadDonald, is an Oracle employee and among the best in the world at the internals of Oracle databases. There are no "performance" reasons for "defragmenting" tables and indexes. The only reasons he gives for doing so is to reclaim space when available data file space is severely limited and running out.

Is Fragmentation Still a Dirty Word

Cheers,
Russ

1 Like
7

Thanks Russ. I agree 100%.