Repairing Tables with Chained Rows

Sometimes DBAs may need to repair chained rows. Row Fragmentation (RF) or "chaining" occurs when an update increases a row length such that it cannot fit in its current data block and must migrate to a new data block that has sufficient room for the entire row. The only way to correct this problem is to delete those chained rows and re-insert them. This requires creating a temporary table with the same structure as the original to hold those rows between operations. So the actual algorithm is to copy the chained rows to the temporary table, delete them from the original table, and then re-insert them into the original table. TOAD provides the Repair Chained Rows screen, which makes both the detection and correction of chained rows simple and painless. It is located on the main menu at DBARepair Chained Rows and is shown in Figure 6.33.

Figure 6.33. TOAD Repair Chained Rows screen ”Add Tables.

When you initially enter this screen, it will be empty. You must first click the Add button to add the tables from the schemas that you want to analyze for possible chained rows. After you've done that, the screen will look like Figure 6.34. Note that you can then click the Add button again and add additional tables from other schemas. You do not have to look at tables from only one schema at a time. Look again at Figure 6.34; see how there are candidate tables from both the BERT and MOVIES_PROD schemas?

Figure 6.34. TOAD Repair Chained Rows screen ”selections.

After you've identified the candidate tables for inspection, you click the Analyze button to have TOAD discover which of the tables possess chained rows. Of course, this requires that you have access to an appropriate CHAINED_ROWS table (such as the one created by Oracle's UTLCHN1.SQL script in the RDBMS admin directory). You can specify the name of the chained row table for TOAD to use at the top of the screen. After you click the Analyze button, you will be taken to the Repair tab and shown the tables that contain chained rows and statistics about their situation. As you can see in Figure 6.35, one table was found that has 3934 chained rows.

Figure 6.35. TOAD Repair Chained Rows screen ”results.

Before clicking the Repair button to fix the chained rows, you have three options to define on this screen. First, there is a check box next to each table indicating whether or not to include it in the repair process. Second, you can optionally specify the name of the temporary table (TOAD will generate unique names for you if this field is left blank). And finally, you can specify the name of the rollback segment to use for the transactions. If you have tables with lots of chained rows, you may need to specify a rollback segment large enough to handle the operations. After you're sure of your settings, simply click the Repair button and TOAD will eliminate all your chained rows. A dialog will be displayed when the operation is complete giving you statistics and information about what just occurred.

As with any DBA restructuring task, this process could fail for any one of a hundred reasons (i.e. run out of space, rollback segment error, database shutdown, etc.) and leave you with incomplete results. Therefore as a wise precaution, you should always backup the selected tables using either a create table as select or the EXPORT utility.

Finally, note that the TOAD Options screen has an option under the DBA category for specifying the default chained row table as depicted in Figure 6.36.

Figure 6.36. TOAD DBA Options for Chained Row Table.