Rebuilding a Table


Sometimes DBAs may need to rebuild a table. For example, they might simply want to relocate a table to a different tablespace or rebuild it with different storage parameters. They might need to drop a column or reorder the columns (and their Oracle version may not support these capabilities natively). Whatever the reasons, the task of rebuilding a table is very complex. You need to maintain all the same qualities, so you must preserve definitions for constraints, indexes, views, triggers, grants and synonyms. Plus you must preserve the data as well. The SQL to accomplish such a relatively simple task is quite substantial.

TOAD's Rebuild Table screen makes short work of generating the complete script necessary for rebuilding a table. This screen, which is accessible from the main menu atToolsRebuild Table, provides a simple four-tabbed step process as shown in Figure 6.20. First, you visit the Options tab, where you select among a few simple choices. It is highly recommended that you do check the Unrecoverable/Nologging box to permit faster index rebuilds. It is also wise to check the box to keep the renamed original table as a backup of the data. And if your database server has multiple CPUs or even just exceptional I/O bandwidth, you should also set the parallel optimizer hint's number of processes.

Figure 6.20. TOAD Rebuild Table screen ”Options.

There is only one caveat with this screen; you must be connected to the schema owning the table you want to rebuild.


Second, you visit the Storage tab shown in Figure 6.21, where you select the table's storage options to apply. As was pointed out earlier, you may only need to rebuild the table in order to correct poor storage selections. For example, you may create the table with an initial and next extent size of 64K. Then after the table reaches hundreds or thousands of extents, you may want to rebuild it with 1M extents. This tab provides you with the storage options for using the original parameters or providing new ones. It also permits you to adjust the parallel properties for the table as well.

Figure 6.21. TOAD Rebuild Table screen ”Storage.

Third, you visit the Columns tab shown in Figure 6.22, where you select which existing columns to preserve and designate their order within the rebuilt table. On this screen you merely drag and drop the columns into the ordering positions you want. And if you want to drop the column, you just drag and drop it to the bottom panel for columns to exclude. There is also a very useful button to order the columns in the table the same as they are ordered in the primary key. Of course, that may not order all the columns for you, as all the columns may not be in the primary key. But it's useful nonetheless. Do note that if you exclude columns that are used by indexes, TOAD will issue a warning that those indexes will not be re-created.

Figure 6.22. TOAD Rebuild Table screen ”Columns.

Finally you visit the SQL tab shown in Figure 6.23, where you should review the generated script for accuracy. Although you can use the Execute toolbar icon to run the script, it probably makes better sense to either copy it to the clipboard or save it to a file as provided by the remaining toolbar icon options. The main reason is that there is so much to a rebuild table script, that you should review it in detail and possibly consider running it one step at a time. Far too often people choose just to run such complex scripts in their entirety and encounter problems. That's just not the best DBA practice to follow.

Figure 6.23. TOAD Rebuild Table screen ”SQL.



TOAD Handbook
TOAD Handbook (2nd Edition)
ISBN: 0321649109
EAN: 2147483647
Year: 2003
Pages: 171

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net